Shulou(Shulou.com)06/01 Report--

Things to pay attention to when using EXPDP and IMPDP:

EXP and IMP are client-side tools that can be used either on the client side or on the server side.

EXPDP and IMPDP are server-side tools, they can only be used on the ORACLE server, not on the client.

IMP applies only to EXP exported files, not EXPDP exported files, and IMPDP applies only to EXPDP exported files, not EXP exported files.

When using the expdp or impdp command, do not specify the username / password @ instance name as identity for the time being, and then enter it as prompted, such as:

Expdp schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1

First, create a logical directory. This command does not create a real directory in the operating system, but is best created by administrators such as system.

Create directory dpdata1 as'd:\ test\ dump'

Second, check the administrator directory (also check whether the operating system exists, because Oracle does not care if the directory exists, if it does not exist, it will make an error)

Select * from dba_directories

Third, give scott users the right to operate in a specified directory, preferably by administrators such as system.

Grant read,write on directory dpdata1 to scott

IV. Export data

1) by user guide

Expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1

2) parallel process parallel

Expdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3

3) Guide by table name

Expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1

4) Guide according to query conditions

Expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20'

5) according to the table space guide

Expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example

6) Import the entire database

Expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y

5. Restore data

1) Import to the specified user

Impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott

2) change the owner of the table

Impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system

3) Import tablespace

Impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example

4) Import database

Impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y

5) additional data

Impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION

Data pump impdp parameters:

1.REMAP_DATAFILE this option is used to convert the source data file name to the target data file name, which is required when moving tablespaces between different platforms. REMAP_DATAFILE=source_datafie:target_datafile

2.REMAP_SCHEMA this option is used to load all objects of the source scheme into the target scheme. REMAP_SCHEMA=source_schema:target_schema

3.REMAP_TABLESPACE imports all objects from the source tablespace into the target tablespace REMAP_TABLESPACE=source_tablespace:target:tablespace

4.REUSE_DATAFILES this option specifies whether to overwrite existing data files when creating tablespaces. The default is N REUSE_DATAFIELS= {Y | N}

5.SKIP_UNUSABLE_INDEXES specifies whether to skip unusable indexes for import. By default, the parameter N6 DDL sqlfile allows you to create DDL script files under directory, so do not specify the absolute path 7.STREAMS_CONFIGURATION specifies whether to import stream metadata (Stream Matadata), the default is Y.

8.TABLE_EXISTS_ACTION this option is used to specify the action to be performed by the import job when the table already exists. The default is SKIP TABBLE_EXISTS_ACTION= {SKIP | APPEND | TRUNCATE | REPLACE} when this option is set to SKIP, the import job will skip the existing table to process the next object; when set to APPEND, the data will be appended; when set to TRUNCATE, the import job will truncate the table and then append new data to it When set to REPLACE, the import job deletes the existing table, rebuilds the table, and appends data

Note that the TRUNCATE option does not apply to the cluster table and NETWORK_LINK options

Import from one user's tablespace to another user's tablespace impdp username/passwd directory=expdptemp dumpfile=2015.11.24.dmp remap_schema=training_zj:hagen.zj REMAP_TABLESPACE=training:zhejiang.jianli

About the problem that the empty table exp will not be exported

1. Oracle11g does not allocate segment to empty tables by default, so when exporting Oracle11g databases using exp, empty tables will not be exported.

2. When the deferred_segment_creation parameter is set to FALSE, segment is assigned to both empty and non-empty tables.

In sqlplus, execute the following command:

SQL > alter system set deferred_segment_creation=false

View:

SQL > show parameter deferred_segment_creation

When this value is set, it only has an effect on the newly added tables, not on the previously created empty tables.

3. You can manually assign Extent to the empty table to solve the problem of exporting the empty table established before. The explanation is as follows:

3.1instructions for using ALLOCATE EXTENT

Use ALLOCATE EXTENT to assign Extent to database objects. The syntax is as follows:

-

ALLOCATE EXTENT {SIZE integer [K | M] | DATAFILE 'filename' | INSTANCE integer}

-

Extent can be manually allocated for data tables, indexes, materialized views, and so on.

Sample usage of ALLOCATE EXTENT:

ALLOCATE EXTENT

ALLOCATE EXTENT (SIZE integer [K | M])

ALLOCATE EXTENT (DATAFILE 'filename')

ALLOCATE EXTENT (INSTANCE integer)

ALLOCATE EXTENT (SIZE integer [K | M] DATAFILE 'filename')

ALLOCATE EXTENT (SIZE integer [K | M] INSTANCE integer)

The complete syntax for data table operations is as follows:

-

ALTER TABLE [schema.] table_name ALLOCATE EXTENT [({SIZE integer [K | M] | DATAFILE 'filename' | INSTANCE integer})]

-

Therefore, you need to build a simple SQL command like this:

-

Alter table aTabelName allocate extent

-

3.2 build a SQL command to allocate space to an empty table

Query all empty tables under the current user (one user preferably corresponds to a default tablespace). The command is as follows:

-

SQL > select table_name from user_tables where NUM_ROWS=0

-

Based on the above query, you can build a command statement to allocate space for an empty table, as follows:

-

SQL > Select 'alter table' | | table_name | | 'allocate extent;' from user_tables where num_rows=0

-

Batch output the SQL statement generated above, and establish C:\ createsql.sql, which contains the following:

-

Set heading off

Set echo off

Set feedback off

Set termout on

Spool C:\ allocate.sql

Select 'alter table' | | table_name | | 'allocate extent;' from user_tables where num_rows=0

Spool off

-

Execute C:\ createsql.sql with the following command:

-

SQL > @ C:\ createsql.sql

-

When the execution is complete, you get the C:\ allocate.sql file.

If you open the file, you will see that you have the command SQL statement to allocate space to all empty tables.

3.4 execute the SQL command to allocate space to the empty table:

Execute C:\ allocate.sql with the following command:

-

SQL > @ C:\ allocate.sql

-

After execution, the table has been changed.

3.4 execute the exp command at this time, and all tables, including empty tables, can be exported normally.

In addition: in Oracle11g, passwords are case-sensitive, that is, letters in passwords are case-sensitive.

In Oracle10g and before, it doesn't matter whether the letters in the password are upper or lower.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.