给你一个比较复杂的例子但是很有实用价值的：
LOCK TABLE ROLEINFO IN EXCLUSIVEMODE ;
--Make backup copy of original table
RENAMEROLEINFO TO ROLEINFO_X ;
--drop fKey constraint from KAIMU.ROLEFUNCTION
ALTER TABLE KAIMU.ROLEFUNCTION DROP CONSTRAINT FK_ROLEFUNCTION_ROLENAME ;
--drop fKey constraint from KAIMU.USERROLE
ALTER TABLE KAIMU.USERROLE DROP CONSTRAINT FK_USERROLE_ROLENAME ;
--Remove all other NAMED Table Constraints because
--they will cause errors when re-creating the table
--Remove original Primary Key now that FKeys are dropped
ALTER TABLE KAIMU."ROLEINFO_X" DROP CONSTRAINT PK_ROLEINFO_ROLENAME ;
Insert into KAIMU."ROLEINFO" (
ROLENAME, DESCRIBE )
SELECT
ROLENAME, DESCRIBE
FROM KAIMU."ROLEINFO_X" ;
Commit ;
--Recreate indexes EXCLUDING those created via Unique Constraints
--Recreate indexes EXCLUDING those created via Unique Constraints
-- Recreate the PKey Constraint
ALTER TABLE KAIMU."ROLEINFO" ADD
CONSTRAINT PK_ROLEINFO_ROLENAME
PRIMARY KEY ( ROLENAME )
USING INDEXPCTFREE 10
STORAGE(INITIAL 53248 NEXT 53248 PCTINCREASE 1 )
TABLESPACE PARTSBASE;
-- Recreate the FKey Constraints from the NEW table
-- Grant any privs associated with the old table
-- Recreate the FKey Constraints that reference the NEW table
ALTER TABLE KAIMU.ROLEFUNCTION ADD
CONSTRAINT FK_ROLEFUNCTION_ROLENAME
FOREIGN KEY (ROLENAME)
REFERENCES KAIMU.ROLEINFO (ROLENAME)
ON DELETE CASCADE ;
ALTER TABLE KAIMU.USERROLE ADD
CONSTRAINT FK_USERROLE_ROLENAME
FOREIGN KEY (ROLENAME)
REFERENCES KAIMU.ROLEINFO (ROLENAME)
ON DELETE CASCADE ;
-- Recompile any dependent objects
ALTER PACKAGEKAIMU.PRIVILEGEMANAGER COMPILE PACKAGE ;
--*** Recompile triggers for the new table
可以在CREATETABLE中做改动
不妨试一试，最好是将此作为一个事务提交
