unixdev.net


Switch to SpeakEasy.net DSL

The Modular Manual Browser

Home Page
Manual: (Debian-5.0)
Page:
Section:
Apropos / Subsearch:
optional field

exp(1)                      General Commands Manual                     exp(1)



NAME
       exp - export an Oracle database.

SYNOPSIS
       exp

       or

       exp  username

       or

       exp  username/password PARAMETER=value [PARAMETER=value ...]


DESCRIPTION
       exp is used to export an Oracle database.

       You can control how Export runs by entering the exp command followed by
       various parameters.

       The available  Export parameters are as follows:

       BUFFER
       Default: operating system-dependent.

       Specifies the size, in bytes, of the buffer used to fetch  rows.  As  a
       result,  this  parameter  determines  the  maximum number of rows in an
       array fetched by Export. Use the following  formula  to  calculate  the
       buffer size: 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  LOBs, LONG, BFILE, REF, ROWID, LOGICAL
       ROWID, or DATE are fetched one row at a time.

       The BUFFER parameter applies only to conventional path Export.  It  has
       no  effect  on  a  direct path Export. For direct path Exports, use the
       RECORDLENGTH parameter to specify the size of the  buffer  that  Export
       uses for writing to the export file.


       COMPRESS
       Default: y

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

       The default, COMPRESS=y, causes Export to flag table data for  consoli-
       dation  into  one initial extent upon import. If extent sizes are large
       (for example, because of  the  PCTINCREASE  parameter),  the  allocated
       space will be larger than the space required to hold the data.

       If  you specify 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 TA-
       BLE or ALTER TABLE statements or the values modified  by  the  database
       system.  For example, the NEXT extent size value may be modified if the
       table grows and if the PCTINCREASE parameter is nonzero.

       Although the actual consolidation is performed  upon  import,  you  can
       specify  the  COMPRESS  parameter  only  when  you export, not when you
       import. The Export utility, not the Import utility, generates the  data
       definitions, including the storage parameter definitions. Therefore, if
       you specify COMPRESS=y when you export, you can import the data in con-
       solidated form only.

       Neither LOB data nor subpartition data is compressed. Rather, values of
       initial extent size and next extent size at  the  time  of  export  are
       used.

       CONSISTENT
       Default: n

       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. You should specify CONSISTENT=y when you  anticipate  that
       other applications will be updating the target data after an export has
       started.

       If you use CONSISTENT=n, each table is usually  exported  in  a  single
       transaction.  However, if a table contains nested tables, the outer ta-
       ble and each inner table are exported as separate transactions.

       Therefore, if nested tables are being updated  by  other  applications,
       the  data that is exported could be inconsistent. To minimize this pos-
       sibility, export those tables at a time  when  updates  are  not  being
       done.

       CONSTRAINTS
       Default: y

       Specifies whether or not the Export utility exports table constraints.

       DIRECT
       Default: n

       Specifies the use of direct path Export.

       Specifying  DIRECT=y  causes Export to extract data by reading the data
       directly, bypassing the SQL command-processing layer  (evaluating  buf-
       fer). This method can be much faster than a 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 FEED-
       BACK=10,  Export  displays a period each time 10 rows are exported. The
       FEEDBACK value applies to all tables being exported; it cannot  be  set
       individually for each table.

       FILE
       Default: expdat.dmp

       Specifies  the names of the export dump files. The default extension is
       .dmp, but you can specify any extension. Because Export supports multi-
       ple  export  files,  you can specify multiple filenames to be used. For
       example: exp scott/tiger FILE  =  dat1.dmp,  dat2.dmp,  dat3.dmp  FILE-
       SIZE=2048

       When  Export reaches the value you have specified for the maximum FILE-
       SIZE, Export stops writing to the current file,  opens  another  export
       file  with the next name specified by the FILE parameter, and continues
       until complete or the maximum value of FILESIZE is  again  reached.  If
       you  do not specify sufficient export filenames to complete the export,
       Export will prompt you to provide additional filenames.

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

       Export supports writing to multiple export files, and Import  can  read
       from multiple export files. If you specify a value (byte limit) for the
       FILESIZE parameter, Export will write only  the  number  of  bytes  you
       specify  to  each dump file.  When the amount of data Export must write
       exceeds the maximum value you specified for FILESIZE, it will  get  the
       name of the next export file from the FILE parameter or, if it has used
       all the names specified in the FILE parameter, it will  prompt  you  to
       provide  a new export filename. If you do not specify a value for FILE-
       SIZE (note that a value of 0 is equivalent to not specifying FILESIZE),
       then  Export  will  write to only one file, regardless of the number of
       files specified in the FILE parameter.

       If the space requirements of your export file exceed the available disk
       space,  Export  will  terminate, and you will have to repeat the Export
       after making sufficient disk space available.

       The FILESIZE parameter has a maximum value equal to the  maximum  value
       that can be stored in 64 bits.

       The  FILESIZE  value  can  also be specified as a number followed by KB
       (number of kilobytes). For example, FILESIZE=2KB is the same  as  FILE-
       SIZE=2048. Similarly, MB specifies megabytes (1024 * 1024) and GB spec-
       ifies gigabytes (1024**3). B remains the shorthand for bytes; the  num-
       ber  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. The export operation is performed with data consistent as of
       this specified SCN.

       FLASHBACK_TIME
       Default: none

       Enables you to specify a timestamp. Export  finds  the  SCN  that  most
       closely  matches  the  specified  timestamp. 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.  The
       object grants that are exported depend on whether you use full database
       mode or user mode. 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.

       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

       If  you specify this parameter, messages are logged in the log file and
       displayed to the terminal display.

       OBJECT_CONSISTENT
       Default: n

       Specifies whether or not the Export utility uses  the  SET  TRANSACTION
       READ ONLY statement to ensure that the data exported is consistent to a
       single point in  time  and  does  not  change  during  the  export.  If
       OBJECT_CONSISTENT is set to y, each object is exported in its own read-
       only transaction. In contrast, if you  use  the  CONSISTENT  parameter,
       then there is only one read-only transaction.

       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  database administrator (DBA), multiple users can be listed.  User-
       mode exports can be used to back up one or  more  database  users.  For
       example,  a  DBA  may want to back up the tables of deleted users for a
       period of time. User mode is also appropriate for  users  who  want  to
       back  up  their  own data or who want to move objects from one owner to
       another.

       PARFILE
       Default: none

       Specifies a filename for a file that contains a list of Export  parame-
       ters.

       QUERY
       Default: none

       This  parameter  enables  you  to select a subset of rows from a set of
       tables when doing a table mode export. The value of the query parameter
       is  a  string  that  contains a WHERE clause for a SQL SELECT statement
       that will be applied to all tables listed in the TABLE parameter.

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

       - The QUERY parameter must be applicable to all specified tables.

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

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

       - You cannot determine from the contents of the export file whether the
       data is the result of a QUERY export.

       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.

       If you do not define this parameter,  it  defaults  to  your  platform-
       dependent value for buffer size.

       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.

       You  can  use this parameter to specify the size of the Export I/O buf-
       fer.

       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 INSTAN-
       CEID'

       The value for this parameter identifies the statement  that  is  resum-
       able.  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 terminated.

       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 sta-
       tistics. 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:

       - There are row errors while exporting

       -  The  client  character set or NCHAR character set does not match the
       server character set or NCHAR character set

       - A QUERY clause is specified

       Specifying ROWS=n does not preclude saving the precalculated statistics
       in  the  export  file.  This  enables  you  to tune plan generation for
       queries in a nonproduction database using statistics from a  production
       database.

       TABLES
       Default: none

       Specifies  that  the  export is a table-mode export and lists the table
       names to export.

       TABLESPACES
       Default: none

       The TABLESPACES parameter specifies that all tables  in  the  specified
       tablespace  be  exported  to  the  Export  dump file. This includes all
       tables contained in the list of tablespaces. Indexes are exported  with
       their tables, regardless of where the index is stored.

       You  must  have the EXP_FULL_DATABASE role to use TABLESPACES to export
       all tables in the tablespace.

       TRIGGERS
       Default: y Specifies whether or not the Export  utility  exports  trig-
       gers. (The default is Y.)

       TTS_FULL_CHECK
       Default: n

       When  TTS_FULL_CHECK  is  set to y, 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 the reverse.

       USERID
       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.  You can specify the username and password without
       specifying the USERID parameter, provided it appears first on the  com-
       mand line. For example:

        exp scott/tiger FULL=y

       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 con-
       nect 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
       Default: none

       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 maxi-
       mum value that can be stored in 64 bits on your platform.

       The VOLSIZE value can be specified as a 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  giga-
       bytes  (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).


