喜极而泣,险些酿成大祸.(恢复设置为unused column的列)
深夜,俺正满头大汗地用exp把数据从oracle9i里导出来^^^^^, 谁知道,有一个表太个性了,有它就不能通过传输空间的验证.
俺正没有办法,发现Oracle 中可以把表中的列设置为"unused column",就是无用的意思,验证就不管他了.
于是,俺就把那个表除了主键外所有的字段都弄成"unused column"了,谁知这个操作属于DDL,也就是说不可逆的,那个表名叫JOBTEN,最重要的表之一,它一坏,首页都看不了了.
还原吧,发现最近的备份还是5天前的,而且IMP导不进去了,吐血.......
折腾,再折腾,折腾三小时,现在凌晨4点半,终于回复旧观了.
拯救的方法如下,太NB了.
SQL>
SQL> select * from obj$ where name='JOBTEN';
OBJ# DATAOBJ# OWNER# NAME NAMESPACE SUBNAME TYPE# CTIME MTIME STIME STATUS REMOTEOWNER LINKNAME FLAGS OID$ SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
------ ---------- -------- ---------- ---------- ---------- ---------- ----------- ----------- ----------- ---------- -------------- ----------- ---------- ------ -------- ---------- ---------- -------- -------- ------
30689 30689 61 TOMS_TEST 1 2 2007-3-19 1 2007-3-19 1 2007-3-19 1 1 0 6 1
SQL> select * from dba_objects where object_name='JOBTEN' and owner='HBJOB';
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY
-------- --------------- ---------------- ---------- -------------- --------------- ----------- ------------- ------------------- ------- --------- --------- ---------
STUDY TOMS_TEST 30689 30689 TABLE 2007-3-19 1 2007-3-19 13: 2007-03-19:13:55:08 VALID N N N
SQL>
SQL> select * from col$ where obj#=30689;
OBJ# COL# SEGCOL# SEGCOLLENGTH OFFSET NAME TYPE# LENGTH FIXEDSTORAGE PRECISION# SCALE NULL$ DEFLENGTH DEFAULT$ INTCOL# PROPERTY CHARSETID CHARSETFORM SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- -------- ------
30689 1 1 22 0 A 2 22 0 0 0 1 0 0 0 0 0 0
30689 2 2 22 0 B 2 22 0 0 0 2 0 0 0 0 0 0
30689 3 3 22 0 C 2 22 0 0 0 3 0 0 0 0 0 0
SQL> select * from tab$ where obj#=30689;
OBJ# DATAOBJ# TS# FILE# BLOCK# BOBJ# TAB# COLS CLUCOLS PCTFREE$ PCTUSED$ INITRANS MAXTRANS FLAGS AUDIT$ ROWCNT BLKCNT EMPCNT AVGSPC CHNCNT AVGRLN AVGSPC_FLB FLBCNT ANALYZETIME SAMPLESIZE DEGREE INSTANCES INTCOLS KERNELCOLS PROPERTY TRIGFLAG SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------- -------- -----------
30689 30689 7 6 2604 3 10 40 1 255 1073741825 -------------------------------------- 3 3 536870912 0 736 2007-3-19 1
SQL> select * from tab$ where obj#=30689;
OBJ# DATAOBJ# TS# FILE# BLOCK# BOBJ# TAB# COLS CLUCOLS PCTFREE$ PCTUSED$ INITRANS MAXTRANS FLAGS AUDIT$ ROWCNT BLKCNT EMPCNT AVGSPC CHNCNT AVGRLN AVGSPC_FLB FLBCNT ANALYZETIME SAMPLESIZE DEGREE INSTANCES INTCOLS KERNELCOLS PROPERTY TRIGFLAG SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------- -------- -----------
30689 30689 7 6 2604 2 10 40 1 255 1073741825 -------------------------------------- 3 3 537395200 0 736 2007-3-19 1
SQL>
SQL> update tab$ set cols=6 where obj#=30689;
5 row updated
SQL> commit;
Commit complete
SQL> select * from tab$ where obj#=30689;
OBJ# DATAOBJ# TS# FILE# BLOCK# BOBJ# TAB# COLS CLUCOLS PCTFREE$ PCTUSED$ INITRANS MAXTRANS FLAGS AUDIT$ ROWCNT BLKCNT EMPCNT AVGSPC CHNCNT AVGRLN AVGSPC_FLB FLBCNT ANALYZETIME SAMPLESIZE DEGREE INSTANCES INTCOLS KERNELCOLS PROPERTY TRIGFLAG SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------- ------- -----------
30689 30689 7 6 2604 3 10 40 1 255 1073741825 -------------------------------------- 3 3 537395200 0 736 2007-3-19 1
***********************************************************************************************************
update col$ set col#=2,name='NAME' where obj#=30689 and SEGCOL#=2;
update col$ set col#=3,name='PASSWORD' where obj#=30689 and SEGCOL#=3;
update col$ set col#=4,name='CHFULLNAME' where obj#=30689 and SEGCOL#=4;
update col$ set col#=5,name='GRADING' where obj#=30689 and SEGCOL#=5;
select * from col$ where obj#=30689;
***************************************************************************************************
5 row updated
SQL> commit;
Commit complete
SQL> update col$ set PROPERTY=0 where obj#=30689 and col#=5;
5 row updated
SQL> commit;
Commit complete
SQL> update tab$ set PROPERTY=536870912 where obj#=30689;
5 row updated
SQL> commit;
Commit complete
SQL>
