oracle在线重定义(二)
_1
2 union all
3 Select 'ou_2' table_name,count(*) from ou_2;
TABLE_NAME COUNT(*)
---------- ----------
ou_1 51409
ou_2 0
SQL> exec dbms_redefinition.can_redef_table('SCOTT','OU_1',DBMS_REDEFINITION.CONS_USE_PK);
begin dbms_redefinition.can_redef_table('SCOTT','OU_1',DBMS_REDEFINITION.CONS_USE_PK); end;
ORA-12089: 不能联机重新定义无主键的表 "SCOTT"."OU_1"
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 137
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: 在 line 2
SQL> exec dbms_redefinition.can_redef_table('SCOTT','OU_1',DBMS_REDEFINITION.cons_use_rowid);
PL/SQL procedure successfully completed
SQL> exec dbms_redefinition.start_redef_table('SCOTT', 'OU_1', 'OU_2');
begin dbms_redefinition.start_redef_table('SCOTT', 'OU_1', 'OU_2'); end;
ORA-12089: 不能联机重新定义无主键的表 "SCOTT"."OU_1"
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 50
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: 在 line 2
SQL> exec dbms_redefinition.start_redef_table('SCOTT', 'OU_1', 'OU_2','',2);
PL/SQL procedure successfully completed
SQL> insert into ou_1 select * from dba_objects where rownum =1;
1 row inserted
SQL> commit
2 ;
Commit complete
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT', 'OU_1', 'OU_2');
PL/SQL procedure successfully completed
SQL> select col#,name,type# from SYS.COL$ WHERE OBJ#=(select object_id from dba_objects where object_name='OU_1' and owner = 'SCOTT');
COL# NAME TYPE#
---------- ------------------------------ ----------
1 OWNER 1
2 OBJECT_NAME 1
3 SUBOBJECT_NAME 1
4 OBJECT_ID 2
5 DATA_OBJECT_ID 2
6 OBJECT_TYPE 1
7 CREATED 12
8 LAST_DDL_TIME 12
9 TIMESTAMP 1
10 STATUS 1
11 TEMPORARY 1
12 GENERATED 1
13 SECONDARY 1
0 SYS_C00014_13041515:44:43$ 1
14 rows selected
此时可发现原表多了一列SYS_C00014_13041515:44:43$，而中间表是没有增加隐藏列的 。注意的是当通过desc tablename查看表字段时是查不到隐藏列的。
如何删除隐藏列
SQL> alter table ou_1 set unused ("SYS_C00014_13041515:44:43$");
Table altered
SQL> alter table ou_1 drop unused columns;
Table altered。