热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
注册
DM8数据库逻辑备份工具
专栏/技术分享/ 文章详情 /

DM8数据库逻辑备份工具

赵国伟 2025/05/23 95 0 0
摘要

DM8数据库逻辑备份工具

一、工具简介

     逻辑导出（dexp）和逻辑导入（dimp）是 DM 数据库的两个命令行工具，分别用来实现对 DM 数据库的逻辑备份和逻辑还原。逻辑备份和逻辑还原都是在联机方式下完成，联机方式是指数据库服务器正常运行过程中进行的备份和还原。dexp 和 dimp 是 DM 数据库自带的工具，只要安装了 DM 数据库，就可以在安装目录/dmdbms/bin 中找到。
     逻辑导出和逻辑导入数据库对象分为四种级别：数据库级、用户级、模式级和表级。四种级别独立互斥，不能同时存在。四种级别所提供的功能：
     数据库级（FULL）：导出或导入整个数据库中的所有对象。
     用户级（OWNER）：导出导入一个或多个用户拥有的所有对象。
     模式级（SCHEMAS）：导出导入一个或多个模式下的所有对象。
     表级（TABLES）：导出导入一个或多个指定的表或表分区

二、参数介绍

2.1 dexp 参数

     dexp 包含 dexp 和 dexpdp 两种语法，其命令行语法完全相同。两种写法区别在于 dexp 导出的文件必须存放在客户端，dexpdp 导出的文件必须存放在服务器端。

[dmdba@monitor ~]$ dexp help
dexp V8
version: 03134284336-20250117-257733-20132
file_version: 26
格式: ./dexp  KEYWORD=value 或 KEYWORD=(value1,value2,...,valueN)
例程:./dexp SYSDBA/SYSDBA GRANTS=Y  TABLES=(SYSDBA.TAB1,SYSDBA.TAB2)
USERID 必须是命令行中的第一个参数
关键字              说明（默认值）
--------------------------------------------------------------------------------
USERID               用户名/口令
示例: SYSDBA/SYSDBA@192.168.1.64:5236
FILE                 导出文件 (dexp.dmp)
DIRECTORY           导出文件所在目录
FULL                	整库导出 (N)
OWNER              以用户方式导出 格式 (user1,user2,...)
SCHEMAS            	以模式方式导出 格式 (schema1,schema2,...)
TABLES               以表方式导出 格式 (table1,table2,...)
FUZZY_MATCH         TABLES选项是否支持模糊匹配 (N)
QUERY               用于导出表的过滤条件
PARALLEL             用于指定导出的过程中所使用的线程数目(1-100, 默认16)
TABLE_PARALLEL      	表内的并发线程数,MPP会转换成单线程(1-50, 默认8)
TABLE_POOL          用于指定表的缓冲区个数(1-20, 默认8)
EXCLUDE             忽略指定的对象 
格式 EXCLUDE=(CONSTRAINTS,INDEXES,ROWS,TRIGGERS,GRANTS,VIEWS,PROCEDURE,
PACKAGE,SEQUENCE,TABLES) or EXCLUDE=TYPE:name1,name2 or
EXCLUDE=TYPE:cond,TYPE:cond                         	TYPE:SCHEMAS,TABLES,VIEWS,PROCEDURE,PACKAGE,SEQUENCE 
INCLUDE              包含指定的对象 
格式 INCLUDE=(CONSTRAINTS,INDEXES,ROWS,TRIGGERS,GRANTS,VIEWS,PROCEDURE,
PACKAGE,SEQUENCE,TABLES) or INCLUDE=TYPE:name1,name2 or
INCLUDE=TYPE:cond,TYPE:cond 
TYPE:SCHEMAS,TABLES,VIEWS,PROCEDURE,PACKAGE,SEQUENCE 
CONSTRAINTS    导出约束 (Y)
TABLESPACE      导出表空间、表空间组定义和对象的表空间、表空间组存储项 (N)
GRANTS         导出权限 (Y)
INDEXES         导出索引 (Y)
TRIGGERS        导出触发器 (Y)
ROWS           导出数据行 (Y)
LOG             屏幕输出的日志文件
NOLOGFILE       不使用日志文件(N)
NOLOG           屏幕上不显示日志信息(N)
LOG_WRITE       日志信息实时写入文件(N)
DUMMY          交互信息处理(P): 打印(P)，所有交互都按YES处理(Y)，所有交互都按NO处理(N)
PARFILE          参数文件名
FEEDBACK        每 x 行显示进度 (0)
COMPRESS       导出数据是否压缩 (N)
COMPRESS_LEVEL 导出数据压缩等级 (0-9 默认1)
ENCRYPT         导出数据是否加密 (N)
ENCRYPT_PASSWORD     导出数据的加密密钥
ENCRYPT_NAME         加密算法的名称
FILESIZE             每个转储文件的最大大小
FILENUM              一个模板可以生成的文件数(1-99, 默认99)
DROP                 导出后删除原表，但不级联删除 (N)
DESCRIBE             导出数据文件的描述信息，记录在数据文件中
COL_DEFAULT_SEPARATE 是否单独导出列默认值 (Y)
WITH_UR              是否允许脏读 (N)
SIMPLE_LOG           是否使用简要日志 (N)
FLASHBACK_SCN        用于重置会话快照的LSN
FLASHBACK_TIME       用于查找最接近的相应LSN值的时间
CTRL_INFO            控制信息(1:控制分区表约束在表定义导出，0:分区表约束分开导出，默认0)
CONFIG_FILE          配置文件
FILE_VERSION         指定导出版本(9-26)
HELP                 打印帮助信息

