oracle 数据库、用户默认数据表空间分配

SQL> select property_name,property_value from database_properties;                                                                                          
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DICT.BASE                      2
DEFAULT_TEMP_TABLESPACE        TEMPTS1
DEFAULT_PERMANENT_TABLESPACE   SYSTEM
DBTIMEZONE                     +08:00
DEFAULT_TBS_TYPE               SMALLFILE
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               ZHS16GBK
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXF
                               F AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXF
                               F AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              10.2.0.4.0
GLOBAL_DB_NAME                 ORCL
EXPORT_VIEWS_VERSION           8
WORKLOAD_CAPTURE_MODE
28 rows selected.
SQL> select username,default_tablespace from dba_users where username='ZWZ';                                                                                
USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
ZWZ                            SYSTEM
SQL> select name from v$tablespace;                                                                                                                         
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMPTS1
CMS
ICME
IPTV
NOVARTIS
SANJI
SIEBELDB
TOPBOX
XMSB
XNH
ZYY
USERS
TEMP2
16 rows selected.
SQL> alter user zwz default tablespace icme;                                                                                                                
User altered.
SQL> select username,default_tablespace from dba_users where username='ZWZ';                                                                                
USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
ZWZ                            ICME
SQL> alter database default tablespace users;                                                                                                               
Database altered.
SQL> select property_name,property_value from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';                                       
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DEFAULT_PERMANENT_TABLESPACE   USERS
SQL> select username,default_tablespace from dba_users where username='ZWZ';                                                                                
USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
ZWZ                            ICME
SQL> create user anbob identified by anbob;                                                                                                                 
User created.
SQL> select username,default_tablespace from dba_users where username='ANBOB';                                                                              
USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
ANBOB                          USERS
SQL> drop user anbob ;                                                                                                                                      
User dropped.
SQL> create tablespace test datafile '/oradata/orcl/test.dbf' size 10m;                                                                                     
 
Tablespace created.
 
SQL> alter database default tablespace test;                                                                                                                
 
Database altered.
 
SQL> select property_name,property_value from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';                                       
 
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DEFAULT_PERMANENT_TABLESPACE   TEST
 
SQL> drop tablespace test;                                                                                                                                  
drop tablespace test
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace
 
 
SQL> alter database default tablespace users;                                                                                                               
 
Database altered.
SQL> drop tablespace test including contents and  datafiles;                                                                                                
 
Tablespace dropped.

DEFAULT_PERMANENT_TABLESPACE 是数据库级默认表空间，如果建用户不指定明空间默认就会用这个参数批定的，当然创建用户时也可以让他自立门户分配一个独的表间default tablespace ，比如数据与索引分别再两个不同盘区的两个表空间来做IO负载均衡,比如我今天发现默认表空间是SYSTEM这种习惯就不太好, 数据库默认表空间 是不允许删除的，现在考考你，用户的默认表空间能不能删呢？删了以后用户默认表空间又是多少呢？