Automatic Export import of Oracle databases

If you are creating a database locally and you want to import the database on the server locally, you can do this:

First create an export import. bat, and then edit the content:

Exp USERNAME/PASSWORD@XXXFILE=C:/AAA.DMP
Sqlplus system/yyyy @1.txt
Imp Username/password full=y file=c:/aaa.dmp

Create a "1.txt" file that reads:

Drop Userusername Cascade
/
Create userusername identified by password default tablespace tablespace_name
/
Grant Resource,connect Tousername
/
Quit
/

This enables you to automate the export of data on the server and then import it locally. I used to use Exit for the exit SQL command in 1.txt files instead of quit old unsuccessful.

If you want to import one or more of these tables, you can use imp username/pwd full=n table= (TA,TB) file=xxx.dmp

==================exp Imp EXPDP impdp==================

Exp:
1. Get Help
Exp help=y

2. The database test,test can also be fully exported in the form of "Ip:port/dbname", and the username System Password Manager is exported to D:/xxx.dmp
Expsystem/manager@testfile=d:/xxx.dmp full=y

3. Export the table of the system user and SYS user in the database
Expsystem/manager@testfile=d:/xxx.dmp wner= (System,sys)

4. Export tables A and B in the database
expusername/password@testfile= d:/xxx.dmp tables= (a,b)

5. Export the fields in the table table1 in the database filed1 the data that starts with "00"
Expusername/password@testfile=d:/xxx.dmp tables= (table1) query= "where filed1 like ' 0% '"

6. If the database is too large, you can export multiple files
Expusername/password@test file=d:/exp01.dmp,d:/exp02.dmp,d:/exp03.dmp filesize=2g buffer=10240000 log=a.log

7. Export table structure, not data, indexing and statistics, etc.
expusername/password@testfile= d:/xxx.dmp rows=n triggers=n index=n statistics=none
Note Before you export, look at the space in which you want to export the data, as follows:
Select sum (bytes)/1024/1024 from user_extents; The values that are generally viewed and the size of the DMP file to be exported are roughly equivalent, so you can determine how many DMP files to export.
You can set the buffer size to speed up the export speed, generally can be set to 10m, that is 102400000, such as
With log parameters, the export information can be stored in the log file.

