常用sql

一、对象操作相关sql

1.查看表占用空间：
SELECT TABLE_USED_PAGES('模式名', '表名') * PARA_VALUE / 1024 / 1024 "USER_USED_SPACE(MB)"
  FROM V$DM_INI
 WHERE PARA_NAME LIKE 'GLOBAL_PAGE_SIZE';
2.拼接sql，批量删除模式下的表：
select  'drop table SYSDBA."' 
|| A .TABLE_NAME
||' ;' 
FROM DBA_TABLES A WHERE OWNER='SYSDBA';
3.查找一个表属于哪个用户；
select * from dba_tables  where TABLE_NAME='表名';
4.查询某一模式下的所有表名
select TABLE_NAME from dba_tables where owner='SYSDBA';
5.查询数据库中所有用户及状态
select username, ACCOUNT_STATUS from dba_users ;
6.查询当前用户拥有哪些角色；
select grantee,granted_role from dba_role_privs where grantee='用户名';
7.查询当前角色下有哪些权限；
select grantee,privilege from dba_sys_privs where grantee='角色名';
8.查询数据库是否兼容其他数据库模式
select * from v$dm_ini where "V$DM_INI".PARA_NAME='COMPATIBLE_MODE';

查询结果为0，表示不兼容；
2：部分兼容 ORACLE，
3：部 分兼容 MS SQL SERVER，
4：部分兼容 MYSQL

9.修改数据库当前兼容模式

见上一条，如果修改为部分兼容 MYSQL；

sp_set_para_value(2,'COMPATIBLE_MODE',4);

见上一条，如果修改为部分兼容 ORACLE；

sp_set_para_value(2,'COMPATIBLE_MODE',2);

修改以后重启数据库实例服务生效；

10.查询表上的唯一索引对应的列

修改PRODUCT_VENDOR为对应的表名

select
        column_name
FROM
        dba_ind_columns
where
        index_name in
        (
                select
                        index_name
                from
                        dba_indexes
                where
                        table_name ='PRODUCT_VENDOR'
                    and uniqueness = 'UNIQUE'
        );
11.查询对应表的主键字段列

---查询MEETING_COPY表主键对应的字段列
select
        column_name
FROM
        dba_ind_columns
where
        index_name in
        (
                select
                        INDEX_NAME
                from
                        ALL_CONSTRAINTS
                where
                        constraint_type='P'
                    AND TABLE_NAME     ='MEETING_COPY'
        )
12.查询SYSDBA模式下含有CJSJ列的表的表名
select
        table_name
from
        dba_tables where owner='SYSDBA'
    AND table_name    in
        (
                select
                        b.name
                from
                        syscolumns a
                left join sysobjects b
                on
                        a.id=b.id
                where
                        a.name='CJSJ'
               and
                        SCHID=(select ID
                from
                        sysobjects
                where
                        type$='SCH'
                    and name ='SYSDBA'
        )
)
13.查询表中字段类型为TIMESTAMP的字段有哪些
select COLUMN_NAME from  USER_TAB_COLUMNS 
where 
table_name='SYS_MENU' 
and 
data_type='TIMESTAMP';
14.根据表名查询表中字段的相关信息
    select
                        a.name as 字段名,
                        a.type$
                        ||'('
                        ||a.LENGTH$
                        ||')'      as 字段类型,
                        b.COMMENT$ as 字段注释,
                        case
                                        (
                                                select count(*) from DBA_IND_COLUMNS c where a.name=c.COLUMN_NAME
                                        ) when 0                THEN '否' else '是' end as 是否有索引,
                        case              when a.NULLABLE$ ='N' then '否' else '是' end as 是否可为空,
                        a.DEFVAL                                                      as 默认值
                from
                        SYSCOLUMNS a
                left join SYSCOLUMNCOMMENTS b
                on
                        a.name=b.colname
                where
                        a.id in
                        (
                                select
                                        id
                                from
                                        sysobjects
                                where
                                        TYPE$   ='SCHOBJ'
                                    AND SUBTYPE$='UTAB'
                                    and NAME    ='表名'
                        )
15.获取表定义
----获取SYSDBA模式下C_TIME表的表定义
CALL SP_TABLEDEF('SYSDBA', 'C_TIME');
16.查询存储过程是否有效
----查询SYSDBA模式下PRO_TEST1存储过程是否有效
select VALID,* from
        sysobjects
