首页 » ORACLE » 恢复表设置为unused的字段

恢复表设置为unused的字段

Unused column in the table is set to reuse
先说说unused的用处,当生产库中有一张特大的表,如果像删除一个字段drop column命令因是ddl,会给表级增加排它锁,所有用到该表的应用都无法查询,同样表很大,就会等待很长的时间,所以ORACLE推出了unuse,先把表的该字段设为unuse,等到一个闲的时间再去真正物理的删除,但如果你标为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>

打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Alton Shiever | #1
    2011-12-21 at 16:09

    Have you ever considered creating an e-book or guest authoring on other sites? I have a blog based on the same theme if you’re interested.