Datapump in Oracle 11g

Datapump import/export are used for data migration. You can export TABLES, SCHEMA, TABLESPACE or DATABASE and restore it on another server.

Quickfacts:

1. Datapump produces a dumpfile with extention .dmp or .imp
2. Privilege 'exp_full_database' is required to export full database
3. Privilege 'imp_full_database' is required to impport full database
4. In datapump, you can user a parfile to store your commands

Getting the help page

expdp help=y

Keyword                                  Description
------------------------------------------------------------------------------
DIRECTORY                          Directory object to be used for dumpfiles and logfiles.
DUMPFILE                             List of destination dump files (expdat.dmp),
                                                 e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
EXCLUDE                              Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FULL                                       Export entire database (N).
HELP                                       Display Help messages (N).
INCLUDE                                Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME                            Name of export job to create.
LOGFILE                                 Log file name (export.log).
NETWORK_LINK                  Name of remote database link to the source system.
NOLOGFILE                           Do not write logfile (N).
PARALLEL                             Change the number of active workers for current job.
PARFILE                                 Specify parameter file.
SCHEMAS                              List of schemas to export (login schema).
TABLES                                  Identifies a list of tables to export - one schema only.
TABLESPACES                     Identifies a list of tablespaces to export.
VERSION                              Version of objects to export where valid keywords are:
                                                (COMPATIBLE), LATEST, or any valid database version.
EXPORT

