结论

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，发现如下文章：
 http://www.eygle.com/archives/2011/12/delete_sdo_geor_ddl_table.html


 上述底层对象是和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.