达梦数据库dexp&dimp工具使用的实战演练

1dexp&dimp简介

逻辑导出和逻辑导入数据库对象分为四种级别：数据库级、用户级、模式级和表级。四
种级别独立互斥，不能同时存在。四种级别所提供的功能：
 数据库级（FULL）：导出或导入整个数据库中的所有对象。
 用户级（OWNER）：导出或导入一个或多个用户所拥有的所有对象。
 模式级（SCHEMAS）：导出或导入一个或多个模式下的所有对象。
 表级（TABLES）：导出或导入一个或多个指定的表或表分区。

2 dexp四种级别的导出方式
2.1 FULL
[root@dmp bin]# ./dexp USERID=SYSDBA/SYSDBA FILE=db_str_%U.dmp LOG=db_str_%U.log FULL=Y DIRECTORY=/home/dmdba
dexp V8

export NO. 1 SYSPACKAGE_DEF : SYSTEM_PACKAGES

export NO. 2 SYSPACKAGE_DEF : SYS_VIEW

----- export total 2 SYSPACKAGE_DEF -----

----- export total 0 SYSPACKAGE_DATA -----

----- export total 0 SYNONYM -----

----- export total 0 DBLINK -----

export NO. 1 USER : ITPUX

----- export total 1 USER -----

export NO. 1 ROLE : itpux

----- export total 1 ROLE -----

----- export total 0 CONTEXT -----

----- export total 0 DIRECTORY -----

----- export total 0 SYSTEM_PRIVILEGE -----

----- export total 0 USER_ROLE_PRIVILEGE -----

export NO. 1 SYS_ROLE_PRIVILEGE : DBA

export NO. 2 SYS_ROLE_PRIVILEGE : PUBLIC

export NO. 3 SYS_ROLE_PRIVILEGE : VTI

export NO. 4 SYS_ROLE_PRIVILEGE : SOI

export NO. 5 SYS_ROLE_PRIVILEGE : VTI

----- export total 5 SYS_ROLE_PRIVILEGE -----

export NO. 1 SYS_ROLE_PRIVILEGE : DBA

export NO. 2 SYS_ROLE_PRIVILEGE : RESOURCE

export NO. 3 SYS_ROLE_PRIVILEGE : PUBLIC

export NO. 4 SYS_ROLE_PRIVILEGE : VTI

export NO. 5 SYS_ROLE_PRIVILEGE : SOI

----- export total 5 SYS_ROLE_PRIVILEGE -----

the privilege of the object at the export mode…

exporting NO. 1 SCHEMA : ITPUX

start export schema[ITPUX]…

----- export total 0 SEQUENCE -----

----- export total 0 VIEW -----

----- export total 0 TRIGGER -----

----- export total 0 COMMENT VIEW -----

----- export total 0 COMMENT COL -----

----- export total 0 PROCEDURE -----

----- export total 0 SYNONYM -----

----- export total 0 DBLINK -----

----- export total 0 TRIGGER -----

----- export total 0 PACKAGE -----

----- export total 0 PKG_BODY -----

----- export total 0 OBJECT of NO REFER OTHER CLASS -----

----- export total 0 OBJECT of REFER OTHER CLASS -----

----- export total 0 JCLASS -----

----- export total 0 CLASS_BODY -----

----- export total 0 DOMAIN -----

the privilege of the object at the export mode…

schema[ITPUX] export terminate…

successfully exported NO.1 SCHEMA : ITPUX

exporting NO. 2 SCHEMA : SYSDBA

start export schema[SYSDBA]…

----- export total 0 SEQUENCE -----

----- export total 0 VIEW -----

----- export total 0 TRIGGER -----

----- export total 0 COMMENT VIEW -----

----- export total 0 COMMENT COL -----

----- export total 0 PROCEDURE -----

----- export total 0 SYNONYM -----

----- export total 0 DBLINK -----

----- export total 0 TRIGGER -----

----- export total 0 PACKAGE -----

----- export total 0 PKG_BODY -----

----- export total 0 OBJECT of NO REFER OTHER CLASS -----

----- export total 0 OBJECT of REFER OTHER CLASS -----

----- export total 0 JCLASS -----

----- export total 0 CLASS_BODY -----

----- export total 0 DOMAIN -----

the privilege of the object at the export mode…

---- [2021-02-25 05:19:00]export table:T -----

the privilege of the object at the export mode…

table :T export terminate, total export 2 rows

---- [2021-02-25 05:19:00]export table:T1 -----

the privilege of the object at the export mode…

table :T1 export terminate, total export 2 rows

---- [2021-02-25 05:19:00]export table:TEST -----

the privilege of the object at the export mode…

table :TEST export terminate, total export 6 rows

schema[SYSDBA] export terminate…

successfully exported NO.2 SCHEMA : SYSDBA

export total 2 SCHEMA

all the export process spent total 0.598 s

terminate export success without warning

2.2 OWNER
[root@dmp bin]# ./dexp USERID=SYSDBA/SYSDBA FILE=db_str_%U.dmp LOG=db_str_%U.log OWNER=ITPUX DIRECTORY=/home/dmdba
dexp V8

[WARNING]FILE “/home/dmdba/db_str_01.dmp” has already existed
whether to overwrite(y/n, 1/0):1

[WARNING]FILE “/home/dmdba/db_str_01.log” has already existed
whether to overwrite(y/n, 1/0):1

exporting NO. 1 SCHEMA : ITPUX

start export schema[ITPUX]…

----- export total 0 SEQUENCE -----

----- export total 0 VIEW -----

----- export total 0 TRIGGER -----

----- export total 0 COMMENT VIEW -----

