开始做一个测试的表吧
# sqlplus zgptest/zgptest
SQL> select * from qq;
A B C
-- --- ---------
a b 06-JUL-11
a b 06-JUL-11
a b 06-JUL-11
a b 06-JUL-11
a b 06-JUL-11
a b 06-JUL-11
a b 06-JUL-11
a b 06-JUL-11
a b 06-JUL-11
a b 06-JUL-11
a b 06-JUL-11
A B C
-- --- ---------
a b 06-JUL-11
a b 06-JUL-11
a b 06-JUL-11
a b 06-JUL-11
a b 06-JUL-11
a b 06-JUL-11
a b 06-JUL-11
a b 06-JUL-11
a b 06-JUL-11
20 rows selected.
SQL> alter table qq set unused COLUMN b;
Table altered.
SQL> select * from qq;
A C
-- ---------
a 06-JUL-11
a 06-JUL-11
a 06-JUL-11
a 06-JUL-11
a 06-JUL-11
a 06-JUL-11
a 06-JUL-11
a 06-JUL-11
a 06-JUL-11
a 06-JUL-11
a 06-JUL-11
A C
-- ---------
a 06-JUL-11
a 06-JUL-11
a 06-JUL-11
a 06-JUL-11
a 06-JUL-11
a 06-JUL-11
a 06-JUL-11
a 06-JUL-11
a 06-JUL-11
20 rows selected.
SQL> commit;
Commit complete.
现在用sys用户开始恢复了
[oracle@localhost ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 7 16:09:43 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT OBJ# FROM OBJ$ WHERE NAME like '%QQ';
OBJ#
----------
52057
52068
SQL> SELECT OBJ# FROM OBJ$ WHERE NAME='QQ';
OBJ#
----------
52057
SQL> SELECT COL#,INTCOL#,NAME FROM COL$ WHERE OBJ#=52057;
COL# INTCOL# NAME
---------- ---------- ------------------------------
1 1 A
0 2 SYS_C00002_11070716:07:57$
2 3 C
SQL> UPDATE COL$ SET COL#=INTCOL# WHERE OBJ#=52057;
3 rows updated.
SQL> UPDATE TAB$ SET COLS=COLS+1 WHERE OBJ#=52057;
1 row updated.
SQL> UPDATE COL$ SET NAME='B' WHERE OBJ#=52057 AND COL#=2;
说明 B是字段名 2是第二个字段
1 row updated.
SQL> UPDATE COL$ SET PROPERTY=0 WHERE OBJ#=52057;
3 rows updated.
SQL> commit;
Commit complete.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 243269632 bytes
Fixed Size 2083040 bytes
Variable Size 134219552 bytes
Database Buffers 100663296 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.
SQL>
然后你用自己的用户去查这个表，已经都恢复数据了。【说明，unused这个字段后，添加数据以及删除数据我都搞过测试，注意，测试的时候这个字段没有非空等约束条件，是否会影响我不敢保证】
