设置为unused 后如何恢复

 

用DBA账户完成下列操作。

1. 得到表的obj#

select obj# from obj$ where name='表名';

2. 修改cols

select cols from tab$ where obj#="obj#";

cols数会小于表的列数，因为你unuse了一些列。

恢复cols到原来的数目。

update tab$ set cols="想要的列数" where obj#="obj#";

3. 修改col$

update col$ set col#=intcol#, property=0 where obj#="obj#";

当你unuse了某些列后，这些列的col#会变成0，intcol#保存了原先的列次序。

4. 重启server。OK。

set unused column怎么重新启用该列？

就是说一个列被设置了unused了，怎么重新启用这个列？

设置unused列之后，并不是将该列数据立即删除，而是被隐藏起来，以下为恢复步骤：对数据字典不熟悉的同学测试前做好备份工作

SQL> conn hsy/hsy

Connected.

SQL> create table t (a number,b number,c varchar2(10),d number);

Table created.

SQL> insert into t values(1,2,'A',3);

1 row created.

SQL> insert into t values(4,5,'B',6);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

A          B C                   D

---------- ---------- ---------- ----------

1          2 A                   3

4          5 B                   6

SQL> alter table t set unused column c;

Table altered.

SQL> select * from t;

A          B          D

---------- ---------- ----------

1          2          3

4          5          6

SQL> SET LINESIZE 200

SQL> SELECT OBJECT_ID,OBJECT_NAME FROM USER_OBJECTS;

OBJECT_ID OBJECT_NAME

---------- -----------------------------------------------

70322 XPLAN_NTT

70321 XPLAN_OT

70323 XPLAN

70324 XPLAN

71929 2  

71930 T

70775 TEST_VIEW

69706 TEST_PROC

8 rows selected.

SQL> conn hsy/hsy as sysdba

Connected.

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

COL#    INTCOL# NAME

---------- ---------- ------------------------------

1          1 A

2          2 B

0          3 SYS_C00003_10071515:33:59$    ---原来的列名为C，被系统修了[/color]

3          4 D

SQL> select cols from tab$ where obj#=71930;

COLS

----------

3                                  ----系统的字段数目也发生了变化 [/color]

SQL> update col$ set col#=intcol# where obj#=71930;

4 rows updated.

SQL> update tab$ set cols=cols+1 where obj#=71930;

1 row updated.

SQL> update col$ set name='C' where obj#=71930 and col#=3;

1 row updated.

SQL> update col$ set property=0 where obj#=71930;

4 rows updated.

SQL> commit;

Commit complete.

SQL> startup force;  -- 这一步是必不可少

ORACLE instance started.

Total System Global Area 1107296256 bytes

Fixed Size                  2020160 bytes

Variable Size             234884288 bytes

Database Buffers          855638016 bytes

Redo Buffers               14753792 bytes

Database mounted.

Database opened.

SQL> select * from hsy.t;

A          B C                   D

---------- ---------- ---------- ----------

1          2 A                   3

4          5 B                   6