2.2 dimp 参数

     dimp 逻辑导入工具利用 dexp 工具生成的备份文件对本地或远程的数据库进行联机逻辑还原。dimp 工具名称有两种写法 dimp 和 dimpdp。两者语法完全相同。唯一的区别在于， dimp 导入的文件必须存放在客户端，dimpdp 导入的文件必须存放在服务器端。

[dmdba@monitor ~]$ dimp help
dimp V8
version: 03134284336-20250117-257733-20132
file_version: 26
dimp V8
version: 03134284336-20250117-257733-20132
file_version: 26
格式: ./dimp KEYWORD=value 或 KEYWORD=(value1,value2,...,valueN)

例程: ./dimp SYSDBA/SYSDBA IGNORE=Y ROWS=Y FULL=Y

USERID 必须是命令行中的第一个参数

关键字                 说明（默认值）
--------------------------------------------------------------------------------
USERID                 用户名/口令
                         格式: {<username>[/<password>] | /}[@<connect_identifier>][<option>] [<os_auth>]
                         参数:
                           <connect_identifier> ::= [<svc_name> | host[:port] | <unixsocket_file>]
                           <option> ::= #{<extend_option>=<value>[,<extend_option>=<value>]...}
                           <os_auth> ::= AS { SYSDBA | SYSSSO | SYSAUDITOR | USERS | AUTO }
                         示例: SYSDBA/SYSDBA@192.168.1.64:5236
                         注意: option参数的{}是为了封装参数之用，书写时需要保留
FILE                   导入文件名称 (dexp.dmp)
DIRECTORY              导入文件所在目录
FULL                   整库导入 (N)
OWNER                  以用户方式导入 格式 (user1,user2,...)
SCHEMAS                以模式方式导入 格式 (schema1,schema2,...)
TABLES                 以表名方式导入 格式(table1,table2,...)
PARALLEL               用于指定导入的过程中所使用的线程数目(1-100, 默认16)
TABLE_PARALLEL         用于指定导入的过程中每个表所使用的子线程数目,在FAST_LOAD为Y时有效(1-16, 默认8)
IGNORE                 忽略创建错误 (N)
TABLE_EXISTS_ACTION    需要的导入表在目标库中存在时采取的操作[SKIP | APPEND | TRUNCATE | REPLACE | TRUNCATE_CASCADE]
FAST_LOAD              是否使用dmfldr来导数据(N)
FLDR_ORDER             使用dmfldr是否需要严格按顺序来导数据(Y)
COMMIT_ROWS            批量提交的行数(5000)
EXCLUDE                忽略指定的对象 
                       格式 EXCLUDE=(CONSTRAINTS,INDEXES,ROWS,TRIGGERS,GRANTS,VIEWS,PROCEDURE,PACKAGE,SEQUENCE,TABLES) or
                            EXCLUDE=TYPE:name1,name2 
                             TYPE:SCHEMAS,TABLES,VIEWS,PROCEDURE,PACKAGE,SEQUENCE 
INCLUDE                包含指定的对象 
                       格式 INCLUDE=(CONSTRAINTS,INDEXES,ROWS,TRIGGERS,GRANTS,VIEWS,PROCEDURE,PACKAGE,SEQUENCE,TABLES) or 
                            INCLUDE=TYPE:name1,name2 
                             TYPE:SCHEMAS,TABLES,VIEWS,PROCEDURE,PACKAGE,SEQUENCE 
GRANTS                 导入权限 (Y)
CONSTRAINTS            导入约束 (Y)
INDEXES                导入索引 (Y)
TRIGGERS               导入触发器 (Y)
ROWS                   导入数据行 (Y)
LOG                    指定日志文件
NOLOGFILE              不使用日志文件(N)
NOLOG                  屏幕上不显示日志信息(N)
LOG_WRITE              日志信息实时写入文件(N)
DUMMY                  交互信息处理(P): 打印(P)，所有交互都按YES处理(Y)，所有交互都按NO处理(N) 
PARFILE                参数文件名
FEEDBACK               每 x 行显示进度 (0)
COMPILE                编译过程, 程序包和函数(Y)
INDEXFILE              将表的索引/约束信息写入指定的文件
INDEXFIRST             导入时先建索引(N)
REMAP_SCHEMA           格式(SOURCE_SCHEMA:TARGET_SCHEMA)
                       将SOURCE_SCHEMA中的数据导入到TARGET_SCHEMA中 
