0394-0397
Page 394
Table 17.1. continued
| Keyword | Description | Default |
| can provide a more efficient solution for exporting because only the changed tables are exported. | ||
| RECORD | Indicates whether to record an incremental export in system tables. | Y |
| HELP | Shows the help listing. | N |
| PARFILE | Specifies a filename to read containing the parameter options. | |
| LOG | Specifies a filename to write log messages. | |
| CONSISTENT | Specifies if tables being exported need to be read- consistent. | N |
| This might be necessary if you need to export related tables while in use. This is a painful option to use. Oracle uses a rollback segment to save the changed rows. Use this parameter during quiet times on the database to minimize impact. | ||
| STATISTICS | Used to collect statistics on the tables and indexes during the import. The options are ESTIMATE, COMPUTE, and NONE. | |
| DIRECT | The direct load option can be much faster because it bypasses the SQL |
Page 395
| Keyword | Description | Default |
| Command Processing layer. Direct path export cannot be used for tables that contain Oracle8 objects (object types, nested tables LOBs, REFs, files, VARRAYs). Only the table's definition is exported, not the data. A warning message is issued when this situation occurs. | ||
| FEEDBACK | Displays a dot for every x number of rows exported. If FEEDBACK = 100, Export displays a dot for every 100 rows exported. | |
| MLS | The MLS options are for Trusted Oracle only. | |
| MLS_LABEL_FORMAT | The MLS options are for Trusted Oracle only. |
Export works in one of three modes, depending on the user exporting the data and what options are chosen . The three modes of export are full database, user, and table. Although Oracle devised these three modes of operation, they are not different modes but different levels of export. In exporting owners (OWNER=scott) and tables (TABLES=emp), for example, a user might want to export only certain tables. The only mode that stands out as a true mode is full database. Export is truly in a different mode if it is run with the parameter FULL=Y. Any TABLE or OWNER parameters conflict with the full mode.
All users can export in the user and table modes, and users with the EXP_FULL_DATABASE role can export in the full database mode as well.
You invoke Export's three modes by using the proper parameter options:
| User mode | OWNER=ownerlist |
| Table mode | TABLES=tablelist |
| Full database mode | FULL=Y |
Page 396
Table Mode
Use the table mode to export a single table or a list of tables rather than an entire database. The default is to export all tables that belong to the user doing the export. Users that have access to other schema can export tables from the schema by qualifying with the schema name . The following code is an example of using table mode:
exp USERID=scott/tiger TABLES=dept
You can also export from another schema.
exp USERID=system/manager TABLES=scott.emp exp USERID=system/manager TABLES=(scott.emp,scott.dept)
User Mode
You primarily use the user mode to export all tables and indexes for a particular user or a list of users. This mode works well when you create a user who owns all of an application's objects. For example, if I have a user named sales who owns all the tables and indexes and other objects in the sales application, the application export might resemble the following code:
exp USERID=system/manager OWNER=sales
This gives me an export file of all the objects owned by sales, independent of other considerations.
Full Database Mode
Full database mode exports all database objects except for objects that are usually created and maintained by SYS. Only users granted the EXP_FULL_DATABASE role can use this option.
Several other nice options are worthy of noting here. By default, Oracle performs a complete export if you specify full database mode (INCTYPE=COMPLETE). If you specify the option INCTYPE=INCREMENTAL, Oracle exports only the tables that contain any rows that have changed since the last full export of any type. If you specify INCTYPE=CUMULATIVE, Oracle exports only tables that contain any changed rows since the last complete or cumulative export.
| NOTE |
The three modes are not very obvious, and you will find it easy to specify conflicting options. For example, specifying FULL=Y and OWNER=(scott,tom) causes the export to fail. |
Command Line Versus Interactive
You can use Export in a limited interactive mode or in a command-line mode.
Page 397
I usually use Export in the interactive mode when I am doing small exports that I am willing to run myself . Compressing the extents of a single table and moving a table from one tablespace to another are good candidates for the interactive method. Otherwise, I use the command-line method. You can obtain the same results either way, for the most part. Use a parameter file when you need to export the same tables regularly or if the command line grows too long.
When Export starts, it prompts you for your USERID if it's not provided on the command line. Next, it prompts you for the buffer size , export filename, export mode, grants, table data, whether you want to compress the table extents, the users to export, and the tables to export.
A typical interactive export might resemble the following:
exp Export: Release 7.1.4.1.0 - Production on Sun Oct 1 09:35:19 1995 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. Username: scott Password: Connected to: Oracle7 Server Release 7.1.4.1.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.0.0 - Production Enter array fetch buffer size: 4096 > 16384 Export file: ./expdat.dmp > scott.dmp (2)U(sers), or (3)T(ables): (2)U > u Export grants (yes/no): yes > y Export table data (yes/no): yes > y Compress extents (yes/no): yes > n
The equivalent command-line method resembles the following:
exp scott/tiger FILE=scott.dmp
Export responds with the following:
About to export SCOTT's objects ... . exporting snapshots . exporting snapshot logs . exporting job queues . exporting refresh groups and children . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export SCOTT's tables ... . exporting table BONUS 0 rows exported . exporting table CUSTOMER 9 rows exported . exporting table DEPT 4 rows exported . exporting table DUMMY 1 rows exported . exporting table EMP 14 rows exported
EAN: 2147483647
Pages: 391