The new feature after oracle11g makes it possible to omit empty tables when allocating table spaces by default, to reduce the resource usage of tablespaces, so that empty tables are ignored when exporting user data using the EXP export of Oracle, which results in incomplete data and, of course, no way to export data using EXP , this has been mentioned before, do not do too much to repeat the http://jim123.blog.51cto.com/4763600/1934205. Using this method solves the empty table when exporting user data using EXP, but there is a more efficient way to import and export Oracle data using the EXPDP/IMPDP--ORACLE data pump, compared to Exp/imp expdp/ IMPDP is a more efficient tool for importing and exporting data, of course, here is the difference between EXPDP/IMPDP and exp/imp simple to say.
Unlike exp, when using EXPDP to export data, you need to use a DBA user into Oracle to execute a specified backup path before authorization can be read and write before the operation, the following steps:
sql> Create or replace directory dpdata1 as '/data/backup/oracle_backup ';D irectory created. Sql> Grant Read,write on the directory dpdata1 to U01; Grant succeeded. Sql> Grant Read,write on the directory dpdata1 to u02; Grant succeeded. Sql> select * from Dba_directories;
Here dpdatal specify the path to back up the exported data, and then authorize this directory to export users, it is important to note that this directory must be a group of Oracle users to ensure that the file permissions can be written, after the completion of the specified backup directory, you can start exporting data with EXPDP. When using EXPDP to export data, there will be Import.log log file generation, because here is the export of multiple users, I am here to specify the name of the log file
[Email protected] ~]$ EXPDP u01/passwword_u01 directory=dpdata1 dumpfile=u01.dmp LOGFILE=U01.LOGEXPDP u02/password_ u02 directory=dpdata1 dumpfile=u02.dmp Logfile=u02.log
After export, backup files and logs are generated under the specified backup directory.
Import using IMPDP Import, the specific use of the same method as IMP, but need to specify the backup directory, the operation is as follows:
[Email protected] oracle_backup]$ IMPDP u01/passwword_u01 directory=dpdata1 dumpfile=u02.dmp full=y[[email protected] oracle_backup]$ IMPDP u02/passwword_u02 directory=dpdata1 dumpfile=u02.dmp full=y
The import is done, but one thing to note here is that when importing data using IMPDP, if the user is not created in Oracle, the user will be automatically created after the import, so when importing, you should be careful not to make the data wrong, simple Oracle data Pump expdp/ IMPDP is roughly the case, the other has to follow the table name, query criteria, the whole library import export operation is basically the same, just need to modify a few parameters, here do not do too much explanation
This article from "Technical essay" blog, declined reprint!
Oracle Data Pump EXPDP/IMPDP