EXAMPLES
        exp SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)

       You  can  let Export prompt you for parameters by entering the exp com-
       mand followed only by your username/password:

        exp SCOTT/TIGER


SEE ALSO
AUTHORS
       Oracle Corporation


REPORTING BUGS
       Report bugs to oraclexeAToracle.com.


COPYRIGHT
       Copyright (C) 2005 Oracle. All rights reserved.


       ;
        name="expdp.1"  Content-Transfer-Encoding:  7bit  Content-Disposition:
       inline;
        filename="expdp.1"




expdp(1)                    General Commands Manual                   expdp(1)



NAME
       expdp - transfer data objects between Oracle databases.

SYNOPSIS
       expdp PARAMETER=value [PARAMETER=value ...]


DESCRIPTION
       The Data Pump export utility provides a mechanism for transferring data
       objects between Oracle databases.

       You can control how Export runs by entering the expdp command  followed
       by various parameters.

       The available Data Pump Export parameters are as follows:

       ATTACH [=[schema_name.]job_name]
       Attaches the client session to an existing export job and automatically
       places you in the  interactive-command  interface.  Export  displays  a
       description  of the job to which you are attached and also displays the
       Export prompt.

       The schema_name is optional. To specify a schema other than  your  own,
       you must have the EXP_FULL_DATABASE role.

       The job_name is optional if only one export job is associated with your
       schema and the job is active. To attach to a stopped job, you must sup-
       ply the job name.

       When  you are attached to the job, Export displays a description of the
       job and then displays the Export prompt.

       When you specify the ATTACH parameter, you  cannot  specify  any  other
       parameters except for the connection string (user/password).

       You  cannot attach to a job in another schema unless it is already run-
       ning.

       If the dump file set or master table for the job have been deleted, the
       attach operation will fail.

       Altering  the  master  table  in  any  way  will  lead to unpredictable
       results.

       COMPRESSION=(METADATA_ONLY | NONE)
       Default: METADATA_ONLY

       Specifies whether to compress metadata before writing to the dump  file
       set.

       METADATA_ONLY results in all metadata being written to the dump file in
       compressed format.

       NONE disables compression for the entire unload.

       CONTENT={ALL | DATA_ONLY | METADATA_ONLY}
       Default: ALL

       Enables you to filter what Export unloads: data only, metadata only, or
       both.

       ALL unloads both data and metadata.

       DATA_ONLY  unloads  only table row data; no database object definitions
       are unloaded.

       METADATA_ONLY unloads only database object definitions;  no  table  row
       data is unloaded.

       DIRECTORY=directory_object
       Default:  DATA_PUMP_DIR

       Specifies  the default location to which Export can write the dump file
       set and the log file.

       The directory_object is the name of a database  directory  object  (not
       the  name  of an actual directory). Upon installation, privileged users
       have access to a default directory object  named  DATA_PUMP_DIR.  Users
       with  access  to  DATA_PUMP_DIR need not use the DIRECTORY parameter at
       all.

       A directory object specified on the DUMPFILE or LOGFILE parameter over-
       rides  any  directory object that you specify for the DIRECTORY parame-
       ter.

       DUMPFILE=[directory_object:]file_name [, ...]
       Default: expdat.dmp

       Specifies the names, and optionally,  the  directory  objects  of  dump
       files for an export job.

       The directory_object is optional if one has already been established by
       the DIRECTORY parameter. If you supply a  value  here,  it  must  be  a
       directory  object  that  already  exists and that you have access to. A
       database directory object that is specified as  part  of  the  DUMPFILE
       parameter  overrides a value specified by the DIRECTORY parameter or by
       the default directory object.

       You can supply multiple file_name specifications as  a  comma-delimited
       list  or in separate DUMPFILE parameter specifications. If no extension
       is given for the filename, then Export uses the default file  extension
       of  .dmp. The filenames can contain a substitution variable (%U), which
       implies that multiple files may be generated. The substitution variable
       is  expanded  in  the  resulting filenames into a 2-digit, fixed-width,
       incrementing integer starting at 01 and ending at 99. If a file  speci-
       fication  contains  two substitution variables, both are incremented at
       the same time.

       For   example,   exp%Uaa%U.dmp   would   resolve   to    exp01aa01.dmp,
       exp02aa02.dmp, and so forth.

       If the FILESIZE parameter is specified, each dump file will have a max-
       imum of that size in bytes and  be  nonextensible.  If  more  space  is
       required for the dump file set and a template with a substitution vari-
       able (%U) was supplied, a new dump file is automatically created of the
       size  specified  by  FILESIZE, if there is room on the device.  As each
       file specification or file template containing a substitution  variable
       is  defined,  it  is instantiated into one fully qualified filename and
       Export attempts to create it. The file specifications are processed  in
       the  order  in  which  they are specified. If the job needs extra files
       because the maximum file size is reached,  then  additional  files  are
       created if file templates with substitution variables were specified.

       Although  it  is  possible to specify multiple files using the DUMPFILE
       parameter, the export job may only require a subset of those  files  to
       hold  the  exported data. The dump file set displayed at the end of the
       export job shows exactly which files were used.  It  is  this  list  of
       files  that  is  required in order to perform an import operation using
       this dump file set.

       If there are preexisting files that match the resulting  filenames,  an
       error is generated. The existing dump files will not be overwritten.

       ENCRYPTION_PASSWORD=password
       Default: none

       Specifies  a  key  for  encrypting  encrypted column data in the export
       dumpfile.

       The password value that is supplied specifies a key  for  re-encrypting
       encrypted  table  columns so that they are not written as clear text in
       the dump file set. If the export  operation  involves  encrypted  table
       columns, but an encryption password is not supplied, then the encrypted
       columns will be written to the dump file set as clear text and a  warn-
       ing will be issued.

       To  use  the  ENCRYPTION_PASSWORD  parameter, you must have Transparent
       Data Encryption set up.

       The ENCRYPTION_PASSWORD parameter applies only to columns that  already
       have encrypted data. Data Pump neither provides nor supports encryption
       of entire dump files.

       The ENCRYPTION_PASSWORD parameter is only used to export encrypted col-
       umns used by Transparent Data Encryption functionality.

       For network exports, the ENCRYPTION_PASSWORD parameter is not supported
       with user-defined external tables that have encrypted columns. The  ta-
       ble will be skipped and an error message will be displayed, but the job
       will continue.

       Encryption attributes for all columns must match between  the  exported
       table  definition and the target table. For example, suppose you have a
       table, EMP, and one of its columns is named EMPNO. Both of the  follow-
       ing  situations  would  result  in  an  error  because  the  encryption
       attribute for the EMP column in the source table would  not  match  the
       encryption attribute for the EMP column in the target table:

       --The  EMP table is exported with the EMPNO column being encrypted, but
       prior to importing the table you remove the encryption  attribute  from
       the EMPNO column.

       --The  EMP  table is exported without the EMPNO column being encrypted,
       but prior to importing the table you enable  encryption  on  the  EMPNO
       column.


       ESTIMATE={BLOCKS | STATISTICS}
       Default: BLOCKS

       Specifies  the  method  that  Export will use to estimate how much disk
       space each table in the export job will consume (in bytes).  The  esti-
       mate  is  printed  in the log file and displayed on the client standard
       output device. The estimate is for table row data  only;  it  does  not
       include metadata.

       If  you  specify  BLOCKS, the estimate is calculated by multiplying the
       number of database blocks used by the source objects, times the  appro-
       priate block sizes.

       If  you specify STATISTICS, the estimate is calculated using statistics
       for each table. For this method to be  as  accurate  as  possible,  all
       tables should have been analyzed recently.

       If  the  Data  Pump  export job involves compressed tables, the default
       size estimation given for the compressed table is inaccurate when ESTI-
       MATE=BLOCKS is used. This is because the size estimate does not reflect
       that the data was stored in a compressed form. To get a  more  accurate
       size estimate for compressed tables, use ESTIMATE=STATISTICS.

       ESTIMATE_ONLY={y | n}
       Default: n

       Instructs  Export to estimate the space that a job would consume, with-
       out actually performing the export operation.

       If ESTIMATE_ONLY=y, then Export estimates the space that would be  con-
       sumed, but quits without actually performing the export operation.

       EXCLUDE=object_type
       Default: none

       Enables  you  to  filter  the  metadata  that is exported by specifying
       objects and object types that you want excluded from the export  opera-
       tion.

       All  object  types for the given mode of export will be included except
       those specified in an EXCLUDE statement. If an object is excluded,  all
       of  its  dependent  objects are also excluded. For example, excluding a
       table will also exclude all indexes and triggers on the table.

       More than one EXCLUDE statement can be specified.

       FILESIZE=integer[B | K | M | G]
       Default: 0 (unlimited)

       Specifies the maximum size of each dump file. If the  size  is  reached
       for any member of the dump file set, that file is closed and an attempt
       is made to create a new file, if the file specification contains a sub-
       stitution variable.

       The  integer  can be followed by B, K, M, or G (indicating bytes, kilo-
       bytes, megabytes, and gigabytes respectively). Bytes  is  the  default.
       The  actual  size of the resulting file may be rounded down slightly to
       match the size of the internal blocks used in dump files.

       The minimum size for a file is ten times the default  Data  Pump  block
       size, which is 4 kilobytes.

       FLASHBACK_SCN=scn_value
       Default: none

       Specifies the system change number (SCN) that Export will use to enable
       the Flashback Query utility.

       The export operation is performed with data that is  consistent  as  of
       the  specified SCN. If the NETWORK_LINK parameter is specified, the SCN
       refers to the SCN of the source database.

       FLASHBACK_SCN and FLASHBACK_TIME are mutually exclusive.

       FLASHBACK_TIME="TO_TIMESTAMP(time-value)"
       Default: none

       The SCN that most closely matches the specified time is found, and this
       SCN  is  used  to enable the Flashback utility. The export operation is
       performed with data that is consistent as of this SCN.

       Because the TO_TIMESTAMP value is enclosed in quotation marks, it would
       be best to put this parameter in a parameter file. Otherwise, you might
       need to use escape characters on the command line in front of the  quo-
       tation marks.

       FLASHBACK_TIME and FLASHBACK_SCN are mutually exclusive.

       FULL={y | n}
       Default: n

       Specifies that you want to perform a full database mode export.

       FULL=y indicates that all data and metadata are to be exported.

       To perform a full export, you must have the EXP_FULL_DATABASE role.

       The  following system schemas are not exported as part of a Full export
       because the metadata they contain is exported as part of other  objects
       in  the  dump file set: SYS, ORDSYS, EXFSYS, MDSYS, DMSYS, CTXSYS, ORD-
       PLUGINS, LBACSYS, XDB, SI_INFORMTN_SCHEMA, DIP, DBSNMP, and WMSYS.

       Grants on objects owned by the SYS schema are never exported.

       HELP = {y | n}.
       Default: N

       If HELP=y is specified, Export displays a summary of  all  Export  com-
       mand-line parameters and interactive commands.

       INCLUDE = object_type
       Default: none

       Enables  you  to  filter  the  metadata  that is exported by specifying
       objects and object types for the current  export  mode.  The  specified
       objects  and  all their dependent objects are exported. Grants on these
       objects are also exported.

       Only object types explicitly specified in INCLUDE statements, and their
       dependent  objects,  are exported. No other object types, including the
       schema definition information that is normally part  of  a  schema-mode
       export when you have the EXP_FULL_DATABASE role, are exported.

       JOB_NAME=jobname_string
       Default: system-generated name of the form SYS_EXPORT_<mode>_NN

       Used  to  identify  the export job in subsequent actions,  such as when
       the ATTACH parameter is used to attach to a job. The job  name  becomes
       the  name  of  the  master table in the schema of the current user. The
       master table is used to control the export job.

       The jobname_string specifies a name of up to 30 bytes for  this  export
       job.  The bytes must represent printable characters and spaces. If spa-
       ces are included, the name must be enclosed in single  quotation  marks
       (for  example, 'Thursday Export'). The job name is implicitly qualified
       by the schema of the user performing the export operation.

       The   default   job   name   is   system-generated    in    the    form
       SYS_EXPORT_<mode>_NN,  where NN expands to a 2-digit incrementing inte-
       ger   starting   at   01.   An   example   of   a   default   name   is
       'SYS_EXPORT_TABLESPACE_02'.

       LOGFILE=[directory_object:]file_name
       Default: export.log

       Specifies  the  name,  and optionally, a directory, for the log file of
       the export job.

       You can specify a database directory_object previously  established  by
       the DBA, assuming that you have access to it. This overrides the direc-
       tory object specified with the DIRECTORY parameter.

       The file_name specifies a name for the log file. The  default  behavior
       is to create a file named export.log in the directory referenced by the
       directory object specified in the DIRECTORY parameter.

       All messages regarding work in progress,  work  completed,  and  errors
       encountered are written to the log file. (For a real-time status of the
       job, use the STATUS command in interactive mode.)

       A log file is always created for an export  job  unless  the  NOLOGFILE
       parameter is specified. As with the dump file set, the log file is rel-
       ative to the server and not the client.

       An existing file matching the filename will be overwritten.

       NETWORK_LINK=source_database_link
       Default: none

       Enables an export from a (source) database identified by a valid  data-
       base  link.  The data from the source database instance is written to a
       dump file set on the connected database instance.

       The NETWORK_LINK parameter initiates an export using a  database  link.
       This  means that the system to which the expdp client is connected con-
       tacts the  source  database  referenced  by  the  source_database_link,
       retrieves  data from it, and writes the data to a dump file set back on
       the connected system.

       The source_database_link provided must be the name of a  database  link
       to  an  available  database.  If the database on that instance does not
       already have a database link, you or your DBA must create one.

       If the source database is read-only, then the user on the source  data-
       base  must  have  a  locally managed tablespace assigned as the default
       temporary tablespace. Otherwise, the job will fail.

       When the NETWORK_LINK parameter is used in conjunction with the  TABLES
       parameter,  only  whole  tables  can  be  exported  (not  partitions of
       tables).

       The only types of database links supported by  Data  Pump  Export  are:
       public, fixed-user, and connected-user. Current-user database links are
       not supported.

       NOLOGFILE={y | n}
       Default: n

       Specifies whether to suppress creation of a log file.

       Specify NOLOGFILE=y to suppress the default behavior of creating a  log
       file.  Progress  and error information is still written to the standard
       output device of  any  attached  clients,  including  the  client  that
       started the original export operation. If there are no clients attached
       to a running job and you specify NOLOGFILE=y, you run the risk of  los-
       ing important progress and error information.

       PARFILE=[directory_path]file_name
       Default: none

       Specifies the name of an export parameter file.

       Unlike  dump and log files, which are created and written by the Oracle
       database, the parameter file is opened and read by the  client  running
       the expdp image. Therefore, a directory object name is neither required
       nor appropriate. The directory path  is  an  operating  system-specific
       directory  specification.  The default is the user's current directory.
       The use of parameter files is  highly  recommended  if  you  are  using
       parameters whose values require the use of quotation marks.

       The PARFILE parameter cannot be specified within a parameter file.

       QUERY=[schema.][table_name:] query_clause
       Default: none

       Enables  you to filter the data that is exported by specifying a clause
       for a SQL SELECT statement, which is  applied  to  all  tables  in  the
       export job or to a specific table.

       The query_clause can be any SQL clause. For example, an ORDER BY clause
       could be used to speed up a migration from a heap-organized table to an
       index-organized  table.  If  a [schema.]table_name is not supplied, the
       query is applied to (and must be valid for) all tables  in  the  export
       job. A table-specific query overrides a query applied to all tables.

       When the query is to be applied to a specific table, a colon must sepa-
       rate the table name from the query clause. More than one table-specific
       query can be specified, but only one can be specified per table. Oracle
       highly recommends that you place QUERY specifications  in  a  parameter
       file; otherwise, you might have to use operating system-specific escape
       characters on the command line before each quotation mark.

       The query must be enclosed in single or double quotation marks.

       To specify a schema other than your own in a table-specific query,  you
       need the EXP_FULL_DATABASE role.

       The  QUERY  parameter  cannot be used in conjunction with the following
       parameters:

       --CONTENT=METADATA_ONLY

       --ESTIMATE_ONLY

       SAMPLE=[[schema_name.]table_name:]sample_percent
       Default: None

       Allows you to specify a percentage of data to be sampled  and  unloaded
       from the source database.

       This  parameter  allows you to export subsets of data by specifying the
       percentage of data to be sampled and exported. The sample_percent indi-
       cates  the probability that a block of rows will be selected as part of
       the sample. It does not mean that the database  will  retrieve  exactly
       that  amount  of  rows  from  the  table. The value you supply for sam-
       ple_percent can be anywhere from .000001 up to, but not including, 100.

       The sample_percent can be applied to specific tables. In the  following
       example, 50% of the HR.EMPLOYEES table will be exported:

       SAMPLE="HR"."EMPLOYEES":50

       If  you  specify  a schema, you must also specify a table. However, you
       can specify a table without specifying a schema; the current user  will
       be  assumed.  If  no  table is specified, then the sample_percent value
       applies to the entire export job.

       The SAMPLE parameter is not valid for network exports.

       SCHEMAS=schema_name [, ...]
       Default: schema of current user

       Specifies that you want to perform a schema-mode export.  This  is  the
       default mode for Export.

       If  you  have the EXP_FULL_DATABASE role, then you can specify a single
       schema  other  than  your  own  or  a  list  of   schema   names.   The
       EXP_FULL_DATABASE  role  also allows you to export additional nonschema
       object information for each specified schema so that the schemas can be
       re-created  at  import  time.  This additional information includes the
       user definitions themselves and all associated system and role  grants,
       user password history, and so on.

       If  you  do  not  have the EXP_FULL_DATABASE role, then you can specify
       only your own schema.

       The SYS schema cannot be used as a source schema for export jobs.

       STATUS=[integer]
       Default: 0

       Specifies the frequency at which the job status display is updated.

       If you supply a value for integer, it specifies how frequently, in sec-
       onds,  job  status  should be displayed in logging mode. If no value is
       entered or if the default value of 0 is used, no additional information
       is  displayed  beyond  information  about the completion of each object
       type, table, or partition.

       This status information is written only to your standard output device,
       not to the log file (if one is in effect).

       TABLES=[schema_name.]table_name [, ...]
       Default: none

       Specifies that you want to perform a table-mode export.

       The  table name that you specify can be preceded by a qualifying schema
       name. All table names specified must reside in  the  same  schema.  The
       schema  defaults to that of the current user. To specify a schema other
       than your own, you must have the EXP_FULL_DATABASE role.

       The use of wildcards is supported for one table name per export  opera-
       tion.  For  example,  TABLES=emp%  would export all tables having names
       that start with 'EMP'.

       Cross-schema references  are  not  exported.  For  example,  a  trigger
       defined  on  a  table  within  one  of  the specified schemas, but that
       resides in a schema not explicitly specified, is not exported.

       Types used by the table are not exported in table mode. This means that
       if  you subsequently import the dump file and the TYPE does not already
       exist in the destination database, the table creation will fail.

       The use of synonyms as values for the  TABLES  parameter  is  not  sup-
       ported.  For  example, if the regions table in the hr schema had a syn-
       onym of regn, it would not be valid to use TABLES=regn. An error  would
       be returned.

       The  export  of  tables that include wildcards in the table name is not
       supported if the table has partitions.

       TABLESPACES=tablespace_name [, ...]
       Default: none

       Specifies a list of tablespace names to be exported in tablespace mode.

       In tablespace mode, only the tables contained in  a  specified  set  of
       tablespaces are unloaded. If a table is unloaded, its dependent objects
       are also unloaded. If any part of a table resides in the specified set,
       then that table and all of its dependent objects are exported.

       VERSION={COMPATIBLE | LATEST | version_string}
       Default: COMPATIBLE

       Specifies  the  version of database objects to be exported. This can be
       used to create a dump file set  that  is  compatible  with  a  previous
       release of Oracle Database.

       The legal values for the VERSION parameter are as follows:

       COMPATIBLE  -  This  is  the default value. The version of the metadata
       corresponds to the database compatibility level. Database compatibility
       must be set to 9.2 or higher.

       LATEST  -  The version of the metadata corresponds to the database ver-
       sion.

       version_string - A specific database version (for example, 10.0.0).  In
       Oracle Database 10g, this value cannot be lower than 9.2.

       Database objects or attributes that are incompatible with the specified
       version will not  be  exported.  For  example,  tables  containing  new
       datatypes  that  are not supported in the specified version will not be
       exported.


   Commands Available in Interactive Mode for Oracle Data Pump Export
       In interactive-command mode, the current  job  continues  running,  but
       logging to the terminal is suspended and the Export prompt (Export>) is
       displayed.

       To start interactive-command mode, do one of the following:

       --From an attached client, press Ctrl+C.

       --From a terminal other than the one on which the job is running, spec-
       ify the ATTACH parameter in an expdp command to attach to the job. This
       is a useful feature in situations in which you start a job at one loca-
       tion and need to check on it at a later time from a different location.

       The  following  commands are valid while in interactive mode (abbrevia-
       tions are allowed):

       ADD_FILE=[directory_object]file_name [,...]
       Adds additional files or wildcard file templates  to  the  export  dump
       file set.

       The file_name must not contain any directory path information. However,
       it can include a substitution variable, %U, which indicates that multi-
       ple  files may be generated using the specified filename as a template.
       It can also specify another directory_object.

       The size of the file being added is determined by the  setting  of  the
       FILESIZE parameter.

       When  you  use the ADD_FILE interactive command, Oracle recommends that
       the DIRECTORY parameter for the job be specified on  the  command  line
       rather than in a parameter file.

       CONTINUE_CLIENT
       Changes the Export mode from interactive-command mode to logging mode.

       In  logging  mode, status is continually output to the terminal. If the
       job is currently stopped, then  CONTINUE_CLIENT  will  also  cause  the
       client to attempt to start the job.

       EXIT_CLIENT
       Stops the export client session, exits Export, and discontinues logging
       to the terminal, but leaves the current job running.

       Because EXIT_CLIENT leaves the job running, you can attach to  the  job
       at a later time.

       FILESIZE=number
       Redefines the default size to be used for any subsequent dump files.

       The  file  size  can  be followed by B, K, M, or G to indicate that the
       size is expressed in bytes, kilobytes, megabytes, or gigabytes, respec-
       tively. The default is B.

       A file size of 0 indicates that there will not be any size restrictions
       on new dump files. They will be extended as needed until the limits  of
       the containing device are reached.

       HELP
       Provides  information  about  Data  Pump  Export  commands available in
       interactive-command mode.

       KILL_JOB
       Detaches all currently attached client sessions and then kills the cur-
       rent job. It exits Export and returns to the terminal prompt.

       A  job  that is killed using KILL_JOB cannot be restarted. All attached
       clients, including the one issuing  the  KILL_JOB  command,  receive  a
       warning  that  the job is being killed by the current user and are then
       detached. After all clients are detached, the process structure of  the
       job  is  immediately  run  down and the master table and dump files are
       deleted. Log files are not deleted.

       START_JOB
       Starts the current job to which you are attached.

       The START_JOB command  restarts  the  current  job  to  which  you  are
       attached  (the job cannot be currently executing). The job is restarted
       with no data loss or corruption after an unexpected  failure  or  after
       you  issued  a  STOP_JOB command, provided the dump file set and master
       table have not been altered in any way.

       STATUS[=integer]
       Displays cumulative status of the job, along with a description of  the
       current  operation.  An  estimated completion percentage for the job is
       also returned. Also allows resetting the display interval  for  logging
       mode status.

       You have the option of specifying how frequently, in seconds, this sta-
       tus should be displayed in logging mode. If no value is entered  or  if
       the  default  value of 0 is used, the periodic status display is turned
       off and status is displayed only once.

       This status information is written only to your standard output device,
       not to the log file (even if one is in effect).

       STOP_JOB[=IMMEDIATE]
       Stops  the current job either immediately or after an orderly shutdown,
       and exits Export.

       If the master table and dump file set are not disturbed when  or  after
       the  STOP_JOB  command  is  issued,  the  job  can  be  attached to and
       restarted at a later time with the START_JOB command.

       To perform an orderly shutdown, use STOP_JOB  (without  any  associated
       value).  A  warning  requiring  confirmation will be issued. An orderly
       shutdown stops the job after worker processes have finished their  cur-
       rent tasks.

       To perform an immediate shutdown, specify STOP_JOB=IMMEDIATE. A warning
       requiring confirmation will be issued. All attached clients,  including
       the one issuing the STOP_JOB command, receive a warning that the job is
       being stopped by the current user and they will be detached. After  all
       clients  are  detached, the process structure of the job is immediately
       run down. That is, the master process will not wait for the worker pro-
       cesses to finish their current tasks. There is no risk of corruption or
       data loss when you specify STOP_JOB=IMMEDIATE. However, some tasks that
       were  incomplete  at  the  time  of  shutdown  may have to be redone at
       restart time.


EXAMPLE
       The following is an example of a table-mode export. Because user hr  is
       exporting  tables in his own schema, it is not necessary to specify the
       schema name for the tables. The NOLOGFILE=y parameter indicates that an
       Export log file of the operation will not be generated.

        expdp hr/hr TABLES=employees,jobs DUMPFILE=dpump_dir1:table.dmp NOLOG-
       FILE=y


SEE ALSO
       imp(1) exp(1) impdp(1) oracle(1) sqlplus(1) htmldb(1) sqlldr(1)


AUTHORS
       Oracle Corporation


REPORTING BUGS
       Report bugs to oraclexeAToracle.com.


COPYRIGHT
       Copyright (C) 2005 Oracle. All rights reserved.



Oracle XE Manual Pages          September 2005                        expdp(1)