前两天在测试的时候删除表空间，发现报错ORA-12919。

错误信息倒是不难理解，删除的表空间是数据库的默认表空间：

SQL> ALTER DATABASE DEFAULT TABLESPACE TEST;

数据库已更改。

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

解决问题也很简单，指定其他的表空间为数据库默认表空间就可以了。不过一时间记不清从哪里可以看到，一个表空间是否是数据库的默认表空间。

检查V$TABLESPACE、DBA_TABLESPACES和V$DATABASE视图，都没有看到相关的字段来表示默认表空间的设置：

SQL> DESC V$TABLESPACE
名称 是否为空? 类型
----------------------------------------------------------------- -------- -------------
TS# NUMBER
NAME VARCHAR2(30)
INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)
BIGFILE VARCHAR2(3)
FLASHBACK_ON VARCHAR2(3)
ENCRYPT_IN_BACKUP VARCHAR2(3)

SQL> DESC DBA_TABLESPACES
名称 是否为空? 类型
----------------------------------------------------------------- -------- -------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)

SQL> DESC V$DATABASE
名称 是否为空? 类型
----------------------------------------------------------------- -------- --------------
DBID NUMBER
NAME VARCHAR2(9)
CREATED DATE
RESETLOGS_CHANGE# NUMBER
RESETLOGS_TIME DATE
PRIOR_RESETLOGS_CHANGE# NUMBER
PRIOR_RESETLOGS_TIME DATE
LOG_MODE VARCHAR2(12)
CHECKPOINT_CHANGE# NUMBER
ARCHIVE_CHANGE# NUMBER
CONTROLFILE_TYPE VARCHAR2(7)
CONTROLFILE_CREATED DATE
CONTROLFILE_SEQUENCE# NUMBER
CONTROLFILE_CHANGE# NUMBER
CONTROLFILE_TIME DATE
OPEN_RESETLOGS VARCHAR2(11)
VERSION_TIME DATE
OPEN_MODE VARCHAR2(10)
PROTECTION_MODE VARCHAR2(20)
PROTECTION_LEVEL VARCHAR2(20)
REMOTE_ARCHIVE VARCHAR2(8)
ACTIVATION# NUMBER
SWITCHOVER# NUMBER
DATABASE_ROLE VARCHAR2(16)
ARCHIVELOG_CHANGE# NUMBER
ARCHIVELOG_COMPRESSION VARCHAR2(8)
SWITCHOVER_STATUS VARCHAR2(20)
DATAGUARD_BROKER VARCHAR2(8)
GUARD_STATUS VARCHAR2(7)
SUPPLEMENTAL_LOG_DATA_MIN VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(3)
FORCE_LOGGING VARCHAR2(3)
PLATFORM_ID NUMBER
PLATFORM_NAME VARCHAR2(101)
RECOVERY_TARGET_INCARNATION# NUMBER
LAST_OPEN_INCARNATION# NUMBER
CURRENT_SCN NUMBER
FLASHBACK_ON VARCHAR2(18)
SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(3)
DB_UNIQUE_NAME VARCHAR2(30)
STANDBY_BECAME_PRIMARY_SCN NUMBER
FS_FAILOVER_STATUS VARCHAR2(21)
FS_FAILOVER_CURRENT_TARGET VARCHAR2(30)
FS_FAILOVER_THRESHOLD NUMBER
FS_FAILOVER_OBSERVER_PRESENT VARCHAR2(7)
FS_FAILOVER_OBSERVER_HOST VARCHAR2(512)

普通的视图中查询不到，莫非在SYS用户下的系统CLUSTER表中，不过SYS.TS$中也找不到对于的字段。

没有办法，只好对DROP TABLESPACE语句进行TRACE，看看Oracle在读取了哪张表来获取默认表空间信息：

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

会话已更改。

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

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

会话已更改。

检查TRACE文件：

