Export Utility Part1

来源：互联网发布：sql删除全部数据编辑：程序博客网时间：2024/05/22 04:31

What Is the Export Utility?

An Export file is an Oracle binary-format dump file that is typically located on disk or tape. The dump files can be transferred using FTP or physically transported (in the case of tape) to a different site.

Export dump files can only be read by the Oracle Import utility. The version of the Import utility cannot be earlier than the version of the Export utility used to create the dump file.

You can also display the contents of an export file without actually performing an import.

Before Using Export

Run the catexp.sql or catalog.sql script

Ensure there is sufficient disk or tape storage to write the export file

Verify that you have the required access privileges

** You can find table sizes in the USER_SEGMENTS view of the Oracle data dictionary. The following query displays disk usage for all tables:

SELECT SUM(BYTES) FROM USER_SEGMENTS WHERE SEGMENT_TYPE='TABLE';

The result of the query does not include disk space used for data stored in LOB (large object) or VARRAY columns or in partitioned tables.

** To use Export, you must have the CREATE SESSION privilege on an Oracle database. To export tables owned by another user, you must have the EXP_FULL_DATABASE role enabled. This role is granted to all DBAs.

** The following schema names are reserved and will not be processed by Export: ORDSYS MDSYS CTXSYS ORDPLUGINS LBACSYS

Invoking Export

Command-Line Entries

exp username/password PARAMETER=value

or exp username/password PARAMETER=(value1,value2,...,valuen)

Interactive Export Prompts

exp username/password

Parameter Files

Create the parameter file using any flat file text editor.

exp PARFILE=filename

exp username/password PARFILE=filename

The syntax for parameter file specifications is one of the following:

PARAMETER=value

PARAMETER=(value)

PARAMETER=(value1, value2, ...)

e.g.:

FULL=y

FILE=dba.imp

GRANTS=y

INDEXES=y

CONSISTENT=y

** you can add comments to the parameter file by preceding them with the pound (#) sign.

** You can specify a parameter file at the same time that you are entering parameters on the command line. E.g: assume the parameter file params.dat contains the parameter INDEXES=y and Export is invoked with the following line: exp username/password PARFILE=params.dat INDEXES=n In this case, because INDEXES=n occurs after PARFILE=params.dat, INDEXES=n overrides the value of the INDEXES parameter in the parameter file.

Invoking Export As SYSDBA

SYSDBA is used internally and has specialized functions; its behavior is not the same as for generalized users.

exp /'username/password AS SYSDBA/'

or, optionally: exp /'username/password@instance AS SYSDBA/'

Export Modes

The Export utility provides four modes of export: Full, User (Owner), Table, Tablespace

All users can export in table mode and user mode. Users with the EXP_FULL_DATABASE role (privileged users) can export in all modes.

Table 1-1 Objects Exported and Imported in Each Mode

Table Mode

User Mode

Full Database Mode

Tablespace Mode

Analyze cluster

Yes

Analyze tables/statistics

Yes

Application contexts

Yes

Auditing information

Yes

B-tree, bitmap, domain functional indexes

Yes

Cluster definitions

Yes

Column and table comments

Yes

Database links

Yes

Default roles

Yes

Dimensions

Yes

Directory aliases

Yes

External tables (without data)

Yes

Foreign function libraries

Yes

Indexes owned by users other than table owner

Yes (Privileged users only)

Yes

Index types

Yes

Java resources and classes

Yes

Job queues

Yes

Nested table data

Yes

Object grants

Yes (Only for tables and indexes)

Yes

Object type definitions used by table

Yes

Object types

Yes

Operators

Yes

Password history

Yes

Postinstance actions and objects

Yes

Postschema procedural actions and objects

Yes

Posttable actions

Yes

Posttable procedural actions and objects

Yes

Preschema procedural objects and actions

Yes

Pretable actions

Yes

Pretable procedural actions

Yes

Private synonyms

Yes

Procedural objects

Yes

Profiles

Yes

Public synonyms

Yes

Referential integrity constraints

Yes

Refresh groups

Yes

Resource costs

Yes

Role grants

Yes

Roles

Yes

Rollback segment definitions

Yes

Security policies for table

Yes

Sequence numbers

Yes

Snapshot logs

Yes

Snapshots and materialized views

Yes

System privilege grants

Yes

Table constraints (primary, unique, check)

Yes

Table data

Yes

Table definitions

Yes

Tablespace definitions

Yes

Tablespace quotas

Yes

Triggers

Yes

YesFoot 2

Yes

Triggers owned by other users

Yes (Privileged users only)

User definitions

Yes

User proxies

Yes

User views

Yes

User-stored procedures, packages, and functions

Yes

Table-Level and Partition-Level Export

Table-Level Export

In table-level Export, you can export an entire table (partitioned or nonpartitioned) along with its indexes and other table-dependent objects. This applies to both direct path Export and conventional path Export. You can perform a table-level export in any Export mode.

Partition-Level Export

In partition-level Export, you can export one or more specified partitions or subpartitions of a table. You can only perform a partition-level export in Table mode.

Getting Online Help

Export_start

ExpModes

ExpTSOpts (tablespaces_spec)

ExpOpts

ExpFileOpts

BUFFER

Default: operating system-dependent.

buffer_size = rows_in_array * maximum_row_size

** If you specify zero, the Export utility fetches only one row at a time.

Tables with columns of type LONG, LOB, BFILE, REF, ROWID, LOGICAL ROWID, or DATE are fetched one row at a time.

COMPRESS

Default: y

Specifies how Export and Import manage the initial extent for table data.

** COMPRESS=n, Export uses the current storage parameters, including the values of initial extent size and next extent size. The values of the parameters may be the values specified in the CREATE TABLE or ALTER TABLE statements or the values modified by the database system.

CONSISTENT

Default: y

Specifies whether or not Export uses the SET TRANSACTION READ ONLY statement to ensure that the data seen by Export is consistent to a single point in time and does not change during the execution of the exp command.

** Specify CONSISTENT=y: anticipate that other applications will be updating the target data after an export has started.

** Specify CONSISTENT=n, each table is usually exported in a single transaction.

** If use CONSISTENT=y: and the volume of updates is large, the rollback segment usage will be large. In addition, the export of each table will be slower because the rollback segment must be scanned for uncommitted transactions.

** Keep in mind the following points about using CONSISTENT=y:

a) CONSISTENT=y is unsupported for exports that are performed when you are connected as user SYS or you are using AS SYSDBA, or both.

