Solved

copy table structures

Posted on 2001-08-24
10
1,264 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 50 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

719 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