Oracle 默认表空间（转）

在oracle9i中，如果未用 DEFAULT TABLESPACE 子句创建的所有用户将以SYSTEM表空间作为它们的默认表空间。同时也不能为数据库指定一个默认的表空间。 
在Oracle 10g中定义了数据库级别的默认表空间USERS，在创建用户时没有定义默认表空间，就会把数据库级别的默认表空间当作自己的默认表空间。 
在10g以前，是把系统表空间（SYSTEMS）当作默认表空间。这样很不合理。

可以使用如下命令查看默认表空间：

SQL> SELECT PROPERTY_VALUE FROM database_properties WHERE PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE';
 
PROPERTY_VALUE
--------------------------------------------------------------------------------
USERS

 可以使用如下命令修改默认表空间： 

ALTER DATABASE DEFAULT TABLESPACE users;

 

这里有几个注意事项： 
1. 如果我们在创建用户时指定了默认表空间，那么在修改默认表空间后，之前用户的默认表空间也会发生改变。 
2. 如果我们在创建用户时没有指定用户表空间，那么默认也会使用DB的默认表空间，这时候如果我们修改了DB的默认表空间，用户的表空间也会发生改变。 
3. 如果我们在创建用户指定用户的表空间是其他的表空间，那么我们修改DB的默认表空间不会影响用户的表空间。 
4. DB的默认表空间不能删除，除非将默认表空间指向其他表空间之后才可以删除。 
5. 如果用户的默认表空间指向其他的表空间，当这个表空间被drop 之后，用户的默认表空间会自动指向DB的默认表空间。

 

示例

1、查看DB 版本： 

SQL> SELECT * FROM v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

 

2、创建用户，不指定默认表空间 

SQL> create user dave1 identified bydave1;
 
User created.
 
SQL> select default_tablespace from dba_users where username ='DAVE1';
 
DEFAULT_TABLESPACE
 
------------------------------
 
USERS

 

--默认表空间为users。 

3、创建用户指定默认表空间为DB的默认表空间 

SQL> CREATE USER tom2 IDENTIFIED BY tom2 DEFAULT TABLESPACE users;
 
用户已创建。
 
SQL> SELECT DEFAULT_TABLESPACE FROM dba_users WHERE username='TOM2';
 
DEFAULT_TABLESPACE
------------------------------
USERS

 

4、 创建用户指定的默认表空间为其他表空间

--创建表空间：

SQL> CREATE TABLESPACE tom3 DATAFILE 'F:\tablespace\tom3.dbf' SIZE 10M;
 
表空间已创建。

 --创建用户： 

 

SQL> CREATE USER tom3 IDENTIFIED BY tom3 DEFAULT TABLESPACE tom3;
 
用户已创建。
 
SQL> SELECT DEFAULT_TABLESPACE FROM dba_users WHERE username='TOM3';
 
DEFAULT_TABLESPACE
------------------------------
TOM3

 

5、修改DB的默认表空间 

SQL> ALTER DATABASE DEFAULT TABLESPACE tom3;
 
数据库已更改。
 
SQL> SELECT PROPERTY_VALUE FROM database_properties WHERE PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE';
 
PROPERTY_VALUE
--------------------------------------------------------------------------------
TOM3

 

6、查看之前创建用户的表空间 

SQL> SELECT DEFAULT_TABLESPACE FROM dba_users WHERE username='TOM';
 
DEFAULT_TABLESPACE
------------------------------
TOM3
 
SQL> SELECT DEFAULT_TABLESPACE FROM dba_users WHERE username='TOM2';
 
DEFAULT_TABLESPACE
------------------------------
TOM3
 
SQL> SELECT DEFAULT_TABLESPACE FROM dba_users WHERE username='TOM3';
 
DEFAULT_TABLESPACE
------------------------------
TOM3

 

--注意这里tom，tom2用户的默认表空间都变成了tom3. 

7、 DB的默认表空间不能删除

--现在tom3是我们的默认表空间 

SQL> SELECT PROPERTY_VALUE FROM database_properties WHERE PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE';
 
PROPERTY_VALUE
--------------------------------------------------------------------------------
TOM3

 --drop dave3 看看： 

 

SQL> DROP TABLESPACE tom3 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE tom3 INCLUDING CONTENTS AND DATAFILES
*
第 1 行出现错误:
ORA-12919: 不能删除默认永久表空间

 我们把默认表空间改成USERS，再测试： 

 

SQL> ALTER DATABASE DEFAULT TABLESPACE USERS;
 
数据库已更改。
 
SQL> SELECT PROPERTY_VALUE FROM database_properties WHERE PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE';
 
PROPERTY_VALUE
--------------------------------------------------------------------------------
USERS

 

SQL> DROP TABLESPACE tom3 INCLUDING CONTENTS AND DATAFILES;
 
表空间已删除。

 tom3表空间成功drop。 注意，我们的tom3用户的表空间之间是指向tom3的，现在我们把tom3表空间drop了，我们看一下tom3现在默认表空间： 

 

SQL> SELECT DEFAULT_TABLESPACE FROM dba_users WHERE username='TOM3';
 
DEFAULT_TABLESPACE
------------------------------
USERS

 --这里自动变成了我们的DB 默认的表空间

 

引用：http://my.oschina.net/xiaomaoandhong/blog/75933