Oracle exp imp Export Import instance

Oracle exp imp Export Import instance 1. the export tool exp1. it is the operating system's next executable file storage directory/ORACLE_HOME/binexp export tool to compress the data in the database into a binary system file. you can migrate www.2cto.com between different operating systems in three modes:. user Mode: export data of all user objects and objects. Java code exp username/password @ XE owner = xxx file = d: \ xxx_20130223.dmp; B. table mode: export all TABLES or specified TABLES. Java code exp username/password @ XE owner = xxx file = d: \ xxx_20130223.dmp TABLES = (SONIC); c. entire database: export all objects in the database. Www.2cto.com Java code exp username/password @ XE file = d: \ xxx_20130223.dmp FULL = Y; 2. import tool imp1. it is the next executable file storage directory in the operating system./ORACLE_HOME/bin imp import tool imports binary system files generated by EXP into the database. it has three modes:. user Mode: import all user objects and data in objects; Java code imp username/password @ XE fromuser = xxx touser = xxx file = d: \ xxx_20130223.dmp; B. table mode: import all or specified TABLES of the user; www.2cto.com Java code imp username/password @ XE fromuser = xxx file = d: \ xxx_20130223.dmp TABLES = (SONIC ); C. entire database: import all objects in the database. Java code imp username/password @ XE file = d: \ xxx_20130223.dmp FULL = Y; 3. procedure for importing and exporting an instance: oracle sqlplus connection Description: Java code cmd sqlplus/nolog conn sys/root @ IBMSPRO as sysdba // conn system/admin @ ORCL prompts "connected." The connection is successful. export the data file from A to the specified directory (the directory name is defined by yourself, as long as you can find it, it has nothing to do with the user name) java code exp xxx/xxx @ IBMS_40 owner = ibms file = d: \ ibms_40_20130223.dmp; 2. view the user's default tablespace on machine A to create the same tablespace during import. Java code select username, default_tablespace from dba_users where username = 'ibms'; 3. view the table space Java code select DISTINCT owner, tablespace_name from dba_extents where owner like 'ibms'; 4. view the data files corresponding to the tablespace so that appropriate data files can be created on B. Java code select file_name, tablespace_name from dba_data_files where tablespace_name in ('ibms-data', 'ibms-datab'); // 'ibms-data ', 'ibms-datab' is the tablespace found above. check the tablespace of machine B to see if there is a IBMS-DATA, IBMS-DATABJava code select name from v $ tablespace where name in ('ibms-data', 'ibms-datab '); if the two tablespaces are not found, they must be created. 6. The server to import data has no IBMS-DATA, IBMS-DATAB tablespace. CREATE Java code create tablespace "IBMS-DATA" logging datafile 'd: \ oracle \ app \ oracle \ product \ 10.2.0 \ oradata \ ibmspro \ mytbs01.dbf' SIZE 500 m autoextend on next 32 m extent management local; create tablespace "IBMS-DATAB" logging datafile 'd: \ oracle \ app \ oracle \ product \ 10.2.0 \ oradata \ ibmspro \ mytbs02.dbf 'size 500 m autoextend on next 32 m extent management local; 7. on server B, check whether the user already exists. select username from dba_users Where username = 'ibms'; there are two cases. If there is no such case, follow the [1] method, if there is a user create user ibms identified by Test2passwd default tablespace cmis temporary tablespace temp profile default according to [2] [1], if the user has drop user ibms cascade; (delete the user and all its objects) # if the user is connected, the drop operation will fail and the user's session must be killed first, then drop userSELECT 'alter system kill session ''' | SID | ',' | SERIAL # | ''' immediate; 'From V $ session where username = 'ibms'; # (if the user is connecting, build the command and kill Java code create user ibms identified by ibmspasswd default tablespace IBMS-DATA temporary tablespace IBMS-DATA IBMS-DATAB default; (create user) grant connect, resource to ibms; (Authorize) -- release all tablespaces -- grant unlimited tablespace to ibms; -- grant all permissions -- grant resource, connect, dba to ibms; 8. copy the file from machine A to machine B. 9. finally, import data to machine A using the user's Java code imp xxx/xxx @ IBMSPRO fromuser = ibms touser = ibms file = d: \ xxx_20130223.dmp ignore = y 4. possible import problems: (1) the imp and exp use different character sets. If the character sets are different, the import will fail. You can change the unix environment variables or the information about NLS_LANG in the NT Registry. after the import is complete, change it back. www.2cto.com (2) imp AND exp versions cannot be compatible with imp. You can successfully import files generated by exp of earlier versions, you cannot import files generated in later versions of exp. You can use imp username/password @ connect_string to describe: connect_string is in/ORACLE_HOME/network/admin/tnsnames. notes for names of local or remote databases defined by ora: UNIX:/etc/hosts: The Host Name of the local or remote database server win2000: winnt \ system32 \ drivers \ etc \ hosts