10gR2里drop一个表的实质

By cui hua on May 4, 2009 7:14 PM | No Comments | No TrackBacks

在如下的这个例子里，我先drop掉了表salxbc（salxbc上有一个包含4个字段的主键），然后将salxbc_new重新命名为salxbc，最后执行了purge dba_recyclebin。

CON$表是一个系统表，存放约束关系。

 

drop表salxbc的实质：

delete from "SYS"."CON$" where "OWNER#" = '56' and "NAME" = 'PK_SALXBC' and "CON#" = '32599' and "SPARE1" IS NULL and "SPARE2" IS NULL and "SPARE3" IS NULL and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAAcAAGAAAAmOAAc';

 

ALTER TABLE "IPRA"."SALXBC" RENAME CONSTRAINT "PK_SALXBC" TO "BIN$aQ3o4LGpYPrgQwoBDzhg+g==$0" ;

 

ALTER TABLE "IPRA"."SALXBC" RENAME CONSTRAINT "SYS_C0032594" TO "BIN$aQ3o4LGqYPrgQwoBDzhg+g==$0" ;

 

ALTER TABLE "IPRA"."SALXBC" RENAME CONSTRAINT "SYS_C0032595" TO "BIN$aQ3o4LGrYPrgQwoBDzhg+g==$0" ;

 

ALTER TABLE "IPRA"."SALXBC" RENAME CONSTRAINT "SYS_C0032596" TO "BIN$aQ3o4LGsYPrgQwoBDzhg+g==$0" ;

 

ALTER TABLE "IPRA"."SALXBC" RENAME CONSTRAINT "SYS_C0032597" TO "BIN$aQ3o4LGtYPrgQwoBDzhg+g==$0" ;

 

