用TOAD中Rebuild Table可以實現。
下面是生成的一段腳本，可以參考它的邏輯。
**********************************************************************
--Note: This rebuild script is not meant to be used when a possibility *
--exists that someone might try to access the table while it is*
--being rebuilt!
*
--
*
--Locks are released when the first DDL, COMMIT or ROLLBACK is *
--performed, so adding a "Lock table" command at the top of this *
--script will not prevent others from accessing the table for*
--the duration of the script.
*
--
*
-- One more important note:
*
--This script will cause the catalog in replicated environments*
--to become out of sync.
*
--**********************************************************************
--Table Rebuild script generated by TOAD
--
--Original table: BCB
--Backup of table: BCB_X
--Date: 2007/1/12 下午 12:33:50
--
SET LINESIZE 200
--
--Make backup copy of original table
ALTER TABLE RMAN.BCB RENAME TO BCB_X ;
-- Drop all user named constraints
ALTER TABLE RMAN.BCB_X DROP CONSTRAINT BCB_F1 ;
ALTER TABLE RMAN.BCB_X DROP CONSTRAINT BCB_U1 ;
--Recreate original table
CREATE TABLE RMAN.BCB
(
BDF_KEY
NUMBER
NOT NULL,
BCB_STAMPNUMBER
NOT NULL,
BCB_RECIDNUMBER
NOT NULL,
PIECE# NUMBER
NOT NULL,
BLOCK# NUMBER
NOT NULL,
BLOCKS NUMBER
NOT NULL,
CORRUPT_SCNNUMBER,
MARKED_CORRUPT VARCHAR2(1 BYTE)
NOT NULL,
CORRUPTION_TYPEVARCHAR2(9 BYTE)
);
--Copy the data from the renamed table
INSERT /*+ APPEND */
INTO RMAN.BCB INS_TBL
(BDF_KEY, BCB_STAMP, BCB_RECID, PIECE#,
BLOCK#, BLOCKS, CORRUPT_SCN, MARKED_CORRUPT,
CORRUPTION_TYPE)
SELECT
BDF_KEY, BCB_STAMP, BCB_RECID, PIECE#,
BLOCK#, BLOCKS, CORRUPT_SCN, MARKED_CORRUPT,
CORRUPTION_TYPE
FROM RMAN.BCB_X SEL_TBL ;
Commit ;
-- Drop all other user named indexes
-- (none)
--Recreate Indexes, Constraints, and Grants
CREATE UNIQUE INDEX RMAN.BCB_U1 ON RMAN.BCB
(BDF_KEY, BCB_RECID, BCB_STAMP)
LOGGING
TABLESPACE CAT
PCTFREE10
INITRANS 2
MAXTRANS 255
STORAGE(
INITIAL
64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE0
FREELISTS1
FREELIST GROUPS1
BUFFER_POOLDEFAULT
)
NOPARALLEL;
ALTER TABLE RMAN.BCB ADD (
CONSTRAINT BCB_U1
UNIQUE (BDF_KEY,BCB_RECID,BCB_STAMP)
USING INDEX
TABLESPACE CAT
PCTFREE10
INITRANS 2
MAXTRANS 255
STORAGE(
INITIAL
64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE0
FREELISTS1
FREELIST GROUPS1
));
ALTER TABLE RMAN.BCB ADD (
CONSTRAINT BCB_F1
FOREIGN KEY (BDF_KEY)
REFERENCES RMAN.BDF (BDF_KEY)
ON DELETE CASCADE);
--There are no FKeys that reference the new table to recreate.
--Recompile any dependent objects
ALTER PACKAGE"RMAN"."DBMS_RCVCAT" COMPILE PACKAGE ;
ALTER VIEW "RMAN"."RC_BACKUP_CORRUPTION" COMPILE ;
--There are no triggers to rebuild.
|
