Solved

copy table structures

Posted on 2001-08-24
10
1,244 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Public Synonyms and Privileges 2 66
exp/imp 25 75
Oracle Listener Not Starting 11 44
Create file system directory from Oracle 10g 4 14
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

809 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