b) Oracle Corporation recommends that you avoid making metadata changes during an export process in which CONSISTENT=y is selected.

c) To minimize the time and space required for such exports, you should export tables that need to remain consistent separately from those that do not.

To avoid the “snapshot too old” error, you should minimize the time taken by a read-consistent export. Also, make the rollback segment as large as possible.

CONSTRAINTS

Default: y

Specifies whether or not the Export utility exports table constraints.

DIRECT

Default: n

Specifies whether you use direct path or conventional path Export.

FEEDBACK

Default: 0 (zero)

Specifies that Export should display a progress meter in the form of a period for n number of rows exported. For example, if you specify FEEDBACK=10, Export displays a period each time 10 rows are exported. The FEEDBACK value applies to all tables being exported; it cannot be set on a per-table basis.

FILE

Default: expdat.dmp

Specifies the names of the export files. The default extension is .dmp, but you can specify any extension. Because Export supports multiple export files, you can specify multiple filenames to be used.

FILESIZE

Default: Data is written to one file until the maximum size.

Table 1-2 Maximum SIze for Dump Files

Operating System

Version of Oracle Server

Maximum Size

Any

Prior to 8.1.5

2 gigabytes

32-bit

8.1.5

2 gigabytes

64-bit

8.1.5 and later

Unlimited

32-bit with 32-bit files

Any

2 gigabytes

32-bit with 64-bit files

8.1.6 and later

Unlimited

** The FILESIZE value can also be specified as a number followed by KB (number of kilobytes). For example, FILESIZE=2KB is the same as FILESIZE=2048. Similarly, MB specifies megabytes (1024 * 1024) and GB specifies gigabytes (1024**3). B remains the shorthand for bytes; the number is not multiplied to obtain the final file size (FILESIZE=2048B is the same as FILESIZE=2048).

FLASHBACK_SCN

Default: none

Specifies the system change number (SCN) that Export will use to enable flashback.

FLASHBACK_TIME

Default: none

Specifies a time. Export finds the SCN that most closely matches the specified time. This SCN is used to enable flashback. The export operation is performed with data consistent as of this SCN.

FULL

Default: n

