結論

1,測試環境為oracle 11.2.0.1
2,如果測試表沒有構建索引，如果你在obj$中刪除測試表對應記錄，會提示
SQL> select a from t_booterr;
select a from t_booterr
*
ERROR at line 1:
ORA-00942: table or view does not exist

3，調整了基表的資料後，為了讓基本的資料變更馬上生效，必須重新整理共享池，否則可能應用表仍會使用共享池ROW CACHE中的快取資料字典資訊
4，如果刪除了col$對應資料，會報
SQL> select a,b from t_booterr;
select a,b from t_booterr
*
ERROR at line 1:
ORA-00904: "B": invalid identifier

5，col$表有long列，遷移備份不方便
6,col$表是基於cluster c_obj#(obj#) --可見col$是構建在cluster table c_obj#上面
7, col$表也是透過obj#與obj表關聯起來
8,col$表有個列property，會定義列在不同操作下各個狀態，見下
property number not null, /* column properties (bit flags): */
/* 0x0001 = 1 = ADT attribute column */
/* 0x0002 = 2 = OID column */
/* 0x0004 = 4 = nested table column */ --nested table column
/* 0x0008 = 8 = virtual column */
/* 0x0010 = 16 = nested table's SETID$ column */
/* 0x0020 = 32 = hidden column */ --hidden column
/* 0x0040 = 64 = primary-key based OID column */
/* 0x0080 = 128 = column is stored in a lob */ --stored in a lob
/* 0x0100 = 256 = system-generated column */
/* 0x0200 = 512 = rowinfo column of typed table/view */
/* 0x0400 = 1024 = nested table columns setid */
/* 0x0800 = 2048 = column not insertable */
/* 0x1000 = 4096 = column not updatable */ --not updatable
/* 0x2000 = 8192 = column not deletable */
/* 0x4000 = 16384 = dropped column */ --dropped column
/* 0x8000 = 32768 = unused column - data still in row */
/* 0x00010000 = 65536 = virtual column */ --virtual column
/* 0x00020000 = 131072 = place DESCEND operator on top */
/* 0x00040000 = 262144 = virtual column is NLS dependent */
/* 0x00080000 = 524288 = ref column (present as oid col) */
/* 0x00100000 = 1048576 = hidden snapshot base table column */
/* 0x00200000 = 2097152 = attribute column of a user-defined ref */
/* 0x00400000 = 4194304 = export hidden column,RLS on hidden col */
/* 0x00800000 = 8388608 = string column measured in characters */
/* 0x01000000 = 16777216 = virtual column expression specified */
/* 0x02000000 = 33554432 = typeid column */
/* 0x04000000 = 67108864 = Column is encrypted */ --encrypted column
/* 0x20000000 = 536870912 = Column is encrypted without salt */

可見功能相當強大

9,當然這個你想恢復基表的資料，首先就要了解應用表的DDL定義會寫到哪些60個基表中，
基於這些基表的聯絡，把損壞的基表資料恢復或還原
所以你要精通這些基表的結構，否則可能會造成資料損壞或不一致

測試

1,資料庫版本
SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

2，建立測試表
SQL> create table t_booterr(a int,b int);

Table created.

SQL> insert into t_booterr values(9,9);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.

3，10046分析查詢sql (select a from t_booterr)，底層涉及哪些bootstrap$儲存的60個物件

可見從10046裡面沒有看到訪問哪些底層表
=====================
PARSING IN CURSOR #4 len=392 dep=1 uid=84 oct=3 lid=84 tim=1446411079521218 hv=1341239449 ad='de37cc40' sqlid='5pj2gg57z3c4t'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(S
UM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ NO_PARALLEL("T_BOOTERR") FULL("T_BOOTERR") NO_PARALLEL_INDEX("T_BOOTERR") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "T
_BOOTERR" "T_BOOTERR") SAMPLESUB

select a from t_booterr

4，手工刪除底層表的obj$的相關記錄（事先要備份，便於恢復）

SQL> create table obj$_75118bak as select * from obj$ where obj#=75118;

Table created.

SQL> delete from obj$ where obj#=75118;

1 row deleted.

SQL> commit;

Commit complete.

