1、查询版本号
SELECT
BANNER
FROM
V
V
E
R
S
I
O
N
W
H
E
R
E
B
A
N
N
E
R
L
I
K
E
′
D
M
D
a
t
a
b
a
s
e
∗
∗
2
、查询实例信息
∗
∗
s
e
l
e
c
t
′
版
本
′
a
s
名称
,
S
V
R
V
E
R
S
I
O
N
a
s
信息
f
r
o
m
v
VERSION WHERE BANNER LIKE 'DM Database%' **2、查询实例信息** select '版本' as 名称,SVR_VERSION as 信息 from v
VERSIONWHEREBANNERLIKE′DMDatabase∗∗2、查询实例信息∗∗select′版本′as名称,SVRVERSIONas信息fromvinstance union all
select ‘数据库名’ as 名称,CUR_DATABASE() 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
′
永久魔术
值
′
,
t
o
c
h
a
r
(
P
E
R
M
A
N
E
N
T
M
A
G
I
C
(
)
)
u
n
i
o
n
a
l
l
s
e
l
e
c
t
′
簇大
小
′
,
S
F
G
E
T
E
X
T
E
N
T
S
I
Z
E
(
)
∣
∣
′
页
′
u
n
i
o
n
a
l
l
s
e
l
e
c
t
′
页大
小
′
,
P
A
G
E
(
)
/
1024
∣
∣
′
K
′
u
n
i
o
n
a
l
l
s
e
l
e
c
t
′
字符
集
′
,
D
E
C
O
D
E
(
U
N
I
C
O
D
E
,
′
0
′
,
′
G
B
1803
0
′
,
′
1
′
,
′
U
T
F
−
8
′
,
′
2
′
,
′
E
U
C
−
K
R
′
)
u
n
i
o
n
a
l
l
s
e
l
e
c
t
′
大小
写
′
,
D
E
C
O
D
E
(
S
F
G
E
T
C
A
S
E
S
E
N
S
I
T
I
V
E
F
L
A
G
,
′
0
′
,
′
不敏
感
′
,
′
1
′
,
′
敏
感
′
)
u
n
i
o
n
a
l
l
s
e
l
e
c
t
′
空格填
充
′
,
D
E
C
O
D
E
(
B
L
A
N
K
P
A
D
M
O
D
E
,
′
0
′
,
′
否
′
,
′
1
′
,
′
是
′
)
u
n
i
o
n
a
l
l
s
e
l
e
c
t
′
日志大
小
′
,
M
A
X
(
R
L
O
G
S
I
Z
E
/
1024
/
1024
)
∣
∣
′
M
B
′
f
r
o
m
v
instance union all select '永久魔术值',to_char(PERMANENT_MAGIC()) union all select '簇大小',SF_GET_EXTENT_SIZE ()||'页' union all select '页大小',PAGE()/1024||'K' union all select '字符集',DECODE(UNICODE,'0','GB18030','1','UTF-8','2','EUC-KR') union all select '大小写',DECODE(SF_GET_CASE_SENSITIVE_FLAG,'0','不敏感','1','敏感') union all select '空格填充',DECODE(BLANK_PAD_MODE,'0','否','1','是') union all select '日志大小',MAX(RLOG_SIZE/1024/1024)||'MB' from v
instanceunionallselect′永久魔术值′,tochar(PERMANENTMAGIC())unionallselect′簇大小′,SFGETEXTENTSIZE()∣∣′页′unionallselect′页大小′,PAGE()/1024∣∣′K′unionallselect′字符集′,DECODE(UNICODE,′0′,′GB18030′,′1′,′UTF−8′,′2′,′EUC−KR′)unionallselect′大小写′,DECODE(SFGETCASESENSITIVEFLAG,′0′,′不敏感′,′1′,′敏感′)unionallselect′空格填充′,DECODE(BLANKPADMODE,′0′,′否′,′1′,′是′)unionallselect′日志大小′,MAX(RLOGSIZE/1024/1024)∣∣′MB′fromvrlogfile union all
select ‘归档状态’,DECODE(ARCH_MODE,‘Y’,‘启用’,‘N’,‘未启用’) from v
d
a
t
a
b
a
s
e
u
n
i
o
n
a
l
l
s
e
l
e
c
t
′
创建时
间
′
,
c
a
s
t
(
C
R
E
A
T
E
T
I
M
E
a
s
v
a
r
c
h
a
r
(
50
)
)
f
r
o
m
v
database union all select '创建时间', cast(CREATE_TIME as varchar(50)) from v
databaseunionallselect′创建时间′,cast(CREATETIMEasvarchar(50))fromvdatabase;
3、查询当前会话 id
SELECT SESSID ();
查询当前登录用户 id
SELECT UID();
4、查询会话信息
SELECT
SESS_ID AS “会话ID”,
DECODE(STATE,
‘CREATE’,
‘创建’,
‘STARTUP’,
‘启动’,
‘IDLE’,
‘空闲’,
‘ACTIVE’,
‘活动’,
‘WAIT’,
‘等待’,
‘UNKNOWN’,
‘未知’) AS “会话状态”,
CREATE_TIME AS “会话创建时间”,
TRX_ID AS “事务ID”,
SQL_TEXT AS “SQL”,
USER_NAME AS “当前用户”,
CURR_SCH AS “当前模式”,
CLNT_TYPE AS “连接类型”,
DECODE(AUTO_CMT,
‘Y’,
‘是’,
‘N’,
‘否’) AS “是否自动提交”,
DECODE(DDL_AUTOCMT,
‘Y’,
‘是’,
‘N’,
‘否’) AS “DDL是否自动提交”,
APPNAME AS “连接程序名”,
CLNT_IP AS “客户机IP”,
CLNT_HOST AS “客户机名”,
OSNAME AS “客户机系统”
FROM
V
S
E
S
S
I
O
N
S
;
杀掉会话
−
−
根据
S
E
S
S
I
D
杀掉会话
S
P
C
L
O
S
E
S
E
S
S
I
O
N
(
157792223
)
;
∗
∗
5
、统计会话信息
∗
∗
S
E
L
E
C
T
S
T
A
T
E
A
S
状态
,
C
L
N
T
I
P
A
S
连接
I
P
,
C
O
U
N
T
(
∗
)
A
S
数量
F
R
O
M
V
SESSIONS; 杀掉会话 --根据SESS_ID杀掉会话 SP_CLOSE_SESSION(157792223); **5、统计会话信息** SELECT STATE AS 状态, CLNT_IP AS 连接IP, COUNT(*)AS 数量 FROM V
SESSIONS;杀掉会话−−根据SESSID杀掉会话SPCLOSESESSION(157792223);∗∗5、统计会话信息∗∗SELECTSTATEAS状态,CLNTIPAS连接IP,COUNT(∗)AS数量FROMVSESSIONS
GROUP BY
STATE,
CLNT_IP
6、查询锁信息
SELECT
ADDR AS “锁地址”,
TRX_ID AS “所属事务ID”,
LTYPE AS “锁类型”,
LMODE AS “锁模式”,
DECODE(BLOCKED,
‘1’,
‘是’,
‘0’,
‘否’) AS “是否阻塞”,
TABLE_ID AS “对应表锁ID”,
ROW_IDX AS “TID锁事务ID”
FROM
V
L
O
C
K
;
7
、查询发生死锁的历史记录
S
E
L
E
C
T
S
E
S
S
I
D
A
S
"
会话
I
D
"
,
T
R
X
I
D
A
S
"
事务
I
D
"
,
S
Q
L
T
E
X
T
A
S
"
产生死锁的
S
Q
L
"
,
H
A
P
P
E
N
T
I
M
E
A
S
"
死锁发生时间
"
F
R
O
M
V
LOCK; 7、查询发生死锁的历史记录 SELECT SESS_ID AS "会话ID", TRX_ID AS "事务ID", SQL_TEXT AS "产生死锁的SQL", HAPPEN_TIME AS "死锁发生时间" FROM V
LOCK;7、查询发生死锁的历史记录SELECTSESSIDAS"会话ID",TRXIDAS"事务ID",SQLTEXTAS"产生死锁的SQL",HAPPENTIMEAS"死锁发生时间"FROMVDEADLOCK_HISTORY;
查询用户列表
SELECT
TABLESPACE_NAME,
TABLE_NAME
FROM
USER_TABLES
查询系统所有表
SELECT
OWNER,
TABLE_NAME
FROM
ALL_TABLES;
8、查询是否开启归档日志
SELECT
ARCH_MODE
FROM
VKaTeX parse error: Expected 'EOF', got '#' at position 61: …IN(FIRST_CHANGE#̲) FROM …ARCHIVED_LOG
WHERE
ARCHIVED = ‘YES’
AND DELETED = ‘NO’
AND STATUS = ‘A’
9、查询有效归档日志中最大的更改编号
SELECT
MAX(NEXT_CHANGE#)
FROM
VKaTeX parse error: Expected 'EOF', got '#' at position 160: … A.FIRST_CHANGE#̲ FIRST_CHANGE, …ARCHIVED_LOG A
WHERE
A.NAME IS NOT NULL
AND A.ARCHIVED = ‘YES’
AND A.STATUS = ‘A’
AND A.NEXT_CHANGE# > 618083584
ORDER BY
7
这边sql 中过滤掉了 SCN 小于的 618083584 的记录
10、删除归档日志
–保留30天归档
SF_ARCHIVELOG_DELETE_BEFORE_TIME(SYSDATE - 30);
11、查询归档日志详情
SELECT
*
FROM
SYS."V
A
R
C
H
I
V
E
D
L
O
G
"
;
∗
∗
12
、查询下一个
r
e
d
o
日志序列号
∗
∗
S
E
L
E
C
T
N
E
X
T
S
E
Q
F
R
O
M
V
ARCHIVED_LOG"; **12、查询下一个redo日志序列号** SELECT NEXT_SEQ FROM V
ARCHIVEDLOG";∗∗12、查询下一个redo日志序列号∗∗SELECTNEXTSEQFROMVRLOG；
查询当前redo 日志序列号
SELECT
NEXT_SEQ -1
FROM
V$RLOG；
如何判断 redo 日志发生切换
两次查询当前的 redo日志序列号,如果两次结果不一样，则说明redo日志发生了切换。
13、查询redo文件信息
SELECT
CLIENT_PATH AS “日志名”,
PATH AS “路径”,
(RLOG_SIZE / 1024 / 1024) AS “文件大小M”,
CREATE_TIME AS “创建时间”
FROM
VKaTeX parse error: Double subscript at position 441: …AGIC, CKPT_N_̲PRIMAY_EP, C…RLOG
查询所有表行数
SELECT
TABLE_NAME AS 表名,
OWNER AS 所属用户,
TABLESPACE_NAME AS 所属表空间,
NUM_ROWS AS 行数
FROM
DBA_TABLES
WHERE
OWNER NOT IN (‘SYS’, ‘SYSTEM’, ‘SYSAUDITOR’, ‘SYSJOB’, ‘SYSSSO’, ‘CTISYS’)
AND TABLESPACE_NAME != ‘TEMP’;
15、查询模式下所有表行数
SELECT
OWNER,
TABLE_NAME,
NUM_ROWS,
TABLESPACE_NAME
FROM
DBA_TABLES T
WHERE
T.OWNER = UPPER(‘TEST’);
16、统计各个模式下表数据量
SELECT
OWNER,
SUM(NUM_ROWS)
FROM
DBA_TABLES
GROUP BY
OWNER;
17、查询模式下各个表索引
SELECT
TABLE_OWNER AS “表归属”,
TABLE_NAME AS “表名”,
OWNER AS “索引归属”,
INDEX_NAME AS “索引名”,
INDEX_TYPE AS “索引类型”,
TABLESPACE_NAME AS “索引所在表空间”,
JOIN_INDEX AS “是否组合索引”,
DECODE(VISIBILITY,
‘VISIBLE’,
‘可见’,
‘INVISIBLE’,
‘不可见’) AS “索引是否可见”
FROM
DBA_INDEXES
WHERE
OWNER = ‘TEST’
其中 TEST是具体的模式名称
18、查询模式下有哪些表
SELECT
NAME AS “表名”
FROM
SYSOBJECTS
WHERE
SUBTYPE$ = ‘UTAB’
AND SCHID IN (
SELECT
A.ID
FROM
SYSOBJECTS A,
ALL_USERS B
WHERE
A.TYPE$ = ‘SCH’
AND A.PID = B.USER_ID
AND A.NAME = ‘TEST’);
其中 TEST是具体的模式名称
19、查询模式下表的总数量
SELECT
count(*)
FROM
all_all_tables
WHERE
owner = ‘TEST’;
其中 TEST是具体的模式名称
20、查询当前用户下有哪些模式
SELECT
A.NAME AS “模式名”,
A.ID AS “模式ID”
FROM
SYSOBJECTS A,
ALL_USERS B
WHERE
A.TYPE$ = ‘SCH’
AND A.PID = B.USER_ID;
21、查询所有用户信息
SELECT
USER_ID AS “用户ID”,
USERNAME AS “用户名”,
ACCOUNT_STATUS AS “状态”,
DEFAULT_TABLESPACE AS “默认表空间”,
CREATED AS “创建时间”,
LOCK_DATE AS “锁定开始时间”,
EXPIRY_DATE AS “密码有效期截止”
FROM
DBA_USERS;
22、查询表结构信息
– 查看TEST模式下TEST_TABLE表的结构
SELECT
TABLEDEF(‘TEST’, ‘TEST_TABLE’)
FROM
DUAL;
23、查询数据库实例信息
SELECT
B.SVR_VERSION AS “数据库版本”,
C.DB_MAGIC AS “数据库MAGIC”,
A.NAME AS “数据库名”,
B.INSTANCE_NAME AS “实例名”,
B.STATUS$ AS “系统状态”,
B.MODE$ AS “模式”,
DECODE(A.ARCH_MODE,
‘Y’,
‘是’,
‘N’,
‘否’) AS “是否归档”,
B.START_TIME AS “服务启动时间”
FROM
V
D
A
T
A
B
A
S
E
A
,
V
DATABASE A, V
DATABASEA,VINSTANCE B,
V
R
L
O
G
C
;
24
、查询表空间信息
S
E
L
E
C
T
N
A
M
E
A
S
名称
,
D
E
C
O
D
E
(
T
Y
P
E
RLOG C; 24、查询表空间信息 SELECT NAME AS 名称, DECODE(TYPE
RLOGC;24、查询表空间信息SELECTNAMEAS名称,DECODE(TYPE,
‘1’,
‘DB类型’,
‘2’,
‘临时表空间’)AS 类型,
DECODE(STATUS
,
′
0
′
,
′
联
机
′
,
′
1
′
,
′
脱
机
′
,
′
2
′
,
′
R
E
S
O
F
F
L
I
N
E
′
,
′
3
′
,
′
C
O
R
R
U
P
T
′
)
A
S
状态
,
T
O
T
A
L
S
I
Z
E
∗
P
A
G
E
/
1024
/
1024
A
S
总大小
M
B
,
F
I
L
E
N
U
M
A
S
包含文件数
F
R
O
M
V
, '0', '联机', '1', '脱机', '2', 'RES_OFFLINE', '3', 'CORRUPT')AS 状态, TOTAL_SIZE * PAGE / 1024 / 1024 AS 总大小MB, FILE_NUM AS 包含文件数 FROM V
,′0′,′联机′,′1′,′脱机′,′2′,′RESOFFLINE′,′3′,′CORRUPT′)AS状态,TOTALSIZE∗PAGE/1024/1024AS总大小MB,FILENUMAS包含文件数FROMVTABLESPACE;
25、查询数据文件信息
SELECT
PATH AS 文件路径,
(TOTAL_SIZE * PAGE / 1024 / 1024)AS 文件大小MB,
(FREE_SIZE * PAGE / 1024 / 1024)AS 剩余大小MB,
(CAST((TOTAL_SIZE-FREE_SIZE)* 100 / TOTAL_SIZE AS NUMERIC(2,
0))|| ‘%’) AS 使用比例,
DECODE(AUTO_EXTEND,
‘0’,
‘关闭’,
‘1’,
‘打开’) AS 自动扩展,
NEXT_SIZE AS 扩充尺寸MB,
MAX_SIZE AS 扩充上限MB,
CREATE_TIME AS 创建时间,
MODIFY_TIME AS 修改时间
FROM
V
D
A
T
A
F
I
L
E
;
26
、查询阻塞信息
d
m
8
W
I
T
H
T
R
X
T
A
B
A
S
(
S
E
L
E
C
T
D
I
S
T
I
N
C
T
O
1.
N
A
M
E
,
L
1.
T
R
X
I
D
F
R
O
M
V
DATAFILE; 26、查询阻塞信息 dm8 WITH TRX_TAB AS( SELECT DISTINCT O1.NAME, L1.TRX_ID FROM V
DATAFILE;26、查询阻塞信息dm8WITHTRXTABAS(SELECTDISTINCTO1.NAME,L1.TRXIDFROMVLOCK L1,
SYSOBJECTS O1
WHERE
L1.TABLE_ID = O1.ID
AND O1.ID <> 0),
TRX_SESS AS (
SELECT
L.TRX_ID WT_TRXID,
L.TID BLK_TRXID,
L.BLOCKED,
(
SELECT
NAME TABLE_NAME
FROM
TRX_TAB A
WHERE
A.TRX_ID = L.TRX_ID) WT_TABLE,
S1.SESS_ID WT_SESS,
S2.SESS_ID BLK_SESS,
S1.USER_NAME WT_USER_NAME,
S2.USER_NAME BLK_USER_NAME,
S1.SQL_TEXT,
S1.CLNT_IP,
DATEDIFF(SS, S1.LAST_SEND_TIME, SYSDATE) SS
FROM
V
L
O
C
K
L
,
V
LOCK L, V
LOCKL,VSESSIONS S1,
V
S
E
S
S
I
O
N
S
S
2
W
H
E
R
E
L
.
T
R
X
I
D
=
S
1.
T
R
X
I
D
A
N
D
L
.
T
I
D
=
S
2.
T
R
X
I
D
)
S
E
L
E
C
T
S
Y
S
D
A
T
E
S
T
A
T
T
I
M
E
,
∗
F
R
O
M
T
R
X
S
E
S
S
W
H
E
R
E
B
L
O
C
K
E
D
=
1
;
d
m
7
W
I
T
H
L
O
C
K
S
A
S
(
S
E
L
E
C
T
O
.
N
A
M
E
,
L
.
∗
,
S
.
S
E
S
S
I
D
,
S
.
S
Q
L
T
E
X
T
,
S
.
C
L
N
T
I
P
,
S
.
L
A
S
T
S
E
N
D
T
I
M
E
F
R
O
M
V
SESSIONS S2 WHERE L.TRX_ID = S1.TRX_ID AND L.TID = S2.TRX_ID )SELECT SYSDATE STATTIME, * FROM TRX_SESS WHERE BLOCKED = 1; dm7 WITH LOCKS AS( SELECT O.NAME, L.*, S.SESS_ID, S.SQL_TEXT, S.CLNT_IP, S.LAST_SEND_TIME FROM V
SESSIONSS2WHEREL.TRXID=S1.TRXIDANDL.TID=S2.TRXID)SELECTSYSDATESTATTIME,∗FROMTRXSESSWHEREBLOCKED=1;dm7WITHLOCKSAS(SELECTO.NAME,L.∗,S.SESSID,S.SQLTEXT,S.CLNTIP,S.LASTSENDTIMEFROMVLOCK L,
SYSOBJECTS O,
V
S
E
S
S
I
O
N
S
S
W
H
E
R
E
L
.
T
A
B
L
E
I
D
=
O
.
I
D
A
N
D
L
.
T
R
X
I
D
=
S
.
T
R
X
I
D
)
,
L
O
C
K
T
R
A
S
(
S
E
L
E
C
T
T
R
X
I
D
W
T
T
R
X
I
D
,
R
O
W
I
D
X
B
L
K
T
R
X
I
D
F
R
O
M
L
O
C
K
S
W
H
E
R
E
B
L
O
C
K
E
D
=
1
)
,
R
E
S
A
S
(
S
E
L
E
C
T
S
Y
S
D
A
T
E
S
T
A
T
T
I
M
E
,
T
1.
N
A
M
E
,
T
1.
S
E
S
S
I
D
W
T
S
E
S
S
I
D
,
S
.
W
T
T
R
X
I
D
,
T
2.
S
E
S
S
I
D
B
L
K
S
E
S
S
I
D
,
S
.
B
L
K
T
R
X
I
D
,
T
2.
C
L
N
T
I
P
,
S
F
G
E
T
S
E
S
S
I
O
N
S
Q
L
(
T
1.
S
E
S
S
I
D
)
F
U
L
S
Q
L
,
D
A
T
E
D
I
F
F
(
S
S
,
T
1.
L
A
S
T
S
E
N
D
T
I
M
E
,
S
Y
S
D
A
T
E
)
S
S
,
T
1.
S
Q
L
T
E
X
T
W
T
S
Q
L
F
R
O
M
L
O
C
K
T
R
S
,
L
O
C
K
S
T
1
,
L
O
C
K
S
T
2
W
H
E
R
E
T
1.
L
T
Y
P
E
=
′
O
B
J
E
C
T
′
A
N
D
T
1.
T
A
B
L
E
I
D
<
>
0
A
N
D
T
2.
L
T
Y
P
E
=
′
O
B
J
E
C
T
′
A
N
D
T
2.
T
A
B
L
E
I
D
<
>
0
A
N
D
S
.
W
T
T
R
X
I
D
=
T
1.
T
R
X
I
D
A
N
D
S
.
B
L
K
T
R
X
I
D
=
T
2.
T
R
X
I
D
)
S
E
L
E
C
T
D
I
S
T
I
N
C
T
W
T
S
Q
L
,
C
L
N
T
I
P
,
S
S
,
W
T
T
R
X
I
D
,
B
L
K
T
R
X
I
D
F
R
O
M
R
E
S
;
26
、查询等待事件
S
E
L
E
C
T
T
H
R
E
A
D
I
D
A
S
"
线程
I
D
"
,
T
R
X
I
D
A
S
"
事务
I
D
"
,
W
A
I
T
C
L
A
S
S
A
S
"
等待类型号
"
,
W
A
I
T
O
B
J
E
C
T
A
S
"
等待对象
"
,
W
A
I
T
S
T
A
R
T
A
S
"
等待开始时间
"
,
T
R
U
N
C
(
W
A
I
T
T
I
M
E
,
6
)
/
1000000
A
S
"
等待时间
"
F
R
O
M
V
SESSIONS S WHERE L.TABLE_ID = O.ID AND L.TRX_ID = S.TRX_ID), LOCK_TR AS( SELECT TRX_ID WT_TRXID, ROW_IDX BLK_TRXID FROM LOCKS WHERE BLOCKED = 1), RES AS( SELECT SYSDATE STATTIME, T1.NAME, T1.SESS_ID WT_SESSID, S.WT_TRXID, T2.SESS_ID BLK_SESSID, S.BLK_TRXID, T2.CLNT_IP, SF_GET_SESSION_SQL(T1.SESS_ID)FULSQL, DATEDIFF(SS, T1.LAST_SEND_TIME, SYSDATE)SS, T1.SQL_TEXT WT_SQL FROM LOCK_TR S, LOCKS T1, LOCKS T2 WHERE T1.LTYPE = 'OBJECT' AND T1.TABLE_ID <> 0 AND T2.LTYPE = 'OBJECT' AND T2.TABLE_ID <> 0 AND S.WT_TRXID = T1.TRX_ID AND S.BLK_TRXID = T2.TRX_ID) SELECT DISTINCT WT_SQL, CLNT_IP, SS, WT_TRXID, BLK_TRXID FROM RES; 26、查询等待事件 SELECT THREAD_ID AS "线程ID", TRX_ID AS "事务ID", WAIT_CLASS AS "等待类型号", WAIT_OBJECT AS "等待对象", WAIT_START AS "等待开始时间", TRUNC(WAIT_TIME, 6)/ 1000000 AS "等待时间" FROM V
SESSIONSSWHEREL.TABLEID=O.IDANDL.TRXID=S.TRXID),LOCKTRAS(SELECTTRXIDWTTRXID,ROWIDXBLKTRXIDFROMLOCKSWHEREBLOCKED=1),RESAS(SELECTSYSDATESTATTIME,T1.NAME,T1.SESSIDWTSESSID,S.WTTRXID,T2.SESSIDBLKSESSID,S.BLKTRXID,T2.CLNTIP,SFGETSESSIONSQL(T1.SESSID)FULSQL,DATEDIFF(SS,T1.LASTSENDTIME,SYSDATE)SS,T1.SQLTEXTWTSQLFROMLOCKTRS,LOCKST1,LOCKST2WHERET1.LTYPE=′OBJECT′ANDT1.TABLEID<>0ANDT2.LTYPE=′OBJECT′ANDT2.TABLEID<>0ANDS.WTTRXID=T1.TRXIDANDS.BLKTRXID=T2.TRXID)SELECTDISTINCTWTSQL,CLNTIP,SS,WTTRXID,BLKTRXIDFROMRES;26、查询等待事件SELECTTHREADIDAS"线程ID",TRXIDAS"事务ID",WAITCLASSAS"等待类型号",WAITOBJECTAS"等待对象",WAITSTARTAS"等待开始时间",TRUNC(WAITTIME,6)/1000000AS"等待时间"FROMVWAIT_HISTORY;
27、查询历史错误信息
SELECT
SESS_ID AS “会话ID”,
TRX_ID AS “事务ID”,
DECODE(SU_FLAG,
‘U’,
‘用户异常’,
‘S’,
‘系统异常’,
‘P’,
‘语法异常’) AS “错误类型”,
SQL_TEXT AS “错误的SQL”,
ECPT_CODE AS “错误提示”,
ERR_TIME AS “产生时间”
FROM
V$RUNTIME_ERR_HISTORY;
28、清除当日之前的空闲会话
DECLARE
vsessid varchar(50);
venddate varchar(8);
isessioncnt int;
CURSOR c1;
BEGIN
vsessid := ‘’;
SELECT
to_char(sysdate , ‘yyyymmdd’)
INTO
venddate
FROM
dual;
–获取指定日期
SELECT
count(1)
INTO
isessioncnt
FROM
v
s
e
s
s
i
o
n
s
W
H
E
R
E
s
t
a
t
e
=
′
I
D
L
E
′
A
N
D
t
o
c
h
a
r
(
l
a
s
t
r
e
c
v
t
i
m
e
,
′
y
y
y
y
m
m
d
d
′
)
<
v
e
n
d
d
a
t
e
;
29
、
−
−
查看会话数
O
P
E
N
c
1
F
O
R
S
E
L
E
C
T
s
e
s
s
i
d
F
R
O
M
v
sessions WHERE state = 'IDLE' AND to_char(last_recv_time, 'yyyymmdd') < venddate; 29、--查看会话数 OPEN c1 FOR SELECT sess_id FROM v
sessionsWHEREstate=′IDLE′ANDtochar(lastrecvtime,′yyyymmdd′)<venddate;29、−−查看会话数OPENc1FORSELECTsessidFROMvsessions
WHERE
state = ‘IDLE’
AND to_char(create_time, ‘yyyymmdd’) < venddate;
–打开游标
loop
–循环获取会话ID
IF c1%NOTFOUND THEN EXIT;
END IF;
FETCH c1
INTO
vsessid;
sp_close_session(vsessid);
–删除会话
END loop;
–关闭游标
CLOSE c1;
END;
清除大于1800的空闲会话
DECLARE
vsessid varchar(50);
venddate varchar(8);
isessioncnt int;
CURSOR c1;
BEGIN
vsessid := ‘’;
SELECT
to_char(sysdate + 1 , ‘yyyymmdd’)
INTO
venddate
FROM
dual;
–获取指定日期
SELECT
count(1)
INTO
isessioncnt
FROM
v
s
e
s
s
i
o
n
s
W
H
E
R
E
s
t
a
t
e
=
′
I
D
L
E
′
A
N
D
t
o
c
h
a
r
(
l
a
s
t
r
e
c
v
t
i
m
e
,
′
y
y
y
y
m
m
d
d
′
)
<
v
e
n
d
d
a
t
e
;
−
−
查看会话数
I
F
i
s
e
s
s
i
o
n
c
n
t
>
1800
T
H
E
N
O
P
E
N
c
1
F
O
R
S
E
L
E
C
T
s
e
s
s
i
d
F
R
O
M
v
sessions WHERE state = 'IDLE' AND to_char(last_recv_time, 'yyyymmdd') < venddate; --查看会话数 IF isessioncnt > 1800 THEN OPEN c1 FOR SELECT sess_id FROM v
sessionsWHEREstate=′IDLE′ANDtochar(lastrecvtime,′yyyymmdd′)<venddate;−−查看会话数IFisessioncnt>1800THENOPENc1FORSELECTsessidFROMvsessions
WHERE
state = ‘IDLE’
AND to_char(create_time, ‘yyyymmdd’) < venddate;
–打开游标
loop
–循环获取会话ID
IF c1%NOTFOUND THEN EXIT;
END IF;
FETCH c1
INTO
vsessid;
sp_close_session(vsessid);
–删除会话
END loop;
CLOSE c1;
–关闭游标
END IF;
END;
缩小TEMP表空间
–缩小TEMP表空间大小为32M
SP_TRUNC_TS_FILE(3,0,32);
清理执行计划缓存
SP_CLEAR_PLAN_CACHE();
查询备份文件类型
SELECT
DECODE(SF_BAK_GET_TYPE(‘/opt/bak/all.bak’),
‘0’,
‘全量’,
‘1’,
‘增量’,
‘2’,
‘B树’);
查询备份文件方式
SELECT
DECODE(SF_BAK_GET_LEVEL(‘/opt/bak/all.bak’),
‘0’,
‘联机备份’,
‘1’,
‘脱机备份’);
查询备份文件时间
SELECT
SF_BAK_GET_TIME(‘/opt/bak/all.bak’);
查询备份文件簇大小
SELECT
SF_BAK_GET_EXTENT_SIZE(‘/opt/bak/all.bak’)|| ‘页’;
查询备份文件页大小
SELECT
SF_BAK_GET_PAGE_SIZE(‘/opt/bak/all.bak’)/ 1024 || ‘K’;
查询备份文件大小写
SELECT
DECODE(SF_BAK_GET_CASE_SENSITIVE(‘/opt/bak/all.bak’),
‘0’,
‘不敏感’,
‘1’,
‘敏感’);
查询备份文件数据库版本
SELECT
SF_BAK_GET_GLOBAL_VERSION(‘/opt/bak/all.bak’);
查询备份文件是否有归档
SELECT
DECODE(SF_BAK_GET_ARCH_FLAG(‘/opt/bak/all.bak’,
‘0’,
‘未归档’,
‘1’,
‘归档’);
查询备份文件是否加密
SELECT
DECODE(SF_BAK_GET_ENCRYPT_TYPE (‘/opt/bak/all.bak’),
‘0’,
‘未加密’,
‘1’,
‘加密’);
查询备份文件是压缩
SELECT
DECODE(SF_BAK_GET_COMPRESSED(‘/opt/bak/all.bak’),
‘0’,
‘未压缩’,
‘1’,
‘压缩’);
监控数据库内存
–创建基础表
CREATE TABLE MEM_POOL(“CONTTIME” TIMESTAMP(0),
“SUM(ORG)” VARCHAR2(20),
“SUM(TOTAL)” VARCHAR2(20));
–插入当前内存信息
INSERT
INTO
MEM_POOL
VALUES (SYSDATE(),
(
SELECT
sum(ORG_SIZE)/ 1024 / 1024
FROM
v
m
e
m
p
o
o
l
)
,
(
S
E
L
E
C
T
s
u
m
(
T
O
T
A
L
S
I
Z
E
)
/
1024
/
1024
F
R
O
M
v
mem_pool), ( SELECT sum(TOTAL_SIZE)/ 1024 / 1024 FROM v
mempool),(SELECTsum(TOTALSIZE)/1024/1024FROMvmem_pool));
COMMIT;
监控指定索引是否使用
CREATE TABLE T1(“C1” CHAR(10)); --创建测试表
CREATE INDEX IDX_T1_C1 ON T1(“C1” ASC); --创建IDX_T1_C1索引
ALTER INDEX SYSDBA.“IDX_T1_C1” MONITORING USAGE; --监控IDX_T1_C1索引
SELECT * FROM T1 WHERE T1.C1 = ‘1’; --使用IDX_T1_C1索引
ALTER INDEX SYSDBA.“IDX_T1_C1” NOMONITORING USAGE; --取消监控INDEX_T1_C1索引
SELECT SCH_NAME AS “所属模式”,TABLE_NAME AS “所属表”,INDEX_NAME AS “索引名称”, MONITORING AS “是否监控”,USED AS “是否使用”,START_MONITORING AS “开始监控时间”,END_MONITORING AS “停止监控时间” FROM V
O
B
J
E
C
T
U
S
A
G
E
;
−
−
查看监控结果监控指定模式下索引是否使用监控
S
Y
S
D
B
A
模式下的索引
S
E
L
E
C
T
′
A
L
T
E
R
I
N
D
E
X
′
∣
∣
O
W
N
E
R
∣
∣
′
.
"
′
∣
∣
O
B
J
E
C
T
N
A
M
E
∣
∣
′
"
′
∣
∣
′
M
O
N
I
T
O
R
I
N
G
U
S
A
G
E
;
′
F
R
O
M
D
B
A
O
B
J
E
C
T
S
W
H
E
R
E
O
B
J
E
C
T
T
Y
P
E
=
′
I
N
D
E
X
′
A
N
D
G
E
N
E
R
A
T
E
D
=
′
N
′
A
N
D
O
W
N
E
R
=
′
S
Y
S
D
B
A
′
;
取消监控
S
Y
S
D
B
A
模式下的索引
S
E
L
E
C
T
′
A
L
T
E
R
I
N
D
E
X
′
∣
∣
O
W
N
E
R
∣
∣
′
.
"
′
∣
∣
O
B
J
E
C
T
N
A
M
E
∣
∣
′
"
′
∣
∣
′
N
O
M
O
N
I
T
O
R
I
N
G
U
S
A
G
E
;
′
F
R
O
M
D
B
A
O
B
J
E
C
T
S
W
H
E
R
E
O
B
J
E
C
T
T
Y
P
E
=
′
I
N
D
E
X
′
A
N
D
G
E
N
E
R
A
T
E
D
=
′
N
′
A
N
D
O
W
N
E
R
=
′
S
Y
S
D
B
A
′
;
查看监控结果
S
E
L
E
C
T
S
C
H
N
A
M
E
A
S
"
所属模式
"
,
T
A
B
L
E
N
A
M
E
A
S
"
所属表
"
,
I
N
D
E
X
N
A
M
E
A
S
"
索引名称
"
,
M
O
N
I
T
O
R
I
N
G
A
S
"
是否监控
"
,
U
S
E
D
A
S
"
是否使用
"
,
S
T
A
R
T
M
O
N
I
T
O
R
I
N
G
A
S
"
开始监控时间
"
,
E
N
D
M
O
N
I
T
O
R
I
N
G
A
S
"
停止监控时间
"
F
R
O
M
V
OBJECT_USAGE; --查看监控结果 监控指定模式下索引是否使用 监控SYSDBA模式下的索引 SELECT 'ALTER INDEX ' || OWNER || '."' || OBJECT_NAME || '" ' || 'MONITORING USAGE;' FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'INDEX' AND GENERATED = 'N' AND OWNER = 'SYSDBA'; 取消监控SYSDBA模式下的索引 SELECT 'ALTER INDEX ' || OWNER || '."' || OBJECT_NAME || '" ' || 'NOMONITORING USAGE;' FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'INDEX' AND GENERATED = 'N' AND OWNER = 'SYSDBA'; 查看监控结果 SELECT SCH_NAME AS "所属模式", TABLE_NAME AS "所属表", INDEX_NAME AS "索引名称", MONITORING AS "是否监控", USED AS "是否使用", START_MONITORING AS "开始监控时间", END_MONITORING AS "停止监控时间" FROM V
OBJECTUSAGE;−−查看监控结果监控指定模式下索引是否使用监控SYSDBA模式下的索引SELECT′ALTERINDEX′∣∣OWNER∣∣′."′∣∣OBJECTNAME∣∣′"′∣∣′MONITORINGUSAGE;′FROMDBAOBJECTSWHEREOBJECTTYPE=′INDEX′ANDGENERATED=′N′ANDOWNER=′SYSDBA′;取消监控SYSDBA模式下的索引SELECT′ALTERINDEX′∣∣OWNER∣∣′."′∣∣OBJECTNAME∣∣′"′∣∣′NOMONITORINGUSAGE;′FROMDBAOBJECTSWHEREOBJECTTYPE=′INDEX′ANDGENERATED=′N′ANDOWNER=′SYSDBA′;查看监控结果SELECTSCHNAMEAS"所属模式",TABLENAMEAS"所属表",INDEXNAMEAS"索引名称",MONITORINGAS"是否监控",USEDAS"是否使用",STARTMONITORINGAS"开始监控时间",ENDMONITORINGAS"停止监控时间"FROMVOBJECT_USAGE;
4760
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
