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

Link: http://blog.itpub.net/28602568/viewspace-759524/

Title: Oracle expdp data pump remote export

Author: expdp tau é ry ©all rights reserved [articles are allowed to be reprinted, but the source address must be indicated by link, otherwise legal liability shall be investigated.] expdp data pump has the advantages of supporting concurrent multithreading mode, can be exported remotely, has significantly improved performance, and can open parallel parallel=n. Compared with the exp/imp utility, the export increases by about 10 times, and the import increases by about 5 times. It can automatically create users and table objects according to the metadata collected during backup, and support job and tablespace backup mode. Disadvantages: create directory authorization needs to be performed on the server. Expdp and exp both support remote local export. (exp does not support field types. BINARY_DOUBLE double precision binary floating point type http://www.2cto.com/database/201202/119920.html number type differs from floating point type "number decimal, BINARY_ double binary (advantages of higher efficiency, faster speed and less space footprint than number. But version compatibility is not good)")

(expdp does not support network chain to load long "Save text Type" column, and exp supports export and import of long)

When "DB is out of space and needs to export backup / restore across machines", you need to use the remote export of expdp to achieve: as long as you create public link and dir in the local database and give the corresponding permissions, the remote DB can be exported to the local path.

The specific experiments are as follows: all operations are performed locally in SQL > create user test identified by test

SQL > grant dba to test

-- > create directory SQL > create directory dump_dir AS'e:\ dump_dir'

SQL > select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories where directory_name='DUMP_DIR'

OWNER DIRECTORY_NAME DIRECTORY_PATH

- -

SYS DUMP_DIR E:\ dump_dir

-- > create DBLINK

SQL > create public database link kfdb7 connect to user identified by "password" using'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = XX.XX.XX.XX) (PORT = 1521 port number))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SID)'

-- > verify dblink validity SQL > conn test/test

Connected.

SQL > select * from tab

No rows selected

SQL > select count (*) from base_dict@kfdb57

COUNT (*)-683

* Note: to create a public database link, if you only create a database link, you can only access the link library data under the current user, but the exported error ORA-39001: invalid parameter value.

ORA-39200: the link name "kfdb5" is invalid.

ORA-02019: no connection instructions were found for the remote database

Specific explanation:

Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services. After you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @ dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement. To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database. Oracle Net must be installed on both the local and remote Oracle databases.

Export command: e:\ oracle\ product\ 10.2.0\ db_1\ BIN > EXPDP.EXE test/test network_link=kfdb57 directory=DUMP_DIR1 dumpfile=base_dict.dmp tables='base_dict' Export: Release 10.2.0.1.0-Production on Monday, November, 2013 17:51:56 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production With the Partitioning, OLAP and Data Mining options launches "TEST". "SYS_EXPORT_TABLE_01": test/* network_link=kfdb57 directory=DUMP_DIR1 dumpfile=base_dict.dmp tables='base_dict' is using the BLOCKS method to estimate. Total estimate of processing object type TABLE_EXPORT/TABLE/TABLE_DATA using the BLOCKS method: 64 KB processing object type TABLE_EXPORT/TABLE/TABLE processing object type TABLE_EXPORT/TABLE/INDEX/INDEX processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS processing object type TABLE_EXPORT/TABLE/COMMENT processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . Exported "SINOSOFT". "BASE_DICT" 35.73 KB line 683 has successfully loaded / unloaded the main table "TEST". "SYS_EXPORT_TABLE_01" * * the dump file set for TEST.SYS_EXPORT_TABLE_01 is: e:\ DUMP_DIR\ BASE_DICT.DMP job "TEST". "SYS_EXPORT_TABLE_01" completed successfully at 17:54:09

Extension: import the exported files into the local database command: impdp.exe test/test directory=dump_dir1 dumpfile=base_dict.dmp tables=base_dict REMAP_SCHEMA=sinosoft:test View Import: SQL > select * from tab

TNAME TABTYPE CLUSTERID

SYS_IMPORT_TABLE_01 TABLE

SQL > select * from tab

TNAME TABTYPE CLUSTERID

SYS_IMPORT_TABLE_01 TABLE-- > the database automatically deletes the master table after completion (no if the export fails)

BASE_DICT TABLE

SQL > select * from tab

TNAME TABTYPE CLUSTERID

BASE_DICT TABLE

SQL > select count (*) from base_dict

COUNT (*)

-

six hundred and eighty three

Delete dblink+ directory drop directory dump_dir / drop public database link kfdb57 after testing

Extension: study the influence relationship between IMPDP [TRANSFORM=segment_attributes:n] [remap_tablespace] 2 parameters-- > for impdp+dblink with or without default tablespace data stored in the tablespace.

[from my notes] if there are any mistakes in writing or expression, please correct them.

This entry is published in the EXP [DP] / IMP [DP] category. Add the fixed connection to your favorites.

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.