Oracle 11g EXPDP IMPDP Detailed use method

A new feature in 11G that does not allocate segment when the table has no data to save space

Workaround such as:

Second, oracle10g later provided the EXPDP/IMPDP tool, also can solve this problem

1. How to export the EXPDP tool using:

First set up directory--expdir (import and export to be established, the corresponding E:/exp folder if not exist, need to build manually)

Export syntax:

If you only want to export some tables, you can use the Include

C:\USERS\GANJH>EXPDP eas/[email protected]_localhost Schemas=eas dumpfile=expdp2.dmp
Directory=expdir include=table:\ "like \ ' ct%\ ' \"

2. How to import IMPDP tools using:

First set up directory--Data_bak

Import Syntax:

C:\USERS\GANJH>IMPDP eas/[email protected]_localhost Directory=db_bak DUMPFILE=EXPDP.
DMP full=y;

Export from one user EXPDP IMPDP import to another user

If the user you want to import already exists:
1. Export user EXPDP User1/pass1 Directory=dumpdir dumpfile=user1.dmp
2. Import user IMPDP User2/pass2 directory=dumpdir dumpfile=user1.dmp remap_schema=user1:user2 EXCLUDE=USER full=y;

If the user you want to import does not exist:
1. Export user EXPDP User1/pass1 Directory=dumpdir dumpfile=user1.dmp
2. Import user IMPDP System/passsystem directory=dumpdir dumpfile=user1.dmp remap_schema=user1:user2 full=y;
3. User2 will be automatically established, and its permissions and use of the table space is the same as User1, but at this time with User2 unable to log in, you must modify the User2 password

---------------------------------------------------------Linux Environment----------------------------------------------- ----------------------

In Oracle11g , exp cannot export empty tables by default. It is troublesome to use traditional exp,imp for heterogeneous platform database migration. However, you can use EXPDP, IMPDP to migrate.

Migrating 64-bit Windows 2003 ORACLE11GR2 databases to 64-bit Linux redhat Enterprise 5 allows you to migrate data using EXPDP, IMPDP.

For example: Windows is a server , Linux is b server, database user is test, the data of a server is migrated to B server

On a server operation:

1.

Sql> Create directory Expdp_dir as ' D:\mzl\backup ';

Sql> Grant Read,write on directory Expdp_dir to test;

2. Create directory in Windows directory D:\mzl\backup

3. Export in DOS command window:

EXPDP test/test Directory=expdp_dir dumpfile=test.dmp logfile=testexpdp.log

To operate in Server B:

4, sql> Create directory Impdp_dir as '/home/oracle/impdp_dir ';

Sql> Grant Read,write on directory Impdp_dir to test;

1, in the system need to have/home/oracle/impdp_dir directory, in the Impdp_dir directory must have read and write permissions

(chmod 777 Impdp_dir)

5. Upload data from a server to the/home/oracle/impdp_dir directory of B server using FTP

Configure the server name of Server B in Server A to import data on a server

6. Export in DOS command window:

IMPPDP test/[email protected]_database directory=impdp_dir dumpfile=test.dmp logfile=testimpdp.log

(Note the case, if Test.dmp is capitalized in Linux, it must be changed to uppercase.) Linux is case sensitive)

A new feature in 11G that does not allocate segment when the table has no data to save space

Workaround such as:

Second, oracle10g later provided the EXPDP/IMPDP tool, also can solve this problem

1. How to export the EXPDP tool using:

First set up directory--expdir (import and export to be established, the corresponding E:/exp folder if not exist, need to build manually)

Export syntax:

If you only want to export some tables, you can use the Include

C:\USERS\GANJH>EXPDP eas/[email protected]_localhost Schemas=eas dumpfile=expdp2.dmp
Directory=expdir include=table:\ "like \ ' ct%\ ' \"

2. How to import IMPDP tools using:

First set up directory--Data_bak

Import Syntax:

