8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Script: recreate_table.sql
( Download Script )
-- ----------------------------------------------------------------------------------- -- File Name : https://oracle-base.com/dba/script_creation/recreate_table.sql -- Author : Tim Hall -- Description : Creates the DDL to recreate the specified table. -- Comments : Mostly used when dropping columns prior to Oracle 8i. Not updated since Oracle 7.3.4. -- Requirements : Requires a number of the other creation scripts. -- Call Syntax : @recreate_table (table-name) (schema-name) -- Last Modified: 28/01/2001 -- ----------------------------------------------------------------------------------- SET SERVEROUTPUT ON SET LINESIZE 100 SET VERIFY OFF SET FEEDBACK OFF SET TERMOUT OFF SPOOL ReCreate_&&1 PROMPT -- ---------------------------------------------- -- Reset the buffer size and display script title -- ---------------------------------------------- BEGIN DBMS_Output.Disable; DBMS_Output.Enable(1000000); DBMS_Output.Put_Line('-------------------------------------------------------------'); DBMS_Output.Put_Line('-- Author : Tim Hall'); DBMS_Output.Put_Line('-- Creation Date : ' || To_Char(SYSDATE,'DD/MM/YYYY HH24:MI:SS')); DBMS_Output.Put_Line('-- Description : Re-creation script for ' || Upper('&&1')); DBMS_Output.Put_Line('-------------------------------------------------------------'); END; / -- ------------------------------------ -- Drop existing FKs to specified table -- ------------------------------------ @Drop_FKs_Ref_Table &&1 &&2 -- ----------------- -- Drop FKs on table -- ----------------- @Drop_FKs_On_Table &&1 &&2 -- ------------------------- -- Drop constraints on table -- ------------------------- @Drop_Cons_On_Table &&1 &&2 -- --------------------- -- Drop indexes on table -- --------------------- @Drop_Indexes &&1 &&2 -- ----------------------------------------- -- Rename existing table - prefix with 'tmp' -- ----------------------------------------- SET VERIFY OFF SET FEEDBACK OFF BEGIN DBMS_Output.Put_Line(' '); DBMS_Output.Put_Line('PROMPT'); DBMS_Output.Put_Line('PROMPT Renaming ' || Upper('&&1') || ' to TMP_' || Upper('&&1')); DBMS_Output.Put_Line('RENAME ' || Lower('&&1') || ' TO tmp_' || Lower('&&1')); DBMS_Output.Put_Line('/'); END; / -- --------------- -- Re-Create table -- --------------- @Table_Structure &&1 &&2 -- --------------------- -- Re-Create constraints -- --------------------- @Table_Constraints &&1 &&2 -- --------------------- -- Recreate FKs on table -- --------------------- @FKs_On_Table &&1 &&2 -- ----------------- -- Re-Create indexes -- ----------------- @Table_Indexes &&1 &&2 -- -------------------------- -- Build up population insert -- -------------------------- SET VERIFY OFF SET FEEDBACK OFF DECLARE CURSOR cu_columns IS SELECT Lower(column_name) column_name FROM all_tab_columns atc WHERE atc.table_name = Upper('&&1') AND atc.owner = Upper('&&2'); BEGIN DBMS_Output.Put_Line(' '); DBMS_Output.Put_Line('PROMPT'); DBMS_Output.Put_Line('PROMPT Populating ' || Upper('&&1') || ' from TPM_' || Upper('&&1')); DBMS_Output.Put_Line('INSERT INTO ' || Lower('&&1')); DBMS_Output.Put('SELECT '); FOR cur_rec IN cu_columns LOOP IF cu_columns%ROWCOUNT != 1 THEN DBMS_Output.Put_Line(','); END IF; DBMS_Output.Put(' a.' || cur_rec.column_name); END LOOP; DBMS_Output.New_Line; DBMS_Output.Put_Line('FROM tmp_' || Lower('&&1') || ' a'); DBMS_Output.Put_Line('/'); -- -------------- -- Drop tmp table -- -------------- DBMS_Output.Put_Line(' '); DBMS_Output.Put_Line('PROMPT'); DBMS_Output.Put_Line('PROMPT Droping TMP_' || Upper('&&1')); DBMS_Output.Put_Line('DROP TABLE tmp_' || Lower('&&1')); DBMS_Output.Put_Line('/'); END; / -- --------------------- -- Recreate FKs to table -- --------------------- @FKs_Ref_Table &&1 &&2 SET VERIFY OFF SET FEEDBACK OFF BEGIN DBMS_Output.Put_Line(' '); DBMS_Output.Put_Line('-------------------------------------------------------------'); DBMS_Output.Put_Line('-- END Re-creation script for ' || Upper('&&1')); DBMS_Output.Put_Line('-------------------------------------------------------------'); END; / SPOOL OFF PROMPT SET VERIFY ON SET FEEDBACK ON SET TERMOUT ON