Oracle 11G R2 with exp cannot export empty table workaround

Iv. Oracle 10g added EXPDP and IMPDP tools to export empty tables with this tool

ORACLEEXPDP/IMPDP Usage Explanation

1) Create a logical directory that does not create a real directory in the operating system, preferably created by an administrator such as system.

CreateDirectory Db_bak as ' d:\test\dump ';

2) View the Management Manager directory (and see if the operating system exists, because Oracle does not care if the directory exists, and if it does not exist, an error occurs)

SELECT * Fromdba_directories;

3) give the system user permission to operate in the specified directory, preferably as a system administrator.

Grantread,write on the directory Db_bak to system;

4) Export data

L according to the user guide

EXPDP system/[email Protected]=system dumpfile=expdp.dmp Directory=db_bak

L Parallel Process Parallel

EXPDP system/[email protected]=db_bak dumpfile=system3.dmp parallel=40 job_name=system3

L Guided by Table name

EXPDP system/[email protected]=emp,dept dumpfile=expdp.dmp Directory=db_bak;

• Guided by query criteria

EXPDP system/[email protected]=db_bak dumpfile=expdp.dmp tables=emp query= ' WHERE deptno=20 ';

L By Table Space Guide

Expdpsystem/manager Directory=db_bak dumpfile=tablespace.dmptablespaces=temp,example;

L GUIDE the entire database

Expdpsystem/mtmadmin Directory=db_bak dumpfile=full.dmp full=y

5) Five, restore data

L 1) leads to the specified user

IMPDP System/managerdirectory=db_bak dumpfile=expdp.dmp Schemas=system;

L 2) Change the owner of the table

Impdpsystem/manager Directory=db_bak dumpfile=expdp.dmp Tables=system.deptremap_schema=system:system;

L 3) Import table space

Impdpsystem/manager Directory=db_bak dumpfile=tablespace.dmp tablespaces=example;

L 4) Import Database

Impdbsystem/mtmadmin Directory=db_bak dumpfile=full.dmp full=y;

L 5) Append Data

Impdpsystem/manager Directory=db_bak dumpfile=expdp.dmp schemas=systemtable_exists_action=append;

Five, the relevant parameters of EXPDP/IMPDP

EXPDP Command-line Options

1. ATTACH

This option is used to establish an association between a client session and an existing export. The syntax is as follows

Attach=[schema_name.] Job_name

The schema_name is used to specify the scheme name, and job_name is used to specify the export job name. Note that if you use the Attach option, you cannot specify any other options except the connection string and the Attach option in the command line, as shown in the following example:

EXPDP System/managerattach=system.export_job

2. CONTENT

This option is used to specify what to export. The default value is all

content={all| data_only | Metadata_only}

When content is set to all, the object definition and all its data are exported. When data_only, only the object data is exported, and only the object definition is exported as Metadata_only

EXPDP System/managerdirectory=dump Dumpfile=a.dump

Content=metadata_only

3. DIRECTORY

Specify the directory where the dump files and log files reside

Directory=directory_object

The directory_object is used to specify the directory object name. Note that directory objects are objects created using the Create DIRECTORY statement, not the OS directory

EXPDP System/managerdirectory=dump Dumpfile=a.dump

To create a directory:

CreateDirectory dump as ' d:dump ';

The query created those subdirectories:

SELECT * fromdba_directories;

4. DumpFile

Used to specify the name of the dump file, the default name is Expdat.dmp

dumpfile=[directory_object:]file_name[,....]

The directory_object is used to specify the directory object name, and file_name is used to specify the dump file name. Note that if you do not specify Directory_object, the export tool automatically uses the directory object specified by the directory option

EXPDP SYSTEM/MANAGERDIRECTORY=DUMP1 Dumpfile=dump2:a.dmp

5. ESTIMATE

Specifies the method by which to estimate the disk space consumed by the exported table. The default value is blocks

extimate={blocks| STATISTICS}

When set to blocks, Oracle calculates the object footprint based on recent statistics by multiplying the number of data blocks occupied by the target object by the size of the data block to estimate the amount of space the object occupies, when set to statistics

EXPDP system/managertables=emp Estimate=statistics

Directory=dumpdumpfile=a.dump

6.extimate_only

Specifies whether to estimate only the disk space occupied by the export job, the default value is N

