在测试环境玩什么东西忘记了，反正是忽然发现有个没用的表空间“UNDOTBS1”删除不掉，以前写过一篇，如何查找某个对象的定义（V$_X$_DBA），
这里重温一下数据字典和动态性能视图：
UNDOTBS1在v$tablespace中可见，但是不能drop，在dba_tablespaces中不可见，说明数据字典和动态性能视图不匹配了（手工删除了基表导致的，忘记是删除了ts$还是file$内容了）：
不得不说Oracle 11.2.0.3以后的版本，对于数据库的一致性校验进行了很人性化的改动，以前这种情况是crash的，现在还open着，带病工作，O(∩_∩)O哈哈~
类似的带病工作的情况，还涉及到很多数据字典的不一致情况，比如以前的i_dependency1, i_dependency2等等。
从这个研究，也证实了如下结论:
V$TABLESPACE的信息是来源于GV$TABLESPACE，GV$TABLESPACE来源于基表 X$KCCTS
而DBA_TABLESPACES是来源于 SYS.TS$ TS 和 SYS.X$KCFISTSA。也就是说，V$TABLESPACE的信息来源于控制文件，而DBA_TABLESPACES的信息是来源于其他基表，手工删除基表信息时，其信息不和控制文件信息同步。
下面有具体看看：
[oracle@lunar ~]$ ss SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 29 10:36:55 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning and Real Application Testing options SYS@bb>select name from v$tablespace; NAME ------------------------------------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS DBTK LUNAR UNDOTBS2 8 rows selected. SYS@bb>drop tablespace undotbs1; drop tablespace undotbs1 * ERROR at line 1: ORA-00959: tablespace 'UNDOTBS1' does not exist SYS@bb> SYS@bb>select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------------------------------------ SYSTEM SYSAUX TEMP USERS DBTK LUNAR UNDOTBS2 7 rows selected. SYS@bb>
看下创建动态性能视图的语句：
[oracle@lunar ~]$ cat /u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms/admin/catalog.sql |grep -v Rem|grep fixed
@@cdfixed.sql
[oracle@lunar ~]$
[oracle@lunar ~]$ cat /u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms/admin/cdfixed.sql |grep -v Rem|grep fixed
create or replace view v_$fixed_table as select * from v$fixed_table; --- v_$fixed_table 实际上是 v$fixed_table 的view
create or replace public synonym v$fixed_table for v_$fixed_table; --- v$fixed_table 是 v_$fixed_table 的同义词
grant select on v_$fixed_table to select_catalog_role;
create or replace view v_$fixed_view_definition as --- v_$fixed_view_definition 实际上是 v$fixed_view_definitiond view
select * from v$fixed_view_definition;
create or replace public synonym v$fixed_view_definition --- v$fixed_view_definition 是 v_$fixed_view_definition 的同义词
for v_$fixed_view_definition;
grant select on v_$fixed_view_definition to select_catalog_role;
create or replace view v_$indexed_fixed_column as --- v_$indexed_fixed_column 实际上是 v$indexed_fixed_column 的view
select * from v$indexed_fixed_column;
create or replace public synonym v$indexed_fixed_column --- v$indexed_fixed_column 是 v_$indexed_fixed_column 的同义词
for v_$indexed_fixed_column;
grant select on v_$indexed_fixed_column to select_catalog_role;
-- Add SQL Performance Analyzer (SPA) fixed views
remark Create synonyms for the global fixed views -- global fixed view实际上是从Oracle 8引入的，因为这个版本，Oracle对OPS进行了增强， --- 添加GV$视图就是为了方便全局管理，V$跟GV$的区别就在这里“where inst_id = USERENV('Instance')”
--- 也就是V$是本实例的视图，而GV$是全局视图
create or replace view gv_$fixed_table as select * from gv$fixed_table; --- gv_$fixed_table 实际上是 gv$fixed_table 的view
create or replace public synonym gv$fixed_table for gv_$fixed_table; --- gv$fixed_table 是 gv_$fixed_table 的同义词
grant select on gv_$fixed_table to select_catalog_role;
create or replace view gv_$fixed_view_definition as --- gv_$fixed_view_definition 实际上是 gv$fixed_view_definition 的view
select * from gv$fixed_view_definition;
create or replace public synonym gv$fixed_view_definition
for gv_$fixed_view_definition;
grant select on gv_$fixed_view_definition to select_catalog_role;
create or replace view gv_$indexed_fixed_column as --- gv_$indexed_fixed_column 实际上是 gv$indexed_fixed_column 的view
select * from gv$indexed_fixed_column;
create or replace public synonym gv$indexed_fixed_column
for gv_$indexed_fixed_column;
grant select on gv_$indexed_fixed_column to select_catalog_role;
[oracle@lunar ~]$
通过上面建库脚本也可以清晰的看到，得到授权的普通用户仍然只能访问V$开头的视图，而不能直接访问V_$开头的视图，因为实际上V$视图是V_$视图的公有同义词（PUBLIC SYNONYM）要想访问V_$必须带上SYS.V_$，例如
set heading off echo off long 100000 pages 10000
select * from v$fixed_view_definition where view_name='V$TABLESPACE';
SYS@bb>select * from v$fixed_view_definition where view_name='V$TABLESPACE';
V$TABLESPACE
select TS# , NAME, INCLUDED_IN_DATABASE_BACKUP, BIGFILE, FLASHBACK_ON, ENCRYPT_IN_BACKUP from GV$TABLESPACE where inst_id = USERENV('Instance')
SYS@bb>select * from v$fixed_view_definition where view_name='GV$TABLESPACE';
GV$TABLESPACE
select inst_id,tstsn,tsnam, decode(bitand(tsflg, 1+2), 1, 'NO', 2,'NO','YES'), decode(bitand(tsflg, 4), 4,'YES','NO'), decode(bitand(tsflg,
8), 8,'NO','YES'), decode(bitand(tsflg, 16+32), 16, 'ON', 32, 'OFF', to_char(null)) from x$kccts where tstsn != -1
SYS@bb>
而查看普通的DBA_ ALL_ USER_ 等视图，可以查看数据字典 dba_views（这个视图从8i开始引入的）
例如：
set heading off echo off long 1000000000 pages 10000
select text from dba_views where view_name ='DBA_TABLESPACES';
SYS@bb>select text from dba_views where view_name ='DBA_TABLESPACES';
select ts.name, ts.blocksize, ts.blocksize * ts.dflinit,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
ts.blocksize * ts.dflincr),
ts.dflminext,
decode(ts.contents$, 1, to_number(NULL), ts.dflmaxext),
decode(bitand(ts.flags, 4096), 4096, ts.affstrength, NULL),
decode(bitand(ts.flags, 3), 1, to_number(NULL), ts.dflextpct),
ts.blocksize * ts.dflminlen,
decode(ts.online$, 1, 'ONLINE', 2, 'OFFLINE',
4, 'READ ONLY', 'UNDEFINED'),
decode(ts.contents$, 0, (decode(bitand(ts.flags, 16), 16, 'UNDO',
'PERMANENT')), 1, 'TEMPORARY'),
decode(bitand(ts.dflogging, 1), 0, 'NOLOGGING', 1, 'LOGGING'),
decode(bitand(ts.dflogging, 2), 0, 'NO', 2, 'YES'),
decode(ts.bitmapped, 0, 'DICTIONARY', 'LOCAL'),
decode(bitand(ts.flags, 3), 0, 'USER', 1, 'SYSTEM', 2, 'UNIFORM',
'UNDEFINED'),
decode(ts.plugged, 0, 'NO', 'YES'),
decode(bitand(ts.flags,32), 32,'AUTO', 'MANUAL'),
decode(bitand(ts.flags,64), 64,'ENABLED', 'DISABLED'),
decode(bitand(ts.flags,16), 16, (decode(bitand(ts.flags, 512), 512,
'GUARANTEE', 'NOGUARANTEE')), 'NOT APPLY'),
decode(bitand(ts.flags,256), 256, 'YES', 'NO'),
decode(tsattr.storattr, 1, 'STORAGE', 'HOST'),
decode(bitand(ts.flags,16384), 16384, 'YES', 'NO'),
decode(bitand(ts.flags,64), 0, null,
(case when bitand(ts.flags, 65536) = 65536
then 'OLTP'
when bitand(ts.flags, (131072+262144)) = 131072
then 'QUERY LOW'
when bitand(ts.flags, (131072+262144)) = 262144
then 'QUERY HIGH'
when bitand(ts.flags, (131072+262144)) = (131072+262144)
then 'ARCHIVE LOW'
when bitand(ts.flags, 524288) = 524288
then 'ARCHIVE HIGH'
else 'BASIC' end))
from sys.ts$ ts, sys.x$kcfistsa tsattr
where ts.online$ != 3
and bitand(flags,2048) != 2048
and ts.ts# = tsattr.tsid
SYS@bb>
X$ 是 Oracle 数据库 的核心部分，这些表用于跟踪内部数据库信息，维护数据库的正常运行。 X$ 表是加密的（除了MOS和直接看源代码以外，我不知道还有什么方法可以查看X$视图）
Oracle 通过 X$和一些基表（TS$, OBJ$, SEG$等）建立起其他大量视图，提供用户查询和管理数据库。
在9i以前
另外，还可以通过X$KQFTA来查看X$表的相关信息：
X$KQFTA
[K]ernel [Q]uery [F]ixed Tables/views Management
[TA]bles
Column Type Description
-------- ---- --------
ADDR RAW(4) address of this row/entry in the array or SGA
INDX NUMBER index number of this row in the fixed table array
INST_ID NUMBER 8.x oracle instance number
KQFTAOBJ NUMBER object ID number - hardcoded in kernel
KQFTAVER NUMBER version number
KQFTANAM VARCHAR2(30) table name
KQFTATYP NUMBER type of table
KQFTVTTB 1 declared using KQFTABL
KQFTVTTP 2 declared using KQFTABP
KQFTVTVI 3 declared using KQFVIEW
KQFTVTCB 4 declared using KQFTABC
KQFTVTIC 5 declared using KQFTABIC
KQFTVTTS 6 declared using KQFTABS
KQFTAFLG NUMBER flag
KQFTVFONE 1 the INDX column of the table is one based
KQFTARSZ NUMBER array element size
KQFTACOC NUMBER column count
SYS@bb>select KQFTAOBJ, KQFTANAM, KQFTATYP from X$KQFTA where KQFTANAM='X$KCFISTSA';
KQFTAOBJ KQFTANAM KQFTATYP
---------- ------------------------------------------------------------ ----------
4294952982 X$KCFISTSA 5
SYS@bb>
类似，就是11.2中新引入的X$表：
[oracle@lunar bin]$ strings oracle|grep KCFISTSA
X$KCFISTSA
[oracle@lunar bin]$
在10g和10g以前是没有的：
[oracle@lunar ~]$ . ora102.env [oracle@lunar ~]$ cd $ORACLE_HOME [oracle@lunar db_1]$ cd bin [oracle@lunar bin]$ strings oracle|grep KCFISTSA [oracle@lunar bin]$
实际上，在10g以前的数据库中，
set heading off echo off long 1000000000 pages 10000
select text from dba_views where view_name =’X$KCFISTSA’;
基表数据字典是根据sql.bsq创建的：
[oracle@lunar ~]$ cat /u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms/admin/sql.bsq |grep -v rem|grep -v Rem dcore.bsq dsqlddl.bsq dmanage.bsq dplsql.bsq dtxnspc.bsq dfmap.bsq denv.bsq drac.bsq dsec.bsq doptim.bsq dobj.bsq djava.bsq dpart.bsq drep.bsq daw.bsq dsummgt.bsq dtools.bsq dexttab.bsq ddm.bsq dlmnr.bsq ddst.bsq [oracle@lunar ~]$
回到我们的正题，通过上面查询可以看到，V$TABLESPACE的信息是来源于GV$TABLESPACE，GV$TABLESPACE来源于基表 X$KCCTS
而DBA_TABLESPACES是来源于 SYS.TS$ TS 和 SYS.X$KCFISTSA。也就是说，V$TABLESPACE的信息来源于控制文件，而DBA_TABLESPACES的信息是来源于其他基表，手工删除基表信息时，其信息不和控制文件信息同步，X$KCCTS定义如下
[K]ernel [C]ache [C]ontrolfile management [T]able[S]pace record
Column Type Description
-------- ---- -----------
ADDR RAW(4) address of this row/entry in the SGA
INDX NUMBER index number of this row in the fixed table array
INST_ID NUMBER oracle instance number
TSRNO NUMBER rec#
TSTSN NUMBER TableSpace Number (same as ts# in data dict.) =================关联TS$.TS#
TSNAM VARCHAR2(30) tablespace NAMe =================表空间名称
TSNRB NUMBER 8.0 Number of Rollback Segments in the tablespace
TSFLG NUMBER 8.1 (rollback segments) Flags defining tablespace
KCCTSFTM 0x0001 Tablespace is temporary - Has only tempfiles
TSDFP NUMBER tablespace's 1st DataFile Pointer (DataFile rec#)
TSPSS VARCHAR2(16) ts Point-in-time recovery mode Start Scn
TSPST VARCHAR2(20) ts Point-in-time recov mode Start Timestamp
TSPCS VARCHAR2(16) last ts Point-in-time recovery Completion Scn
TSPCT VARCHAR2(20) last ts Point-in-time recov Completion Timestp
Bug 13832069 : QUERY USING DBA_TABLESPACES (X$KCFISTSA) CAN LEAK CURSORS AND GIVE WRONG RESULTS
而X$KCFISTSA是定义在kcfis2.h中的，其结构定义如下：
SYS@bb>desc sys.X$KCFISTSA Name Null? Type ---------------------------------------------------------------------------------- -------- ------------------------------------------------------- ADDR RAW(8) INDX NUMBER INST_ID NUMBER TSID NUMBER STORATTR NUMBER SYS@bb> ADDR RAW Address of buffer used to store row. INDX NUMBER Index number (used to uniquely differentiate rows). INST_ID NUMBER Instance number. TSID NUMBER Based on struct element tsid_kcfistsa. tsid_kcfistsa was found in file kcfis2.h, line 377 and is described as : Tablespace ID STORATTR NUMBER Based on struct element storattr_kcfistsa. storattr_kcfistsa was found in file kcfis2.h, line 378 and is described as : Tablespace storage attributes
具体请参考大师的说明：
http://www.juliandyke.com/Internals/FixedTables/X_KCFISTSA.html
再来看看ts$的定义：
SYS@bb>desc sys.TS$; Name Null? Type ---------------------------------------------------------------------------------- -------- ------------------------------------------------------- TS# NOT NULL NUMBER NAME NOT NULL VARCHAR2(30) OWNER# NOT NULL NUMBER ONLINE$ NOT NULL NUMBER CONTENTS$ NOT NULL NUMBER UNDOFILE# NUMBER UNDOBLOCK# NUMBER BLOCKSIZE NOT NULL NUMBER INC# NOT NULL NUMBER SCNWRP NUMBER SCNBAS NUMBER DFLMINEXT NOT NULL NUMBER DFLMAXEXT NOT NULL NUMBER DFLINIT NOT NULL NUMBER DFLINCR NOT NULL NUMBER DFLMINLEN NOT NULL NUMBER DFLEXTPCT NOT NULL NUMBER DFLOGGING NOT NULL NUMBER AFFSTRENGTH NOT NULL NUMBER BITMAPPED NOT NULL NUMBER PLUGGED NOT NULL NUMBER DIRECTALLOWED NOT NULL NUMBER FLAGS NOT NULL NUMBER PITRSCNWRP NUMBER PITRSCNBAS NUMBER OWNERINSTANCE VARCHAR2(30) BACKUPOWNER VARCHAR2(30) GROUPNAME VARCHAR2(30) SPARE1 NUMBER SPARE2 NUMBER SPARE3 VARCHAR2(1000) SPARE4 DATE SYS@bb>
在TS$中，我昨天手工清理了一条NAME=UNDOTBS1的记录，这就是V$TABLESPACE和DBA_TABLESPACES中表空间名称不一致的原因
SYS@bb>select ts#, name, online$ from ts$;
TS# NAME ONLINE$
---------- ------------------------------------------------------------ ----------
0 SYSTEM 1
1 SYSAUX 1
3 TEMP 1
4 USERS 1
5 LMTBSB 3
6 DBTK 1
7 YKDBAWRTS1 3
8 LUNAR 1
9 UNDOTBS 3
10 UNDOTBS2 1
10 rows selected.
SYS@bb>
可以看到，除了我手工删除的一条记录（UNDOTBS1）以外，所有数据库创建以来的表空间名称等信息都保留在TS$中，这样的设计，我猜是为了能够重用表空间名称，减少基表更新等操作（性能考虑吧？ 我也不知道，O(∩_∩)O哈哈~）
比如，这里的UNDOTBS和YKDBAWRTS1等都已经是被删除的表空间。
再看看还有那些依赖于X$KCFISTSA的数据字典：
通过查询dependency$，我们可以发现有3个对象依赖于 X$KCFISTSA基表：
SYS@bb>select * from dependency$ where P_OBJ#=4294952982;
D_OBJ# D_TIMESTAMP ORDER# P_OBJ# P_TIMESTAMP D_OWNER# PROPERTY D_ATTRS D_REASON
---------- ------------------- ---------- ---------- ------------------- ---------- ---------- --------------- ---------------
4943 2013-06-23 09:37:34 0 4294952982 1991-01-02 00:00:00 5 0003000030
4945 2013-06-23 09:37:34 0 4294952982 1991-01-02 00:00:00 5 0003000030
12409 2013-06-23 09:45:16 30 4294952982 1991-01-02 00:00:00 5 0003000030
SYS@bb>
SYS@bb>desc dependency$
Name Null? Type
---------------------------------------------------------------------------------- -------- -------------------------------------------------------
D_OBJ# NOT NULL NUMBER
D_TIMESTAMP NOT NULL DATE
ORDER# NOT NULL NUMBER
P_OBJ# NOT NULL NUMBER
P_TIMESTAMP NOT NULL DATE
D_OWNER# NUMBER
PROPERTY NOT NULL NUMBER
D_ATTRS RAW(2000)
D_REASON RAW(2000)
SYS@bb>
1
知道了这个就很清晰了，处理思路：
1，针对file$的不一致信息，需要手工清除：
SYS@bb>select file#,status$,ts# from file$ where ts#=2;
FILE# STATUS$ TS#
---------- ---------- ----------
3 2 2
SYS@bb>
2，可以根据11g的新特性Health Manager的检查结果来判断，还有哪些相关对象不一致的，是否需要手工清理：
SYS@bb>select obj#,ts#,file# from tab$ where ts#=2;
no rows selected
SYS@bb>select obj#,ts#,file# from ind$ where ts#=2;
no rows selected
SYS@bb>
3，当其他信息都一致后，针对X$KCCTS的不一致信息，由于其来源于控制文件，那么重建控制文件解决
SYS@bb>select TSTSN,TSNAM from X$KCCTS where TSNAM='UNDOTBS1';
TSTSN TSNAM
---------- ------------------------------------------------------------
2 UNDOTBS1
SYS@bb>
之前就是这个思路错误了，导致了一堆的其他问题，当然，也知道和跟多其他好玩东西，比如用gdb跳过数据库启动时的数据字典检查项，知道了重建控制文件的风险其实很大（在不了解现场情况和重建控制文件的细节时）……