Indicates that the Export is a full database mode Export (that is, it exports the entire database). Specify FULL=y to export in full database mode. You need to have the EXP_FULL_DATABASE role to export in this mode.

GRANTS

Default: y

Specifies whether or not the Export utility exports object grants. In full database mode, all grants on a table are exported. In user mode, only those granted by the owner of the table are exported. System privilege grants are always exported.

HELP

Default: n

Displays a description of the Export parameters.

INDEXES

Default: y

Specifies whether or not the Export utility exports indexes.

LOG

Default: none

Specifies a filename to receive informational and error messages. For example:

exp SYSTEM/password LOG=export.log

OWNER

Default: none

Indicates that the Export is a user-mode Export and lists the users whose objects will be exported. If the user initiating the export is the DBA, multiple users may be listed.

PARFILE

Default: none

Specifies a filename for a file that contains a list of Export parameters.

QUERY

Default: none

This parameter allows you to select a subset of rows from a set of tables when doing a table mode export.

**. For example, (this example is UNIX-based):

1）．exp scott/tiger TABLES=emp QUERY=/"WHERE job=/'SALESMAN/' and sal/<1600/"

When executing , do this first: SELECT * FROM emp WHERE job='salesman' and sal <1600;

2）. exp scott/tiger TABLES=emp,bonus QUERY=/"WHERE job=/'salesman/' and sal/<1600/"

When executing export, do this first:

SELECT * FROM emp WHERE job='salesman' and sal <1600;

SELECT * FROM bonus WHERE job='salesman' and sal <1600;

3) If a table is missing the columns specified in the QUERY clause, an error message will be produced, and no rows will be exported for the offending table.

Restrictions

The parameter QUERY cannot be specified for full, user, or tablespace mode exports.

The parameter QUERY must be applicable to all specified tables.

The parameter QUERY cannot be specified in a direct path export (DIRECT=y)

The parameter QUERY cannot be specified for tables with inner nested tables.

RECORDLENGTH

Default: operating system-dependent

Specifies the length, in bytes, of the file record. The RECORDLENGTH parameter is necessary when you must transfer the export file to another operating system that uses a different default value.

** You can set RECORDLENGTH to any value equal to or greater than your system's buffer size. (The highest value is 64 KB.) Changing the RECORDLENGTH parameter affects only the size of data that accumulates before writing to the disk. It does not affect the operating system file block size.

RESUMABLE

Default: n

The RESUMABLE parameter is used to enable and disable resumable space allocation. Because this parameter is disabled by default, you must set RESUMABLE=y in order to use its associated parameters, RESUMABLE_NAME and RESUMABLE_TIMEOUT.

RESUMABLE_NAME

Default: 'User USERNAME (USERID), Session SESSIONID, Instance INSTANCEID'

The value for this parameter identifies the statement that is resumable. This value is a user-defined text string that is inserted in either the USER_RESUMABLE or DBA_RESUMABLE view to help you identify a specific resumable statement that has been suspended.

This parameter is ignored unless the RESUMABLE parameter is set to y to enable resumable space allocation.

RESUMABLE_TIMEOUT

Default: 7200 seconds (2 hours)

The value of the parameter specifies the time period during which an error must be fixed. If the error is not fixed within the timeout period, execution of the statement is aborted.

This parameter is ignored unless the RESUMABLE parameter is set to y to enable resumable space allocation.

ROWS

Default: y

Specifies whether or not the rows of table data are exported.

STATISTICS

Default: ESTIMATE

Specifies the type of database optimizer statistics to generate when the exported data is imported. Options are ESTIMATE, COMPUTE, and NONE.

In some cases, Export will place the precalculated statistics in the export file as well as the ANALYZE statements to regenerate the statistics. However, the precalculated optimizer statistics will not be used at export time if a table has columns with system-generated names. The precalculated optimizer statistics are flagged as questionable at export time if: a) There are row errors while exporting, b) the client character set or NCHAR character set does not match the server character set or NCHAR character set, c) a QUERY clause is specified.

TABLES

Default: none

