1.查内存使用情况
select
SYSDATE ,
name ,
sum(total_size)/1024/1024,
avg(total_size)/1024/1024,
count( *) ,
max(total_size)/1024/1024,
avg(data_size) /1024/1024,
max(data_size) /1024/1024
from
v$mem_pool
group by
name;
2.查询在线sql时间超过1s的sql
select * from (
SELECT user_name,substr(clnt_ip,8) clnt_ip,sess_id,sql_text,datediff(ss,last_recv_time,sysdate) ss,
SF_GET_SESSION_SQL(SESS_ID) fullsql,appname
FROM V$SESSIONS WHERE STATE=‘ACTIVE’
and user_name!=‘SYSDBA’)
where ss>=1 order by 5 desc;
3.查询阻塞信息(需要执行被阻塞命令时同时查询以下语句)
SELECT SYSDATE STATTIME,DATEDIFF(SS,S1.LAST_SEND_TIME,SYSDATE) SS,
‘被阻塞的信息’ WT,S1.SESS_ID WT_SESS_ID,S1.SQL_TEXT WT_SQL_TEXT,S1.STATE WT_STATE,S1.TRX_ID WT_TRX_ID,
S1.USER_NAME WT_USER_NAME,S1.CLNT_IP WT_CLNT_IP,S1.APPNAME WT_APPNAME,S1.LAST_SEND_TIME WT_LAST_SEND_TIME,
‘引起阻塞的信息’ FM,S2.SESS_ID FM_SESS_ID,S2.SQL_TEXT FM_SQL_TEXT,S2.STATE FM_STATE,S2.TRX_ID FM_TRX_ID,
S2.USER_NAME FM_USER_NAME,S2.CLNT_IP FM_CLNT_IP,S2.APPNAME FM_APPNAME,S2.LAST_SEND_TIME FM_LAST_SEND_TIME
FROM V
S
E
S
S
I
O
N
S
S
1
,
V
SESSIONS S1,V
SESSIONSS1,VSESSIONS S2,V$TRXWAIT W
WHERE S1.TRX_ID=W.ID
AND S2.TRX_ID=W.WAIT_FOR_ID;
4.查询模式和用户对应关系
select
SCH_OBJ.NAME ,
SCH_OBJ.ID ,
SCH_OBJ.CRTDATE,
USER_OBJ.NAME
from
(
select NAME, ID, PID, CRTDATE from SYS.SYSOBJECTS where TYPE
=
′
S
C
H
′
)
S
C
H
O
B
J
,
(
s
e
l
e
c
t
N
A
M
E
,
I
D
f
r
o
m
S
Y
S
.
S
Y
S
O
B
J
E
C
T
S
w
h
e
r
e
T
Y
P
E
='SCH' ) SCH_OBJ, ( select NAME, ID from SYS.SYSOBJECTS where TYPE
=′SCH′)SCHOBJ,(selectNAME,IDfromSYS.SYSOBJECTSwhereTYPE=‘UR’ and SUBTYPE$=‘USER’
)
USER_OBJ
where
SCH_OBJ.PID=USER_OBJ.ID
ORDER BY
SCH_OBJ.NAME
5.查看指定用户占用的空间大小,如查看SYSDBA用户占用的空间大小
SELECT USER_USED_SPACE(‘SYSDBA’) * PARA_VALUE / 1024 / 1024 “USER_USED_SPACE(MB)”
FROM V$DM_INI
WHERE PARA_NAME LIKE ‘GLOBAL_PAGE_SIZE’;
6.查询数据库相关参数信息
SELECT ‘页大小’,cast(PAGE()/1024 as varchar) union all
SELECT ‘簇大小’,cast(SF_GET_EXTENT_SIZE() as varchar) union all
SELECT ‘字符集’,CASE SF_GET_UNICODE_FLAG() WHEN ‘0’ THEN ‘GBK18030’ WHEN ‘1’ then ‘UTF-8’ when ‘2’ then ‘EUC-KR’ end union all
SELECT ‘大小写敏感’,cast(SF_GET_CASE_SENSITIVE_FLAG() as varchar) union all
select ‘VARCHAR类型长度是否以字符为单位’,para_value from v
d
m
i
n
i
w
h
e
r
e
p
a
r
a
n
a
m
e
=
′
L
E
N
G
T
H
I
N
C
H
A
R
′
u
n
i
o
n
a
l
l
s
e
l
e
c
t
′
空格兼容模
式
′
,
p
a
r
a
v
a
l
u
e
f
r
o
m
v
dm_ini where para_name='LENGTH_IN_CHAR' union all select '空格兼容模式',para_value from v
dminiwhereparaname=′LENGTHINCHAR′unionallselect′空格兼容模式′,paravaluefromvdm_ini where para_name=‘BLANK_PAD_MODE’ union all
select ‘实例名称’ 数据库选项,INSTANCE_NAME 数据库集群相关参数值 FROM v
i
n
s
t
a
n
c
e
u
n
i
o
n
a
l
l
s
e
l
e
c
t
′
数据库
名
′
,
n
a
m
e
f
r
o
m
v
instance union all select '数据库名',name from v
instanceunionallselect′数据库名′,namefromvdatabase union all
select ‘端口号’,para_value from v
d
m
i
n
i
w
h
e
r
e
p
a
r
a
n
a
m
e
=
′
P
O
R
T
N
U
M
′
u
n
i
o
n
a
l
l
s
e
l
e
c
t
′
数据库版
本
′
,
s
u
b
s
t
r
(
s
v
r
v
e
r
s
i
o
n
,
i
n
s
t
r
(
s
v
r
v
e
r
s
i
o
n
,
′
(
′
)
)
F
R
O
M
v
dm_ini where para_name='PORT_NUM' union all select '数据库版本',substr(svr_version,instr(svr_version,'(')) FROM v
dminiwhereparaname=′PORTNUM′unionallselect′数据库版本′,substr(svrversion,instr(svrversion,′(′))FROMvinstance union all
select ‘数据库模式’,MODE$ from v
i
n
s
t
a
n
c
e
u
n
i
o
n
a
l
l
S
E
L
E
C
T
′
字符数据类
型
′
N
A
M
E
,
C
A
S
E
V
A
L
U
E
W
H
E
N
0
T
H
E
N
′
B
Y
T
E
′
W
H
E
N
1
T
H
E
N
′
C
H
A
R
′
e
n
d
F
R
O
M
v
instance union all SELECT '字符数据类型' NAME, CASE VALUE WHEN 0 THEN 'BYTE' WHEN 1 THEN 'CHAR' end FROM v
instanceunionallSELECT′字符数据类型′NAME,CASEVALUEWHEN0THEN′BYTE′WHEN1THEN′CHAR′endFROMvparameter WHERE name like ‘%LENGTH_IN_CHAR%’ union all
select ‘唯一魔数’,cast(permanent_magic as varchar) union all
select ‘LSN’,cast(cur_lsn as varchar) from v
r
l
o
g
u
n
i
o
n
a
l
l
s
e
l
e
c
t
′
K
E
Y
文件属
性
′
,
c
l
u
s
t
e
r
t
y
p
e
f
r
o
m
v
rlog union all select 'KEY文件属性', cluster_type from v
rlogunionallselect′KEY文件属性′,clustertypefromvlicense;
或者通过v$options也可以查询
7.查询IP来源连接数信息
select substr(clnt_ip,1,19) ,count(*) from v$sessions group by substr(clnt_ip,1,19) ;
8.查看所有表空间大小及其使用情况
SELECT F.TABLESPACE_NAME,
(T.TOTAL_SPACE - F.FREE_SPACE) / 1024 “USED (GB)”,
F.FREE_SPACE / 1024 “FREE (GB)”,
T.TOTAL_SPACE / 1024 “TOTAL(GB)”,
(ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100)) || '% ’ PER_FREE
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BLOCKS *
(SELECT PARA_VALUE / 1024
FROM V$DM_INI
WHERE PARA_NAME = ‘GLOBAL_PAGE_SIZE’) / 1024)) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;
9.生成所有表的统计信息语句
select ‘DBMS_STATS.GATHER_TABLE_STATS(’‘’||OWNER||‘’‘,’‘’||TABLE_NAME||‘’‘);’ from dba_tables where owner=‘GMP_BPMS’;
select ‘DBMS_STATS.GATHER_INDEX_STATS(’‘’||OWNER||‘’‘,’‘’||TABLE_NAME||‘’‘);’ from dba_indexes where owner=‘GMP_BPMS’;
–收集索引的统计信息
STAT 100 ON 索引名;
–收集模式索引的统计信息
DBMS_STATS.GATHER_SCHEMA_STATS(‘模式名’,100,TRUE,‘FOR ALL COLUMNS SIZE AUTO);
–收集指定表的统计信息
DBMS_STATS.GATHER_INDEX_STATS(‘SYSDBA’,‘ITEST1’,NULL,100);
–收集表的指定列的统计信息
stat 100 on 表(列名)
–收集表的指定列的统计信息
DBMS_STATS.GATHER_TABLE_STATS(‘JQ_FSSC’’,‘AP_DEBTBILL’,null,100,TRUE,‘FOR ALL COLUMNS SIZE AUTO’);
10.可以估算下系统运行时对UNDO_EXTENT_NUM的要求
select sum(N_PAGES)/count(*) /SF_GET_EXTENT_SIZE () from V$PSEG_ITEMS;