Oracle Data Pump

The Data Pump utility was introduced from Oracle 10g R1 onwards. This boasts very high data transfer between databases. The Data Pump provides two utilities as mentioned below:

• Data Pump Export : expdp
• Data Pump Import : impdp

They might seem similar to the traditional exp and imp commands but they actually aren't! The export files from expdp cant be imported using the traditional import command imp. Also export files from exp cant be imported using impdp. The utilities are server related rather than client related with regard to the traditional ones.

Okay, enough of theories! Let's look at some examples:

Step 1: Log into the database as SYS user:

splus sys as sysdba

Step 2: Creating directory for dumping and setting permissions

create directory DUMPDIR as '/home/oracle/dumpdir';

Grant necessary permissions for the user who needs the export their schema:

grant read,write on directory dumpdir to user1;

Step 3: Exporting the schema

expdp user1/passwd DIRECTORY=DUMPDIR DUMPFILE=user1.expdp.dmp SCHEMAS=user1;

Step 4: Importing the schema

impdp user1/password DIRECTORY=DUMPDIR DUMPFILE=user1.expdp.dmp SCHEMAS=user1;

Oracle wont start: ORA-27300, ORA-27301, ORA-27302 (No space left on device)

Ever encountered this error while starting up your Oracle database? At first glance, as the error informs with the line familiar to most administrators "No space left on device", its not actually what you think it is!

Even if you have loads of HDD space and free memory left, this error can happen. Its because you don't have enough semaphores left. A semaphore is a value in a designated place in operating system (or kernel) storage that each process can check and then change (ref).

ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpsemsper

To fix this error, edit your /etc/sysctl.conf file and add/modify the below parameter to the values mentioned below:

kernel.sem = 250 32000 100 128

Now to apply these changes, run the below command:

sysctl -p

Once its done, try starting your database. It should start without the previous error.

Oracle Export and Import - The Traditional Way!

Even though its possible to pass the requires parameters during exp or imp command run time, its convenient to use single-line full commands when its to be used in an automated export/import scripts:

Complete Database Export:

To export the complete Oracle Database and import it to another machine, it requires SYSTEM user privileges. Here is how its done:

exp SYSTEM/password FULL=y FILE=completedb.dmp LOG=dba.log CONSISTENT=y

 Here:

Full=y : denotes a complete database export is required.
FILE=filename.dmp : denotes the file name of the export file.
LOG=logname.log : denotes the log file to be written.
CONSISTENT=y : denotes no further changes shall be affected in the export file.

To import the complete database in another machine, here is how to do it:

imp SYSTEM/password FULL=y FIlE=completedb.dmp commit=y

User Schema Export:

An user can export his/her own schema using the below command:

exp username/password FILE=userdb.dmp OWNER=username

To import the schema, the below command shall be used:

imp SYSTEM/password FROMUSER=username1 TOUSER=username2 FILE=userdb.dmp

Here "username1" is the name of the user who exported the schema and "username2" is the name of the user to which the schema needs to be imported.

User Schema Export (Table specific):

To export a schema with only selected tables: 

exp username/password FILE=userdb-tables.dmp TABLES=(table1,table2)

To import that export file back to another user, the below command shall be used:

imp SYSTEM/password FROMUSER=username1 TOUSER=username2 FILE=userdb-tables.dmp TABLES=(table1,table2)