Import and export of Oracle Data and objects

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:

1 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 Bug and patch have been around for a long time, but it is not systematic, and there is little documentation and technical support, this is far from MS. If you read the articles related to Oracle's development experience, you will know that Oracle has been like this since the 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.