rem ----------------------------------------------------------------------- rem Filename: tabreorg.sql rem Purpose: Show how a table can be reorganized on-line using the rem DBMS_REDEFINITION package introduced in Oracle9i. rem Date: 22-May-2003 rem Author: Frank Naude, Oracle FAQ rem ----------------------------------------------------------------------- --------------------------------------------------------------------- -- Connect as SYSDBA to grant privs to scott... --------------------------------------------------------------------- connect / as sysdba grant execute on dbms_redefinition to scott; grant dba to scott; --------------------------------------------------------------------- -- Create new empty interim table... --------------------------------------------------------------------- connect scott/tiger CREATE TABLE emp_work AS SELECT * FROM emp WHERE 1=2; --------------------------------------------------------------------- -- Test if table can be redefined... --------------------------------------------------------------------- EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('scott', 'emp', 2); --------------------------------------------------------------------- -- Start table redefinition... --------------------------------------------------------------------- EXEC DBMS_REDEFINITION.START_REDEF_TABLE('scott', 'emp', 'emp_work', NULL, 2); -------------------------------------------------------------------- -- Add ALL constraints, indexes, triggers, grants, etc... --------------------------------------------------------------------- ALTER TABLE emp ADD PRIMARY KEY (empno); --------------------------------------------------------------------- -- Finish the redefinition process (this will swap the two tables)... --------------------------------------------------------------------- EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('scott', 'emp', 'emp_work'); --------------------------------------------------------------------- -- Drop the interim working table... --------------------------------------------------------------------- DROP TABLE emp_work;