Orcale data import, export, and database version query for different versions

View Oracle version

(1) connectDatabase, Execute select * from V $ instance
View version items

(2) Select * From product_component_version

(3) or query v $ version to view component-level information

Import and export of different versions:

In general, the DMP files dumped by exp of earlier versions can be imported into databases of the current version and later versions, but not to databases with lower exp versions.
1. For data of earlier versions to be used in later versions, the data can be exported in earlier versions. Later versions can be imported directly, which is compatible.
2. You can use a database of a lower version to export data of a later version.

Import and Export details:

 

1. Export tool exp

1. It is the directory for storing the next executable file in the operating system/ORACLE_HOME/bin
The exp export tool compresses data backup in the database into a binary system file, which can be migrated between different operating systems.
It has three modes:
A. User Mode: export data of all user objects and objects;
B. Table mode: export all or specified tables of the user;
C. entire database: export all objects in the database.

2. Example of using the export tool exp interactive command line

$ Exp test/test123 @ appdb
Enter array fetch buffer size: 4096> press ENTER
Export File: expdat. DMP> M. dmp generate exported file name
(1) E (ntire database), (2) U (SERS), or (3) T (Ables): (2) U> 3
Export table data (yes/no): Yes> press ENTER
Compress extents (yes/no): Yes> press ENTER
Export done in zhs16gbk Character Set and zhs16gbk nchar Character Set
About to export specified tables via conventional path...
Table (t) or partition (T: p) to be exported: (return to quit)> name of the table to be exported by cmamenu
.. Exporting table cmamenu 4336 rows exported
Table (t) or partition (T: p) to be exported: (return to quit)> name of the table to be exported n
Table (t) or partition (T: p) to be exported: (return to quit)> press ENTER
Export terminated successfully without warnings.

The following describes the Import and Export instances.
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 the inner_policy and policy_staff_relat tables in the database.
Exp aichannel/aichannel @ testdb2 file = D: \ data \ newsmgnt. dmp tables = (inner_policy, policy_staff_relat)
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 '201312 '\"

3. Example of export tool exp in non-interactive Command Line Mode

$ Exp Scott/tiger tables = (EMP, Dept) file =/directory/Scott. dmp grants = y
Note: The EMP and dept tables in the Scott user are exported to the file/directory/Scott. dmp.

$ Exp Scott/tiger tables = EMP query = where job = salesman and Sal <1600 file =/directory/scott2.dmp
Note: add the query condition job = salesman and Sal for exporting EMP to exp <1600

(But I am rarely using this method. It is more convenient to generate a temporary table for the records meeting the conditions, and then use exp)

$ Exp parfile = username. Par file =/directory1/username_1.dmp,/directory1/username_2.dmp filesize = 2000 m log =/directory2/username_exp.log

Parameter file username. Par content
Userid = username/userpassword
Buffer= 8192000
Compress = N
Grants = y

Description: username. Par is the parameter file used for exporting tool exp. The specific parameters can be modified as needed.
Filesize specifies the maximum number of bytes of the generated binary backup file
(It can be used to solve the limitation of 2 GB physical files in some operating systems, accelerate the compression speed, and facilitate the engraving of historical data CDs)

Ii. Import tool imp

1. It is the directory for storing the next executable file in the operating system/ORACLE_HOME/bin
The IMP import tool imports binary system files generated by exp into the database.

It has three modes:
A. User Mode: export data of all user objects and objects;
B. Table mode: export all or specified tables of the user;
C. entire database: export all objects in the database.
Only users with imp_full_database and DBA permissions can import the entire database.

IMP steps:
(1) Create Table (2) Insert data (3) create index (4) Create triggers, Constraints

2. Import tool imp interactive command line method example
$ Imp
Import: Release 8.1.6.0.0-production on Friday December 7 17:01:08 2001
(C) copyright 1999 Oracle Corporation. All rights reserved.
User name: Test
Password :****
Connect to: Oracle8i Enterprise Edition Release 8.1.6.0.0-64bit Production
With the partitioning Option
Jserver release 8.1.6.0.0-Production

Import file: expdat. DMP>/tmp/M. dmp
Input buffer size (minimum 8192) 30720>
Export files created by export: v08.01.06 in the normal path
Warning: This object is exported by test instead of the current user.
The zhs16gbk Character Set and zhs16gbk nchar character set have been imported.
Only list the content of the imported file (yes/no): No>
The creation error is ignored because the object already exists (yes/no): No> Yes
Import Permission (yes/no): Yes>
Import table data (yes/no): Yes>
Import the entire exported file (yes/no): No> Yes
. Importing the test object to Scott.
... Importing table cmamenu row 4336
Import is terminated successfully, but a warning is displayed.

3. Import tool imp non-interactive command line method example

$ Imp system/manager fromuser = Jones tables = (accts)
$ Imp system/manager fromuser = Scott tables = (EMP, Dept)
$ Imp system/manager fromuser = Scott touser = Joe tables = EMP
$ Imp Scott/tiger file = expdat. dmp full = y
$ Imp Scott/tiger file =/mnt1/t1.dmp show = n buffer = 2048000 ignore = n commit = y grants = y full = y log =/oracle_backup/log/imp_scott.log
$ Imp system/manager parfile = Params. dat
Params. dat content
File = DBA. dmp show = n ignore = n grants = y fromuser = Scott tables = (Dept, EMP)

4. Problems with the import tool imp

(1) The database object already exists.

Generally, tables, sequences, functions/processes, and triggers under the target data should be completely deleted before data is imported;
The database object already exists. If you use the default imp parameter, the Import fails.
If the ignore = y parameter is used, the data content in the exp file will be imported.
If the table has a constraint that contains a unique keyword, the table will not be imported if the condition is not met.
If the table does not have a constraint for a unique keyword, record duplication occurs.

(2) database objects are subject to primary and foreign key constraints

Data Import fails if it does not comply with the primary and foreign key constraints.
Solution: import the dependency table first.
The primary and foreign key constraints of the disable object to be imported. After the data is imported, enable them

(3) Insufficient Permissions

If you want to import user a's data to user B, user a must have the imp_full_database permission.

(4) failed to allocate storage when importing large tables (greater than 80 m)

Compress = Y for the default exp, that is, compress all data into one data block.
If there is no continuous big data block during import, the import will fail.
When exporting a large table larger than 80 Mb, remember to compress = n, and this will not cause this error.

(5) imp and exp use different character sets.

If the character set is different, the import will fail. You can change the Unix environment variable or the information about nls_lang in the NT Registry.
After the import is complete, change it back.

(6) imp and exp versions cannot be compatible

IMP can successfully import files generated by exp of earlier versions. Files generated by exp of later versions cannot be imported. We can use
$ Imp username/password @ connect_string
Connect_string is in/ORACLE_HOME/Network/admin/tnsnames. ora
Name of the defined local or remote database

Note:

UNIX:/etc/hosts to define the Host Name of the local or remote database server
Win98: windowing between windowshosts and IP addresses