duplicate table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: duplicate table

  1. #1
    Join Date
    Jun 2003
    Posts
    132

    duplicate table

    Hello,

    How can I duplicate a table with all of its attributes?

    Thank you

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Create table new_tab as Select * from old_tab;

    This won't create the indexes tho'

  3. #3
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Nor the constraints (except for not null constraints).

    exp/imp can do the trick provided that you do the import into a different schema...
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Nor the grants, nor the triggers

    You could also export the table, then rename it, it's indexes, triggers etc., then import.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    There is one package called "DBMS_METADATA" Under that package u can use method "get_ddl".This method gives back the script of the table u have given as a parameter to it.Use this thing in connection with user_tables.This method gives scripts with all constraints and all grants.Spool it to some file and then just change the name of tables in file manually.I think this should solve your problem.

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    DBMS_METADATA is new in 9i.

  7. #7
    Join Date
    May 2001
    Location
    Seattle
    Posts
    9
    dbms_metadata.get_ddl does not extract all the indexes but other wise its performance is pretty good.

  8. #8
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by cyaniv
    dbms_metadata.get_ddl does not extract all the indexes but other wise its performance is pretty good.
    Can you elaborate on this ? Which indexes are you talking about ?
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  9. #9
    Join Date
    May 2001
    Location
    Seattle
    Posts
    9
    Indexes on the table whose definition you are trying to extract with
    dbms_metadata.get_ddl('TABLE','TABLE_NAME','SCHEMA')

  10. #10
    Join Date
    May 2001
    Location
    Seattle
    Posts
    9
    you can use
    dbms_metadata.get_dependent_ddl(object_type,base_object_name,base_object_schema,version,model,transf orm,object_count)
    to generate all the indexes for the table though.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width