当前位置：首页 > news >正文

达梦数据库常用命令行

news 2025/8/9 3:05:24

导出dmp文件（迁移用）

管理工具在dmdbms下的tool文件夹下
使用tool目录下的manage程序，导出dmp文件

导入dmp文件

切到tool目录下

./dimp 用户id/密码@ip:5236 file="导入的文件路径(包括文件名)" 导入的模式（一般与库名相同）

查看所有表

--------------------统计所有用户表行数
SELECTT2.NAME AS 模式名,T1.NAME AS 表名,TABLE_ROWCOUNT(T2.NAME,T1.NAME) AS 行数
FROMSYSOBJECTS T1,SYSOBJECTS T2
WHERET1.SCHID = T2.IDAND T1.SUBTYPE$ = 'UTAB'AND T2."TYPE$" = 'SCH'
ORDER BY 3 DESC;
--------------------统计所有用户表行数以及筛查某行数级别以上表行数 输入参数1：百万，千万，亿...等等，也可以共存，复制一行
SELECT 模式名,COUNT(表名) AS 表数量,COUNT(CASE WHEN 行数 > ? THEN 行数 ELSE NULL END) AS 百万表数量
FROM
(SELECTT2.NAME AS 模式名,T1.NAME AS 表名,TABLE_ROWCOUNT(T2.NAME,T1.NAME) AS 行数FROMSYSOBJECTS T1,SYSOBJECTS T2WHERET1.SCHID = T2.IDAND T1.SUBTYPE$ = 'UTAB'AND T2."TYPE$" = 'SCH'--ORDER BY 3 DESC
)
GROUP BY 模式名
--------------------获取所有用户表定义
SELECTT2.NAME AS 模式名, T1.NAME AS 表名,T1.CRTDATE AS 创建时间,DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('TABLE',T1.NAME,T2.NAME)) AS 表定义
FROM SYSOBJECTS T1, SYSOBJECTS T2
WHERE T1."SUBTYPE$" = 'UTAB' AND T1.SCHID = T2.ID AND T2."TYPE$" = 'SCH';
--------------------统计用户表列信息
SELECTS1.NAME AS 表名,S2.NAME AS 列名,S2."TYPE$" AS 字段类型,S2."LENGTH$" AS 字段长度
FROMSYSOBJECTS S1,SYSCOLUMNS S2
WHERES1."SUBTYPE$" = 'UTAB'AND S1.ID = S2.ID;
--------------------查看表占用空间大小
SELECTS2.NAME AS 模式名,S1.NAME AS 表名,TABLE_USED_SPACE(S2.NAME,S1.NAME) * PAGE /1024.0/1024.0 AS "表占用空间(MB)"
FROMSYSOBJECTS S1,SYSOBJECTS S2
WHERES1.SCHID = S2.IDAND S1."SUBTYPE$" = 'UTAB'AND S2."TYPE$" = 'SCH'
ORDER BY3 DESC;