insert into "SYS"."RECYCLEBIN$"("OBJ#","OWNER#","ORIGINAL_NAME","OPERATION","TYPE#","TS#","FILE#","BLOCK#","DROPTIME","DROPSCN","PARTITION_NAME","FLAGS","RELATED","BO","PURGEOBJ","BASE_TS#","BASE_OWNER#","SPACE","CON#","SPARE1","SPARE2","SPARE3") values ('101254','56','PK_SALXBC','0','2','9','11','9

740',TO_DATE('04-5月 -09', 'DD-MON-RR'),'39011264961',NULL,'82','101252','101252','101254',NULL,NULL,'128','32599',NULL,NULL,NULL);

 

delete from "SYS"."OBJ$" where "OBJ#" = '101254' and "DATAOBJ#" = '101254' and "OWNER#" = '56' and "NAME" = 'PK_SALXBC' and "NAMESPACE" = '4' and "SUBNAME" IS NULL and "TYPE#" = '1' and "CTIME" = TO_DATE('16-4月 -09', 'DD-MON-RR') and "MTIME" = TO_DATE('16-4月 -09', 'DD-MON-RR') and "STIME" = TO_DAT

E('16-4月 -09', 'DD-MON-RR') and "STATUS" = '1' and "REMOTEOWNER" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0' and "OID$" IS NULL and "SPARE1" = '0' and "SPARE2" = '65535' and "SPARE3" IS NULL and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAASAABAAAMT8AA4';

 

ALTER INDEX "IPRA"."PK_SALXBC" RENAME TO "BIN$aQ3o4LGuYPrgQwoBDzhg+g==$0" ;

 

insert into "SYS"."RECYCLEBIN$"("OBJ#","OWNER#","ORIGINAL_NAME","OPERATION","TYPE#","TS#","FILE#","BLOCK#","DROPTIME","DROPSCN","PARTITION_NAME","FLAGS","RELATED","BO","PURGEOBJ","BASE_TS#","BASE_OWNER#","SPACE","CON#","SPARE1","SPARE2","SPARE3") values ('101252','56','SALXBC','0','1','8','21','5900

',TO_DATE('04-5月 -09', 'DD-MON-RR'),'39011264967',NULL,'30','101252','101252','101252',NULL,NULL,'128','0',NULL,NULL,NULL);

 

delete from "SYS"."OBJ$" where "OBJ#" = '101252' and "DATAOBJ#" = '101252' and "OWNER#" = '56' and "NAME" = 'SALXBC' and "NAMESPACE" = '1' and "SUBNAME" IS NULL and "TYPE#" = '2' and "CTIME" = TO_DATE('16-4月 -09', 'DD-MON-RR') and "MTIME" = TO_DATE('16-4月 -09', 'DD-MON-RR') and "STIME" = TO_DATE('

16-4月 -09', 'DD-MON-RR') and "STATUS" = '1' and "REMOTEOWNER" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0' and "OID$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3" IS NULL and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAASAABAAAMT8AA1';

 

ALTER TABLE "IPRA"."SALXBC" RENAME TO "BIN$aQ3o4LGvYPrgQwoBDzhg+g==$0" ;

drop table SALXBC

 

将salxbc_new重新命名为salxbc的实质：

delete from "SYS"."OBJ$" where "OBJ#" = '103604' and "DATAOBJ#" = '103604' and "OWNER#" = '56' and "NAME" = 'SALXBC_NEW' and "NAMESPACE" = '1' and "SUBNAME" IS NULL and "TYPE#" = '2' and "CTIME" = TO_DATE('04-5月 -09', 'DD-MON-RR') and "MTIME" = TO_DATE('04-5月 -09', 'DD-MON-RR') and "STIME" = TO_DA

TE('04-5月 -09', 'DD-MON-RR') and "STATUS" = '1' and "REMOTEOWNER" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0' and "OID$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3" IS NULL and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAASAAGAAACWJAA5';

 

rename SALXBC_NEW to SALXBC

insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('103604','103604','56','SALXBC','1',NULL,'2',TO_DATE('04-5月 -09', 'DD-MON-

RR'),TO_DATE('04-5月 -09', 'DD-MON-RR'),TO_DATE('04-5月 -09', 'DD-MON-RR'),'1',NULL,NULL,'0',NULL,'6','2',NULL,NULL,NULL,NULL);

 

insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('103633','103633','56','PK_SALXBC','4',NULL,'1',TO_DATE('04-5月 -09', 'DD-M

ON-RR'),TO_DATE('04-5月 -09', 'DD-MON-RR'),TO_DATE('04-5月 -09', 'DD-MON-RR'),'1',NULL,NULL,'0',NULL,'0','65535',NULL,NULL,NULL,NULL);

 

CREATE UNIQUE INDEX "IPRA"."PK_SALXBC" on "IPRA"."SALXBC"("SXCPRF","SXCFRM","SXCTKT","SXCCPN")tablespace IPRAIDX

alter table SALXBC

insert into "SYS"."CON$"("OWNER#","NAME","CON#","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('56','PK_SALXBC','33752',NULL,NULL,NULL,NULL,NULL,NULL);

 

purge dba_recyclebin的实质：

drop table "IPRA"." BIN$aQ3o4LGvYPrgQwoBDzhg+g==$0" purge;

 

delete from "SYS"."RECYCLEBIN$" where "OBJ#" = '101254' and "OWNER#" = '56' and "ORIGINAL_NAME" = 'PK_SALXBC' and "OPERATION" = '0' and "TYPE#" = '2' and "TS#" = '9' and "FILE#" = '11' and "BLOCK#" = '9740' and "DROPTIME" = TO_DATE('04-5月 -09', 'DD-MON-RR') and "DROPSCN" = '39011264961' and "PARTIT

ION_NAME" IS NULL and "FLAGS" = '82' and "RELATED" = '101252' and "BO" = '101252' and "PURGEOBJ" = '101254' and "BASE_TS#" IS NULL and "BASE_OWNER#" IS NULL and "SPACE" = '128' and "CON#" = '32599' and "SPARE1" IS NULL and "SPARE2" IS NULL and "SPARE3" IS NULL and ROWID = 'AAAAK+AABAAABUTAAA';

 

delete from "SYS"."RECYCLEBIN$" where "OBJ#" = '101252' and "OWNER#" = '56' and "ORIGINAL_NAME" = 'SALXBC' and "OPERATION" = '0' and "TYPE#" = '1' and "TS#" = '8' and "FILE#" = '21' and "BLOCK#" = '5900' and "DROPTIME" = TO_DATE('04-5月 -09', 'DD-MON-RR') and "DROPSCN" = '39011264967' and "PARTITION

_NAME" IS NULL and "FLAGS" = '30' and "RELATED" = '101252' and "BO" = '101252' and "PURGEOBJ" = '101252' and "BASE_TS#" IS NULL and "BASE_OWNER#" IS NULL and "SPACE" = '128' and "CON#" = '0' and "SPARE1" IS NULL and "SPARE2" IS NULL and "SPARE3" IS NULL and ROWID = 'AAAAK+AABAAABUTAAB';


delete from "SYS"."CON$" where "OWNER#" = '56' and "NAME" = ' BIN$aQ3o4LGpYPrgQwoBDzhg+g==$0' and "CON#" = '6602' and "SPARE1" IS NULL and "SPARE2" IS NULL and "SPARE3" IS NULL and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAAcAAFAAAAIFAAs';

 

delete from "SYS"."CON$" where "OWNER#" = '56' and "NAME" = ' BIN$aQ3o4LGqYPrgQwoBDzhg+g==$0' and "CON#" = '6602' and "SPARE1" IS NULL and "SPARE2" IS NULL and "SPARE3" IS NULL and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAAcAAFAAAAIFAAs';

 

delete from "SYS"."CON$" where "OWNER#" = '56' and "NAME" = ' BIN$aQ3o4LGrYPrgQwoBDzhg+g==$0' and "CON#" = '6602' and "SPARE1" IS NULL and "SPARE2" IS NULL and "SPARE3" IS NULL and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAAcAAFAAAAIFAAs';

 

delete from "SYS"."CON$" where "OWNER#" = '56' and "NAME" = ' BIN$aQ3o4LGsYPrgQwoBDzhg+g==$0' and "CON#" = '6602' and "SPARE1" IS NULL and "SPARE2" IS NULL and "SPARE3" IS NULL and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAAcAAFAAAAIFAAs';

 

delete from "SYS"."CON$" where "OWNER#" = '56' and "NAME" = ' BIN$aQ3o4LGtYPrgQwoBDzhg+g==$0' and "CON#" = '6602' and "SPARE1" IS NULL and "SPARE2" IS NULL and "SPARE3" IS NULL and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAAcAAFAAAAIFAAs';

 

Categories:

Tags:

No TrackBacks

TrackBack URL: http://dbsnake.com/cgi-bin/mt/mt-tb.cgi/31

Leave a comment