Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

copy table structures

Posted on 2001-08-24
10
Medium Priority
?
1,280 Views
Last Modified: 2007-11-27
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
Comment
Question by:solraccheffy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
10 Comments
 

Accepted Solution

by:
enayet earned 200 total points
ID: 6422431
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
 
LVL 2

Expert Comment

by:renuraj
ID: 6422440
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
 
LVL 1

Expert Comment

by:chdba
ID: 6422445
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:solraccheffy
ID: 6422456
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
 
LVL 1

Expert Comment

by:chdba
ID: 6422479
Didn't I answer this question already?

chdba
0
 
LVL 1

Expert Comment

by:dbasupport
ID: 6422485
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
 

Expert Comment

by:enayet
ID: 6422664
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
 

Author Comment

by:solraccheffy
ID: 6423150
You da man!
0
 

Expert Comment

by:enayet
ID: 6423283
Thankx.
I am glad that, everything worked like a charm :))
0
 

Author Comment

by:solraccheffy
ID: 6423294
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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

647 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question