Import and Export Oracle Data imp/exp? Batch File

Source: http://secyaher.blog.163.com/blog/static/389557720092935857930/

How to Write batch processing files to automatically import data to the oracle database Oracle database for automatic import and export has been widely used in many places. for example, after an Oracle database application system is developed, if you want to install the system on your computer, you need to import the initial data to your Oracle database.
The above functions need to be implemented in recent projects. It takes some time to search for the experiment and sort it out as follows: First, export the initial data file to be installed as initial. dmp, then install oracle on the customer's computing, and then execute the batch processing file impInitialData. bat (the same directory as the batch processing file also has createuser. SQL, initial. two dmp files) impInitialData. the content in bat is as follows:
@ Echo off
Sqlplus system/manager @ createuser
Imp system/manager file = initial. dmp fromuser = initialuser touser = initialuser ignore = y the createuser above is a file with the SQL extension. put the same directory as the batch file. the content is:
Create user initialuser identified by test;
Grant dba, resource, connect to initialuser;
Exit; briefly explained:
@ Echo off: the subsequent commands are not displayed in the Command Prompt window.
Sqlplus is a tool program of oracle. You must install oracle before using it.
System/manager is a default Administrator Account of oracle. If the account is locked or the password is changed, it cannot be used. You can replace it with another account/password;
@ Createuser: Execute the createuser. SQL file.
In this SQL file, create user xx identified by yy; indicates creating a user xx whose password is yy;
Grant dba, resource, connect to xx; Authorize xx dba, resource, connect permission imp is the import data command, and file in the following parameters indicates the database file to be imported; fromuser indicates the username of the original exported database file; touser indicates the username of the database to be imported; ignore indicates the general method to ignore errors, you can add other commands to perform more complex operations. import and Export Oracle Data imp/exp

Function: Oracle Data Import/Export imp/exp is equivalent to oracle data restoration and backup.
In most cases, you can use Oracle data import and export to back up and restore data (without causing data loss ).
 
Oracle has a benefit. Although your computer is not a server, you have installed an oracle client and established a connection.
(Use the local --> service name in net8 assistant to add the correct service name
In fact, you can think that the client and the server have built a path, and then the data can be pulled)
In this way, you can export data locally, although the server may be far away from you.
You can also import the dmp file locally to a database server in a distance.
With this function, you can build two identical databases, one for testing and the other for formal use.
 
