First, 8i EXP Common Options
1, FULL, this is used to export the entire database, and the structure of the entire database can be exported when ROWS = N is used. E.g:
Exp Sys file =. / db_str.dmp log =. / db_str.log full = y rows = n compress = y Direct = Y
2, Buffer and Feedback, when derived more data, I will consider setting these two parameters. E.g:
Exp New file = yw97_2003.dmp log = yw97_2003_3.log feedback = 10000 buffer = 100000000 Tables = WO4, OK_YT
3, Fill and Log, these two parameters specify backup DMP names and log names, including file names and directories, and see above.
It should be noted that exp can be backed up directly into the tape, even if we use file = / dev / rmt0 (tape device name), but generally don't do this, there is two: One, this speed will be much slower, two Now, it is generally used to use the tape library, and it is not recommended to operate directly. As for friends who have not used the library, they can consider using Unix Tar in combination.
If you really want to use Exp to tape, you can refer to Metalink article "Exporting to Tape on Unix Systems" (document number: 30428.1), which is explained in detail.
4, the Compress parameter will consolidate the pieces while exporting, try to compress the data into the EXTENT of Initial, the default is N, generally recommended. The Direct parameter will tell EXP to read data directly, not like traditional Exp, using SELECT to read data in the table, which reduces the SQL statement processing. It is generally recommended. However, in some cases the Direct parameter cannot be used.
5, how to use sysdba to perform EXP / IMP?
This is a very realistic problem, sometimes we need to use SysDBA to perform EXP / IMP, such as Exp / IMP for transport tablespace, and use SYSDBA when using SYS users in 9i, . We can use the following to connect EXP / IMP:
Exp "'sys / sys sysdba'" file = 1.dmp tables = gototop.t rows = n
6. The query parameters are WHERE conditions, it is worth noting that the entire WHERE clause needs to be used "", the WHERE clause is the same, if it is all the UNIX platform "and 'need to use / u26469 Shield their special meanings:
Exp Gototop / gototop file = 1.dmp log = 1.log Tables = cyx.t query = "Where c1 = 20 and c2 = gototop"
If it is a Windows platform, use the following format:
Exp C / C @ ncn file = c.dmp log = c.log Tables = T query = "" "" "" where id = 1 and name = 'gototop' "" "
Second, 8i IMP common option
1, fromuser and touser, use them to import data from one Schema to another Schema. 2, Ignore, GRANTS, and INDEXES, where the Ignore parameter will ignore the existence of the table, continue to import, this is useful to use the storage parameters that need to adjust the table, we can first build a table according to the actual situation, and then import directly data. GRANTS and Indexes indicate whether to import authorization and index, if you want to reconstruct the index with new storage parameters, or to speed up the speed, we can consider setting indexes to N, and GRANTS is generally Y.
Another parameter with EXP / IMP is PARFILE, which is used to define the parameter file for exp / IMP, that is, the above parameters can be written in a parameter file, but we generally use it.
Third, Oracle9i EXP function description
Oracle9i EXP adds some new parameters on the original basis, and according to the functionality of the following sections:
1, Object_constent - Used to set the EXP object to read only to maintain the consistency of the object. The default is N.
2, flashback_scn and flashback_time - add new to the flashback function.
3, Resumable, Resumable_name and Resumable_Timeout - Added to support Resumable spatial allocation.
4, TTS_FULL_CHECK - Used to use a dependency check when transporting tablespace.
5, Template - Used to support IAS.
6, TABLESPACES - Set the tablespace export mode. Personally, for the general user, this is the most practical one in the new parameters, allowing users to make more options on the original Full, Owner, Tables, making EXP more flexible.
Fourth, different versions of Exp / IMP problem?
In general, imports from low versions are not big, trouble is to import high versions of data into low versions, before Oracle9i, the EXP / IMP between different versions of Oracle can be solved by the following method:
1. Run the bottom version of catexp.sql on the high version database;
2. Use a low version of Exp to export high versions of data;
3. Use the low version of IMP to import the database into the final version of the database;
4. Run the high version of the catexp.sql script on the high version of the database.
But in 9i, the above method does not solve the problem. If you use the bottom version of EXP / IMP directly, you will have the following error:
EXP-00008: Oracle Error% Lu Encountered
ORA-00904: Invalid Column Name
This is already a published bug, you need to wait until Oracle10.0 can solve, the BUG number is 2261722, you can go to Metalink to see more information about this bug.
BUG is returned to BUG, our work is still to do, before we don't have Oracle's support, we will solve themselves. Perform the following SQL reconstruction EXU81RLS view in Oracle9i.
Create or Replace View Exu81RLS
(Objown, ObjnaM, Policy, Pollow, Polsch, Polfun, Stmts, Chkopt, Enabled, Spolicy)
As SELECT U.NAME, O.NAME, R.PNAME, R.PFSCHMA, R.PPNAME, R.PFNAME,
Decode (Bitand (R.STMT_TYPE, 1), 0, '', 'SELECT,')
|| Decode (Bitand (R.STMT_TYPE, 2), 0, '', 'INSERT,') || Decode (Bitand (R.STMT_TYPE, 4), 0, ',' Update, ')
|| Decode (Bitand (R.STMT_TYPE, 8), 0, '', 'Delete,'),
R.Check_opt, r.enable_flag,
Decode (Bitand (R.STMT_TYPE, 16), 0, 0, 1)
From user $ u, Obj $ O, RLS $ R
Where u.user # = o.owner #
And r.obj # = o.obj #
AND (uid = 0 or)
Uid = o.owner # or
EXISTS (SELECT * from session_roles where role = 'select_catalog_role')
)
/
Grant Select On Sys.EXU81RLS to PUBLIC;
/
V. Other issues
This article only discusses some of Oracle8i and 9i's exp / IMP, for previous versions, in 8.0.x, other differences in addition to the Query parameter cannot be used. For situations without query, we can first use query conditions in the database to create a temporary intermediate table, and then export this intermediate table using Exp. As for Oracle7 because there are fewer people, Gototop doesn't plan to explain this in detail. If the reader friend has a demand, you can refer to the Metalink document: "Overview of export and import in oracle7" (Document Number: 61949.1). Detailed parameter information about Exp / IMP You can get through exp / IMP help = y.
Further information on the transmission table space can refer to the following MeteLink document, this article is no longer detailed.
[NOTE: 77523.1] TRANSPORTABLE TABLESPACES - AN EXAMPLE TO SETUP AND USE.
[NOTE: 100698.1] Perform TableSpace Point-in-Time Recovery Using Transportable TableSpace.
When performing parallel EXP / IMP, if the IMP process is indexed, it is not recommended to run more than 5 IMP simultaneously. If you want to speed up the speed, you can not build an index when Imp, so you can run a few more than the memory allows you. , Then the SQL script creates the index required.
There may be many friends to mention the EXP / IMP issues of different character sets. Gototop thinks that this problem will appear purely because the database platform is not planned, and the actual situation is too complicated. If you are interested, you can discuss it, this article will Hit.
