Oracle恢复表字段,恢复表设置为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>