Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Unanswered: Beginner: Copy Structure

    I'm learning oracle and I need to make a copy of a table structure in case I accidentally blow it away.
    In my past lives with Fox, Access it was pretty intuitive.
    Even though I'm using TOAD with Oracle 9i, I can't seem to find a way to do it.

    If someone could point me in the right direction, I would sure appreciate it.

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    I guess you learn on one schema (user login) so backup/restore of schema should be enought for you. To do so use export/import utilities

    export:
    exp <schema(login)>/<passwd>@<database SID> file=file_name log=log_file_name
    Code:
    exp scott/tiger file=emp.dmp log=emp.log
    import:
    imp <schema(login)>/<passwd>@<database SID> file=file_name
    Code:
    imp scott/tiger file=emp.dmp
    for more details see manual, or use google. there's a lot of stuff on internet. you told you wanna learn, so go ahead :-)

  3. #3
    Join Date
    Jan 2004
    Posts
    492
    Or alternatively:
    Code:
    create table table_backup as
    select *
    from table
    where 1= 2
    This will create a table with the structure, but no data. If you want the data too, remove the 1=2 part.
    Oracle OCPI (Certified Practicing Idiot)

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    TOAD will generate a script for you if you are really only interested in the structure, and not the data. Just right-click on the table in the Schema Browser, choose Create Script, and then select the options which include those feature you are most interested in (ie including indexes, referential integrity).

    Using the CTAS (create table as method) will not retain indexes, RI, etc, only the table structure itself.

    -Chuck

Posting Permissions

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