where
        SYSOBJECTS."SUBTYPE$"='PROC'
        and name='PRO_TEST1'
    AND SCHID               in
        (
                select id from sysobjects where type$='SCH' AND NAME='SYSDBA'
        );

二、优化相关sql

1.更新模式下统计信息：
DBMS_STATS.GATHER_SCHEMA_STATS( 
'模式名',
100, 
FALSE, 
'FOR ALL COLUMNS SIZE AUTO');
2.更新单表统计信息：
SP_TAB_STAT_INIT('模式名','表名');
 或者 
DBMS_STATS.GATHER_TABLE_STATS('模式名','表名',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO')
3.更新单列统计信息：
  STAT 100 ON 表名(列名);
  sp_col_stat_init('模式名','表名','列名');
  sp_tab_col_stat_init('模式名','表名');
4.查询慢sql:
select datediff(ss,sysdate,last_send_time) a,
       SF_GET_SESSION_SQL(SESS_ID) ,--获取完整sql
        sess_id ,
        sess_seq ,
        sql_text ,
        state ,
        seq_no ,
        user_name ,
        trx_id ,
        create_time,
        clnt_ip
        
from
        v$sessions 
where state='ACTIVE' and sess_id != sessid 
order by a;
5.查询阻塞锁
select * from v$lock where blocked=1;
6.查询锁等待
select * from v$trxwait;
7.按照耗时排序查询慢sql
select timestampdiff(ss,LAST_RECV_TIME,sysdate),sf_get_session_sql(sess_id),* 
from SYS."V$SESSIONS"
where sess_id<>sessid
order by 6 asc,1 desc;
8.查询死锁涉及的事务信息
select
        dh.trx_id ,
        sh.sess_id,
        wm_concat(top_sql_text)
from
        V$DEADLOCK_HISTORY dh,
        V$SQL_HISTORY sh
where
        dh.trx_id =sh.trx_id
    and dh.sess_id=sh.sess_id
group by
        dh.trx_id,
        sh.sess_id

三、 其他常用sql

1.初始化代理环境：
 SP_INIT_JOB_SYS(1);
2.授权激活：
sp_load_lic_info();
3.查询数据库使用的字符集
SELECT sf_get_unicode_flag()

结果为1，表示字符集为utf-8;
结果为0，表示字符集为gbk;

4.查询数据库大小写是否敏感
select sf_get_case_sensitive_flag()

结果为1，表示大小写敏感；
结果为0，表示大小写不敏感；

5.查询varchar类型是否以字符为单位
select sf_get_length_in_char()

结果为1，表示以字符为单位；
结果为0，表示以字节为单位；

6.清理归档：
SF_ARCHIVELOG_DELETE_BEFORE_TIME(SYSDATE - 15);
7.查看用户会话空闲时间，登录失败次数限制
select  
        a.ID         as 用户ID,
        b.name as 用户名,
        CONN_IDLE_TIME as 用户会话的最大空闲时间,
        FAILED_NUM as 用户登录失败次数限制,
        SESS_PER_USER,
        LOCK_TIME as 用户口令锁定时间
from
        SYSUSERS a,sysobjects b
        where a.id=b.id
8.查询用户的会话持续期以及会话使用cpu时间上限
select
        a.ID   as 用户ID,
        b.name as 用户名,
        a.INFO1 as 会话持续期,
        a.CPU_PER_SESSION as 会话使用cpu时间上限
from
        SYSRESOURCES a,
        sysobjects b
where
        a.id=b.id
9.查询数据库有哪些定时任务及信息
select * from SYSJOB.SYSJOBS
10查询数据库最近的10次定时任务执行情况
select * from SYSJOB.SYSJOBHISTORIES2 
order by start_time 
limit 10;

欢迎关注达梦云适配中心；

# 数据库 # sql
Logo
华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

cover

Redis开源社区持续壮大，华为云为Valkey项目注入新的活力

avatar 华为云开发者联盟
cover

CCE云原生混部场景下的测试案例

avatar 华为云开发者联盟
cover

详解数仓的3A安全能力

avatar 华为云开发者联盟