create table test1(id number,name varchar2(10));
alter table test1 add constraint pk_test1_id primary key(id);
drop table test1;
flashback table test1 to before drop;
闪回成功后,发现表test1上的主键约束变了名字了,能让它不变名字吗?或有什么变法让它变回原来的名字吗?
alter table test1 add constraint pk_test1_id primary key(id);
drop table test1;
flashback table test1 to before drop;
闪回成功后,发现表test1上的主键约束变了名字了,能让它不变名字吗?或有什么变法让它变回原来的名字吗?
SQL> select constraint_name,index_name,constraint_type from user_constraints
2 where table_name='T1' and owner='SCOTT';CONSTRAINT_NAME INDEX_NAME C
------------------------------ ------------------------------ -
SYS_C0023070 SYS_C0023070 P
SQL> alter index SYS_C0023070 rename to T1_PK;Index altered.SQL> select constraint_name,index_name,constraint_type from user_constraints
2 where table_name='T1' and owner='SCOTT'; --这步是更改索引的名称 你不用也行CONSTRAINT_NAME INDEX_NAME C
------------------------------ ------------------------------ -
SYS_C0023070 T1_PK P
SQL> alter table t1 rename constraint SYS_C0023070 to PK_T1;Table altered.SQL> select constraint_name,index_name,constraint_type from user_constraints
2 where table_name='T1' and owner='SCOTT';CONSTRAINT_NAME INDEX_NAME C
------------------------------ ------------------------------ -
PK_T1 T1_PK P
Oracle Flashback 技术 总结 中的Flashback Drop 部门, 讲的比较细..
http://blog.csdn.net/tianlesoftware/archive/2009/10/16/4677378.aspx------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群:62697716
SQL> drop table t1;Table dropped.SQL> flashback table t1 to before drop;Flashback complete.SQL> select constraint_name,index_name,constraint_type from user_constraints
2 where table_name='T1' and owner='SCOTT';CONSTRAINT_NAME INDEX_NAME C
------------------------------ ------------------------------ -
BIN$AFG3WJBzSzOTDZZ7bv5gUQ==$0 BIN$8Ffi65t9Qf2I74Hg5D201g==$0 P
[TEST1@kokooa]>create table t1(id number,name varchar2(10));Table created.[TEST1@kokooa]>alter table t1 add constraint pk_t1_id primary key(id);Table altered.[TEST1@kokooa]>drop table t1;Table dropped.[TEST1@kokooa]>flashback table t1 to before drop;Flashback complete.[TEST1@kokooa]>select constraint_name,index_name,constraint_type from user_constraints
2 where table_name='T1' and owner='TEST1';CONSTRAINT_NAME INDEX_NAME C
------------------------------ ------------------------------ -
BIN$EIBux8K6T8S9MpkBqcGDtQ==$0 BIN$I0OB2jsTS82bLT8IXFCnXA==$0 P[TEST1@kokooa]>alter index BIN$I0OB2jsTS82bLT8IXFCnXA==$0 rename to t1_pk;
alter index BIN$I0OB2jsTS82bLT8IXFCnXA==$0 rename to t1_pk
*
不能够采用改名的方法。
我前面考虑的不够全面
如果在表没被删除以前 表的约束和索引都是可以改名的
可是如果当被删除了 再从recyclebin中flashback回来
那么这个时候约束名和索引名都已经变成系统给命的名字
这个时候虽然它们存在 不过已经无法修改了下面的实验可以证明他们的存在
SQL> select * from t1;no rows selectedSQL> insert into t1 values(1,'wh');1 row created.SQL> insert into t1 values(1,'wp');
insert into t1 values(1,'wp')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.BIN$lC5b55ezSeiIHnfArnvg1w==$0) violated
[TEST1@kokooa]>select constraint_name,index_name,constraint_type from user_constraints
2 where table_name='T1' and owner='TEST1';CONSTRAINT_NAME INDEX_NAME C
------------------------------ ------------------------------ -
BIN$EIBux8K6T8S9MpkBqcGDtQ==$0 BIN$I0OB2jsTS82bLT8IXFCnXA==$0 P[TEST1@kokooa]>alter table t1 rename constraint "BIN$EIBux8K6T8S9MpkBqcGDtQ==$0" to "pk_t1";Table altered.[TEST1@kokooa]>select constraint_name,index_name,constraint_type from user_constraints
2 where table_name='T1' and owner='TEST1';CONSTRAINT_NAME INDEX_NAME C
------------------------------ ------------------------------ -
pk_t1 BIN$I0OB2jsTS82bLT8IXFCnXA==$0 P这是个朋友告诉我的。要加上双引号实践出真知啊
我刚才试的时候就只试了下单引号
是不行的
SQL> alter table t1 rename constraint 'BIN$lC5b55ezSeiIHnfArnvg1w==$0' to PK_T1;alter table t1 rename constraint 'BIN$lC5b55ezSeiIHnfArnvg1w==$0' to PK_T1
*
ERROR at line 1:
ORA-02250: missing or invalid constraint name
SQL> alter table t1 rename constraint "BIN$lC5b55ezSeiIHnfArnvg1w==$0" to PK_T1;
Table altered.