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