This discussion is archived
10 Replies Latest reply: Jan 1, 2011 11:38 PM by 827870 RSS

Copying table structure.

449702 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    No. You'll need to re-create the index again
  • 2. Re: Copying table structure.
    LucasJellema Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    You can if you have SQL Developer.

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

    John
  • 6. Re: Copying table structure.
    787956 Newbie
    Currently Being Moderated
    IS there an PL/SQL example for copying indexes that anyone can share? Thanks
  • 7. Re: Copying table structure.
    orawiss Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    dbms_metadata.get_ddl is also e solutiion :=)
  • 10. Re: Copying table structure.
    827870 Newbie
    Currently Being Moderated
    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;