5,再次執行查詢 select a from t_booterr

並未報錯，可見一切正常
SQL> select a from t_booterr;

A
----------
9

可見重新整理緩衝，也是一切正常
SQL> alter system flush buffer_cache;

System altered.

SQL> select a from t_booterr;

A
----------
9

6,重新整理共享池，這個報錯了

SQL> alter system flush shared_pool;

System altered.

SQL>
SQL>
SQL> select a from t_booterr;
select a from t_booterr
*
ERROR at line 1:
ORA-00942: table or view does not exist

7,我們用10046來分析上述報錯
PARSE ERROR #2:len=23 dep=0 uid=84 oct=3 lid=84 tim=1446411812202901 err=942
select a from t_booterr
=====================
PARSING IN CURSOR #6 len=168 dep=1 uid=57 oct=47 lid=57 tim=1446411812213232 hv=337957580 ad='de1c6798' sqlid='a6u3yjca29nqc'
declare
m_stmt varchar2(512);
begin
m_stmt:='delete from sdo_geor_ddl__table$$';
EXECUTE IMMEDIATE m_stmt;
EXCEPTION
WHEN OTHERS THEN
NULL;
end;
END OF STMT
PARSE #6:c=0,e=10111,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=0,tim=1446411812213229
WAIT #5: nam='resmgr:cpu quantum' ela= 10960 location=3 =0 =0 obj#=13482 tim=1446411812336105
=====================
PARSING IN CURSOR #5 len=33 dep=2 uid=57 oct=7 lid=57 tim=1446411812490176 hv=1949913731 ad='de4a28e8' sqlid='3972rvxu3knn3'
delete from sdo_geor_ddl__table$$
END OF STMT
PARSE #5:c=3999,e=276736,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,plh=3007952250,tim=1446411812490173
EXEC #5:c=0,e=6836,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=3007952250,tim=1446411812497086
STAT #5 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE SDO_GEOR_DDL__TABLE$$ (cr=0 pr=0 pw=0 time=0 us)' --SDO_GEOR_DDL__TABLE$$
STAT #5 id=2 cnt=0 pid=1 pos=1 obj=65927 op='TABLE ACCESS FULL SDO_GEOR_DDL__TABLE$$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)'
CLOSE #5:c=0,e=8,dep=2,type=3,tim=1446411812532584
EXEC #6:c=6999,e=319299,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=0,tim=1446411812532647

可見這個物件是表物件
SQL> select obj#,name,namespace,type# from obj$ where name like 'SDO_GEOR_DDL%';

OBJ# NAME NAMESPACE TYPE#
---------- ------------------------------------------------------------ ---------- ----------
65927 SDO_GEOR_DDL__TABLE$$ 1 2

namespace number not null, /* namespace of object (see KQD.H): */
/* 1 = TABLE/PROCEDURE/TYPE, 2 = BODY, 3 = TRIGGER, 4 = INDEX, 5 = CLUSTER, */
/* 8 = LOB, 9 = DIRECTORY, */
/* 10 = QUEUE, 11 = REPLICATION OBJECT GROUP, 12 = REPLICATION PROPAGATOR, */
/* 13 = JAVA SOURCE, 14 = JAVA RESOURCE */
/* 58 = (Data Mining) MODEL */
subname varchar2("M_IDEN"), /* subordinate to the name */
type# number not null, /* object type (see KQD.H): */
/* 1 = INDEX, 2 = TABLE, 3 = CLUSTER, 4 = VIEW, 5 = SYNONYM, 6 = SEQUENCE, */
/* 7 = PROCEDURE, 8 = FUNCTION, 9 = PACKAGE, 10 = NON-EXISTENT, */
/* 11 = PACKAGE BODY, 12 = TRIGGER, 13 = TYPE, 14 = TYPE BODY, */
/* 19 = TABLE PARTITION, 20 = INDEX PARTITION, 21 = LOB, 22 = LIBRARY, */
/* 23 = DIRECTORY , 24 = QUEUE, */
/* 25 = IOT, 26 = REPLICATION OBJECT GROUP, 27 = REPLICATION PROPAGATOR, */
/* 28 = JAVA SOURCE, 29 = JAVA CLASS, 30 = JAVA RESOURCE, 31 = JAVA JAR, */
/* 32 = INDEXTYPE, 33 = OPERATOR , 34 = TABLE SUBPARTITION, */
/* 35 = INDEX SUBPARTITION */
/* 82 = (Data Mining) MODEL */
/* 92 = OLAP CUBE DIMENSION, 93 = OLAP CUBE */
/* 94 = OLAP MEASURE FOLDER, 95 = OLAP CUBE BUILD PROCESS */