Specifies that the Export is a table-mode Export and lists the table names and partition and subpartition names to export. You can specify the following when you specify the name of the table: a) schemaname specifies the name of the user's schema from which to export the table or partition. (ORDSYS, MDSYS, CTXSYS, and ORDPLUGINS are reserved by Export). b) tablename specifies the name of the table or tables to be exported. Table-level export lets you export entire partitioned or nonpartitioned tables. If a table in the list is partitioned and you do not specify a partition name, all its partitions and subpartitions are exported. The table name can contain any number of '%' pattern matching characters, which can each match zero or more characters in the table name against the table objects in the database. All the tables in the relevant schema that match the specified pattern are selected for export, as if the respective table names were explicitly specified in the parameter. C) partition_name indicates that the export is a partition-level Export. Partition-level Export lets you export one or more specified partitions or subpartitions within a table.

** The syntax you use to specify the preceding is in the form:

schemaname.tablename:partition_name

schemaname.tablename:subpartition_name

If you use tablename:partition_name, the specified table must be partitioned, and partition_name must be the name of one of its partitions or subpartitions. If the specified table is not partitioned, the partition_name is ignored and the entire table is exported.

** Table Name Restrictions

a) By default, table names in a database are stored as uppercase. The name must exactly match the table name stored in the database.

b) Some operating systems require that quotation marks on the command line be preceded by an escape character. The following are examples of how case-sensitivity can be preserved in the different Export modes.

In command-line mode:

TABLES='/"Emp/"'

In interactive mode:

Table(T) to be exported: "Emp"

In parameter file mode:

TABLES='"Emp"'

E.g. if the parameter file contains the following line, Export interprets everything on the line after emp# as a comment and does not export the tables dept and mydata:

TABLES=(emp#, dept, mydata)

However, given the following line, the Export utility exports all three tables because emp# is enclosed in quotation marks:

TABLES=("emp#", dept, mydata)

For a TABLES parameter that specifies multiple schema.tablename:(sub)partition_name arguments, Export attempts to purge duplicates before processing the list of objects.

TABLESPACES

Default: none

The TABLESPACES parameter specifies that all tables in the tablespace be exported to the Export dump file. You must have the EXP_FULL_DATABASE role to use TABLESPACES to export all tables in the tablespace.

When TABLESPACES is used in conjunction with TRANSPORT_TABLESPACE=y, you can specify a limited list of tablespaces to be exported from the database to the export file.

TRANSPORT_TABLESPACE

Default: n

When specified as y, this parameter enables the export of transportable tablespace metadata.

TRIGGERS

Default: y

Specifies whether or not the Export utility exports triggers.

TTS_FULL_CHECK

Default: FALSE

When TTS_FULL_CHECK is set to TRUE, Export verifies that a recovery set (set of tablespaces to be recovered) has no dependencies (specifically, IN pointers) on objects outside the recovery set, and vice versa.

USERID (username/password)

Default: none

Specifies the username/password (and optional connect string) of the user performing the export. If you omit the password, Export will prompt you for it.

USERID can also be:

username/password AS SYSDBA

or username/password@instance AS SYSDBA

If you connect as user SYS, you must also specify AS SYSDBA in the connect string. Your operating system may require you to treat AS SYSDBA as a special string, in which case the entire string would be enclosed in quotation marks.

VOLSIZE

Specifies the maximum number of bytes in an export file on each volume of tape.

The VOLSIZE parameter has a maximum value equal to the maximum value that can be stored in 64 bits. See your operating system-specific documentation for more information.

The VOLSIZE value can be specified as number followed by KB (number of kilobytes). For example, VOLSIZE=2KB is the same as VOLSIZE=2048. Similarly, MB specifies megabytes (1024 * 1024) and GB specifies gigabytes (1024**3). B remains the shorthand for bytes; the number is not multiplied to get the final file size (VOLSIZE=2048B is the same as VOLSIZE=2048).

Parameter Interactions

Certain parameters can conflict with each other.

1) because specifying TABLES can conflict with an OWNER specification, the following command causes Export to terminate with an error:

exp SYSTEM/password OWNER=jones TABLES=scott.emp

2) Similarly, OWNER and TABLES conflict with FULL=y.

Example Export Session in Full Database Mode

Only users with the DBA role or the EXP_FULL_DATABASE role can export in full database mode. In this example, an entire database is exported to the file dba.dmp with all GRANTS and all data.

Parameter File Method

> exp SYSTEM/password PARFILE=params.dat

The params.dat file contains the following information:

FILE=dba.dmp

GRANTS=y

FULL=y

ROWS=y

Command-Line Method

> exp SYSTEM/password FULL=y FILE=dba.dmp GRANTS=y ROWS=y