Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the role of impdp,expdp in oracle database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

When migrating an oracle database, it is recommended to use a data pump if the amount of data in the database is less than 100G. If the amount of data in the database is greater than 100G, it is recommended to use other data synchronization tools.

If the user has no requirement for downtime, you can consider data migration by user.

Impdp,expdp can achieve cross-platform, cross-version data migration.

Oracle database uses data pump to import and export the same user name and table space. If you want to see the export, you can add the name of the logfile= log to view the detailed process of export and import. If it is not the same user name and table space, you need to add relevant parameters.

Create a catalog

Create directory dump_dir as'/ home/oracle/dump/'

Grant read,write on directory dump_dir to public

Export of data

-- 1) by user guide

Expdp scott/tiger@192.168.0.110:1521/orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dump_dir

-- 2) parallel process parallel

Expdp scott/tiger@192.168.0.110:1521/orcl directory=dump_dir dumpfile=scott3.dmp parallel=40 job_name=scott3

-- 3) Guide by table name (multiple tables are separated by commas)

Expdp scott/tiger@192.168.0.110:1521/orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dump_dir

-- 4) Guide according to query conditions

Expdp scott/tiger@192.168.0.110:1521/orcl directory=dump_dir dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20'

-5) guided by tablespaces (multiple tablespaces are separated by commas)

Expdp system/oracle DIRECTORY=dump_dir DUMPFILE=tablespace.dmp TABLESPACES=temp,example

-- 6) Import the entire database

Expdp\'/ as sysdba\ 'DIRECTORY=dump_dir DUMPFILE=tablespace.dmp FULL=y

-

Import of data

-- 1) Import to the specified user

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

-- 2) Import tables

Impdp system/oracle DIRECTORY=dump_dir DUMPFILE=expdp.dmp TABLES=scott.dept

-- 3) Import tablespace

Impdp system/oracle DIRECTORY=dump_dir DUMPFILE=tablespace.dmp TABLESPACES=example

-- 4) Import database

Impdp system/oracle DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y

-

If it is a different version of the database, different tablespaces, different users need to use version,remap_tablespace,remap_schema

Among them

-

If a user from the original library only exports the table structure under the user, not the data, that is, we often say that only the metadata under the user is exported (the metadata is called the data for managing data, for example, the structure of the table is called metadata), it needs to be used in expdp.

Content=metadata_only parameter

-

Multiple tablespace objects are imported into one tablespace

Impdp yngtts/yngtts DIRECTORY=DATA_PUMP_DIR dumpfile=mydir:yngtts0514.dmp logfile=mydir:yngtts0514.log remap_schema=yngtts/yngtts remap_tablespace=' (TS_YNGT:TS_YNGT,CWFTS:TS_YNGT,TS_EDIDB_DEFAULT:TS_YNGT)'

-

TABLE_EXISTS_ACTION

TABLE_EXISTS_ACTION= [skip | APPEND | TRUNCATE | REPLACE]

SKIP leaves the table as is and moves to the next object. This is not a valid option DATA_ONLY if the CONTENT parameter is set to.

APPEND loads rows from the source and leaves existing rows unchanged.

TRUNCATE deletes the existing row and then loads the row from the source.

REPLACE deletes the existing table and then creates and loads it from the source. This is not a valid option DATA_ONLY if the CONTENT parameter is set to.

The following considerations apply when using these options:

When using TRUNCATE or REPLACE, make sure that the rows in the affected table are not the target of any reference constraints.

Using SKIP,APPEND or TRUNCATE existing table dependent objects (such as indexes, authorizations, triggers, and constraints) in the source will not be modified. Because of REPLACE, if they are not explicitly or implicitly excluded (using EXCLUDE) and they exist in the source dump file or system, the dependent objects are deleted from the source and recreated.

When using APPEND or TRUNCATE, a check is performed to ensure that the rows in the source are compatible with the existing table before performing any action.

If the existing table has active constraints and triggers, load it using external table access methods. If any row violates the active constraint, the load fails and no data is loaded. You can override this behavior by specifying it on the Import command line through DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS.

If you have data that must be loaded, but may cause a constraint violation, consider disabling the constraint, loading the data, and then deleting the problem row before re-enabling the constraint.

When using APPEND, the data is always loaded into the new space; even if it is available, the existing space will not be reused. Therefore, you may want to compress the data after loading.

This is the end of the content of "what is the use of impdp,expdp in oracle database". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.