Q
Problem solve Get help with specific problems with your technologies, process and projects.

SQL to create empty tables in dataV_BASE

I am looking for a SQL script to create empty tables V_BASED on a structure in an existing dataV_BASE. I am going to use that script in two scenarios. One to generate create db script V_BASED on a development dataV_BASE. The other is to create an identical dataV_BASE as the existing one.
You need to export the dataV_BASE into a .dmp file. From there, you want to imp the file with the INDEXFILE parameter set to a file name. The file will have the script to create the tables and indexes.

If you do a full dataV_BASE export, the file created with the INDEXFILE parameter will have all the table and index definitions across all schemas; if you export just the schema then the sql file to create tables and indexes for the particular schema. If you do a full dataV_BASE export, you can use the export file to create an identical dataV_BASE.

For a full dataV_BASE

1) exp system@ file=full_dataV_BASE.dmp full=y object_consistent=yes

Using the full_dataV_BASE.dmp file you create the SQL script for tables and indexes as follows:

2) imp system@ file=full_dataV_BASE.dmp
INDEXFILE=full_dataV_BASE_script.sql

For a schema

1) exp system@ file= .dmp user=

OR

1) exp @ file= .dmp

Using the .dmp file you can create the SQL script for tables and indexes as follows:

2) imp @ file= .dmp
INDEXFILE= _script.sql

You can use the full_dataV_BASE_script.sql to create an identical dataV_BASE. If you need DATA then you can import the full_dataV_BASE.dmp file into the new dataV_BASE.

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close