达梦数据库命令汇总

-- 1. 数据库基础信息
SELECT name "数据库名", arch_mode "归档状态" FROM V$DATABASE;

-- 2. 实例运行状态
SELECT instance_name "实例名", host_name "主机名", db_version "数据库版本", start_time "启动时间", status$ "运行状态" FROM V$INSTANCE;

-- 3. 表空间信息
SELECT id "ID", name "表空间名", total_size/1024/1024||'MB' "总大小" FROM V$TABLESPACE;

-- 4. 数据文件信息（修正版）
SELECT path "文件路径", total_size/1024/1024||'MB' "总大小" FROM V$DATAFILE;

-- 5. 用户信息
SELECT username "用户名", created "创建时间", default_tablespace "默认表空间" FROM DBA_USERS;

-- 6. 用户权限
SELECT grantee "用户名", granted_role "拥有角色" FROM DBA_ROLE_PRIVS;

-- 7. 表信息统计
SELECT table_name "表名", tablespace_name "表空间" FROM DBA_TABLES WHERE owner='SYSDBA';

-- 8. 对象依赖关系
SELECT referenced_owner "被引用用户", referenced_name "被引用对象", owner "依赖用户", name "依赖对象" FROM DBA_DEPENDENCIES WHERE owner='SYSDBA';

-- 9. 当前活动会话
SELECT sess_id "会话ID", sql_text "执行SQL", state "状态" FROM V$SESSIONS WHERE state='ACTIVE';

-- 10. 归档配置
SELECT '归档路径:'||(SELECT para_value FROM V$DM_INI WHERE para_name='ARCH_DEST')||' 空间限制:'||(SELECT para_value FROM V$DM_INI WHERE para_name='ARCH_SPACE_LIMIT')/1024||'MB' AS "归档信息" FROM DUAL;

-- 11. 归档状态
SELECT PARA_NAME "参数名", PARA_VALUE "值" FROM V$DM_INI WHERE PARA_NAME LIKE 'ARCH%';

-- 12 查看数据库状态命令
SELECT status$ "当前状态" FROM V$INSTANCE;

-- 13 查看表内容
SELECT * FROM TEST_DATA_TYPES;

-- 14. 参数配置
SELECT para_name "参数名", para_value "当前值" FROM V$DM_INI WHERE para_name IN ('MEMORY_POOL', 'BUFFER_POOLS', 'MAX_SESSIONS');

-- 15. 版本信息
SELECT banner "版本信息" FROM V$VERSION;

-- 16. 数据量统计（验证数据完整性查询）
SELECT 'TEST_BACKUP表' AS "表名", COUNT(*) AS "记录数" FROM TEST_BACKUP
UNION ALL
SELECT 'TEST_DATA_TYPES表', COUNT(*) FROM TEST_DATA_TYPES;

-- 17. 逻辑备份前检查数据状态
SELECT 
    (SELECT COUNT(*) FROM TEST_BACKUP) AS "测试表数据量",
    (SELECT COUNT(*) FROM TEST_DATA_TYPES) AS "类型表数据量",
    (SELECT MAX(id) FROM TEST_BACKUP) AS "最大ID",
    (SELECT MAX(create_date) FROM TEST_DATA_TYPES) AS "最新日期"
FROM DUAL;

-- 18. 查看当前用户下的所有表
SELECT table_name AS "表名", tablespace_name AS "表空间", num_rows AS "行数"
FROM DBA_TABLES
WHERE owner = 'SYSDBA'
ORDER BY table_name;

-- 19. 查看用户创建的业务表
SELECT owner AS "用户", table_name AS "业务表名"
FROM DBA_TABLES
WHERE owner NOT IN ('SYS', 'SYSAUDITOR', 'SYSSSO')
AND table_name NOT LIKE 'SYS%'
ORDER BY owner, table_name;

-- 20. 删除部分数据（删除ID大于5的记录）
DELETE FROM TEST_BACKUP WHERE id > 5; 

-- 21. 检查表是否存在
SELECT * FROM USER_TABLES WHERE TABLE_NAME = 'TEST_BACKUP';

-- 22. 安全删除（如果表存在则删除）
DROP TABLE IF EXISTS SYSDBA.TEST_BACKUP;

-- 23. 查询TEST_BACKUP表所属模式
SELECT OWNER AS "模式名" 
FROM DBA_TABLES 
WHERE TABLE_NAME = 'TEST_BACKUP';