• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1284
  • Last Modified:

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?
0
solraccheffy
Asked:
solraccheffy
  • 3
  • 3
  • 2
  • +2
1 Solution
 
enayetCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now