Export table structure

Toad for Oracle
1

hi guys,

i have 10 tables in my database and i want to export all the table structure from one database and i want to import it to the another database…
can anyone help

thanks,
latheesh

2

Morning Latheesh,

i have 10 tables in my database and i want to export all the
table structure from one database and i want to import it to
the another database.......
can anyone help
Hopefully, I can.

In the schema browser, select tables. IN the Left Hand Side (LHS) select
the 10 you want to create elsewhere. Right click and select "create
script". Fill in the options and OK.

You now have a script that will recreate the selected table structure
that you can run in the other database. There may be other ways to do
this same thing in Toad, however, this method gives you something for
your audit trail and also for your version control system.

Equally, outside of toad you can use EXP with ROWS=NO to export the 10
tables, without data, and then IMP the resulting file into the other
database.

As with many things in life, there are many ways to skin a cat!

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

3

hi
thanks for your response. I have tried the first method as you mentioned…i have created an script file …now how to run this file in the destination db…can you please explain me step by step this process

4

Morning Latheesh,

thanks for your response. I have tried the first method as
you mentioned....i have created an script file ....now how
to run this file in the destination db....can you please
explain me step by step this process

In Toad:

  • Connect to the user in the other database where you wish to create the
    tables by running the script.

  • In the Editor, File->Open File.

  • Navigate to where the script is stored, and select it. You may need to
    change the file type to get the correct extension if you can't see the
    file you saved.

  • Perform a "sanity check" to make sure that you are in the correct
    database and in the correct schema before you proceed.....

  • Make sure you click in the editor window where the script has been
    loaded to. Do not highlight anything, just click.

  • Press F5 (or Editor->Execute as script).

  • Check the results.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

5

hi Norm,
i figured it how to open the file in the destination db but now i am facing a problem
let me explain it for your understanding
in the source db i have created a table “Test” and i have created a script file for the same. In the destination db i opened the file ,it shows some thing like this

DROP TABLE TEST CASCADE CONSTRAINTS;
CREATE TABLE TEST
(
SNO NUMBER,
NAME VARCHAR2(20 BYTE)
)
TABLESPACE PAGETL_DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

when i try to execute this query it showing me an error that …table or view does not exist …i think its because of the “DROP TABLE TEST CASCADE CONSTRAINTS;” the first line of the query …with this i cant able to execute …can you help me with this

regards,
latheesh

6

thanks, norm

i figured it …your answer is very helpful thanks for your response