----- export total 0 COMMENT COL -----

----- export total 0 PROCEDURE -----

----- export total 0 SYNONYM -----

----- export total 0 DBLINK -----

----- export total 0 TRIGGER -----

----- export total 0 PACKAGE -----

----- export total 0 PKG_BODY -----

----- export total 0 OBJECT of NO REFER OTHER CLASS -----

----- export total 0 OBJECT of REFER OTHER CLASS -----

----- export total 0 JCLASS -----

----- export total 0 CLASS_BODY -----

----- export total 0 DOMAIN -----

the privilege of the object at the export mode…

schema[ITPUX] export terminate…

successfully exported NO.1 SCHEMA : ITPUX

export total 1 SCHEMA

all the export process spent total 0.057 s

terminate export success without warning

2.3 SCHEMAS

[root@dmp bin]# ./dexp USERID=ITPUX/ITPUX9999 FILE=db_str.dmp LOG=db_str.log DIRECTORY=/home/dmdba SCHEMAS=ITPUX
dexp V8

[WARNING]FILE “/home/dmdba/db_str.dmp” has already existed
whether to overwrite(y/n, 1/0):1

[WARNING]FILE “/home/dmdba/db_str.log” has already existed
whether to overwrite(y/n, 1/0):1

exporting NO. 1 SCHEMA : ITPUX

start export schema[ITPUX]…

----- export total 0 SEQUENCE -----

----- export total 0 VIEW -----

----- export total 0 TRIGGER -----

----- export total 0 COMMENT VIEW -----

----- export total 0 COMMENT COL -----

----- export total 0 PROCEDURE -----

----- export total 0 SYNONYM -----

----- export total 0 DBLINK -----

----- export total 0 TRIGGER -----

----- export total 0 PACKAGE -----

----- export total 0 PKG_BODY -----

----- export total 0 OBJECT of NO REFER OTHER CLASS -----

----- export total 0 OBJECT of REFER OTHER CLASS -----

----- export total 0 JCLASS -----

----- export total 0 CLASS_BODY -----

----- export total 0 DOMAIN -----

the privilege of the object at the export mode…

schema[ITPUX] export terminate…

successfully exported NO.1 SCHEMA : ITPUX

export total 1 SCHEMA

all the export process spent total 0.054 s

terminate export success without warning

2.4 TABLES

[root@dmp bin]# ./dexp USERID=SYSDBA/SYSDBA FILE=db_str_%Y_%U.dmp LOG=db_str_%Y_%U.log TABLES=SYSDBA.TEST DIRECTORY=/home/dmdba
dexp V8

---- [2021-02-25 05:29:01]export table:TEST -----

the privilege of the object at the export mode…

table :TEST export terminate, total export 6 rows

all the export process spent total 0.058 s

terminate export success without warning

3 dimp四种级别的导入方式

3.1 FULL
[root@dmp bin]# ./dimp USERID=SYSDBA/SYSDBA FILE=/home/dmdba/db_str_01.dmp LOG=db_str1.log FULL=Y DIRECTORY=/home/dmdba
dimp V8

start importing schema[ITPUX]…

start importing the NECESSARY GLOBAL objects of the schema…

finish importing the NECESSARY GLOBAL objects of the schema…

start importing the GLOBAL objects of the schema…

finish importing the GLOBAL objects of the schema…

import schema[ITPUX] finish…

all the import process spent total 0.000 s

terminate import success without warning

3.2 OWNER
[root@dmp bin]# ./dimp USERID=SYSDBA/SYSDBA FILE=/home/dmdba/db_str_01.dmp LOG=db_str1.log OWNER=ITPUX DIRECTORY=/home/dmdba
dimp V8

[WARNING]FILE “/home/dmdba/db_str1.log” has already existed
whether to overwrite(y/n, 1/0):y

start importing schema[ITPUX]…

start importing the NECESSARY GLOBAL objects of the schema…

finish importing the NECESSARY GLOBAL objects of the schema…

start importing the GLOBAL objects of the schema…

finish importing the GLOBAL objects of the schema…

import schema[ITPUX] finish…

all the import process spent total 0.000 s

terminate import success without warning

3.3 SCHEMAS

[root@dmp bin]# ./dimp USERID=SYSDBA/SYSDBA FILE=/home/dmdba/db_str_01.dmp LOG=db_str1.log SCHEMAS=ITPUX DIRECTORY=/home/dmdba
dimp V8

[WARNING]FILE “/home/dmdba/db_str1.log” has already existed
whether to overwrite(y/n, 1/0):1

start importing schema[ITPUX]…

start importing the NECESSARY GLOBAL objects of the schema…

finish importing the NECESSARY GLOBAL objects of the schema…

start importing the GLOBAL objects of the schema…

finish importing the GLOBAL objects of the schema…

import schema[ITPUX] finish…

all the import process spent total 0.000 s

terminate import success without warning
3.4 TABLES

[root@dmp bin]# ./dimp USERID=SYSDBA/SYSDBA FILE=/home/dmdba/db_str_%DATE_01.dmp LOG=db_str1.log TABLES=SYSDBA.TEST DIRECTORY=/home/dmdba
dimp V8

[WARNING]FILE “/home/dmdba/db_str1.log” has already existed
whether to overwrite(y/n, 1/0):1

----- [2021-02-25 05:43:05]import table:TEST -----

create table TEST

import table TEST , has coped with 6 rows

all the import process spent total 0.185 s

terminate import success without warning

4总结
逻辑导出和导入都分为4中方式，其中owner、schema、table都可以一次指定多个，但是full不可以与owner、schema、table同时指定，会报错。导入还可以启动多线程加快导入速度。