ANBOB™

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这种习惯就不太好, 数据库默认表空间 是不允许删除的，现在考考你，用户的默认表空间能不能删呢？删了以后用户默认表空间又是多少呢？

打赏

微信扫一扫，打赏作者吧～