1, EXP / IMP logic backup import / export is the oldest two command line tools survived in Oracle, in fact, I never think that EXP / IMP is a good backup method, the correct statement is that Exp / IMP can only be A good dump tool, especially in the dump of small databases, migration of table space, extraction, detection logic, and physical conflict, etc. Of course, we can also put it as a logical auxiliary backup after the physical backup of a small database, is also a good suggestion. For increasing databases, especially the TB-level database, and more and more data warehouses, Exp / IMP is getting stronger, this time, database backups turn to RMAN and third-party tools. Let's take a brief introduction to the use of Exp / IMP.
i, how to use
Exp Parameter_name = Valueor Exp Parameter_name = (Value1, Value2 ...) As long as you enter the parameter help = y, you can see all help, such as: c: /> set nls_lang = simplified Chinese_china.zhs16gbkc: /> exp -helpexport: release 8.1.6.0 .0 - Production on Thursday, April 10 19:09:21 2003 (C) Copyright 1999 Oracle Corporation. All Rights Reserved.
By entering the exp command and username / password, you can post the user / password: instance: EXP Scott / Tiger
Alternatively, you can also control the "Export" operating mode by entering the EXP command with various parameters. To specify parameters, you can use keywords:
Format: exp keyword = value or keyword = (value1, value2, ..., value) instance: exp Scott / Tiger GRANTS = Y Tables = (EMP, DEPT, MGR) or TABLES = (T1: P1, T1: P2) If T1 is the partition table UserID must be the first parameter in the command line. Keyword description (default) keyword description (default) ------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Size Owner Owner Owners Owner Owner User Name List File Output File (Expdat.dmp) Tables Table Name List Compress Imports a Scope (Y) Recordlength IO Record Length GRANTS Export Permissions (Y) IncTy Increment Export Type Indexes Export Index (Y ) RECORD Tracking Export (Y) ROWS Export Data Row (Y) PARFILE Parameter File Name Constraints Export Restriction (Y) Conistent Cross Table Consistency Log Screen Output Log File Statistics Analysis Object (Estimate) Direct Direct Path (N) Triggers Export Trigger (Y) Feedback Displays the maximum size Query of each dump file for each x row (0) Query Query selected exporting clause
The following keywords are only used for the transferable tablespace Transport_TablesPACE Exports Transfer Table Space Metadata (N) TableSpaces Successfully terminated the transferred tablespace lists without warning. C: /> Help has specified the meaning and use of parameters, and lists several simple examples. Note that from 8i start, the method of supporting data subsets has begun to specify its own WHERE. Conditions, you can export or multi-line data from the table. Note The above set nls_lang = Simplified Chinese_China.zHS16GBK, by setting the environment variable, allows EXP's help to display in Chinese, if set nls_lang = american_america. Character set, then your help is English. Incremental and cumulative exports must be effective in the entire library, and in most cases, incremental and accumulated exports do not imagine it. Oracle starts from 9i, no longer supports incremental export and accumulated export. II, table space transmission
Table space transmission is an approach to a new number of mobile data between 8i. It is to attach a format data file on a database to another database, not to export data into a DMP file, which is sometimes It is very tube, because the transfer table space mobile data is as fast as the copy file. There are some rules regarding the transmission table space, namely: • Source database and target database must run on the same hardware platform. • The source database and the target database must use the same character set. · Source Database and Target Database must have the same size data block • The target database does not have table space with the migration table space, and SYS objects cannot be migrated. · You must transfer the contained object set · There are some objects, such as physical chemical views, Function-based indexing, etc., cannot be transmitted to detect a table space or a set of table spaces in line with the transmission criteria: EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK ('TableSpace_name', true); select * from sys.transport_set_vioc; if There is no line selection, indicating that the tablespace contains only table data and is included. For some non-contained tablespaces, such as data table spaces and index tables, you can transfer together. The following is a brief use of the steps, if you want to refer to the details, you can also refer to Oracle online help. a. Set the table space for read-only (assuming table space name is app_data and app_index) ALTER TABLESPACE APP_DATA READ ONLY; ALTER TABLESPACE APP_IDEX READ iNLY; b. Send EXP Userid = "" "Sys / Password As Sysdba" "Transport_Tablespace = Y TableSpace = (app_data, app_index) What need to be noted above • In order to perform EXP in SQL, UserId must use three quotes, and must pay attention to avoid" / "in Unix, after 816 and later. You must use sysdba to operate. This command must be placed in a row in SQL (here because the display is placed in two lines) c. Copy the data file to another, that is, the target database can be CP (UNIX) or COPY (Windows ) Or via the FTP transfer file (must be in the BIN mode) d. Set the local table space to read and write E. Additional data files in the target database IMP file = expdat.dmp userid = "" "SYS / Password As Sysdba" "Transport_Tablespace = Y" DataFile = (C: / Temp / App_Data, C: / Temp / App_index) "F. Setting the Target Database Table Space for reading and writing ALTER TABLESPACE App_Data Read Write; Alter TableSpace App_index Read Write; III, Export / Import and character set
Understand Oracle's multi-language settings, Oracle multi-language settings are to support world-wide language and character sets, generally for language tips, monetary forms, sorting methods, and CHAR, VARCHAR2, CLOB, and long fields of data. Oracle's multi-language settings is the main two feature of national language settings and character set settings. The national language setting determines the language type of interface or prompt, and the character set determines the database save and character set. (Such as text) encoding rules. As a small example of the above, the environment variable NLS_LANG is different, which leads to the Help Help, which is the role of multi-language settings (NLS_LANG contains national language settings and character set settings, and the role is a national language setting, not characters. set). Oracle character set setting, divided into database character sets and client character set environment settings. In the database end, the character set is set when the database is created, and saved in the database PROPS Table, for 8i products, you can use the "ALTER DATABASE Character Set" to modify the character set of the database, but it is only From subset to supercharge, do not modify the character set through the Update Props $, if it is not supported, you may lose all the data related to the character set, which is the supported conversion, or the database is not working properly. The character set is divided into single-byte character set and multi-byte character set, US7ASCII is a typical single-byte character set, in this character set Length = Lengthb, and zHS16GBK is a common double-word character set, here Lengthb = 2 * Length. The character set environment in the client is relatively simple, mainly environment variables or registration items NLS_LANG, note NLS_LANG's priority to: Parameter file à REPUS à Environment Variable àalter session. The group of NLS_LANG is "national language settings. Character set", such as NLS_LANG = Simplified Chinese_China.zHS16GBK. The client's character set is preferably the same as the database end (country language settings can be different, such as the zhs16GBK character set, the client can be NLS_LANG = Simplified Chinese_China.zhs16GBK or AMEIRCAN_AMERICA.ZHS16GBK, do not affect the normal display of the database character), if The character set is different, and the conversion of the character set is not compatible, then the client's data display and the data related to the export / imported and the character set will be garbled. With a little bit of skill, you can convert data on the database of different character sets. Here you need a 2 credit editing tool, such as uedit32.
Open the exported DMP file with edit mode, get 2, 3-byte content, such as 00 01, first convert it to 10-carry number, 1, use the function NLS_CHARSET_NAME to get the character set: SQL> SELECT NLS_CHARSET_NAME 1) from Dual; NLS_CHARSET_NAME (1) ----------------- US7ASCII can know the DMP file character set is US7ASCII, if you need to turn the DMP file Enabled to get the number of the character set with nls_charset_id: SQL> SELECT NLS_CHARSET_ID ('zhs16GBK') from dual; nls_charset_id ('zhs16gbk') -------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ 852 replaced 852 to 16-based number, 354, replace 2, 3 bytes of 00 01 to 03 54, the transformation of the DMP file character set from US7ASCII to ZHS16GBK In this way, it is possible to import the DMP file into the zhs16GBK character set. (Note that the conversion between the decimal number and the hexadecimal, it wants to understand the truth) IV, cross-version use exp / impexp / IMP, can be used across version, such as exporting imports between version 7 and version 8 Data, but do this must choose the correct version, the rules are: • Always use the version of the IMP to match the database version, if you want to import to 816, use 816 import tools. · Always use EXP versions to match the low version of the two databases, such as mutual exchange between 815 and 816, use 815 EXP tools.
