当列设置了unused之后的恢复

来源：互联网发布：安大网络教学综合平台编辑：程序博客网时间：2024/04/29 11:31

SQL> create table test_a(id number(3),name varchar2(10),age number(3));

Table created.

SQL> insert into test_a values(1,'a',18);

1 row created.

SQL> insert into test_a values(2,'b',19);

1 row created.

SQL> insert into test_a values(3,'c',20);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_a;

ID NAME AGE
---------- ----------------------------- ----------
1 a 18
2 b 19
3 c 20

SQL> alter session set events'10046 trace name context forever ,level 10';

Session altered.

SQL> alter table test_a set unused(age);

Table altered.

SQL> select * from test_a;

ID NAME
---------- -----------------------------
1 a
2 b
3 c
SQL> alter session set events'10046 trace name context off';

Session altered.

SQL> conn /as sysdba
Connected.
SQL> select table_name,column_name from dba_tab_columns where table_name='TEST_A' and owner='SCOTT';

TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
TEST_A ID
TEST_A NAME
--此处，连数据字典dba_tab_columns都没有相关的列信息
SQL> select table_name,column_name from dba_tab_cols where table_name='TEST_A' and owner='SCOTT';

TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
TEST_A SYS_C00003_13092622:48:49$
TEST_A NAME
TEST_A ID
--但对于dba_tab_cols这个数据字典是可以产看到隐藏列的。比如，这个表中的unused字段。可见，这里Oracle 对他做了重命名！

SQL> select object_name,object_id,status,object_type from dba_objects where object_name='TEST_A' ;

OBJECT_NAME OBJECT_ID STATUS OBJECT_TYPE
-------------------- ---------- ------- -------------------
TEST_A 58013 VALID TABLE
SQL> select obj#,segcol#,intcol# from col$ where obj#=58013;

OBJ# SEGCOL# INTCOL#
---------- ---------- ----------
58013 1 1
58013 2 2
58013 3 3
--可对比后面增加一列的情况

SQL> conn /as sysdba
Connected.
SQL> update col$ set col#=intcol# where obj#=58013;

3 rows updated.
--这一步，我认为是，将col$的状态调正到初始化的状态。
SQL> update tab$ set cols=cols+1 where obj#=58013;

1 row updated.
--更新tab$的字段，把tab$.col#的数量+1.很可能你在drop或者unused一个列的时候，tab$.col会-1.
SQL> update col$ set name='AGE' where obj#=58013 and col#=3;

1 row updated.
--把列的命名更新回来。原来为“SYS_C00003_13092622:48:49$”
SQL> update col$ set property=0 where obj#=58013;

3 rows updated.
--注意更新了三列了。也就是你unused列的时候，也是更新了3条记录。当时相当于把所有age列给重名了。
--不过这个col$.property 我不知道property=0是什么意思。
查看col$的定义的时候。

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 */

SQL> commit;

Commit complete.
--注意提交。现在基本信息都有了。不过，数据字典的表是需要重新加载的。
SQL> startup force
ORACLE instance started.   Database mounted.
Database opened.
SQL> select * from scott.test_a;

ID NAME AGE
---------- -------------------------------------------------- ----------
1 a 18
2 b 19
3 c 20
--已恢复出来

SQL> alter session set events'10046 trace name context forever ,level 10';

Session altered.

SQL> conn scott/oracle
Connected.
SQL> alter table test_a add (sex varchar2(3));

Table altered.

SQL> conn /as sysdba
Connected.

SQL> select obj#,col#,intcol#,segcol# ,name from col$ where obj#=58013;

OBJ# COL# INTCOL# SEGCOL# NAME
---------- ---------- ---------- ---------- --------------------------------------------------
58013 1 1 1 ID
58013 2 2 2 NAME
58013 3 3 3 AGE
58013 4 4 4 SEX

--intcol#为创建表时的列顺序，col#初始情况下跟intcol#相同
--而segcol#表示列在数据段上存储时的顺序。

最后，发现，10046要跟踪的SQL信息，竟然没有？？？