可見$$相關的表名是可以查詢出來的，為何上述的底層表就查詢不出來呢
SQL> create table t_test$$(a int);

Table created.

SQL> desc t_test$$;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER(38)

SQL> select object_id,object_name from user_objects where object_name like '%T_TEST$$%';

OBJECT_ID OBJECT_NAME
---------- ---------------
75120 T_TEST$$

SQL> select object_id,object_name from user_objects where object_name='T_TEST$$';

OBJECT_ID OBJECT_NAME
---------- ---------------
75120 T_TEST$$

繼續分析，希望可以找到線索，注意到表命名中間有2個橫線
SQL> col object_name for a50
SQL> select object_id,object_name,object_type from dba_objects where object_id=65927;

OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- -------------------------------------------------- --------------------------------------
65927 SDO_GEOR_DDL__TABLE$$ TABLE

SQL> create table t_test_ddl__tab$$(a int);

Table created.

SQL> desc t_test_ddl__tab$$;
Name Null? Type
----------------- -------- ------------
A NUMBER(38)

經過BAIDU，發現如下文章：

上述底層物件是和SPATIAL相關，看來和我們的問題沒有相關性

好我們繼續分析

採用重啟庫，看可否發現什麼線索
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

可見資料庫載入到MOUNT，僅讀取控制檔案，和資料檔案無關
SQL> startup mount
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size 2215064 bytes
Variable Size 1409287016 bytes
Database Buffers 721420288 bytes
Redo Buffers 4964352 bytes
Database mounted.

可見只要不是核心物件損壞，雖然是刪除了業務應用表的資料在OBJ$，不會影響資料庫重啟操作
SQL> alter database open;

Database altered.

重試查詢測試表

你看我分析半天，我真是呆子，在10046跟蹤不到是正常的啊，因為這個表沒有索引，相關表定義的內容在obj$啊
SQL> select a from t_booterr;
select a from t_booterr
*
ERROR at line 1:
ORA-00942: table or view does not exist

好，我們恢復obj$
SQL> insert into obj$ select * from obj$_75118bak;

1 row created.

SQL> commit;

Commit complete.

也就是說字典表或叫基表obj$之類的59個底層物件是快取中共享池中的row cache裡面，除非重新整理共享池，變更的基表內容才會重新從磁碟讀入共享池，否則還是沿用變前之前的基表資料
SQL> select a from t_booterr;
select a from t_booterr
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> alter system flush shared_pool;

System altered.

SQL> select a from t_booterr;

A
----------
9

好，我們繼續測試，刪除col$中的記錄，看業務表查詢及DML會如何

SQL> select obj#,col#,name,type# from col$ where obj#=75118;

OBJ# COL# NAME TYPE#
---------- ---------- ------------------------------------------------------------ ----------
75118 1 A 2
75118 2 B 2