ENCRYPT_PASSWORD       数据的加密密钥
ENCRYPT_NAME           加密算法的名称
SHOW/DESCRIBE          打印出指定文件的信息(N)
TASK_THREAD_NUMBER     用于设置dmfldr处理用户数据的线程数目
BUFFER_NODE_SIZE       用于设置dmfldr读入文件缓冲区大小(10)
TASK_SEND_NODE_NUMBER  用于设置dmfldr发送节点个数[16,65535]
LOB_NOT_FAST_LOAD      如果一个表含有大字段，那么不使用dmfldr，因为dmfldr是一行一行提交的(N)
PRIMARY_CONFLICT       主键冲突的处理方式[IGNORE|OVERWRITE|OVERWRITE2],默认报错
TABLE_FIRST            是否先导入表(N)
SHOW_SERVER_INFO       是否打印服务器信息(N)
IGNORE_INIT_PARA       忽略建库参数差异(0): CASE_SENSITIVE(1),LENGTH_IN_CHAR(2)
AUTO_FREE_KEY          导入数据完成后, 是否释放密钥(N)
REMAP_TABLE            格式(SOURCE_SCHEMA.SOURCE_TABLE:TARGET_TABLE)
                       将SOURCE_TABLE中的数据导入到TARGET_TABLE中 
REMAP_TABLESPACE       格式(SOURCE_TABLESPACE:TARGET_TABLESPACE)
                       将SOURCE_TABLESPACE表空间映射到TARGET_TABLESPACE表空间中 
SIMPLE_LOG             是否使用简要日志 (N)
DATA_ONLY              仅导入数据 (N)
INDEX_OPTION           索引选项 (2)
                       1 不刷新二级索引，数据按照索引先排序，装载完后再
                       将排序的数据插入索引
                       2 不刷新二级索引，数据装载完成后重建所有二级索引
                       3 刷新二级索引, 数据装载的同时将数据插入二级索引
CTRL_INFO              控制信息(1:导入表空间; 2:校验MD5; 4:校验MD5不导入; 8:导入时忽略or replace;
                       16:remap替换列默认值中序列模式名，默认0)
CONFIG_FILE            配置文件
FILE_VERSION           指定降级的目标文件版本(9-25)
HELP                   打印帮助信息

三、dexp/dimp实战

3.1 库级导出导入

– 导出

[dmdba@dm-standalone ~]$ dexp sysdba/Dameng_123 file=dexp_full.dmp log=dexp_full.log full=y
dexp V8

version: 03134284336-20250117-257733-20132

