10 Replies Latest reply: Jan 2, 2011 1:38 AM by 827870 RSS

    Copying table structure.

    449702
      create table temp2 as select * from temp1 where 1=2

      above SQL will create new table temp2; having structure of temp1.
      Is there any way to copy indexes, and triggers from temp1 to temp2 in similar fashion?

      Thanks in Advance,

      Shailesh
        • 1. Re: Copying table structure.
          430321
          No. You'll need to re-create the index again
          • 2. Re: Copying table structure.
            Lucas Jellema
            There is no direct way - at least that I know of. However, if you are on Oracle 9i or above, the supplied package dbms_metadata can be very helpful.

            You can use dbms_metadata.ddl to extract the DDL for tables+constraints and indexes and triggers separately. After tweaking these CLOB values just a little to prevent name collisions, you can use EXECUTE IMMEDIATE in a simple PL/SQL statement to copy the objects you require.

            For example to copy the triggers:
            declare
            p_src_tbl varchar2(30):= 'PERSONS';
            p_trg_tbl varchar2(30):= 'PSN2';
            l_ddl varchar2(32000);
            begin
            for trg in (select trigger_name from user_triggers where table_name = p_src_tbl) loop
            l_ddl:= cast(replace(replace(dbms_metadata.get_ddl( 'TRIGGER', trg.trigger_name),p_src_tbl,p_trg_tbl),trg.trigger_name,substr(p_trg_tbl||trg.trigger_name, 1, 30)) as varchar2);
            execute immediate substr(l_ddl, 1, instr(l_ddl,'ALTER TRIGGER')-1);
            end loop;
            end;
            /

            best regards,

            Lucas
            • 3. Re: Copying table structure.
              690124
              You can if you have SQL Developer.

              Edited by: user2598164 on Mar 10, 2009 8:22 AM
              • 4. Re: Copying table structure.
                SomeoneElse
                You can if you have SQL Developer.
                Which didn't exist 4 years ago.
                • 5. Re: Copying table structure.
                  John Spencer
                  and it just calls dbms_metadata which did exist 4 years ago :-)

                  John
                  • 6. Re: Copying table structure.
                    787956
                    IS there an PL/SQL example for copying indexes that anyone can share? Thanks
                    • 7. Re: Copying table structure.
                      orawiss
                      yes
                      CREATE TABLE MY_TEST (ID NUMBER PRIMARY KEY) 
                      
                      SELECT *
                      FROM dba_indexes d WHERE d.table_name = 'MY_TEST'
                      
                      SELECT replace(dbms_metadata.get_ddl('TABLE','MY_TEST','SYS'),'"MY_TEST"','"MY_TEST2"' ) from dual;
                       
                      select replace(replace(dbms_metadata.get_ddl('INDEX','SYS_C0011003','SYS'),'"MY_TEST"','"MY_TEST2"' ),'SYS_C0011003','INDEX_MYTEST2') from dual;
                      • 8. Re: Copying table structure.
                        SomeoneElse
                        example for copying indexes
                        You don't copy indexes.

                        Like John Spencer said, use the package dbms_metedata to capture the DDL of the existing index, change what you need (table_name, index_name, etc) and execute it.
                        • 9. Re: Copying table structure.
                          orawiss
                          dbms_metadata.get_ddl is also e solutiion :=)
                          • 10. Re: Copying table structure.
                            827870
                            Here is a proc I created to mirror an oracle table (staging template table) and it's indexes.
                            I pass the original template table name and what I call a source_flag ( I need several copies to mirror several different sources) so I call load all the sources in parallel.
                            The reason I'm copying them is that I plan to in-parallel copy data from multiple sources to a a data warehouse where the sources are identical in structure. The plan is to bulk load the data to a transient (short lived) staging table, do a partition exchange (partitioned by list on the source_flag) then drop the transient staging table.
                            Remember, the table sturcture, constraints and indexes need to be identical between the staging table and the target locally indexed partitioned table.


                            CREATE OR REPLACE PROCEDURE SF.MIRROR_TABLE_INDEXES_SP (TAB_NAME IN VARCHAR2, SOURCE_FLG IN VARCHAR2) AUTHID DEFINER AS

                            TABLE_NM VARCHAR2(30) := TAB_NAME;
                            SRC_FLG VARCHAR2(30) := SOURCE_FLG;
                            SQLstr LONG;


                            CURSOR CUR1 IS
                            SELECT
                            'CREATE INDEX '||'SF.'||SUBSTR(TABLE_NAME||SRC_FLG||'_IDX'||ROWNUM,DECODE(SIGN(29 - LENGTH(TABLE_NAME||SRC_FLG||'_IDX'||ROWNUM)),-1,-30,- LENGTH(TABLE_NAME||SRC_FLG||'_IDX'||ROWNUM)))||' ON '||TABLE_NAME||SRC_FLG||' ('||SUBSTR(SYS_CONNECT_BY_PATH(COLUMN_NAME, ','),2)||') TABLESPACE INDX' AS script
                            FROM
                            (
                            SELECT
                            TABLE_NAME,
                            INDEX_NAME,
                            COLUMN_NAME,
                            COLUMN_POSITION,
                            COUNT(*) OVER ( PARTITION BY INDEX_NAME ) CNT,
                            ROW_NUMBER () OVER ( PARTITION BY INDEX_NAME ORDER BY INDEX_NAME,COLUMN_POSITION) SEQ
                            FROM
                            ALL_IND_COLUMNS
                            WHERE
                            INDEX_OWNER = 'SF' AND TABLE_NAME = TABLE_NM)
                            WHERE
                            SEQ=CNT
                            START WITH
                            SEQ=1
                            CONNECT BY PRIOR
                            SEQ+1=SEQ
                            AND PRIOR
                            INDEX_NAME=INDEX_NAME;

                            BEGIN

                            SQLstr := 'CREATE TABLE '||TABLE_NM||SRC_FLG||' AS SELECT * FROM '||TABLE_NM||' WHERE 1=0';
                            --DBMS_OUTPUT.PUT_LINE (SQLSTR);

                            EXECUTE IMMEDIATE SQLstr;

                            FOR indx_cursor in cur1
                            LOOP
                            SQLstr := indx_cursor.script;
                            -- DBMS_OUTPUT.PUT_LINE (SQLstr);
                            EXECUTE IMMEDIATE SQLstr;
                            END LOOP;

                            DBMS_OUTPUT.PUT_LINE ('Table '||TABLE_NM||' created with indexes');

                            END MIRROR_TABLE_INDEXES_SP;