在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 sys/abcdefg@oracle10g 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 paul/paul@oracle10g
Connected.
SQL> create table test as select rownum id from dual;

SQL>