达梦数据库常用sql(持续更新)

达梦云适配中心上线啦，可以在线玩数据库，在线玩sql，在线问问题~各位老表们快戳连接来玩吧：

https://eco.dameng.com/community/question

达梦7
1.查询会话中程序占用会话数

select appname,count(*) from v$sessions group by  "V$SESSIONS".APPNAME;

2.查看用户下指定表占用空间

SELECT TABLE_USED_SPACE('username','tablename')*SF_GET_PAGE_SIZE()/1024/1024||'M'

3.查看用户下指定表实际使用的空间

SELECT TABLE_USED_PAGES('unsername','tablename')*SF_GET_PAGE_SIZE()/1024/1024||'M'

4.批量删除包含test字段的表

select 'drop table '||owner||'.'||table_name||'; 'from DBA_TABLES where TABLE_NAME like '%test%' and OWNER = 'test';

5.批量查询表空间大小以及占用百分比

select
        a.tablespace_name                    ,
        a.bytes        /1024/1024 "Sum MB"          ,
        (a.bytes       -b.bytes)/1024/1024 "used MB",
        b.bytes        /1024/1024 "free MB"         ,
        round(((a.bytes-b.bytes)/a.bytes)*100, 2) "percent_used"
from
        (
                select
                        tablespace_name,
                        sum(bytes) bytes
                from
                        dba_data_files
                group by
                        tablespace_name
        )
        a,
        (
                select
                        tablespace_name ,
                        sum(bytes) bytes,
                        max(bytes) largest
                from
                        dba_free_space
                group by
                        tablespace_name
        )
        b
where
        a.tablespace_name=b.tablespace_name
order by
        ((a.bytes-b.bytes)/a.bytes) desc

6.查询用户下有哪些模式

select ur.name,sch.name from sysobjects sch ,sysbojects ur where ur.subtype$='USER' and sch.type$='SCH' and ur.name not like 'SYS%' and sch.pid=ur.id oder by 1;

7.查询某个用户下被授权了哪些角色

select * from "SYS"."DBA_ROLE_PRIVS" where GRANTED_ROLE like '%用户名%';

8.列出平均工资在1000之上，并且最低工资不低于800的工作信息（基本函数使用）

select job,avg(sal),min(sal) from emp group by job having avg(sal)>1000 and min(sal)>=800

9.列出不是销售员最低薪资大于1500的各种工作的信息（基本函数使用）

select job,min(sal) from emp where job<>'SALESMAN' group by job having min(sal)>1500

10.显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于$5000，输出结果按月工资的合计升序排列（基本函数使用）

SELECT job,SUM(sal) S_U_M FROM emp WHERE job<>'SALESMAN' GROUP BY job HAVING S_U_M>5000 ORDER BY S_U_M 

11.查看使用内存大的SQL语句：

SELECT SF_GET_SESSION_SQL(SESSID),MAX_MEM_USED,SQL_TXT FROM V$SQL_STAT ORDER BY MAX_MEM_USED DESC;
SELECT * FROM V$LARGE_MEM_SQLS;

12.查看未提交的事务

select t2.name from v$lock t1,sysobjects t2 where t1.table_id=t2.id and subtype$='UTAB';

13.查看最大会话数

select para_value from v$dm_ini where para_name='MAX_SESSIONS';

14.显示最近 100 条执行时间较长的 SQL 语句

select * from v$long_exec_sqls;

15.显示服务器启动以来执行时间最长的20 条 SQL 语句

select * from v$system_long_exec_sqls;

16.查询执行SQL错误码

select * from V$ERR_INFO where code = '-6111';

17.表重命名

alter table test rename to test_table

18.查询表注释

select comments from user_tab_comments where table_name = 'test_table'

19.增加列

alter table test_table add mozaiti int

20.增加一列不为空

alter table test_table add id_notnull int not null

21.删除列，列名：

alter table test_table drop mozaiti

22.修改列名

alter table test_table rename column idss to ids

23.禁用所有表的外键

select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||'; ' from sys.dba_constraints where owner not like 'SYS%' and constraint_type='R';

24.某张表执行delete操作时卡住，sql查出与问题表关联的外键后删除数据库后启用

select
       'alter table "'||OWNER||'"."'||rel.table_name||'" disable constraint "'||rel.constraint_name||'";' 
from
        sysobjects ptab,
        sysobjects pidx,
        dba_constraints rel
where
        pidx.pid=ptab.id
    and rel.r_constraint_name=pidx.name
    and rel.constraint_type='R'
    and ptab.name    ='表名不用带模式名'

达梦6
1.查询cpu占用较长时间未释放的sql

select sql_text,app_name,login_name,cpu_time_call frome v$session where cpu_time_call > 1000;

--dm6,dm7登录问题

问题原因：
@作为特殊符号使用，有歧义无法在密码中直接识别
解决方法：
使用用例：
（1）DM6的密码包含@特殊字符处理办法：
使用时请注意，如果密码中包含@符号，则连接串中的数据库服务器名或者ip必须被填充。
例如：./isql test/test@ch@127.0.0.1,最后一个@后的字符为数据库服务器ip
 （2）DM7的密码包含特殊字符处理办法：
有时候密码包含了@等特殊字符导致disql无法直接连接和运行。需要通过转移符来处 理。disql转义符使用如下
1.linux下，需要使用双引号将密码包含进来，同时外层再使用单引号进行转移，具体例子如下：
./disql SYSDBA/'"abcd@efgh"'@localhost
2.windows下，需要使用双引号将密码包含进来，同时对双引号使用\进行转移，具体例子如下：
disql SYSDBA/\"abcd@efgh\"@localhost
背景知识：
使用’””’或者\”\”的方式可将特殊字符转化为普通字符使用
--动态开启dm.ini参数
call sp_set_para_value(2,’RLOG_APPEND_LOGIC’,1)；
call sp_set_para_value(2,'ENABLE_ENCRYPT',0);

25.已执行超过 2 秒的活动 SQL

select
        *
from
        (
                SELECT
                        sess_id                                        ,
                        sql_text                                       ,
                        datediff(ss, last_send_time, sysdate) Y_EXETIME,
                        SF_GET_SESSION_SQL(SESS_ID) fullsql            ,
                        clnt_ip
                FROM
                        V$SESSIONS
                WHERE
                        STATE='ACTIVE'
        )
where
        Y_EXETIME>=2;