copy table structures

There are 3 tables in a database that I want to copy the structure from to another database, the thing is I don't have the ddl to create the tables.
Is there a way to get the tables (ddl) or do I have to do a describe on the original database get the table information and manually  "create table" in the new database
I want the table structure but not the data.
Any ideas?
solraccheffyAsked:
Who is Participating?
 
enayetConnect With a Mentor Commented:
Hi,

You can export the 3 tables with option rows=n.

exp user/pwd tables=(t1,t2,t3) rows=n file=exp.dmp

This will create a dmp file which will only have ddl, but no data. You can copy and edit that file to your new database and run from sqlplus to the tables.

There are another solution using 'select as ..' which I don't remeber now.

But the export method will work, try that.
0
 
renurajCommented:
Using COPY command u can copy the structure from one database to another.
Ex:
COPY FROM SCOTT/TIGER@BOSTONDB
  TO TODD/FOX@CHICAGODB
  CREATE NEWDEPT (DNUMBER, DNAME, CITY)
  USING SELECT * FROM DEPT;

* NEWDEPT is the new table name to be created in TODD schema and DEPT is the table existing in SCOTT schema.

Regards,
0
 
chdbaCommented:
I had done it the same way as enayet. Just a little bit more detailed:

On Windows:

exp80 [user]@[oracle_sid] tables=([table1],[table2],[...]) file=.\exp.dmp compress=y rows=n grants=n indexes=n
--
(may be you'll need to use also FROMUSER/TOUSER)
imp80 [user]@[oracle_sid] full=y file=.\exp.dmp ignore=y

On UNIX:

exp [user]@[oracle_sid] tables=\([table1],[table2],[...]\) file=./exp.dmp compress=y rows=n grants=n indexes=n
--
(may be you'll need to use also FROMUSER/TOUSER)
imp [user]@[oracle_sid] full=y file=./exp.dmp ignore=y

chdba
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
solraccheffyAuthor Commented:
I think I am leaning toward enayet comment, how would I do the import on these tables?
I like the comment renuraj gave, will keep that in mind for future purposes but it seems like a little more work........
0
 
chdbaCommented:
Didn't I answer this question already?

chdba
0
 
dbasupportCommented:
If you don't have a tool that can extract the ddl, then you could easily use the oracle exp (export) utility with the option rows=n and tables=table1,table2,table3 to export the tables without the rowdata.

Then you would just need to imp (import) to the other database.

Using this ensures any dependencies (eg constraints,indexes) are carried across

imp help=y
exp help=y

The indexfile option on import allows you to create ddl for the entire database.

Depending whether its dev or production, you should pay attention to storage i.e appropriate sizing of tables.

Check sql/administrators guides for an overview
0
 
enayetCommented:
There are two options:
1. import that exp.dmp file like this:
   imp user/pw press enter.
    And go interectively.
   or You can enter everything from a line command.

2. open that .dmp file in any text editor (vi or notepad)
   You will see the sql statement ('create table..') with full details. You can change anything from there and save it as a createTable.sql script and run it from a sqlplus.

like this:
step 1. vi exp.dmp
        make changes if needed.
Step 2. save as createTable.sql
step 3. Run the script from sqlplus
        sqlplus> @createtable.sql
        It will create the table with no data.

Unfortunately, I am very good giving detail info like others. But I can draw the outline, which definitely works.

Do some experiment, be brave!
0
 
solraccheffyAuthor Commented:
You da man!
0
 
enayetCommented:
Thankx.
I am glad that, everything worked like a charm :))
0
 
solraccheffyAuthor Commented:
Hold on a minute......I was playing around the export command....then I went for lunch.....I said you da man......then I tried it.....now I get an error.....

exp-00056: oracle error 12560 encountered
ora-12560: tns:protocol adpater error
exp-00000: export terminated unsuccessfully
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.