2016.8.17 server-side database user import and Export method EXPDP and IMPDP

Exp and IMP are client tool programs that can be used either on the client or on the server side.

EXPDP and IMPDP are server-side utilities that can only be used on the Oracle server and not on the client side.

IMP is only available for exp exported files, not for EXPDP export files, IMPDP only for EXPDP exported files, not for exp export files.

EXPDP Export command:

C:\USERS\ADMINISTRATOR>EXPDP Ndp_admin/[email protected] schemas=ndp2016_04 dumpfile=snndp.dmp Directory=data_ Pump_dir

Export the user ndp2016_04 to the D:\app\Administrator\admin\orcl\dpdump directory on the server.

You can use the system directory directly without creating a directory in advance Data_pump_dir

IMPDP Import command: Do not create the receiving user beforehand! Specify a new user name directly on import, the password is automatically set to be the same as the export user, very convenient!

Note User conversions are to be exported with Remap_schema=ndp2016_04:myuser users: Import Users

IMPDP Ndp_admin/[email protected] Directory=data_pump_dir dumpfile=snndp.dmp

Remap_schema=ndp2016_04:myuser Table_exists_action=replace

Table_exists_action=replace in fact useless, because the user's empty, if not, there are sequences, views, functions and other bad operation.

Further, do not export the DMP file, directly synchronize the user to this instance or other instances on the new user!!!

1, first create a database connection, you must first login Sqlplus

sql> Create public database link Ndpself connect to system identified by ORCL using ' ORCL '

Note: Ndpself is the new database link name and can be used casually. After connect to system identified by ORCL is the login name and password, the using ' ORCL ' is the actual instance name of the database on the server, not the connection name on the client

2, Exit Sqlplus, directly run the IMPDP command, create a new user directly and import data

IMPDP Ndp_admin/[email protected] network_link=ndpself schemas=ndp_admin remap_schema=ndp_admin:newndpadmin

Note Ndp_admin/[email protected] is the connection name on the client that executes this line of command, Schemas=ndp_admin indicates the export user in the export library

Further, the users of different servers and different instances are synchronized directly

Run the script on the C client and synchronize the naip2019_09 user in the instance on the 111,a server to the instance on Server B (the connection name on the B server that adds connection A to the connection named Transnaip,c B) under new09 user.

1. Create Dblink

Connect the B server on the C client first, establish the database connection above

Sqlplus Ndp_admin/[email protected]//NDP C-even B connection name

Create Public database link Tans_1 connect to system identified by ORCL using ' transnaip '//transnaip:b connecting a connection name

Exit Sqlplus

2. Import Users

IMPDP Ndp_admin/[email protected] network_link= trans_1 schemas=naip2016_09 remap_schema=naip2016_09:my09 transform= Oid:n

Add:

0, Db_link best with the system user connection, or may not be successful connection, the reason is unknown

1, multiple users can be imported with a statement, between users, separated

IMPDP Flt_admin/[email protected] network_link= fltself Sche

MAS=NVDUSER_FLT,NVDUSER_FMAP2 remap_schema=nvduser_flt:nn1,nvduser_fmap2:nn2 Transform=oid:n

2. Transform=oid:n must be added, otherwise the type cannot be created because Oralce will not allow the same ID for the type under the same instance

3, the newly created user password is not available, you must change the password of the new user afterwards alter user NN1 identified by Nvduser_flt

4. You can access user objects under different instances on different servers through the databse link, for example:

SELECT * from [email protected] trans_1 to access tables on other servers

5. View all Dblink connections

SELECT * from Dba_db_links;

6. Delete Dblink Connection

Drop public database link dblinkname;

2016.8.17 server-side database user import and Export method EXPDP and IMPDP