extimate_only={y| N

When set to Y, the export only estimates the disk space occupied by the object, not the export job, which, when N, not only estimates the disk space occupied by the object, but also performs an export operation.

EXPDP system/managerestimate_only=y Nologfile=y

7. EXCLUDE

This option is used to specify whether to dispose of object types or related objects when an operation is performed

exclude=object_type[:name_clause][,....]

The object_type is used to specify the type of object to exclude, and name_clause is used to specify the specific object to exclude. Exclude and include cannot be used at the same time

EXPDP system/managerdirectory=dump dumpfile=a.dup Exclude=view

8. FILESIZE

Specifies the maximum size of the exported file, which defaults to 0 (indicates no limit on file size)

9.flashback_scn

Specify table data to export specific SCN moments

Flashback_scn=scn_value

The scn_value is used to identify the SCN value. FLASHBACK_SCN and Flashback_time cannot be used simultaneously

EXPDP System/managerdirectory=dump Dumpfile=a.dmp

flashback_scn=358523

10.flashback_time

Specify the export of table data at a specific point in time

Flashback_time= "To_timestamp (time_value)"

EXPDP system/managerdirectory=dump dumpfile=a.dmp flashback_time=

"To_timestamp (' 25-08-2004 14:35:00 ', ' dd-mm-yyyy HH24:MI:SS ')"

One. Full

Specify database schema export, default = N

Full={y | N

Is Y, the identity performs a database export.

Help

Specifies whether to display help information for the EXPDP command-line option, default = N

When set to Y, the help information for the export option is displayed.

EXPDP help=y

. INCLUDE

Specify the type of objects to include in the export and related objects

INCLUDE =object_type[:name_clause] [,...]

Job_name.

Specifies the name of the action to export, which defaults to Sys_xxx

Job_name=jobname_string

LOGFILE.

Specifies the name of the exported log file file, the default name is Export.log

Logfile=[directory_object:]file_name

The directory_object is used to specify the directory object name, and file_name is used to specify the export log file name. If you do not specify Directory_object. The export function automatically uses the appropriate option values for your directory.

EXPDP system/managerdirectory=dump dumpfile=a.dmp Logfile=a.log

16.network_link

Specifies the database chain name, which must be set if you want to export the remote database object to a dump file for a local routine.

Nologfile.

This option specifies that the export log file is prohibited from being generated and the default value is N.

PARALLEL.

Specifies the number of parallel processes that perform the export operation, with a default value of 1

Parfile.

Specify the name of the export parameter file

Parfile=[directory_path]file_name

. QUERY

Used to specify where conditions to filter the exported data

Query=[schema.] [TABLE_NAME:] Query_clause

Schema is used to specify the scheme name, TABLE_NAME is used to specify the table name, and Query_clause is used to specify a conditional restriction clause. The query option cannot be used in conjunction with options such as Connect=metadata_only,extimate_only,transport_tablespaces.

EXPDP System/managerdirectory=dump Dumpfiel=a.dmp

tables=empquery= ' wheredeptno=20 '

SCHEMAS.

This scenario is used to specify the execution scheme pattern export, which is implicitly considered the current user scenario.

STATUS

Specifies the verbose status of the export process, with a default value of 0

TABLES.

Specify table Schema Export

Tables=[schema_name.] table_name[:p artition_name][,...]

Schema_name is used to specify the scheme name, TABLE_NAME is used to specify the name of the exported table, and partition_name is used to specify the name of the partition to export.

24.TABLESPACES

Specify the table space list to export

25.transport_full_check

This option is used to specify the check method for the moved table space and the non-moving Tablespace association relationship, by default, N.

When set to Y, the export checks the full association of the table space directly, and an error message is displayed if only one tablespace is moved in the table space where the tablespace or its index resides. When set to N, the export only checks for single-ended dependencies, if the table space of the index is removed, but the table space is not moved. An error message is displayed, and if the table space of the move table is not moved, the table space in which the index is located is not displayed.

26.transport_tablespaces

Specify to perform table space mode export

VERSION

Specifies the database version of the exported object, the default value is compatible.

version={compatible| LATEST | Version_string}

When compatible, the object metadata is generated based on the initialization parameter compatible, and the object metadata is generated based on the actual version of the database when latest. Version_string is used to specify the database version string. Call EXPDP

When using the EXPDP tool, its dump file can only be stored in the OS directory of the directory object, not directly specifying the OS directory where the dump file resides. Therefore,

When you use the EXPDP tool, you must first establish a directory object. And you need to grant database users permission to use directory objects.