Execution environment: it can be executed in SQLPLUS. EXE or DOS (command line,
When DOS can be executed, the \ ora81 \ BIN installation directory in oracle 8i is set to a global path,
The EXP. EXE and IMP. EXE files in this directory are used for import and export.
Oracle is written in java. I think the SQLPLUS. EXE, EXP. EXE, and IMP. EXE files are packaged class files.
SQLPLUS. EXE calls EXP. EXE and IMP. EXE to complete the import and export functions.
 
The following describes the Import and Export instances. You can import and export instances to the Import and Export instances, because the import and export operations are very simple.
Data export:
1. Export the database TEST completely, and the username system Password manager is exported to D: \ daochu. dmp.
Exp system/manager @ TEST file = d: \ daochu. dmp full = y
2. Export the tables of system users and sys users in the database
Exp system/manager @ TEST file = d: \ daochu. dmp owner = (system, sys)
3. Export tables table1 and table2 in the database
Exp system/manager @ TEST file = d: \ daochu. dmp tables = (table1, table2)
4. Export the data with the field filed1 in table 1 in the database starting with "00"
Exp system/manager @ TEST file = d: \ daochu. dmp tables = (table1) query = \"
Where filed1 like '000000 '\"

The above is a commonly used export. I am not very concerned about compression. I can use winzip to compress the dmp file.
However, add compress = y to the command above.

Data Import
1. import data from D: \ daochu. dmp to the TEST database.
Imp system/manager @ TEST file = d: \ daochu. dmp
The above may be a problem, because some tables already exist, and then it will report an error, the table will not be imported.
Add ignore = y to the end.
2. Import table 1 in d: \ daochu. dmp
Imp system/manager @ TEST file = d: \ daochu. dmp tables = (table1)
 
The preceding import and export operations are sufficient. In many cases, I completely delete the table and then import it.
 
Note:
If you have sufficient permissions, the system will prompt you.
Databases can be connected. You can use tnsping TEST to obtain whether the database TEST can be connected.

Previously, PL/SQL developer was used for implementation, but PL/SQL developer had two problems in exporting and importing data:
1. Separate and export table data and objects (stored procedures and views)
2. If the exported view has comments, errors often occur during import.

In fact, the PL/SQL Export and Import functions use the Oracle built-in tools exp and imp. These two tools provide a lot of functionality for exporting and importing data, PL/SQL only uses some of these features. The following is a brief introduction to them:

exp/imp usage and Examples
  
EXP/imp is a database backup and recovery tool. It can also be used to transmit data between different databases. The operating system of the two databases can be different. Exp can be used to export database data as binary files. IMP can be used to export data files are then imported to the same database or different databases.
  
There are four Database Export modes: Full (full Database Export), owner (User export), table (table export), and tablespace (table space export ).
  
Full (full Database Export): exports data of all users except ordsys, MDSYS, ctxsys, ordplugins, and lbacsys.
  
Owner (User export): exports all permissions and data of one or more users.
  
Tables (Table export): exports the structure and data of some tables (which can be of different users.
  
Tablespace (tablespace export): tablespace export data.
  
There are three methods to execute Exp: Interactive, command line, and parameter file
  
Interactive: You can directly enter the exp command to perform operations step by step according to the exp prompt. This is relatively simple.
  
Command Line: Enter the command line exp username/password parameter = value.
  
Parameter file: input the parameters required for command line exp username/password parfile = filename exp to be introduced from the parameter file.
  
The parameter can be repeated. The priority is that the command line is better than the parameter value after the parameter file. The parameter value overwrites the preceding parameter value.

Parameter Introduction

For more information, see exp help = y or imp helpe = y. The following describes some common parameters.

  
Buffer size if this value is set to 0, only one record can be imported at a time to export a large amount of data. You can set a large general default value.
  
You can specify one or more file names in the file export list with the default extension. dmp. The default exported file name is expdat. dmp.
  
When the maximum number of bytes of the exported file exceeds filesize exp, the next file name is obtained from the file list. If no, a new file name is displayed.
  
Help displays the export parameter help information
  
Inctype incremental Backup Types: complete (full), cumulative (cumulative) and incremental (incremental ).
  
Full or incremental export can only be performed in full Database Export mode. only incremental export of tables that have been modified since the last cumulative export or full export can be exported only from the last incremental accumulation or the table that has been modified since the full export is completely exported. All objects in the database are exported no matter whether or not and when they are modified.
  
Log: log File. Generally, if you set the log when importing data using a command line, you can see all the import information. If there is an error in the import information, you can import the database objects.
  
Show (imp): only used to display the content of the backup data file.
  
Full: If the value is Y, the default value is N.
  
Tables: You can specify one or more table names in the exported table list.
  
Fromuser (IMP): You can import data objects in one user mode in the exported file to another user mode. This parameter indicates the user mode in the exported file.
  
Touser (IMP ): this parameter indicates the user mode objects used for import to the database. For example, to export all the objects of caittmdba to a file in full database or user mode, you must change the user mode name to caittmdba1. At this time, fromuser is caittmdba., touser is caittmdba1

Export and Import instances

Export data:
D: \ oracle \ ora92 \ bin \ exp userid = caittmdba/cait@YSDB_192.168.1.2 owner = caittmdba file = E: \ % date %. dmp log = E: \ exp. Log
You can use this command to export all the data and objects of the user caittmdba to a file, and write the exported logs in E: \ exp. in the log file, the local service connected to Oracle is named ysdb_192.168.1.2, And the exported file is E: \ % date %. DMP. The file name is the current date. If you write this command to process files in batches and put it into a scheduled task, you can automatically back up the data.

Import data:
D: \ oracle \ ora92 \ bin \ imp userid = caittmdba/cait@YSDB_192.168.1.2 fromuser = caittmdba touser = caittmdba file = E: \ data. dmp log = E: \ imp. Log

Notes

Oracle 942 Errors often occur when exporting data. This error occurs only after Oracle is upgraded, this is a Bug of the Oracle Upgrade Program (Oracle's bugs and patches have been around for a long time and fail to be systematic, and there is little documentation and technical support, which is far from MS. If you read the articles related to Oracle's development experience, you will know from the very beginning ).
This Bug only requires the execution of the ORACLE_HOME/rdbms/admin/catpatch. SQL script. At the same time, you must increase the size of java_pool_size and shared_pool_size parameters, otherwise it will take a lot of time. Log on to SQL Plus as sysdba and execute the following command:
SQL> shutdown immediate;
SQL> startup migrate;
SQL> @? /Rdbms/admin/catpatch. SQL
The execution can be completed in about half an hour.