话不多说直接上货：
1.【查看数据库数据文件大小】
SQL>SELECT
ROUND(TOTAL_G-FREE_G, 1)
FROM
(
SELECT
SUM(TOTAL_SIZE)*AVG(PAGE_SIZE)/1024/1024/1024/1024 TOTAL_G,
SUM(FREE_SIZE) *AVG(PAGE_SIZE)/1024/1024/1024/1024 FREE_G
FROM
V$DATAFILE
);
SQL>select round((SUM(bytes)/1024/1024/1024/1024.0),1) from dba_data_files;
SQL>select SUM(bytes) from dba_data_files;2.【创建表空间和增加数据文件语句】
【创建表空间命令】
SQL>create tablespace "XXX" datafile '+DMDATA1/data/dsc/XXX.dbf' size 128 ;【增加数据文件命令】
SQL>ALTER TABLESPACE "SG_DATACENTER" ADD DATAFILE '+DMDATA1/data/dsc/SG_DATACENTER01.DBF' SIZE 1024 AUTOEXTEND ON NEXT 2048;3.【查询表空间使用率】
SQL>SELECT
A.GROUP_ID
,B.NAME
,A.TOTAL_M
,A.FREE_M
,A.TOTAL_M - A.FREE_M USED_M
,A.MAX_M
,CAST((A.TOTAL_M - A.FREE_M)/ A.TOTAL_M AS DEC(5,4)) RATE_USED
,CAST(A.TOTAL_M / A.MAX_M AS DEC(5,4)) MAXRATE_USED
FROM
(
SELECT
GROUP_ID ,
SUM(TOTAL_SIZE)*AVG(PAGE_SIZE)/1024/1024 TOTAL_M,
SUM(FREE_SIZE) *AVG(PAGE_SIZE)/1024/1024 FREE_M ,
SUM(MAX_SIZE) MAX_M
FROM
V$DATAFILE
GROUP BY
GROUP_ID
)
A,
V$TABLESPACE B
WHERE
A.GROUP_ID = B.ID4.【查询purge】
SQL>select
b.NAME,COUNT(1) AS NUM
from
V$PURGE_PSEG_TAB a,
SYS.SYSOBJECTS b
where
a.TAB_ID = b.ID
GROUP BY b.NAME
ORDER BY NUM DESC5.【查看purge对应记录数】
SQL>select
sch.name,
s.name ,
sum(abs(row_count))
from
v$purge_pseg_tab p,
sysobjects s ,
sysobjects sch
where
s.id =p.tab_id
and s.schid=sch.id
group by
sch.name,
s.name
order by
3 desc6.【hint优化手段】
SQL>select /*+adaptive_npln_flag(0)*/7.【查询数据库90秒内purge占用多的40条语句】
SQL>select sch.name,s.name,count(*),sum(abs(row_count)) from v$purge_pseg_tab p ,sysobjects s,sysobjects sch where s.id=p.tab_id and s.schid=sch.id group by sch.name,s.name order by 3 desc8.【查实时SQL执行情况简单版】
SQL>select timestampdiff(ss,last_send_time,sysdate),thrd_id,* from v$sessions where state = 'ACTIVE' order by 1 desc9.【查实时SQL执行情况带硬解析并可以加载长SQL版本】
SELECT
SF_GET_SESSION_SQL(SESS_ID) AS 完整SQL,
USER_NAME AS 用户名,
CURR_SCH AS 当前模式,
DATEDIFF(SS,LAST_RECV_TIME,SYSDATE) AS 运行时间,
APPNAME AS 应用名称,
CLNT_TYPE AS 接口类型,
CLNT_IP AS 会话IP,
CLNT_VER AS 接口版本,
THRD_ID AS 线程号,
TRX_ID AS 事务号,
CREATE_TIME AS 会话创建时间,
CLNT_HOST AS 会话主机名,
OSNAME AS 会话操作系统名,
'SP_CLOSE_SESSION(' || SESS_ID || ');' AS 停止命令,
HARD_PARSE_CNT AS 硬解析次数,
HARD_PARSE_TIME AS 硬解析时间,
LOGIC_READ_CNT AS 逻辑读页次数,
PHY_READ_CNT AS 物理读页次数,
RECYCLE_LOGIC_READ_CNT AS 临时表空间逻辑读次数,
RECYCLE_PHY_READ_CNT AS 临时表空间物理读次数
FROM
GV$SESSIONS S,V$SQL_STAT ST
WHERE
STATE = 'ACTIVE'
AND SESS_ID <> SESSID()
AND S.SESS_ID=ST.SESSID
ORDER BY
运行时间 DESC;10.【查锁与阻塞】
SQL>【DSC】select * from v$sessions where trx_id in (select trx_id from v$dsc_trxwait);
SQL>SELECT
DS.SESS_ID "被阻塞的会话ID" ,
DS.SQL_TEXT "被阻塞的SQL" ,
DS.TRX_ID "被阻塞的事务ID" ,
(CASE L.LTYPE WHEN 'OBJECT' THEN '对象锁' WHEN 'TID' THEN '事务锁' END CASE ) "被阻塞的锁类型",
DS.CREATE_TIME "开始阻塞时间" ,
SS.SESS_ID "占用锁的会话ID" ,
SS.SQL_TEXT "占用锁的SQL" ,
SS.CLNT_IP "占用锁的IP" ,
L.TID "占用锁的事务ID"
FROM
V$LOCK L
LEFT JOIN V$SESSIONS DS
ON
DS.TRX_ID = L.TRX_ID
LEFT JOIN V$SESSIONS SS
ON
SS.TRX_ID = L.TID
WHERE
L.BLOCKED = 1;
SQL>select s.name,trx_id from v$lock l ,sysobjects s where blocked=0 and ign_flag!=1 and lmode like '%X' and l.table_id=s.id11.【查询连接限制】
SQL>select
A.allow_addr ,
A.not_allow_addr,
A.allow_dt ,
A.not_allow_dt ,
B.USERNAME
from
SYSUSERS A,
DBA_USERS B
where
A.ID=B.USER_ID;12.【查重】
SQL>select id,count(*) from "XXX"."XXX" group by id having count(*) >1 13.【查询用户被授予了某个模式下哪些表的什么权限】
SQL>SELECT grantee,owner, table_name,
LISTAGG(privilege, ',') WITHIN GROUP (ORDER BY table_name) AS permissions
FROM dba_tab_privs
WHERE grantee = 'XXX'
AND owner LIKE 'XXX'
GROUP BY grantee,owner, table_name;14.【查询top中pid线程执行的sql】
SQL>select
SESS_ID ,
"V$SESSIONS".USER_NAME ,
SQL_TEXT ,
"V$SESSIONS".TRX_ID ,
"V$SESSIONS".THRD_ID,
"V$SESSIONS".CREATE_TIME,
"V$SESSIONS".CLNT_IP
FROM
V$SESSIONS
WHERE
"V$SESSIONS".THRD_ID IN (XXX);15.【查询执行时间大于10秒的sql】
SQL>SELECT
*
FROM
(
SELECT
USER_NAME ,
CLNT_IP ,
SESS_ID ,
SQL_TEXT ,
DATEDIFF(SS, LAST_SEND_TIME, SYSDATE) SS,
SF_GET_SESSION_SQL(SESS_ID) FULLSQL ,
APPNAME
FROM
V$SESSIONS
WHERE
STATE='ACTIVE'
AND USER_NAME!='SYSDBA'
)
WHERE
SS>10
ORDER BY
5 DESC16.【查看CPU占用较多的进程】
【shell:】ps aux|grep -v PID|sort -nr -k3 |head -n 1017.【查看内存占用较多的进程】
【shell:】ps aux|grep -v PID|sort -nr -k4 |head -n 1018.【查看内存占用大的SQL语句】
SQL>select * from v$sql_stat order by max_mem_used;
SQL>SELECT SF_GET_SESSION_SQL(SESSID),MAX_MEM_USED,SQL_TXT FROM V$SQL_STAT ORDER BY MAX_MEM_USED DESC;
SQL>SELECT * FROM V$LARGE_MEM_SQLS;19.【获取完整sql】
SQL>SF_GET_SESSION_SQL(SESS_ID);20.【查询应用连接数据库情况】
【shell】netstat -an |grep ESTABLISHED |awk '{print $5}' |awk -F: '{print $1}' |sort|uniq -c |sort -rn |head -n 2021.【dump缓存中执行计划语句】
SQL>select * from v$cachepln where sqlstr like '%XXX%';
SQL>alter session set events 'immediate trace name plndump level XXXX,dump_file ''/home/dmdba/dmdbms/XXX.log''';
SQL>call sp_clear_plan_cache(cache_item);22.【常用修改动态参数命令】
SQL>call SF_SET_SYSTEM_PARA_VALUE('ENABLE_DDL_ANY_PRIV',1,0,1);
SQL>call SF_SET_SYSTEM_PARA_VALUE('PWD_POLICY',1,0,1);
SQL>call sp_set_para_value(2,’RLOG_APPEND_LOGIC’,1);
SQL>call sp_set_para_value(2,'ENABLE_ENCRYPT',0);
SQL>call sp_set_para_value(1,'SVR_LOG',1);
SQL>call sp_set_para_value(1,'SVR_LOG',0);
SQL>call SP_REFRESH_SVR_LOG_CONFIG();
SQL>call sp_set_para_value(1,'LARGE_MEM_THRESHOLD',10000);
SQL>call sp_set_para_double_value(1,'UNDO_RETENTION',200);
SQL>call sp_sql_stat_init(' select * from dba_cons_columns');23.【commit日志执行时间长SQL过滤】
cat dmsql_DSC0_2024xxxx_xxxxxx.log | grep 'EXECTIME: [0-9][0-9][0-9][0-9]'24.【数据库日志检查点抓取刷新】
tail -100f dm_DSC1_202405.log |grep -E "checkpoint end|checkpoint begin"25.【当外键引用过多删除数据缓慢时，在一个manager窗口执行会话级SQL可以忽略约束关联检查】
set_session_cons_chk(0)26.【查询用户下有哪些模式】
select
ur.name,
sch.name
from
sysobjects sch,
sysobjects ur
where
ur.subtype$='USER'
and sch.type$ ='SCH'
and ur.name not like 'SYS%'
and sch.pid=ur.id
order by
1;27.【将disql命令行执行结果输出到文件】
spool /data/files/test.txt
select * from users;
spool off;28.【查询约束在哪个表】
SELECT * FROM DBA_CONSTRAINTS where CONSTRAINT_NAME='CONSxxxxxxxxxx';29.【查看某个模式下所有表的表的数据量】
【建中间表方式查询数据量】
CREATE TABLE "SYSDBA"."COUNT_XXXXX"
(
"TAB_NAME" VARCHAR(1000),
"ROW_NUM" INT) STORAGE(ON "MAIN", CLUSTERBTR) ;
select * from "SYSDBA"."COUNT_XXXXX" ORDER BY ROW_NUM DESC;
DECLARE
COUNT_ROWS INT;
V_SQL VARCHAR(100);
BEGIN
COUNT_ROWS:=0;
FOR A IN
(
SELECT
TABLE_NAME
FROM
DBA_TABLES
WHERE
OWNER='XXXX'
AND TABLE_NAME LIKE '%xxxx%')
LOOP
V_SQL:='SELECT COUNT(*) FROM xxxxx.'||A.TABLE_NAME;
EXECUTE IMMEDIATE V_SQL INTO COUNT_ROWS;
INSERT INTO COUNT_XXXXX VALUES
(A.TABLE_NAME,COUNT_ROWS
);
END LOOP;
END;
SELECT * FROM COUNT_XXXXX ORDER BY ROW_NUM DESC;【直接查询数据量】
select
t.owner,
t.table_name as "表名" ,
table_rowcount(t.owner, t.table_name) as "数据量"
--u.comments as "注释"
from
all_tables t,
all_tab_comments u
where
t.table_name = u.table_name
and
t.owner = 'XXXX';
select
t.owner ,
t.table_name as "表名",
table_rowcount(t.owner, t.table_name) as "数据量"
from
all_tables t
where
t.owner = 'XXXXX'30.【查看某个模式下所有表的大小GB】
select
owner ,
table_name,
table_used_pages(owner, table_name)*(page()/1024)/1024/1024 GB
from
all_tables
WHERE
owner not in ('SYS', 'SYSJOB', 'CTISYS') ORDER BY GB DESC
SELECT TABLE_USED_SPACE('username','tablename')*SF_GET_PAGE_SIZE()/1024/1024||'M' 31.【更新统计信息与清空执行计划】
sp clear plan cache
stat 100 on index xxxxxxx.xxxxxxx ;
stat 100 on xxxxxxx.xxxxxxx(xxxxx);
评论已关闭