恢复表设置为unused的字段
SQL> conn system/oracle
Connected.
SQL> create user test identified by test;
User created.
SQL> grant connect to test;
Grant succeeded.
SQL> grant resource to test;
Grant succeeded.
SQL> create table test.test_unuse as
2 select * from dba_objects;
Table created.
SQL> conn test/test
Connected.
SQL> select count(*) from test_unuse;
COUNT(*)
———-
50706
SQL> desc test_unuse;
Name Null? Type
—————————————– ——– —————————-
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> alter table test_unuse set unused column object_id;
Table altered.
SQL> desc test_unuse;
Name Null? Type
—————————————– ——– —————————-
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
–开始恢复 基表的owner 为sys
SQL> conn sys/oracle as sysdba;
Connected.
SQL> select obj# from obj$ where name=’test_unuse’;
no rows selected
SQL> select obj# from obj$ where name=’TEST_UNUSE’;
OBJ#
———-
66274
SQL> col name for a30
SQL> select col#,intcol#,name from col$ where obj#=66274
COL# INTCOL# NAME
———- ———- ——————————
1 1 OWNER
2 2 OBJECT_NAME
3 3 SUBOBJECT_NAME
0 4 SYS_C00004_11040814:07:01$
4 5 DATA_OBJECT_ID
5 6 OBJECT_TYPE
6 7 CREATED
7 8 LAST_DDL_TIME
8 9 TIMESTAMP
9 10 STATUS
10 11 TEMPORARY
11 12 GENERATED
12 13 SECONDARY
13 rows selected.
SQL> select cols from tab$ where obj#=66274;
COLS
———-
12
SQL> update col$ set col#=intcol# where obj#=66274;
13 rows updated.
SQL> update tab$ set cols=13 where obj#=66274;
1 row updated.
SQL> update col$ set name=’OBJECT_ID’ WHERE obj#=66274 and col#=4;
1 row updated.
SQL> select obj#,col#,name,property
2 from col$
3 where obj#=66274;
OBJ# COL# NAME PROPERTY
———- ———- —————————— ———-
66274 1 OWNER 14336
66274 2 OBJECT_NAME 14336
66274 3 SUBOBJECT_NAME 14336
66274 4 OBJECT_ID 47136
66274 5 DATA_OBJECT_ID 14336
66274 6 OBJECT_TYPE 14336
66274 7 CREATED 14336
66274 8 LAST_DDL_TIME 14336
66274 9 TIMESTAMP 14336
66274 10 STATUS 14336
66274 11 TEMPORARY 14336
66274 12 GENERATED 14336
66274 13 SECONDARY 14336
13 rows selected.
SQL> update col$ set property=0 where obj#=66274;
13 rows updated.
SQL> commit;
Commit complete.
SQL> CONN TEST/TEST
SQL> desc test_unuse;
Name Null? Type
—————————————– ——– —————————-
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL>CONN SYSTEM/ORACLE
SQL>ALTER SYSTEM flush shared_pool;
SQL>CONN TEST/TEST
SQL> desc test_unuse;
Name Null? Type
—————————————– ——– —————————-
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> COL OBJECT_NAME FOR A10
SQL> select object_name,object_id from test_unuse WHERE ROWNUM<3;
OBJECT_NAM OBJECT_ID
———- ———-
ICOL$ 20
I_USER1 44
SQL> show user
USER is “TEST”
SQL> alter table test_unuse set unused column object_name;
Table altered.
SQL> alter table test_unuse drop unused column;
Table altered.
SQL> conn sys/oracle as sysdba
Connected.
SQL> select col#,intcol#,name from col$ where obj#=66274;
COL# INTCOL# NAME
———- ———- ——————————
1 1 OWNER
2 2 SUBOBJECT_NAME
3 3 OBJECT_ID
4 4 DATA_OBJECT_ID
5 5 OBJECT_TYPE
6 6 CREATED
7 7 LAST_DDL_TIME
8 8 TIMESTAMP
9 9 STATUS
10 10 TEMPORARY
11 11 GENERATED
12 12 SECONDARY
12 rows selected.
SQL>
微信扫一扫，打赏作者吧～上一篇： 修正ORACLE表的高水位线HWM
目前这篇文章有1条评论(Rss)评论关闭。