oracle 10g R2 建立使用者時預設表空間改變

在10g以前,如果我們建立使用者時 要是沒指定預設表空間,系統就會把預設表空間指向system表空間

這樣在這個使用者下建立的所有表都放在system表空間,會影響到系統表空間

 

而10g在這方面做了改進,要是建立使用者沒指定表空間 ,預設放在user表空間下

以下是測試驗證過程:

1:oracle 9i

C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Wed May 7 15:16:54 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> create user paul identified by paul;

User created.

SQL> conn /as sysdba
Connected.

SQL> grant connect to paul;

Grant succeeded.

SQL> grant resource to paul;

Grant succeeded.

SQL> conn paul/paul
Connected.
SQL> create table test as select rownum id from dual;

Table created.

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEST                           SYSTEM

SQL> conn /as sysdba
Connected.

SQL> select username,default_tablespace from dba_users where username='PAUL';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
PAUL                           SYSTEM

SQL>

 

2:ORACLE 10g R2

SQL> conn as sysdba
Connected.
SQL> create user paul identified by paul;

User created.

SQL> grant connect to paul;

Grant succeeded.

SQL> grant resource to paul;

Grant succeeded.

SQL> conn
Connected.
SQL> create table test as select rownum id from dual;

Table created.

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEST                           USERS

SQL> conn as sysdba
Connected.
SQL> select username,default_tablespace from dba_users where username='PAUL';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
PAUL                           USERS

SQL>