C:\USERS\GANJH>IMPDP eas/[email protected]_localhost Directory=db_bak DUMPFILE=EXPDP.
DMP full=y;

Export from one user EXPDP IMPDP import to another user

If the user you want to import already exists:
1. Export user EXPDP User1/pass1 Directory=dumpdir dumpfile=user1.dmp
2. Import user IMPDP User2/pass2 directory=dumpdir dumpfile=user1.dmp remap_schema=user1:user2 EXCLUDE=USER full=y;

If the user you want to import does not exist:
1. Export user EXPDP User1/pass1 Directory=dumpdir dumpfile=user1.dmp
2. Import user IMPDP System/passsystem directory=dumpdir dumpfile=user1.dmp remap_schema=user1:user2 full=y;
3. User2 will be automatically established, and its permissions and use of the table space is the same as User1, but at this time with User2 unable to log in, you must modify the User2 password

---------------------------------------------------------Linux Environment----------------------------------------------- ----------------------

In Oracle11g , exp cannot export empty tables by default. It is troublesome to use traditional exp,imp for heterogeneous platform database migration. However, you can use EXPDP, IMPDP to migrate.

Migrating 64-bit Windows 2003 ORACLE11GR2 databases to 64-bit Linux redhat Enterprise 5 allows you to migrate data using EXPDP, IMPDP.

For example: Windows is a server , Linux is b server, database user is test, the data of a server is migrated to B server

On a server operation:

1.

Sql> Create directory Expdp_dir as ' D:\mzl\backup ';

Sql> Grant Read,write on directory Expdp_dir to test;

2. Create directory in Windows directory D:\mzl\backup

3. Export in DOS command window:

EXPDP test/test Directory=expdp_dir dumpfile=test.dmp logfile=testexpdp.log

To operate in Server B:

4, sql> Create directory Impdp_dir as '/home/oracle/impdp_dir ';

Sql> Grant Read,write on directory Impdp_dir to test;

1, in the system need to have/home/oracle/impdp_dir directory, in the Impdp_dir directory must have read and write permissions

(chmod 777 Impdp_dir)

5. Upload data from a server to the/home/oracle/impdp_dir directory of B server using FTP

Configure the server name of Server B in Server A to import data on a server

6. Export in DOS command window:

IMPPDP test/[email protected]_database directory=impdp_dir dumpfile=test.dmp logfile=testimpdp.log

(Note the case, if Test.dmp is capitalized in Linux, it must be changed to uppercase.) Linux is case sensitive)

2. Create temporary tablespace eas_t_nskftest_standard tempfile ' F:\oradata\oradata\orcltest\EAS_T_NSKFTEST_STANDARD ' Size 500M
4. Autoextend on next 50M maxsize 2048M extent management local;
6. Create tablespace eas_d_nskftest_standard datafile ' F:\oradata\oradata\orcltest\EAS_D_NSKFTEST_STANDARD ' size 2048M
8. Autoextend on next 100M maxsize 5000M extent management local;
10. Create temporary tablespace eas_d_nskftest_temp2 tempfile ' F:\oradata\oradata\orcltest\EAS_D_NSKFTEST_TEMP2 ' size 500M
12. Autoextend on next 50M maxsize 2048M extent management local;
14. Create user nskftest identified by Kingdee default tablespace eas_d_nskftest_standard temporary tablespace EAS_T_NSKFTEST _standard;
16. Grant CONNECT,RESOURCE,DBA to Nskftest;
18. IMPDP Nskftest/kingdee directory=db_bak dumpfile=xwy_schemas20150717.dmp remap_schema=nskf:nskftest remap_ Tablespace=eas_d_nskf_standard:eas_d_nskftest_standard Remap_tablespace=eas_t_nskf_standard:eas_t_nskftest_ Standard REMAP_TABLESPACE=EAS_D_NSKF_TEMP2:EAS_D_NSKFTEST_TEMP2 Exclude=user full=y;

Oracle 11g EXPDP IMPDP Detailed use method