Keyword description (default value)
USERID User name/password
Full export entire file (N)
Buffer Data buffers Size
Owner User Name list
File output files (expdat. DMP)
Table List of tables
COMPRESS Import to a zone (Y)
Length of RecordLength IO record
Grants Export Permission (Y)
Inctype Incremental Export Type
INDEXES Export Index (Y)
Record tracking incremental export (Y)
Direct directly path (N)
Triggers export triggers (Y)
Log screen output logs file
STATISTICS Analysis Object (Estimat
Rows export rows of data (Y)
Parfile parameter filename
Consistent cross table consistency (N)
CONSTRAINTS Export Constraints (Y

Object_consistent transactions that are set to read-only during object export (N)
FEEDBACK Show progress per x line (0)
FILESIZE the maximum size of each dump file
FLASHBACK_SCN is used to set the session snapshot back to the previous state of the SCN
Flashback_time is used to get the SCN time closest to the specified time
Select clause used by QUERY to export a subset of tables
Resumable hangs (N) when it encounters a space-related error
Resumable_name the text string used to identify recoverable statements
Resumable_timeout Resumable's Waiting time
Tts_full_check perform a full or partial dependency check on TTS
tablespaces List of table spaces to export
Transport_tablespace export of removable tablespace metadata (N)
TEMPLATE Call IAS-mode exported template name

Imp
1. Get Help
Imp help=y

2. Import a complete database
Imp system/manager file=bible_db log=dible_db full=y ignore=y

3. Import one or a group of all tables, indexes, and other objects to which the specified user belongs
Imp System/manager file=c:/xxx.dmp log=c:/a.log fromuser=username
or Impusername/password@testfile=username Log=username
4. Import the data that a user belongs to another user
Imp system/manager file=c:/xxx.dmp log=c:/a.log fromuser=u1 touser=u2
Imp System/manager file=c:/xxx.dmp log=c:/a.log fromuser= (U1,U2) touser= (U3,U4)

5. Import a table
Imp system/manager file=c:/xxx.dmp log=c:/a.log fromuser=username tables= (a,b)

6. Import from multiple files
Imp System/manager file= (C:/EXP01.DMP,C:/EXP02.DMP,C:/EXP03.DMP,C:/EXP04.DMP)
Log=paycheck, filesize=1g full=y

7. Use parameter file
Imp System/manager Parfile=bible_tables.par
Bible_tables.par parameter file:
#Import the sample tables used for the oracle8i Database Administrator ' s
Bible. Fromuser=username touser=username_copy File=username Log=username_import

In the case of large amount of data need to set the buffer and commit parameters, buffer generally around 10m, that is 10240000.
Setting ignore can ignore imported errors, avoid errors and abort the import operation

Sometimes the following error occurs when you import a partitioned table :
IMP-00003: ORACLE error 20001 encountered
Ora-20001:invalid or inconsistent input values
Ora-06512:at "SYS. Dbms_stats ", Line 6053
Ora-06512:at Line 1
Imp-00017:following statement failed with ORACLE error 20001:
"BEGIN dbms_stats." Set_table_stats (NULL, ' HCL ', ' "sys_subp5290" ")
", null,null,-1,-1,-1,0); End; "
It is possible to import the data after the import of statistics error, the test found that the amount of data exported is not the case of the fault, for specific reasons. Avoid this type of error by adding Statistics=none to the import, which means that statistics are no longer imported:
IMP/C full=n file=h.dmp tables= (HCL) buffer=12800000 Statistics=none log=a.log commit=y
For large import data, an import error occurs if the buffer is set too small: The IMP-00032:SQL statement exceeds the buffer length
Tables or indexes in this imported user do not perform statistical analysis.

Imp parameters are as follows:
Keyword description (default value)
USERID User name/password
Full import entire file (N)
Buffer Data buffers Size
List of Fromuser owner user names
File input files (expdat. DMP)
Touser List of user names
Show lists only the contents of the file (N)
Table List of tables
IGNORE Ignore creation error (N)
Length of RecordLength IO record
Grants Import permission (Y)
Inctype Incremental Import Type
INDEXES Import Index (Y)
Commit-commit array insert (
Rows Import data row (Y)
Parfile parameter filename
Log screen output logs file
CONSTRAINTS Import Limit (Y)
DESTROY Coverage Table space data file (N)
Indexfile writes table/index information to the specified file
Skip_unusable_indexes Skip maintenance for index not available (N)
FEEDBACK Show progress per x line (0)
Toid_novalidate to skip validation of the specified type ID
FILESIZE the maximum size of each dump file
STATISTICS always import the estimated statistical information
Resumable hangs (N) when it encounters a space-related error
Resumable_name a text string used to identify recoverable statements
Resumable_timeout
Resumable Time to wait
COMPILE compilation process, package and function (Y)
Streams_configuration the general metadata for the import stream (Y)
Streams_instantiation Import Stream instantiation metadata (N)

The

Oracle database 10g introduces the latest data pump technology that enables DBAs or developers to quickly move database metadata (object definitions) and data to another Oracle database.
Data pump the role of export import (EXPDP and IMPDP)
1, Implementing logic backup and logical recovery.
2, move the object between database users.
3, moving objects between the databases
4 to implement tablespace removal.
Data pump export import differs from traditional export import
before 10g, traditional export and import using exp tools and IMP tools, starting with 10g, not only retained the original exp and IMP tools, Data pump Export Import tools EXPDP and IMPDP are also provided. Matters to be noted when using EXPDP and IMPDP;
Exp and IMP are customer segment tool programs that can be used either on the client or on the server.
EXPDP and IMPDP are service-side tools that are used only on the Oracle server and cannot be used on clients
IMP applies only to exp export files, not to EXPDP export files, IMPDP only to EXPDP export files, It does not apply to the EXP export file.
Data pump export includes export table, export scheme, export table space, export database 4 ways.

EXPDP:
When using the EXPDP tool, its dump file can only be stored in the corresponding OS directory of the directory object, not directly the OS directory where the dump file resides. Therefore, when using the EXPDP tool, You must first establish a directory object. And you need to grant database users permission to use directory objects.
Sql>conn/as sysdba
sql>create DIRECTORY dump dir as ' d:dump ';
Sql>grant READ, Wirte on DIRECTORY dump_dir to test;
1, export table
EXPDP test/test directory=dump_dir dumpfile=tab.dmp tables=dept,emp
2, export scheme
EXPDP test/test Directory=dump_dir dumpfile=schema.dmp
Schemas=system,test
3. Export table Space
EXPDP System/manager DIRECTORY=dump_ Dir dumpfile=tablespace.dmp
Tablespaces=user01,user02
4, export database
EXPDP System/manager directory=dump_dir Dumpfile=full.dmp Full=y

IMPDP:
IMPDP test/test Directory=dump_dir dumpfile=tab.dmp
Tables=dept,emp
IMPDP system/manage Directory=dump_dir dumpfile=tab.dmp
Tables=test.dept,test.emp Remap_schema=test:system
The first method represents importing the Dept and EMP tables into the test scenario, and the second represents the system scenario in which the Dept and EMP tables are imported.
Note that if you want to import a table into another scenario, you must specify the Remap schema option.
2, Import scheme
IMPDP test/test Directory=dump_dir dumpfile=schema.dmp
Schemas=test
IMPDP System/manager Directory=dump_dir dumpfile=schema.dmp
Schemas=test Remap_schema=test:system
3, Import table space
IMPDP System/manager Directory=dump_dir dumpfile=tablespace.dmp
Tablespaces=user01
4, Import the database
IMPDP System/manager Directory=dump_dir dumpfile=full.dmp full=y

http://space.itpub.net/559237/viewspace-448811