以下测试环境为： ORACLE 10.2.0.5
因为方法是修改数据字典，主要的命令是（试验环境或者自己玩玩）：
注意：9i和10g的方法略有细小区别，11g的还没有测试。。。
update col$ set col#=segcol# where obj#=12068;
update col$ set property=0 where obj#=12068;
因此，这种还原set unused columns的方法及其他方法均非官方支持，生产及重要环境慎用！！记住，官方的说法：
you cannot roll back the results of this clause. That is, you cannot issueSET USED counterpart to retrieve a column that you haveSET UNUSED.
[oracle@rh1 udump]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 1 05:12:55 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> select obj# from obj$ where name='LUNAR_TEST' order by obj#;
OBJ#
----------
12069
12070
12071
12072
12073
12068
6 rows selected.
SQL>
SQL> l
1* select * from obj$ where name='LUNAR_TEST'
SQL> /
OBJ# DATAOBJ# OWNER# NAME NAMESPACE SUBNAME TYPE# CTIME
---------- ---------- ---------- ------------------------------ ---------- ------------------------------ ---------- ------------
MTIME STIME STATUS REMOTEOWNER
------------ ------------ ---------- ------------------------------
LINKNAME
--------------------------------------------------------------------------------------------------------------------------------
FLAGS OID$ SPARE1 SPARE2 SPARE3
---------- -------------------------------- ---------- ---------- ----------
SPARE4
---------------------------------------------------------------------------------------------------------------------------------
SPARE5
---------------------------------------------------------------------------------------------------------------------------------
SPARE6
------------
12069 12069 31 LUNAR_TEST 1 PART_1 19 01-AUG-12
01-AUG-12 01-AUG-12 1
0 6 65535
12070 12070 31 LUNAR_TEST 1 PART_2 19 01-AUG-12
01-AUG-12 01-AUG-12 1
0 6 65535
12071 12071 31 LUNAR_TEST 1 PART_3 19 01-AUG-12
01-AUG-12 01-AUG-12 1
0 6 65535
12072 12072 31 LUNAR_TEST 1 PART_4 19 01-AUG-12
01-AUG-12 01-AUG-12 1
0 6 65535
12073 12073 31 LUNAR_TEST 1 PART_5 19 01-AUG-12
01-AUG-12 01-AUG-12 1
0 6 65535
12068 31 LUNAR_TEST 1 2 01-AUG-12
01-AUG-12 01-AUG-12 1
0 6 3
6 rows selected.
SQL>
05:16:38 SQL> show user
USER is "LUNAR"
05:16:55 SQL>
05:18:00 SQL> set pages 999
05:18:15 SQL> col object_name for a30
05:18:56 SQL> col SUBOBJECT_NAME for a15
05:19:03 SQL>05:21:38 SQL> select object_name, subobject_name,object_id from user_objects order by object_id;
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID
------------------------------ --------------- ----------
FQ_TEST 12048
FQ_TEST PART_1 12049
FQ_TEST PART_2 12050
FQ_TEST PART_3 12051
FQ_TEST PART_4 12052
FQ_TEST PART_5 12053
BIN$xidZPgfBjs/gQKjACwogqg==$0 PART_1 12055
BIN$xidZPgfBjs/gQKjACwogqg==$0 PART_2 12056
BIN$xidZPgfBjs/gQKjACwogqg==$0 PART_3 12057
BIN$xidZPgfBjs/gQKjACwogqg==$0 PART_4 12058
BIN$xidZPgfBjs/gQKjACwogqg==$0 PART_5 12059
FQ_TEST2 12061
FQ_TEST2 PART_1 12062
FQ_TEST2 PART_2 12063
FQ_TEST2 PART_3 12064
FQ_TEST2 PART_4 12065
FQ_TEST2 PART_5 12066
FQ_TEST_PART_2 12067
LUNAR_TEST 12068
LUNAR_TEST PART_1 12069
LUNAR_TEST PART_2 12070
LUNAR_TEST PART_3 12071
LUNAR_TEST PART_4 12072
LUNAR_TEST PART_5 12073
LUNAR_TEST2 12074
LUNAR_TEST2 PART_1 12075
LUNAR_TEST2 PART_2 12076
LUNAR_TEST2 PART_3 12077
LUNAR_TEST2 PART_4 12078
LUNAR_TEST2 PART_5 12079
LUNAR_TEST_PART_2 12080
31 rows selected.
Elapsed: 00:00:00.03
05:21:41 SQL>
SQL> select cols from tab$ where obj#='12068';
COLS
----------
7
SQL> select cols from tab$ where obj#='12069';
no rows selected
SQL>
SQL> col SPARE4 for a15
SQL> col SPARE5 for a15
SQL> col AUDIT$ for a5
SQL> select * from tab$ where obj#='12068';
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
--------------- --------------- ------------
12068 0 0 0 7 0 0
0 0 1073741825 -----
-----
-----
-----
-----
-----
-----
---
9 9 537395232 0 736
01-AUG-12
SQL>
SQL> col LINKNAME for a10
SQL> l
1* select obj#,SUBNAME,LINKNAME,FLAGS,OID$ from obj$ where name='LUNAR_TEST'
SQL> /
OBJ# SUBNAME LINKNAME FLAGS OID$
---------- ------------------------------ ---------- ---------- --------------------------------
12069 PART_1 0
12070 PART_2 0
12071 PART_3 0
12072 PART_4 0
12073 PART_5 0
12068 0
6 rows selected.
SQL>
05:41:12 SQL> select object_name, subobject_name,object_id from user_objects where object_name='LUNAR_10GTEST' order by object_id;
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID
------------------------------ --------------- ----------
LUNAR_10GTEST 12082
LUNAR_10GTEST PART_1 12083
LUNAR_10GTEST PART_2 12084
LUNAR_10GTEST PART_3 12085
LUNAR_10GTEST PART_4 12086
LUNAR_10GTEST PART_5 12087
6 rows selected.
Elapsed: 00:00:00.00
05:41:47 SQL>
SQL> select obj#,SUBNAME,LINKNAME,FLAGS,OID$ from obj$ where name='LUNAR_10GTEST';
OBJ# SUBNAME LINKNAME FLAGS OID$
---------- ------------------------------ ---------- ---------- --------------------------------
12083 PART_1 0
12084 PART_2 0
12085 PART_3 0
12086 PART_4 0
12087 PART_5 0
12082 0
6 rows selected.
SQL>
select * from tab$ where obj#='12068'; --有2个unused 列
select * from tab$ where obj#='12082'; --没有unused 列
SQL> select * from tab$ where obj#='12068';
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
--------------- --------------- ------------
12068 0 0 0 7 0 0
0 0 1073741825 -----
-----
-----
-----
-----
-----
-----
---
9 9 537395232 0 736
01-AUG-12
SQL>
SQL> select * from tab$ where obj#='12082';
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
--------------- --------------- ------------
12082 0 0 0 9 0 0
0 0 1073741825 -----
-----
-----
-----
-----
-----
-----
---
9 9 536870944 0 736
01-AUG-12
SQL>
select * from obj$ where name='LUNAR_TEST'; --有2个unused 列
select * from obj$ where name='LUNAR_10GTEST'; --没有unused 列
SQL> select * from obj$ where name='LUNAR_TEST';
OBJ# DATAOBJ# OWNER# NAME NAMESPACE SUBNAME TYPE# CTIME
---------- ---------- ---------- ------------------------------ ---------- ------------------------------ ---------- ------------
MTIME STIME STATUS REMOTEOWNER LINKNAME FLAGS OID$
------------ ------------ ---------- ------------------------------ ---------- ---------- --------------------------------
SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ---------- --------------- --------------- ------------
12069 12069 31 LUNAR_TEST 1 PART_1 19 01-AUG-12
01-AUG-12 01-AUG-12 1 0
6 65535
12070 12070 31 LUNAR_TEST 1 PART_2 19 01-AUG-12
01-AUG-12 01-AUG-12 1 0
6 65535
12071 12071 31 LUNAR_TEST 1 PART_3 19 01-AUG-12
01-AUG-12 01-AUG-12 1 0
6 65535
12072 12072 31 LUNAR_TEST 1 PART_4 19 01-AUG-12
01-AUG-12 01-AUG-12 1 0
6 65535
12073 12073 31 LUNAR_TEST 1 PART_5 19 01-AUG-12
01-AUG-12 01-AUG-12 1 0
6 65535
12068 31 LUNAR_TEST 1 2 01-AUG-12
01-AUG-12 01-AUG-12 1 0
6 3
6 rows selected.
SQL>
SQL>
SQL>
SQL> select * from obj$ where name='LUNAR_10GTEST';
OBJ# DATAOBJ# OWNER# NAME NAMESPACE SUBNAME TYPE# CTIME
---------- ---------- ---------- ------------------------------ ---------- ------------------------------ ---------- ------------
MTIME STIME STATUS REMOTEOWNER LINKNAME FLAGS OID$
------------ ------------ ---------- ------------------------------ ---------- ---------- --------------------------------
SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ---------- --------------- --------------- ------------
12083 12083 31 LUNAR_10GTEST 1 PART_1 19 01-AUG-12
01-AUG-12 01-AUG-12 1 0
6 65535
12084 12084 31 LUNAR_10GTEST 1 PART_2 19 01-AUG-12
01-AUG-12 01-AUG-12 1 0
6 65535
12085 12085 31 LUNAR_10GTEST 1 PART_3 19 01-AUG-12
01-AUG-12 01-AUG-12 1 0
6 65535
12086 12086 31 LUNAR_10GTEST 1 PART_4 19 01-AUG-12
01-AUG-12 01-AUG-12 1 0
6 65535
12087 12087 31 LUNAR_10GTEST 1 PART_5 19 01-AUG-12
01-AUG-12 01-AUG-12 1 0
6 65535
12082 31 LUNAR_10GTEST 1 2 01-AUG-12
01-AUG-12 01-AUG-12 1 0
6 1
6 rows selected.
SQL>
SQL>
select * from obj$ where obj#='12069'; --被设置为unused 列
select * from obj$ where obj#='12083'; --没有没设置为unused 列
SQL> select * from obj$ where obj#='12069';
OBJ# DATAOBJ# OWNER# NAME NAMESPACE SUBNAME TYPE# CTIME
---------- ---------- ---------- ------------------------------ ---------- ------------------------------ ---------- ------------
MTIME STIME STATUS REMOTEOWNER LINKNAME FLAGS OID$
------------ ------------ ---------- ------------------------------ ---------- ---------- --------------------------------
SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ---------- --------------- --------------- ------------
12069 12069 31 LUNAR_TEST 1 PART_1 19 01-AUG-12
01-AUG-12 01-AUG-12 1 0
6 65535
SQL> select * from obj$ where obj#='12083';
OBJ# DATAOBJ# OWNER# NAME NAMESPACE SUBNAME TYPE# CTIME
---------- ---------- ---------- ------------------------------ ---------- ------------------------------ ---------- ------------
MTIME STIME STATUS REMOTEOWNER LINKNAME FLAGS OID$
------------ ------------ ---------- ------------------------------ ---------- ---------- --------------------------------
SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ---------- --------------- --------------- ------------
12083 12083 31 LUNAR_10GTEST 1 PART_1 19 01-AUG-12
01-AUG-12 01-AUG-12 1 0
6 65535
SQL>
SQL> update tab$ set cols=9 where obj#=12068;
1 row updated.
SQL>
SQL> update tab$ set cols=9 where obj#=12068;
1 row updated.
没有重启数据库是没有作用的：
SQL> desc lunar.LUNAR_10GTEST
Name Null? Type
---------------------------------------------------------------------- -------- ------------------------------------------------
OBJECT_ID NUMBER
OWNER VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
STATUS VARCHAR2(7)
TIMESTAMP VARCHAR2(19)
CREATED DATE
GENERATED VARCHAR2(1)
OBJECT_TYPE VARCHAR2(19)
SQL> desc lunar.LUNAR_TEST
Name Null? Type
---------------------------------------------------------------------- -------- ------------------------------------------------
OBJECT_ID NUMBER
OWNER VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
STATUS VARCHAR2(7)
TIMESTAMP VARCHAR2(19)
GENERATED VARCHAR2(1)
SQL> commit;
Commit complete.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 188743680 bytes
Fixed Size 1272720 bytes
Variable Size 130024560 bytes
Database Buffers 54525952 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.
SQL> desc lunar.LUNAR_10GTEST
Name Null? Type
---------------------------------------------------------------------- -------- ------------------------------------------------
OBJECT_ID NUMBER
OWNER VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
STATUS VARCHAR2(7)
TIMESTAMP VARCHAR2(19)
CREATED DATE
GENERATED VARCHAR2(1)
OBJECT_TYPE VARCHAR2(19)
SQL> desc lunar.LUNAR_TEST
Name Null? Type
---------------------------------------------------------------------- -------- ------------------------------------------------
OBJECT_ID NUMBER
OWNER VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
STATUS VARCHAR2(7)
TIMESTAMP VARCHAR2(19)
GENERATED VARCHAR2(1)
SP2-0642: SQL*Plus internal error state 2131, context 0:0:0
Unsafe to proceed
SQL>
SQL> select obj#,name,PROPERTY from sys.col$ where obj# in (12068,12069,12083,12083);
OBJ# NAME PROPERTY
---------- ------------------------------ ----------
12068 OBJECT_ID 0
12068 OWNER 0
12068 SUBOBJECT_NAME 0
12068 OBJECT_NAME 0
12068 STATUS 0
12068 TIMESTAMP 0
12068 SYS_C00007_12080105:01:20$ 32800
12068 GENERATED 0
12068 SYS_C00009_12080105:01:22$ 32800
9 rows selected
SQL>
SQL> select obj#,name,PROPERTY from sys.col$ where obj# in (12068,12069,12070,12082,12083,12084);
OBJ# NAME PROPERTY
---------- ------------------------------ ----------
12068 OBJECT_ID 0
12068 OWNER 0
12068 SUBOBJECT_NAME 0
12068 OBJECT_NAME 0
12068 STATUS 0
12068 TIMESTAMP 0
12068 SYS_C00007_12080105:01:20$ 32800
12068 GENERATED 0
12068 SYS_C00009_12080105:01:22$ 32800
12082 OBJECT_ID 0
12082 OWNER 0
12082 SUBOBJECT_NAME 0
12082 OBJECT_NAME 0
12082 STATUS 0
12082 TIMESTAMP 0
12082 CREATED 0
12082 GENERATED 0
12082 OBJECT_TYPE 0
18 rows selected
SQL>
[oracle@rh1 udump]$ sqlplus lunar/lunar
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 1 07:11:18 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> alter table lunar_10gtest set unused column created;
alter table lunar_10gtest set unused column object_type;
Table altered.
SQL>
Table altered.
SQL>
SQL>
SQL> select obj#,name,PROPERTY from sys.col$ where obj# in (12068,12069,12070,12082,12083,12084);
OBJ# NAME PROPERTY
---------- ------------------------------ ----------
12068 OBJECT_ID 0
12068 OWNER 0
12068 SUBOBJECT_NAME 0
12068 OBJECT_NAME 0
12068 STATUS 0
12068 TIMESTAMP 0
12068 SYS_C00007_12080105:01:20$ 32800
12068 GENERATED 0
12068 SYS_C00009_12080105:01:22$ 32800
12082 OBJECT_ID 0
12082 OWNER 0
12082 SUBOBJECT_NAME 0
12082 OBJECT_NAME 0
12082 STATUS 0
12082 TIMESTAMP 0
12082 SYS_C00007_12080107:11:19$ 32800
12082 GENERATED 0
12082 SYS_C00009_12080107:11:19$ 32800
18 rows selected
SQL>
SQL> select * from sys.tab$ where obj#='12068';
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
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----------
12068 0 0 0 9 0 0 0 0 1073741825 -------------------------------------- 9 9 537395232 0 736 2012/8/1 5:
SQL> select * from sys.tab$ where obj#='12082';
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
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----------
12082 0 0 0 7 0 0 0 0 1073741825 -------------------------------------- 9 9 537395232 0 736 2012/8/1 7:
SQL> select * from sys.obj$ where obj#='12082';
OBJ# DATAOBJ# OWNER# NAME NAMESPACE SUBNAME TYPE# CTIME MTIME STIME STATUS REMOTEOWNER LINKNAME FLAGS OID$ SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ---------- ------------------------------ ---------- ------------------------------ ---------- ----------- ----------- ----------- ---------- ------------------------------ -------------------------------------------------------------------------------- ---------- -------------------------------- ---------- ---------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----------
12082 31 LUNAR_10GTEST 1 2 2012/8/1 5: 2012/8/1 7: 2012/8/1 7: 1 0 6 3
SQL> select * from sys.obj$ where obj#='12068';
OBJ# DATAOBJ# OWNER# NAME NAMESPACE SUBNAME TYPE# CTIME MTIME STIME STATUS REMOTEOWNER LINKNAME FLAGS OID$ SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ---------- ------------------------------ ---------- ------------------------------ ---------- ----------- ----------- ----------- ---------- ------------------------------ -------------------------------------------------------------------------------- ---------- -------------------------------- ---------- ---------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----------
12068 31 LUNAR_TEST 1 2 2012/8/1 5: 2012/8/1 5: 2012/8/1 5: 1 0 6 3
SQL>
select name,obj#,intcol# from obj$ where name='LUNAR_10GTEST';
select cols from tab$ where obj#=12082;
update col$ set col#=intcol# where obj#=12082;
update tab$ set cols=cols+2 where obj#=12082;
--update col$ set name='C' where obj#=12082 and col#=3;
update col$ set property=0 where obj#=63779;
startup force;
SQL> select name,obj# from obj$ where name='LUNAR_10GTEST';
NAME OBJ#
------------------------------ ----------
LUNAR_10GTEST 12083
LUNAR_10GTEST 12084
LUNAR_10GTEST 12085
LUNAR_10GTEST 12086
LUNAR_10GTEST 12087
LUNAR_10GTEST 12082
6 rows selected.
SQL> select cols from tab$ where obj#=12082;
COLS
----------
7
SQL>
SQL> select * from sys.col$ where obj#=12082;
OBJ# COL# SEGCOL# SEGCOLLENGTH OFFSET NAME TYPE# LENGTH FIXEDSTORAGE PRECISION# SCALE NULL$ DEFLENGTH DEFAULT$ INTCOL# PROPERTY CHARSETID CHARSETFORM SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ---------- ------------ ---------- ------------------------------ ---------- ---------- ------------ ---------- ---------- ---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----------
12082 1 1 22 0 OBJECT_ID 2 22 0 0 1 0 0 0 0 0 0
12082 2 2 30 0 OWNER 1 30 0 0 2 0 852 1 0 0 30
12082 3 3 30 0 SUBOBJECT_NAME 1 30 0 0 3 0 852 1 0 0 30
12082 4 4 128 0 OBJECT_NAME 1 128 0 0 4 0 852 1 0 0 128
12082 5 5 7 0 STATUS 1 7 0 0 5 0 852 1 0 0 7
12082 6 6 19 0 TIMESTAMP 1 19 0 0 6 0 852 1 0 0 19
12082 0 7 7 0 SYS_C00007_12080107:11:19$ 12 7 0 0 7 32800 0 0 0 0 0
12082 7 8 1 0 GENERATED 1 1 0 0 8 0 852 1 0 0 1
12082 0 9 19 0 SYS_C00009_12080107:11:19$ 1 19 0 0 9 32800 852 1 0 0 19
9 rows selected
SQL>
SQL> select * from sys.col$ where obj#=12068;
OBJ# COL# SEGCOL# SEGCOLLENGTH OFFSET NAME TYPE# LENGTH FIXEDSTORAGE PRECISION# SCALE NULL$ DEFLENGTH DEFAULT$ INTCOL# PROPERTY CHARSETID CHARSETFORM SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ---------- ------------ ---------- ------------------------------ ---------- ---------- ------------ ---------- ---------- ---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----------
12068 1 1 22 0 OBJECT_ID 2 22 0 0 1 0 0 0 0 0 0
12068 2 2 30 0 OWNER 1 30 0 0 2 0 852 1 0 0 30
12068 3 3 30 0 SUBOBJECT_NAME 1 30 0 0 3 0 852 1 0 0 30
12068 4 4 128 0 OBJECT_NAME 1 128 0 0 4 0 852 1 0 0 128
12068 5 5 7 0 STATUS 1 7 0 0 5 0 852 1 0 0 7
12068 6 6 19 0 TIMESTAMP 1 19 0 0 6 0 852 1 0 0 19
12068 0 7 7 0 SYS_C00007_12080105:01:20$ 12 7 0 0 7 32800 0 0 0 0 0
12068 7 8 1 0 GENERATED 1 1 0 0 8 0 852 1 0 0 1
12068 0 9 19 0 SYS_C00009_12080105:01:22$ 1 19 0 0 9 32800 852 1 0 0 19
9 rows selected
SQL>
update col$ set col#=segcol# where obj#=12068;
update col$ set property=0 where obj#=12068;
SQL> update col$ set col#=segcol# where obj#=12068;
update col$ set property=0 where obj#=12068;
9 rows updated.
SQL>
9 rows updated.
SQL> commit;
Commit complete.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> select * from sys.col$ where obj#=12068;
OBJ# COL# SEGCOL# SEGCOLLENGTH OFFSET NAME TYPE# LENGTH FIXEDSTORAGE PRECISION# SCALE NULL$ DEFLENGTH DEFAULT$ INTCOL# PROPERTY CHARSETID CHARSETFORM SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ---------- ------------ ---------- ------------------------------ ---------- ---------- ------------ ---------- ---------- ---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----------
12068 1 1 22 0 OBJECT_ID 2 22 0 0 1 0 0 0 0 0 0
12068 2 2 30 0 OWNER 1 30 0 0 2 0 852 1 0 0 30
12068 3 3 30 0 SUBOBJECT_NAME 1 30 0 0 3 0 852 1 0 0 30
12068 4 4 128 0 OBJECT_NAME 1 128 0 0 4 0 852 1 0 0 128
12068 5 5 7 0 STATUS 1 7 0 0 5 0 852 1 0 0 7
12068 6 6 19 0 TIMESTAMP 1 19 0 0 6 0 852 1 0 0 19
12068 7 7 7 0 SYS_C00007_12080105:01:20$ 12 7 0 0 7 0 0 0 0 0 0
12068 8 8 1 0 GENERATED 1 1 0 0 8 0 852 1 0 0 1
12068 9 9 19 0 SYS_C00009_12080105:01:22$ 1 19 0 0 9 0 852 1 0 0 19
9 rows selected
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 188743680 bytes
Fixed Size 1272720 bytes
Variable Size 130024560 bytes
Database Buffers 54525952 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.
SQL> desc lunar.lunar_test
Name Null? Type
---------------------------------------------------------------------- -------- ------------------------------------------------
OBJECT_ID NUMBER
OWNER VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
STATUS VARCHAR2(7)
TIMESTAMP VARCHAR2(19)
SYS_C00007_12080105:01:20$ DATE
GENERATED VARCHAR2(1)
SYS_C00009_12080105:01:22$ VARCHAR2(19)
SQL>
SQL> select * from sys.col$ where obj#=12068;
OBJ# COL# SEGCOL# SEGCOLLENGTH OFFSET NAME TYPE# LENGTH FIXEDSTORAGE PRECISION# SCALE NULL$ DEFLENGTH DEFAULT$ INTCOL# PROPERTY CHARSETID CHARSETFORM SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ---------- ------------ ---------- ------------------------------ ---------- ---------- ------------ ---------- ---------- ---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----------
12068 1 1 22 0 OBJECT_ID 2 22 0 0 1 0 0 0 0 0 0
12068 2 2 30 0 OWNER 1 30 0 0 2 0 852 1 0 0 30
12068 3 3 30 0 SUBOBJECT_NAME 1 30 0 0 3 0 852 1 0 0 30
12068 4 4 128 0 OBJECT_NAME 1 128 0 0 4 0 852 1 0 0 128
12068 5 5 7 0 STATUS 1 7 0 0 5 0 852 1 0 0 7
12068 6 6 19 0 TIMESTAMP 1 19 0 0 6 0 852 1 0 0 19
12068 7 7 7 0 SYS_C00007_12080105:01:20$ 12 7 0 0 7 0 0 0 0 0 0
12068 8 8 1 0 GENERATED 1 1 0 0 8 0 852 1 0 0 1
12068 9 9 19 0 SYS_C00009_12080105:01:22$ 1 19 0 0 9 0 852 1 0 0 19
9 rows selected
SQL>
update col$ set name='AAA' where obj#=12068 and col#=7;
update col$ set name='BBB' where obj#=12068 and col#=9;
SQL> startup
ORACLE instance started.
Total System Global Area 188743680 bytes
Fixed Size 1272720 bytes
Variable Size 130024560 bytes
Database Buffers 54525952 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.
SQL> desc lunar.lunar_test
Name Null? Type
---------------------------------------------------------------------- -------- ------------------------------------------------
OBJECT_ID NUMBER
OWNER VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
STATUS VARCHAR2(7)
TIMESTAMP VARCHAR2(19)
SYS_C00007_12080105:01:20$ DATE
GENERATED VARCHAR2(1)
SYS_C00009_12080105:01:22$ VARCHAR2(19)
SQL> update col$ set name='AAA' where obj#=12068 and col#=7;
update col$ set name='BBB' where obj#=12068 and col#=9;
1 row updated.
SQL>
1 row 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 188743680 bytes
Fixed Size 1272720 bytes
Variable Size 130024560 bytes
Database Buffers 54525952 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.
SQL>
SQL> desc lunar.lunar_test
Name Null? Type
---------------------------------------------------------------------- -------- ------------------------------------------------
OBJECT_ID NUMBER
OWNER VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
STATUS VARCHAR2(7)
TIMESTAMP VARCHAR2(19)
AAA DATE
GENERATED VARCHAR2(1)
BBB VARCHAR2(19)
SQL>
create table lunar_test_part_2 as select * from lunar_test partition(PART_2);
alter table lunar_test exchange partition PART_2 with table lunar_test_part_2;
SQL> create table lunar_test_part_2 as select * from lunar_test partition(PART_2);
Table created.
SQL> alter table lunar_test exchange partition PART_2 with table lunar_test_part_2;
Table altered.
SQL>
SQL> alter table lunar_test set unused column aaa;
Table altered.
SQL> alter table lunar_test set unused column bbb;
Table altered.
SQL> desc lunar.lunar_test
Name Null? Type
---------------------------------------------------------------------- -------- ------------------------------------------------
OBJECT_ID NUMBER
OWNER VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
STATUS VARCHAR2(7)
TIMESTAMP VARCHAR2(19)
GENERATED VARCHAR2(1)
SQL> alter table lunar_test exchange partition PART_2 with table lunar_test_part_2;
Table altered.
SQL>
SQL> desc lunar_test_part_2
Name Null? Type
---------------------------------------------------------------------- -------- ------------------------------------------------
OBJECT_ID NUMBER
OWNER VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
STATUS VARCHAR2(7)
TIMESTAMP VARCHAR2(19)
GENERATED VARCHAR2(1)
SQL> desc lunar.lunar_test
Name Null? Type
---------------------------------------------------------------------- -------- ------------------------------------------------
OBJECT_ID NUMBER
OWNER VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
STATUS VARCHAR2(7)
TIMESTAMP VARCHAR2(19)
GENERATED VARCHAR2(1)
SQL>
SQL> select * from USER_UNUSED_COL_TABS;
TABLE_NAME COUNT
------------------------------ ----------
SQL>
SQL> /
TABLE_NAME COUNT
------------------------------ ----------
LUNAR_10GTEST 2
SQL>
715
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