create table col$ /* column table */
( obj# number not null, /* object number of base object */
col# number not null, /* column number as created */
segcol# number not null, /* column number in segment */
segcollength number not null, /* length of the segment column */
offset number not null, /* offset of column */
name varchar2("M_IDEN") not null, /* name of column */
type# number not null, /* data type of column */
/* for ADT column, type# = DTYADT */
length number not null, /* length of column in bytes */
fixedstorage number not null, /* flags: 0x01 = fixed, 0x02 = read-only */
precision# number, /* precision */
scale number, /* scale */
null$ number not null, /* 0 = NULLs permitted, */
/* > 0 = no NULLs permitted */
deflength number, /* default value expression text length */
default$ long, /* default value expression text */

/*
* If a table T(c1, addr, c2) contains an ADT column addr which is stored
* exploded, the table will be internally stored as
* T(c1, addr, C0003$, C0004$, C0005$, c2)
* Of these, only c1, addr and c2 are user visible columns. Thus, the
* user visible column numbers for (c1, addr, C0003$, C0004$, C0005$, c2)
* will be 1,2,0,0,0,3. And the corresponding internal column numbers will
* be 1,2,3,4,5,6.
*
* Some dictionary tables like icol$, ccol$ need to contain intcol# so
* that we can have indexes and constraints on ADT attributes. Also, these
* tables also need to contain col# to maintain backward compatibility.
* Most of these tables will need to be accessed by col#, intcol# so
* indexes are created on them based on (obj#, col#) and (obj#, intcol#).
* Indexes based on col# have to be non-unique if ADT attributes might
* appear in the table. Indexes based on intcol# can be unique.
*/
intcol# number not null, /* internal column number */
property number not null, /* column properties (bit flags): */
/* 0x0001 = 1 = ADT attribute column */
/* 0x0002 = 2 = OID column */
/* 0x0004 = 4 = nested table column */
/* 0x0008 = 8 = virtual column */
/* 0x0010 = 16 = nested table's SETID$ column */
/* 0x0020 = 32 = hidden column */
/* 0x0040 = 64 = primary-key based OID column */
/* 0x0080 = 128 = column is stored in a lob */
/* 0x0100 = 256 = system-generated column */
/* 0x0200 = 512 = rowinfo column of typed table/view */
/* 0x0400 = 1024 = nested table columns setid */
/* 0x0800 = 2048 = column not insertable */
/* 0x1000 = 4096 = column not updatable */
/* 0x2000 = 8192 = column not deletable */
/* 0x4000 = 16384 = dropped column */
/* 0x8000 = 32768 = unused column - data still in row */
/* 0x00010000 = 65536 = virtual column */
/* 0x00020000 = 131072 = place DESCEND operator on top */
/* 0x00040000 = 262144 = virtual column is NLS dependent */
/* 0x00080000 = 524288 = ref column (present as oid col) */
/* 0x00100000 = 1048576 = hidden snapshot base table column */
/* 0x00200000 = 2097152 = attribute column of a user-defined ref */
/* 0x00400000 = 4194304 = export hidden column,RLS on hidden col */
/* 0x00800000 = 8388608 = string column measured in characters */
/* 0x01000000 = 16777216 = virtual column expression specified */
/* 0x02000000 = 33554432 = typeid column */
/* 0x04000000 = 67108864 = Column is encrypted */
/* 0x20000000 = 536870912 = Column is encrypted without salt */

/* The spares may be used as the column's NLS character set,
* the number of distinct column values, and the column's domain.
*/
/* the universal character set id maintained by NLS group */
charsetid number, /* NLS character set id */
/*
* charsetform
*/
charsetform number,
/* 1 = implicit: for CHAR, VARCHAR2, CLOB w/o a specified set */
/* 2 = nchar: for NCHAR, NCHAR VARYING, NCLOB */
/* 3 = explicit: for CHAR, etc. with "CHARACTER SET ..." clause */
/* 4 = flexible: for PL/SQL "flexible" parameters */
spare1 number, /* fractional seconds precision */
spare2 number, /* interval leading field precision */
spare3 number, /* maximum number of characters in string */
spare4 varchar2(1000), /* NLS settings for this expression */
spare5 varchar2(1000),
spare6 date
)
cluster c_obj#(obj#) --可見col$是構建在cluster table c_obj#上面

可見col$中有long列
SQL> create table col$_bak as select * from col$ where obj#=75118 and col#=2;
create table col$_bak as select * from col$ where obj#=75118 and col#=2
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

SQL> delete from col$ where obj#=75118 and col#=2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter system flush shared_pool;

System altered.

可見從col$表刪除測試表某列後，確實找不到這個表列了
SQL> select a,b from t_booterr;
select a,b from t_booterr
*
ERROR at line 1:
ORA-00904: "B": invalid identifier

清理測試表環境
SQL> drop table t_booterr purge;

Table dropped.

oracle 11g如果應用表依賴的bootstrap$某些某表如obj$_col$資料損壞系列四

結論

測試

相關文章