We help IT Professionals succeed at work.

import/export or ddl extract.

eyalshani asked
Medium Priority
Last Modified: 2012-05-04
I have a 350 GIGA database with about 5000 tables, 50 tablespaces, and 500 schemes (or users).
I want to create a new database for developers which will contain all the tables,tablespdces,users and other objects (views, constraints, etc') but without the data in the tables ( so it will be a lot smaller) , only in some few tables.
what are the for and against using full export/import with full = Y and rows = N (can I do it??), export the scemes one by one,
or by extracting the ddl of all the objects and tablespaces, and users. and create the entire database by ddl scripts.n (remember I have 5000 tables, views, triggers, constraints etc')
I know the Oracle docs, so please don't copy and paste it.

Watch Question

Off the top of my head here is what I would do

Create the 50 tablespaces on the new database by hand. (This is easy for me as I always create tablespaces / datafiles via a script which I then save away for moments like this !)
Do the same thing for the users.
If you don't already have scripts you could create your own scripts to doing something along the lines of spooling out the following select statement
select 'CREATE USER '||username||
from dba_users;

Again, you could write a query of dba_tablespaces and dba_data_files to write your tablespace creation scripts.

At least this means you are not having to create the scripts to create all the objects, as this is handled by the import

You could then do as you suggest by doing an export full=y rows=n

export the entire database with full=Y  and imports it with rows=N is the fastest way to do in your case.

The other possible ways are cloning the database, transportable tablespace, creating the database/schema from scripts etc.

I would recommend the export/import method , since you do not require any data , this is the fastest mathod.

hi there !!!
hope these details on EXP and IMP utility of Oracle will help u ...

Export provides a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations. Export extracts the object definitions and table data from an Oracle database and stores them in an Oracle binary-format Export dump file located typically on disk or tape.
Such files can then be transferred using FTP or physically transported (in the case of tape) to a different site. The files can then be used with the Import utility to transfer data between databases that are on machines not connected through a network. The files can also be used as backups in addition to normal backup procedures.

Access Privileges
To use Export, you must have the CREATE SESSION privilege on an Oracle database. To export tables owned by another user, you must have the EXP_FULL_DATABASE role enabled. This role is granted to all DBAs.

Example Export Session in Full Database Mode
Only users with the DBA role or the EXP_FULL_DATABASE role can export in full database mode. In this example, an entire database is exported to the file dba.dmp with all GRANTS and all data.

Command-Line Method
exp system/manager full=Y file=dba.dmp grants=Y rows=Y
This command will export a full database in a dba.dmp file.

Example Export Session in User Mode
Exports in user mode can back up one or more database users. For example, a DBA may want to back up the tables of deleted users for a period of time. User mode is also appropriate for users who want to back up their own data or who want to move objects from one owner to another. In this example, user SCOTT is exporting his own tables.

Command-Line Method
exp scott/tiger file=scott.dmp owner=scott grants=Y rows=Y compress=y
This command will export a User / Schema (Scott) in a scott.dmp file.

In this example, a DBA exports specified tables for two users.

Command-Line Method
exp system/manager tables=(scott.emp,blake.dept) grants=Y indexes=Y

The basic concept behind Import is very simple. Import inserts the data objects extracted from one Oracle database by the Export utility (and stored in an Export dump file) into another Oracle database. Export dump files can only be read by Import.
Import reads the object definitions and table data that the Export utility extracted from an Oracle database and stored in an Oracle binary-format Export dump file located typically on disk or tape

Example Import of Selected Tables for a Specific User
In this example, using a full database export file, an administrator imports the DEPT and EMP tables into the SCOTT schema.

Command-Line Method
imp system/manager file=dba.dmp fromuser=scott tables=(dept,emp)

Example Import of Tables Exported by Another User
This example illustrates importing the UNIT and MANAGER tables from a file exported by BLAKE into the SCOTT schema.

Command-Line Method
imp system/manager fromuser=blake touser=scott file=blake.dmp tables=(unit,manager)

Example Import of Tables from One User to Another
In this example, a DBA imports all tables belonging to SCOTT into user BLAKE's account.

Command-Line Method
imp system/manager file=scott.dmp fromuser=scott touser=blake tables=(*)


Hi !

The solutions for your problem are:

1. Transporting Tablespaces (Depends on the Version)
2. DB Cloning
3. Exp and Imp

But even thought things permit you can'nt use 1 and 2 in ur case because u said clearly that you don't want the records of 5000 tables, views etc.

If you follow 1 & 2 things would definelty work but again you have to say TRUNCATE or DELETE on the tables, which is again a time consuming effort.

Therefore the easiest and simple thing would be Install an Initial Database and do an import.  In this cause you don't have to worry of creating new tablespaces, users etc.  Because a full export would take care of it.  Only ensure that the datafiles don't exist in the new db locations.

Hope this would relieve u.

R. K.
The only problem with using exp rows=n and imp is that the INITIAL_EXTENT size declared for all these tables is likely to add up to a LOT of space, a lot more than you'll probably want if you don't plan to put a lot of data into it.  

You should go ahead and create the new database with all the tablespaces as recommended by RACHER above.  If your INITIAL_EXTENT situation is not a problem then you can go ahead with the import and then you're done.  If the INITIAL_EXTENT situation is going to use more space than you want, then you'll have to take some kind of action.

There are two mechanisms that are easily scripted.

You can extract the create table and create index commands from the export file, modify the initial_extent specifications, and then create the tables and indexes.  You can follow this step with a full import with IGNORE=Y, and that will bring in the constraints, the grants, the sequences and the roles.  [You don't have to extract the create index commands, you can use the index file feature of the import to capture those and then modify them.]  Before you start creating the tables and indexes you will have to explicitly create the users as recommended by RACHER.  

Extracting the CREATE TABLE commands is not too difficult under UNIX but it does demand a certain amount of UNIX experience.  Editting the INITIAL specs is a job for a UNIX awk script, and that too can be tricky.

Alternatively, you may try importing from the export script user by user.  In order to do this you will need to pre-create the users as recommended by RACHER but also you will need to pre-create the roles, if you are using any.

Then import from a particular user.  If this user uses a lot of disk space due to large INITIAL_EXTENT sizes then use the ALTER TABLE ... MOVE TABLESPACE .... STORAGE(...) command to change the initial extent size of thet able.  THIS WILL FAIL FOR TABLES WITH LONG FIELDS.  You will have to handle those separately.

Repeat for each user.