常用sql
一、对象操作相关sql1.查看表占用空间：2.拼接sql，批量删除模式下的表：3.查找一个表属于哪个用户；4.查询某一模式下的所有表名5.查询数据库中所有用户及状态6.查询当前用户拥有哪些角色；7.查询当前角色下有哪些权限；8.查询数据库是否兼容其他数据库模式9.修改数据库当前兼容模式10.查询表上的唯一索引对应的列11.查询SYSDBA模式下含有CJSJ列的表的表名12.查询表中字段类型为TIMESTAMP的字段有哪些 二、优化相关sql1.更新模式下统计信息：2.更新单表统计信息：3.更新单列统计信息：4.查询慢sql:5.查询阻塞锁6.查询锁等待7.按照耗时排序查询慢sql8.查询死锁涉及的事务信息 三、 其他常用sql1.初始化代理环境：2.授权激活：3.查询数据库使用的字符集4.查询数据库大小写是否敏感5.查询varchar类型是否以字符为单位6.清理归档：7.查看用户会话空闲时间，登录失败次数限制8.查询用户的会话持续期以及会话使用cpu时间上限9.查询数据库有哪些定时任务及信息10查询数据库最近的10次定时任务执行情况一、对象操作相关sql
1.查看表占用空间：
SELECT TABLE_USED_PAGES('模式名', '表名') * PARA_VALUE / 1024 / 1024 "USER_USED_SPACE(MB)"FROM V$DM_INIWHERE 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为对应的表名
selectcolumn_nameFROMdba_ind_columnswhereindex_name in(selectindex_namefromdba_indexeswheretable_name ='PRODUCT_VENDOR'and uniqueness = 'UNIQUE');
11.查询SYSDBA模式下含有CJSJ列的表的表名
selecttable_namefromdba_tables where owner='SYSDBA'AND table_name in(selectb.namefromsyscolumns aleft join sysobjects bona.id=b.idwherea.name='CJSJ'andSCHID=(select IDfromsysobjectswheretype$='SCH'and name ='SYSDBA'))
12.查询表中字段类型为TIMESTAMP的字段有哪些
select COLUMN_NAME from USER_TAB_COLUMNS where table_name='SYS_MENU' and data_type='TIMESTAMP';
二、优化相关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) ,--获取完整sqlsess_id ,sess_seq ,sql_text ,state ,seq_no ,user_name ,trx_id ,create_time,clnt_ipfromv$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<>sessidorder by 6 asc,1 desc;
8.查询死锁涉及的事务信息
selectdh.trx_id ,sh.sess_id,wm_concat(top_sql_text)fromV$DEADLOCK_HISTORY dh,V$SQL_HISTORY shwheredh.trx_id =sh.trx_idand dh.sess_id=sh.sess_idgroup bydh.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 用户口令锁定时间fromSYSUSERS a,sysobjects bwhere a.id=b.id
8.查询用户的会话持续期以及会话使用cpu时间上限
selecta.ID as 用户ID,b.name as 用户名,a.INFO1 as 会话持续期,a.CPU_PER_SESSION as 会话使用cpu时间上限fromSYSRESOURCES a,sysobjects bwherea.id=b.id
9.查询数据库有哪些定时任务及信息
select * from SYSJOB.SYSJOBS
10查询数据库最近的10次定时任务执行情况
select * from SYSJOB.SYSJOBHISTORIES2 order by start_time limit 10;
欢迎关注达梦云适配中心；