*** 2008-11-16 15:43:46.734
*** ACTION NAME:() 2008-11-16 15:43:46.703
*** MODULE NAME:(SQL*Plus) 2008-11-16 15:43:46.703
*** SERVICE NAME:(ytk102.ytk_thinkpad) 2008-11-16 15:43:46.703
*** SESSION ID:(154.310) 2008-11-16 15:43:46.703
=====================
PARSING IN CURSOR #4 len=69 dep=0 uid=72 oct=42 lid=72 tim=109220815278 hv=1576194328 ad='25b4a240'
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'
END OF STMT
EXEC #4:c=0,e=75,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=109220815273
WAIT #4: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=109220854249
WAIT #4: nam='SQL*Net message from client' ela= 24841 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=109220879221
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=109220879448
WAIT #0: nam='SQL*Net message from client' ela= 3438493 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=109224318074
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #3 len=39 dep=0 uid=72 oct=41 lid=72 tim=109224318718 hv=0 ad='7a5fc04'
DROP TABLESPACE TEST INCLUDING CONTENTS
END OF STMT
PARSE #3:c=0,e=372,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=109224318714
BINDS #3:
=====================
PARSING IN CURSOR #9 len=652 dep=1 uid=38 oct=47 lid=38 tim=109224319507 hv=3055982128 ad='334ec20c'
BEGIN
BEGIN
IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner)) THEN
xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name);
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner, xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN
xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name);
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
END;
END OF STMT
PARSE #9:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=109224319503
BINDS #9:
=====================
.
.
.
=====================
PARSING IN CURSOR #9 len=62 dep=1 uid=0 oct=3 lid=0 tim=109224450413 hv=4201259531 ad='336c1e30'
select value$ from props$ where name='DEFAULT_TEMP_TABLESPACE'
END OF STMT
PARSE #9:c=0,e=1303,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=109224450408
BINDS #9:
EXEC #9:c=0,e=40,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=109224450517
FETCH #9:c=0,e=50,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=109224450583
STAT #9 id=1 cnt=1 pid=0 pos=1 obj=96 op='TABLE ACCESS FULL PROPS$ (cr=3 pr=0 pw=0 time=46 us)'
=====================
PARSING IN CURSOR #4 len=33 dep=1 uid=0 oct=3 lid=0 tim=109224450839 hv=3824812794 ad='336c0f78'
select ts# from ts$ where name=:1
END OF STMT
PARSE #4:c=0,e=130,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=109224450835
BINDS #4:
kkscoacd
Bind#0
oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=08900824 bln=32 avl=04 flg=05
value="TEMP"
EXEC #4:c=0,e=431,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=109224451322
FETCH #4:c=0,e=37,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=109224451376
STAT #4 id=1 cnt=1 pid=0 pos=1 obj=16 op='TABLE ACCESS BY INDEX ROWID TS$ (cr=2 pr=0 pw=0 time=40 us)'
STAT #4 id=2 cnt=1 pid=1 pos=1 obj=43 op='INDEX UNIQUE SCAN I_TS1 (cr=1 pr=0 pw=0 time=17 us)'
=====================
PARSING IN CURSOR #8 len=67 dep=1 uid=0 oct=3 lid=0 tim=109224451829 hv=3983473295 ad='25b557a4'
select value$ from props$ where name='DEFAULT_PERMANENT_TABLESPACE'
END OF STMT
PARSE #8:c=0,e=370,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=109224451825
BINDS #8:
EXEC #8:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=109224451906
FETCH #8:c=0,e=36,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=109224451958
STAT #8 id=1 cnt=1 pid=0 pos=1 obj=96 op='TABLE ACCESS FULL PROPS$ (cr=3 pr=0 pw=0 time=31 us)'
=====================
PARSING IN CURSOR #9 len=33 dep=1 uid=0 oct=3 lid=0 tim=109224452053 hv=3824812794 ad='336c0f78'
select ts# from ts$ where name=:1
END OF STMT
PARSE #9:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=109224452049
BINDS #9:
kkscoacd
Bind#0
oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=08900824 bln=32 avl=04 flg=05
value="TEST"
EXEC #9:c=0,e=80,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=109224452182
FETCH #9:c=0,e=29,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=109224452226
STAT #9 id=1 cnt=1 pid=0 pos=1 obj=16 op='TABLE ACCESS BY INDEX ROWID TS$ (cr=2 pr=0 pw=0 time=33 us)'
STAT #9 id=2 cnt=1 pid=1 pos=1 obj=43 op='INDEX UNIQUE SCAN I_TS1 (cr=1 pr=0 pw=0 time=12 us)'
EXEC #3:c=93750,e=133254,p=0,cr=124,cu=0,mis=0,r=0,dep=0,og=1,tim=109224452526
ERROR #3:err=12919 tim=10921757
WAIT #3: nam='SQL*Net break/reset to client' ela= 5 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=109224453078
WAIT #3: nam='SQL*Net break/reset to client' ela= 140 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=109224453241
WAIT #3: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=109224453268
WAIT #3: nam='SQL*Net message from client' ela= 66723 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=109224520049
WAIT #0: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=109224520135
*** 2008-11-16 15:43:57.125
WAIT #0: nam='SQL*Net message from client' ela= 6728543 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=109231248708
=====================
PARSING IN CURSOR #4 len=55 dep=0 uid=72 oct=42 lid=72 tim=109231249086 hv=524428051 ad='25b494ac'
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'
END OF STMT
PARSE #4:c=0,e=286,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=109231249082
BINDS #4:
EXEC #4:c=0,e=70,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=109231249210

从上面的TRACE文件中可以清晰的看到，Oracle是读取props$表获取的记录，而PROPS$表记录的正式数据库中一些配置信息：

SQL> SELECT * FROM SYS.PROPS$;

NAME VALUE$ COMMENT$
------------------------------ ------------------------------ ----------------------------------
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE TEST Name of default permanent tablespace
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
NLS_LANGUAGE AMERICAN Language
NLS_TERRITORY AMERICA Territory
NLS_CURRENCY $ Local currency
NLS_ISO_CURRENCY AMERICA ISO currency
NLS_NUMERIC_CHARACTERS ., Numeric characters
NLS_CHARACTERSET ZHS16GBK Character set
NLS_CALENDAR GREGORIAN Calendar system
NLS_DATE_FORMAT DD-MON-RR Date format
NLS_DATE_LANGUAGE AMERICAN Date language
NLS_SORT BINARY Linguistic definition
NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format
NLS_DUAL_CURRENCY $ Dual currency symbol
NLS_COMP BINARY NLS comparison
NLS_LENGTH_SEMANTICS BYTE NLS length semantics
NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
NLS_RDBMS_VERSION 10.2.0.1.0 RDBMS version for NLS parameters
GLOBAL_DB_NAME YTK102.YTK_THINKPAD Global database name
EXPORT_VIEWS_VERSION 8 Export views revision #
DBTIMEZONE 00:00 DB time zone

已选择27行。

database_properties

嗯，DATABASE_PROPERTIES视图访问的就是SYS.PROPS$。