Example 1: Joe is a user on a database, Joe needs to export the whole database.
Solution:
c:\> sqlplus sys/**** as sysdba
     grant exp_full_database to joe;
     commit;
     exit;
c:\> expdp joe/**** full=y dumpfile=joe_database.dmp logfile=joe.log compression=n

^full database is exported
^Dumpfile will be created in the default location, which is 'c:\app\user\admin\orcl\dpdump'

Example 2: System needs to export Joe's schema with compression
Solution:
c:\> expdp system/**** full=n schemas=joe dumpfile=joe_schema.dmp logfile=joe1.log compression=y

^Joe's schema is exported
^Dumpfile will be created in the default location, which is 'c:\app\user\admin\orcl\dpdump'
^Dumpfile will be compressed

Example 3: Joe needs to export 2 tables (Student, Lecturer) to a location on disc ('c:\oracle')
Solution:
c:\> sqlplus joe/****
     create directory oracle as 'c:\oracle';
     commit;
     exit;
c:\> expdp joe/**** full=n tables=Student,Lecturer directory=oracle dumpfile=joe_tables.dmp logfile=joe2.log

^Joe's table are exported
^Dumpfile will be created in 'c:\oracle'
^There should be no space between the tables...e.g tables=(student1,student2,student3)

IMPORT

Example 4: Joe needs to import 2 tables (Student, Lecturer) from a directory called 'oracle'
Solution:
c:\> impdp joe/**** directory=oracle dumpfile=joe_tables.dmp tables=Student,Lecturer logfile=joe3.log

^Joe's table will be imported

Example 5: Joe needs to import 2 tables (Student, Lecturer) and change the tables name to 'student1' and 'student2'
Solution:
c:\> impdp joe/**** directory=oracle dumpfile=joe_tables.dmp logfile=joe3.log remap_table=joe.Student:student1,joe.Lecturer:student2

^Joe's table will be imported and renamed
^Tables must be separated by comma, the colon (:) must separate the oldname and new name

Example 6: System needs to import the whole database from a dumpfile on a flashdrive
Solution:
c:\> cd c:\app\user\admin\orcl\dpdump
Copy dumpfile to the folder
Launch impdp from this location
c:\app\user\admin\orcl\dpdump> impdp system/**** full=y dumpfile=database.dmp logfile=db.log

Example 7: System needs to import joe's schema from dumpfile (of full database) on a flashdrive
Solution:
c:\> cd c:\app\user\admin\orcl\dpdump
Copy dumpfile to the folder
Launch impdp from this location
c:\app\user\admin\orcl\dpdump> impdp system/**** full=n schemas=joe dumpfile=database.dmp logfile=db1.log

^Joe's schema will be imported


Example 8: System needs to import joe's schema and change name to DANIEL and also change the tablespace to daniel's tablespace
from dumpfile (of full database) on a flashdrive
Solution:
c:\> cd c:\app\user\admin\orcl\dpdump
Copy dumpfile to the folder
Launch impdp from this location
c:\app\user\admin\orcl\dpdump> impdp system/**** full=n dumpfile=database.dmp logfile=db2.log remap_schema=joe:daniel remap_tablespace=users:daniel_tbs

^Joe's schema will be imported and changed to Daniel
^Tablespace will be changed from users to Daniel_tbs
^you MUST physically create this tablespace and grant access to Daniel.

Example 9: System needs to import joe's schema using a parameter file
Solution:

create a parfile called 'joe.txt'

USERNAME=system/****
FULL=N
SCHEMA=(joe,daniel,class)
DIRECTORY=oracle
DUMPFILE=database.dmp
LOGFILE=db4.log

Save file and exit

c:\> impdp parfile=joe.txt

Parfile makes it more convenient to adjust datapump commands. It also help when you need to constantly run same commands.

Example 10: System needs to import joe's schema from dumpfile and replace all tables
Solution:
c:\> impdp system/**** full=n schemas=joe dumpfile=database.dmp logfile=db6.log table_exists_action=replace

^Joe's schema will be imported
^Joe's tables will be replaced if they already exist on the database prior to import

TEMPFILE ERROR ON ORACLE 10r2/LINUX

Hi folks. Here is one of the errors you can encounter on your job as a Database Java1istrator.
I was running a full export of a database (software) and I ran into this error. I hope sharing this error and the solution will help someone out there.

Environment
Linux 4.9
Oracle 10.2
Database name: software

Error
Linux Error: 2: No such file or directory
Additional information: 3

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 6234
----- PL/SQL Call Stack -----
object line object
handle number name
0x34ec54e8 14916 package body SYS.KUPW$WORKER
0x34ec54e8 6293 package body SYS.KUPW$WORKER
0x34ec54e8 2339 package body SYS.KUPW$WORKER
0x34ec54e8 6854 package body SYS.KUPW$WORKER
0x34ec54e8 1259 package body SYS.KUPW$WORKER
0x5803b23c 2 anonymous block
Job "SYSTEM"."SYS_EXPORT_FULL_06" stopped due to fatal error at 18:54:47


[oracle@software ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Dec 26 18:58:12 2012
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.3.0 - Production

SQL> desc v$tempfile
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)

SQL> select name, status from v$tempfile;

NAME STATUS
-------------------------------------
/u10/oradata/software/temp01.dbf ONLINE

/u10/oradata/software/java1temp01.dbf ONLINE

/u10/oradata/software/java2temp01.dbf ONLINE

/u10/oradata/software/java3temp01.dbf ONLINE

4 rows selected.

Here comes the errors . From here you have a clue where the problem is and what you need to do to resolve this issue

SQL> desc dba_tempfile;
ERROR:
ORA-04043: object dba_tempfile does not exist


SQL> desc dba_tempfiles;
ERROR:
ORA-04043: object dba_tempfiles does not exist


SQL> select FILE_NAME, TABLESPACE_NAME from dba_temp_files;
ERROR:
ORA-01116: error in opening database file 204
ORA-01110: data file 204: '/u10/oradata/software/java301.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
no rows selected

Solution

From this error, you can tell that there is a problem with a tempfle named '/u10/oradata/software/java301.dbf'. What we need to do is figure out how to get that tempfile online and alter the user presently using this tempfile to use the default tempfile from TEMP tablespace.

Crosscheck the default and temporary tablespaces in the database. This will give you an idea of where to go to get the issue resolved

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
JAVA1
JAVA1TEMP
JAVA2
JAVA2TEMP
JAVA3
JAVA3TEMP


Check for the user(s) presently using the JAVA3TEMP that has issues

SQL> select username from dba_users
2 where temporary_tablespace='JAVA3TEMP';

USERNAME
------------------------------
JAVA3

Change the temporary tablespace of user JAVA3 from JAVA3TEMP to TEMP. This will ensure that the user can use the default temporary tablespace

SQL> alter user JAVA3
2 temporary tablespace TEMP;

User altered.

Check to ensure that the user is properly altered to use TEMP instead of JAVA3TEMP

SQL> select username from dba_users
2 where temporary_tablespace='JAVA3TEMP';

no rows selected

Take the problematic tempfile offline

SQL> alter database tempfile '/u10/oradata/software/java3temp01.dbf' offline;

Database altered.

Please drop a comment if you have any questions. Thanks and watch this space for more posts!