start dexp: 
sysdba/******@LOCALHOST:5236 file=dexp_full.dmp log=dexp_full.log full=y 

export object type: SYSPACKAGE_DEF

export total 2 SYSPACKAGE_DEF 

export object type: USER

export total 2 USER 

export object type: DIRECTORY

export total 1 DIRECTORY 

export object type: SYSTEM_PRIVILEGE

export total 2 SYSTEM_PRIVILEGE 

export object type: SYS_ROLE_PRIVILEGE_TO_USER

export total 4 SYS_ROLE_PRIVILEGE_TO_USER 

exporting NO. 1 SCHEMA : SYSDBA 

start export schema[SYSDBA].....

export object type: VIEW

export total 1 VIEW 

---- [2025-04-22 21:29:06]export table:SYSDBA.T_TRIGER -----

[TABLE: T_TRIGER]export trigger : BEF_DEL 

---- [2025-04-22 21:29:06]export table:SYSDBA.LIST_TABLE -----

table :SYSDBA.LIST_TABLE export terminate, total export 0 rows, size 0 KB

---- [2025-04-22 21:29:06]export table:SYSDBA.T_RANGE -----

table :SYSDBA.T_RANGE export terminate, total export 0 rows, size 0 KB

---- [2025-04-22 21:29:06]export table:SYSDBA.T_HUGE -----

table :SYSDBA.T_HUGE export terminate, total export 0 rows, size 0 KB

---- [2025-04-22 21:29:06]export table:SYSDBA.T1 -----

[TABLE: T1]export index :IDX2 

---- [2025-04-22 21:29:06]export table:SYSDBA.T_M_VIEW -----

table :SYSDBA.T_M_VIEW export terminate, total export 0 rows, size 0 KB

---- [2025-04-22 21:29:06]export table:SYSDBA.TABLE1 -----

table :SYSDBA.TABLE1 export terminate, total export 0 rows, size 0 KB

---- [2025-04-22 21:29:06]export table:SYSDBA.T_HASH -----

table :SYSDBA.T_HASH export terminate, total export 0 rows, size 0 KB

---- [2025-04-22 21:29:06]export table:SYSDBA.T3 -----

table :SYSDBA.T_TRIGER export terminate, total export 4 rows, size 0.023 KB

---- [2025-04-22 21:29:06]export table:SYSDBA.T2 -----

[TABLE: T2]export index :CTI_CONTENT 

table :SYSDBA.T1 export terminate, total export 4 rows, size 0.050 KB

---- [2025-04-22 21:29:06]export table:SYSDBA.T_LIST -----

table :SYSDBA.T_LIST export terminate, total export 0 rows, size 0 KB

table :SYSDBA.T2 export terminate, total export 1 rows, size 0.365 KB

table :SYSDBA.T3 export terminate, total export 1 rows, size 0.365 KB

export total 11 TABLE 

schema[SYSDBA] export terminate.....

successfully exported NO.1 SCHEMA : SYSDBA


exporting NO. 2 SCHEMA : TEST 

start export schema[TEST].....

---- [2025-04-22 21:29:06]export table:TEST.T1 -----

table :TEST.T1 export terminate, total export 1 rows, size 0.003 KB

export total 1 TABLE 

schema[TEST] export terminate.....

successfully exported NO.2 SCHEMA : TEST

exporting NO. 3 SCHEMA : TEST1 

start export schema[TEST1].....

---- [2025-04-22 21:29:06]export table:TEST1.T1 -----

table :TEST1.T1 export terminate, total export 0 rows, size 0 KB

export total 1 TABLE 

schema[TEST1] export terminate.....

successfully exported NO.3 SCHEMA : TEST1

exporting NO. 4 SCHEMA : ZGW 

start export schema[ZGW].....

---- [2025-04-22 21:29:06]export table:ZGW.TABLE2 -----

---- [2025-04-22 21:29:06]export table:ZGW.TEST -----

---- [2025-04-22 21:29:06]export table:ZGW.TEST1 -----

---- [2025-04-22 21:29:06]export table:ZGW.TABLE1 -----

table :ZGW.TABLE2 export terminate, total export 5 rows, size 0.122 KB

table :ZGW.TEST export terminate, total export 3 rows, size 0.041 KB

table :ZGW.TEST1 export terminate, total export 4 rows, size 0.023 KB

table :ZGW.TABLE1 export terminate, total export 8 rows, size 0.258 KB

export total 4 TABLE 

schema[ZGW] export terminate.....

successfully exported NO.4 SCHEMA : ZGW

export total 4 SCHEMA


all the export process spent total    1.235 s

terminate export success without warning

– 删除test 及 zgw 用户及其相关数据库对象

SQL> drop user zgw cascade;
executed successfully
used time: 84.507(ms). Execute id is 9102.
SQL> drop user test cascade;
executed successfully

– 导入

[dmdba@dm-standalone ~]$ dimp sysdba/Dameng_123 file=dexp_full.dmp log=dimp_full.log full=y 
dimp V8

version: 03134284336-20250117-257733-20132

start dimp: 
sysdba/******@LOCALHOST:5236 file=dexp_full.dmp log=dimp_full.log full=y 

local code: PG_UTF8, dump file code: PG_GB18030

[0/32]import instance's GLOBAL objects...

[0/32]import instance's SYSPACKAGES_DEF objects...

[1/32]import instance's SYSPACKAGES_DEF objects...

[2/32]import instance's USER objects : TEST

[3/32]import instance's USER objects : ZGW

[4/32]import instance's DIRECTORY objects : EXTDIR

[5/32]import instance's SYS_PRIV objects...

[6/32]import instance's SYS_PRIV objects...

[7/32]import instance's SR_PRIV objects...

[8/32]import instance's SR_PRIV objects...

[9/32]import instance's SR_PRIV objects...

[10/32]import instance's SR_PRIV objects...

[11/32]finish importing GLOBAL objects...

[11/32]import instance's SCHEMA objects...

[11/32]start importing schema[SYSDBA]...

[11/32]start importing the NECESSARY GLOBAL objects of the schema...

[11/32]finish importing the NECESSARY GLOBAL objects of the schema...

----- [2025-04-22 21:31:27]import table:SYSDBA.T_HUGE -----

[WARNING]table exist, import table SYSDBA.T_HUGE fail...

----- [2025-04-22 21:31:27]import table:SYSDBA.T_HASH -----

----- [2025-04-22 21:31:27]import table:SYSDBA.T_RANGE -----

[WARNING]table exist, import table SYSDBA.T_HASH fail...

----- [2025-04-22 21:31:27]import table:SYSDBA.T_LIST -----

[WARNING]table exist, import table SYSDBA.T_LIST fail...

----- [2025-04-22 21:31:27]import table:SYSDBA.T1 -----

[WARNING]table exist, import table SYSDBA.T1 fail...

----- [2025-04-22 21:31:27]import table:SYSDBA.LIST_TABLE -----

[WARNING]table exist, import table SYSDBA.LIST_TABLE fail...

----- [2025-04-22 21:31:27]import table:SYSDBA.T_TRIGER -----

----- [2025-04-22 21:31:27]import table:SYSDBA.T_M_VIEW -----

----- [2025-04-22 21:31:27]import table:SYSDBA.T2 -----

[WARNING]table exist, import table SYSDBA.T_TRIGER fail...

----- [2025-04-22 21:31:27]import table:SYSDBA.T3 -----

[WARNING]table exist, import table SYSDBA.T3 fail...

----- [2025-04-22 21:31:27]import table:SYSDBA.TABLE1 -----

[WARNING]table exist, import table SYSDBA.TABLE1 fail...

[WARNING]table exist, import table SYSDBA.T2 fail...

[WARNING]table exist, import table SYSDBA.T_RANGE fail...

[WARNING]table exist, import table SYSDBA.T_M_VIEW fail...

[11/32]start importing the GLOBAL objects of the schema...

[11/32]import instance's VIEW objects : SYSDBA.NORMAL_VIEW2

[12/32]finish importing the GLOBAL objects of the schema...

[12/32]import schema[SYSDBA] finish...

[12/32]start importing schema[TEST]...

[12/32]start importing the NECESSARY GLOBAL objects of the schema...

[12/32]finish importing the NECESSARY GLOBAL objects of the schema...

----- [2025-04-22 21:31:28]import table:TEST.T1 -----

[12/32][TABLE: T1]create table TEST.T1  

[13/32][TABLE: T1]creating table is complete, table T1 data is importing...

[TABLE: T1]import table TEST.T1 , has coped with 1 rows, size 0.003 KB

[13/32]start importing the GLOBAL objects of the schema...

[13/32]finish importing the GLOBAL objects of the schema...

[13/32]import schema[TEST] finish...

[13/32]start importing schema[TEST1]...

[13/32]start importing the NECESSARY GLOBAL objects of the schema...

[13/32]finish importing the NECESSARY GLOBAL objects of the schema...

----- [2025-04-22 21:31:28]import table:TEST1.T1 -----

[13/32][TABLE: T1]create table TEST1.T1  

[14/32][TABLE: T1]creating table is complete, table T1 data is importing...

finish importing table TEST1.T1 , import total : 0 rows, size 0 KB

[14/32]start importing the GLOBAL objects of the schema...

[14/32]finish importing the GLOBAL objects of the schema...

[14/32]import schema[TEST1] finish...

[14/32]start importing schema[ZGW]...

[14/32]start importing the NECESSARY GLOBAL objects of the schema...

[14/32]finish importing the NECESSARY GLOBAL objects of the schema...

----- [2025-04-22 21:31:28]import table:ZGW.TABLE2 -----

[14/32][TABLE: TABLE2]create table ZGW.TABLE2  

----- [2025-04-22 21:31:28]import table:ZGW.TEST -----

----- [2025-04-22 21:31:28]import table:ZGW.TEST1 -----

----- [2025-04-22 21:31:28]import table:ZGW.TABLE1 -----

[15/32][TABLE: TABLE2]creating table is complete, table TABLE2 data is importing...

[15/32][TABLE: TEST]create table ZGW.TEST  

[15/32][TABLE: TEST1]create table ZGW.TEST1  

[16/32][TABLE: TEST1]creating table is complete, table TEST1 data is importing...

[17/32][TABLE: TEST]creating table is complete, table TEST data is importing...

[17/32][TABLE: TABLE1]create table ZGW.TABLE1  

[TABLE: TABLE2]import table ZGW.TABLE2 , has coped with 5 rows, size 0.122 KB

[18/32][TABLE: TABLE1]creating table is complete, table TABLE1 data is importing...

[TABLE: TEST]import table ZGW.TEST , has coped with 3 rows, size 0.041 KB

[TABLE: TABLE1]import table ZGW.TABLE1 , has coped with 8 rows, size 0.258 KB

[TABLE: TEST1]import table ZGW.TEST1 , has coped with 4 rows, size 0.023 KB

[18/32]start importing the GLOBAL objects of the schema...

[18/32]finish importing the GLOBAL objects of the schema...

[18/32]import schema[ZGW] finish...

[18/32]start compile object...
    compile object[1/1]
[18/32]finish compile object

[18/32]all the import process spent total    7.164 s


terminate import success with 11 warnings

3.2 用户级导出导入

– 导出

[dmdba@dm-standalone ~]$ dexp sysdba/Dameng_123 file=dexp_owner_zgw.dmp 	log=dexp_owner_zgw.log owner=zgw
dexp V8

version: 03134284336-20250117-257733-20132

start dexp: 
sysdba/******@LOCALHOST:5236 file=dexp_owner_zgw.dmp log=dexp_owner_zgw.log owner=zgw 


export object type: USER

export total 1 USER 

export object type: SYSTEM_PRIVILEGE

export total 1 SYSTEM_PRIVILEGE 

export object type: SYS_ROLE_PRIVILEGE_TO_USER

export total 2 SYS_ROLE_PRIVILEGE_TO_USER 

exporting NO. 1 SCHEMA : ZGW 

start export schema[ZGW].....

---- [2025-04-22 19:48:21]export table:ZGW.TABLE2 -----

---- [2025-04-22 19:48:21]export table:ZGW.TEST -----

---- [2025-04-22 19:48:21]export table:ZGW.TEST1 -----

---- [2025-04-22 19:48:21]export table:ZGW.TABLE1 -----

table :ZGW.TEST export terminate, total export 3 rows, size 0.041 KB

table :ZGW.TEST1 export terminate, total export 4 rows, size 0.023 KB

table :ZGW.TABLE2 export terminate, total export 5 rows, size 0.122 KB

table :ZGW.TABLE1 export terminate, total export 8 rows, size 0.258 KB

export total 4 TABLE 

schema[ZGW] export terminate.....

successfully exported NO.1 SCHEMA : ZGW


export total 1 SCHEMA


all the export process spent total    0.421 s


terminate export success without warning

– 删除用户及其拥有的数据库对象

SQL> drop user zgw cascade;
executed successfully
used time: 79.358(ms). Execute id is 7801.
SQL>

– 导入

[dmdba@dm-standalone ~]$ dimp userid=sysdba/Dameng_123 file=dexp_owner_zgw.dmp log=imp_owner_zgw.log owner=zgw
dimp V8

version: 03134284336-20250117-257733-20132

start dimp: 
sysdba/******@LOCALHOST:5236 file=dexp_owner_zgw.dmp log=imp_owner_zgw.log owner=zgw 

local code: PG_UTF8, dump file code: PG_GB18030

[0/8]import instance's GLOBAL objects...

[0/8]import instance's USER objects : ZGW

[1/8]import instance's SYS_PRIV objects...

[2/8]import instance's SR_PRIV objects...

[3/8]import instance's SR_PRIV objects...

[4/8]finish importing GLOBAL objects...

[4/8]start importing schema[ZGW]...

[4/8]start importing the NECESSARY GLOBAL objects of the schema...

[4/8]finish importing the NECESSARY GLOBAL objects of the schema...


----- [2025-04-22 19:49:07]import table:ZGW.TABLE2 -----

[4/8][TABLE: TABLE2]create table ZGW.TABLE2  

----- [2025-04-22 19:49:07]import table:ZGW.TEST -----

----- [2025-04-22 19:49:07]import table:ZGW.TABLE1 -----

[5/8][TABLE: TABLE2]creating table is complete, table TABLE2 data is importing...

----- [2025-04-22 19:49:07]import table:ZGW.TEST1 -----

[5/8][TABLE: TEST]create table ZGW.TEST  

[6/8][TABLE: TEST]creating table is complete, table TEST data is importing...

[6/8][TABLE: TEST1]create table ZGW.TEST1  

[7/8][TABLE: TEST1]creating table is complete, table TEST1 data is importing...

[7/8][TABLE: TABLE1]create table ZGW.TABLE1  

[8/8][TABLE: TABLE1]creating table is complete, table TABLE1 data is importing...

[TABLE: TABLE2]import table ZGW.TABLE2 , has coped with 5 rows, size 0.122 KB

[TABLE: TEST]import table ZGW.TEST , has coped with 3 rows, size 0.041 KB

[TABLE: TEST1]import table ZGW.TEST1 , has coped with 4 rows, size 0.023 KB

[TABLE: TABLE1]import table ZGW.TABLE1 , has coped with 8 rows, size 0.258 KB

[8/8]start importing the GLOBAL objects of the schema...

[8/8]finish importing the GLOBAL objects of the schema...

[8/8]import schema[ZGW] finish...

[8/8]all the import process spent total    0.087 s

terminate import success without warning

– 查看用户下表信息

[dmdba@dm-standalone ~]$ disql zgw/Zgw_1234     

Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 6.454(ms)
disql V8
SQL> select table_name from user_tables;

LINEID     TABLE_NAME
---------- ----------
1          TABLE2
2          TEST
3          TEST1
4          TABLE1

used time: 35.822(ms). Execute id is 8901.
SQL>

3.3 模式级导出导入

– 导出

[dmdba@dm-standalone ~]$ dexp userid=sysdba/Dameng_123 file=dexp_schema_zgw.dmp log=dexp_schema_zgw.log schemas=zgw
dexp V8

version: 03134284336-20250117-257733-20132

start dexp: 
sysdba/******@LOCALHOST:5236 file=dexp_schema_zgw.dmp log=dexp_schema_zgw.log schemas=zgw 


exporting NO. 1 SCHEMA : ZGW 

start export schema[ZGW].....

---- [2025-04-22 19:40:09]export table:ZGW.TABLE2 -----

---- [2025-04-22 19:40:09]export table:ZGW.TABLE1 -----

---- [2025-04-22 19:40:09]export table:ZGW.TEST1 -----

---- [2025-04-22 19:40:09]export table:ZGW.TEST -----

table :ZGW.TABLE2 export terminate, total export 5 rows, size 0.122 KB

table :ZGW.TEST export terminate, total export 3 rows, size 0.041 KB

table :ZGW.TABLE1 export terminate, total export 8 rows, size 0.258 KB

table :ZGW.TEST1 export terminate, total export 4 rows, size 0.023 KB

export total 4 TABLE 

schema[ZGW] export terminate.....

successfully exported NO.1 SCHEMA : ZGW


export total 1 SCHEMA


all the export process spent total    1.027 s


terminate export success without warning
[dmdba@dm-standalone ~]$

– 删除对应模式下所有表

SQL> select 'drop table '||table_name||';' from user_tables;

LINEID     'droptable'||TABLE_NAME||';'
---------- ----------------------------
1          drop table TABLE2;
2          drop table TABLE1;
3          drop table TEST;
4          drop table TEST1;

used time: 49.485(ms). Execute id is 5402.
SQL> drop table TABLE2;
executed successfully
used time: 30.433(ms). Execute id is 5403.
SQL> drop table TABLE1;
executed successfully
used time: 21.443(ms). Execute id is 5404.
SQL> drop table TEST;
executed successfully
used time: 26.746(ms). Execute id is 5405.
SQL> drop table TEST1;
executed successfully
used time: 23.424(ms). Execute id is 5406.
SQL>

– 导入

[dmdba@dm-standalone ~]$ dimp sysdba/Dameng_123 file=dexp_schema_zgw.dmp log=dimp_schema_zgw.log schemas=zgw
dimp V8

version: 03134284336-20250117-257733-20132

start dimp: 
sysdba/******@LOCALHOST:5236 file=dexp_schema_zgw.dmp log=dimp_schema_zgw.log schemas=zgw 


local code: PG_UTF8, dump file code: PG_GB18030

[0/4]start importing schema[ZGW]...

[0/4]start importing the NECESSARY GLOBAL objects of the schema...

[0/4]finish importing the NECESSARY GLOBAL objects of the schema...


----- [2025-04-22 19:44:07]import table:ZGW.TABLE2 -----


----- [2025-04-22 19:44:07]import table:ZGW.TABLE1 -----

[0/4][TABLE: TABLE2]create table ZGW.TABLE2  


----- [2025-04-22 19:44:07]import table:ZGW.TEST -----


----- [2025-04-22 19:44:07]import table:ZGW.TEST1 -----

[1/4][TABLE: TABLE2]creating table is complete, table TABLE2 data is importing...

[1/4][TABLE: TABLE1]create table ZGW.TABLE1  

[1/4][TABLE: TEST]create table ZGW.TEST  

[2/4][TABLE: TEST]creating table is complete, table TEST data is importing...

[3/4][TABLE: TABLE1]creating table is complete, table TABLE1 data is importing...

[3/4][TABLE: TEST1]create table ZGW.TEST1  

[4/4][TABLE: TEST1]creating table is complete, table TEST1 data is importing...

[TABLE: TABLE2]import table ZGW.TABLE2 , has coped with 5 rows, size 0.122 KB

[TABLE: TEST]import table ZGW.TEST , has coped with 3 rows, size 0.041 KB

[TABLE: TEST1]import table ZGW.TEST1 , has coped with 4 rows, size 0.023 KB

[TABLE: TABLE1]import table ZGW.TABLE1 , has coped with 8 rows, size 0.258 KB

[4/4]start importing the GLOBAL objects of the schema...

[4/4]finish importing the GLOBAL objects of the schema...

[4/4]import schema[ZGW] finish...

[4/4]all the import process spent total    0.082 s

terminate import success without warning

– 查看导入后表

SQL> select table_name from user_tables;

LINEID     TABLE_NAME
---------- ----------
1          TABLE2
2          TEST
3          TABLE1
4          TEST1

used time: 6.243(ms). Execute id is 5408.
SQL>

3.4 表级导出导入

– 导出zgw用户下表test和test1

[dmdba@dm-standalone ~]$ dexp sysdba/Dameng_123 file=dexp_tables.dmp log=dexp_tables.log tables=\(zgw.TEST,zgw.TEST1\) 
dexp V8

version: 03134284336-20250117-257733-20132

start dexp: 
sysdba/******@LOCALHOST:5236 file=dexp_tables.dmp log=dexp_tables.log tables=(zgw.TEST,zgw.TEST1) 


---- [2025-04-22 19:03:34]export table:ZGW.TEST -----

---- [2025-04-22 19:03:34]export table:ZGW.TEST1 -----

table :ZGW.TEST export terminate, total export 3 rows, size 0.041 KB

table :ZGW.TEST1 export terminate, total export 4 rows, size 0.023 KB

export total 2 TABLE 

all the export process spent total    0.496 s


terminate export success without warning
[dmdba@dm-standalone ~]$

– 删除表test 和 test1

SQL> drop table if exists test;
executed successfully
used time: 22.580(ms). Execute id is 806.
SQL> drop table if exists test1;
executed successfully
used time: 20.164(ms). Execute id is 807.
SQL> commit;
executed successfully
used time: 2.903(ms). Execute id is 808.

– 导入zgw用户下表test1和test2

[dmdba@dm-standalone ~]$ dimp sysdba/Dameng_123 file=dexp_tables.dmp log=imp_tables.log tables=\(zgw.test,zgw.test1\)
dimp V8

[WARNING]FILE "imp_tables.log" has already existed
whether to overwrite(y/n, 1/0):y

version: 03134284336-20250117-257733-20132

start dimp: 
sysdba/******@LOCALHOST:5236 file=dexp_tables.dmp log=imp_tables.log tables=(zgw.test,zgw.test1) 


local code: PG_UTF8, dump file code: PG_GB18030


----- [2025-04-22 19:08:55]import table:ZGW.TEST -----

[0/2][TABLE: TEST]create table ZGW.TEST  


----- [2025-04-22 19:08:55]import table:ZGW.TEST1 -----

[1/2][TABLE: TEST]creating table is complete, table TEST data is importing...

[1/2][TABLE: TEST1]create table ZGW.TEST1  

[2/2][TABLE: TEST1]creating table is complete, table TEST1 data is importing...

[TABLE: TEST]import table ZGW.TEST , has coped with 3 rows, size 0.041 KB

[TABLE: TEST1]import table ZGW.TEST1 , has coped with 4 rows, size 0.023 KB

[2/2]all the import process spent total    0.042 s


terminate import success without warning
[dmdba@dm-standalone ~]$

– 查看test 和 test1 数据

SQL> select * from test;

LINEID     C1          C2          C3        
---------- ----------- ----------- ----------
1          1           1           2015-11-06
2          2           2           2015-11-05
3          3           3           2015-11-04

used time: 3.019(ms). Execute id is 811.
SQL> select * from test1;

LINEID     C1          C2         
---------- ----------- -----------
1          2           3
2          3           2
3          4           8
4          9           1

used time: 2.876(ms). Execute id is 812.
SQL>

四、密码 PASSWD 一些规范要求

4.1 window 环境

关键字符：
     要求对连接串的特殊字符需要使用双引号括起来，操作系统要求再最外加双引号和转义。例用户名:user01 密码为 aa/aa。连接串写法：./dexp user01/""“aa/aa “””
空格：
     需要使用双引号括起来作为一个整体。例如：用户名为 user01，密码为 aaaa aaaa ，连接串写法：./dexp user01/“aaaa aaaa”
双引号：
     要求对双引号需要使用双引号括起来，同时双引号需要转义 “aaaa”“aaaa” 操作系统要求再对双引号转义和最外层加双引号""“aaaa”""“aaaa”""。例如：用户名为user01，密码为 aaaa"aaaa，连接串写法：./dexp user01/""“aaaa”""“aaaa”""

4.2 Linux 环境

关键字符：
     要求对连接串的特殊字符需要使用双引号括起来。根据操作系统的要求直接在最外面加单引号。例如：用户名为 user01，密码为 aaaa/aaaa，连接串写法：./dexp user01/’“aaaa/aaaa”’。
双引号：
     要求对双引号需要使用双引号括起来，同时双引号需要转义。例如:密码为 aaa"\aaaa，根据操作系统的要求直接加单引号。例如：用户名为 user01，密码为 aaa"\aaaa，
那么连接串要写成：./dexp user01/’“aaa”"\aaaa"’
单引号：
     根据操作系统的要求，只能将单引号放入双引号中。例如：用户名为 user01， 密码为 aaaa’aaaa，连接串写法：./dexp user01/“aaaa’aaaa”。
单引号+操作系统下的特殊字符：
     根据操作系统的要求，因为单引号只能放在双引号内， 同时双引号中还有一些特殊字符不能被识别需要加反斜杠转义。例如: 用户名为 user01，密码为 aaa’aaaa，使用双引号括起来，同时对加反斜杠转义。连接串写法：./d exp user01/“aaa’$aaaa”。
单引号+双引号：
     根据操作系统的要求，单引号需要放在双引号中，在双引号中表示双 引号则使用反斜杠转义双引号。例如: 用户名为 user01，密码为 aaa"'aaaa，同时考 虑操作的要求，因为含有单引号，只能将整个密码放入双引号中，同时对双引号使 用反斜杠转义，连接串写法： ./dexp user01/"“aaa”"'aaaa""

上一篇：
DPC集群MP主备切换步骤
评论
后发表回复

作者

文章

阅读量

获赞

扫一扫
联系客服
logo
logo
logo
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范

鄂ICP备18017926号-1