system表空间100%,导致数据库无法访问
系统表空间正常情况下只存放了数据字典之类的东西,所以占用的空间一般在500M以下。如果你的系统表空间占用比较多的空间,可能有以下几方面的原因:
1)没有为用户明确指定默认表空间,导致system系统表空间作为用户默认表空间
2)开启了审计,请检查此表的大小AUD$
你可以运行以下查询来检查一下系统表空间哪些表比较大:
SQL> select * from (select SEGMENT_NAME,sum(bytes)/1024/1024 sx from dba_segments
where tablespace_name=‘SYSTEM’ group by segment_name)
where sx>100 order by sx desc;
查看该表纪录数
select count(*) sum from AUD$;
解决方法大概可分为以下几种:
一、为system表空间另外新增一个数据文件。
alter tablespace system add datafile ‘D:\oracleXE\oradata\XE\system_01.dbf’ resize 1024M;
二、更改system表空间的数据文件SYSTEM.dbf分配空间。
alter database datafile ‘D:\oracleXE\oradata\XE\system_01.dbf’ autoextend on;
alter database datafile ‘D:\oracleXE\oradata\XE\system_01.dbf’ resize 1024M;
三、truncate掉AUD
表
并
关
闭
审
计
功
能
(
我
是
使
用
的
这
种
,
效
果
立
竿
见
影
,
不
过
如
果
不
关
闭
此
功
能
,
需
要
定
期
清
理
此
表
)
:
S
Q
L
>
a
l
t
e
r
s
y
s
t
e
m
s
e
t
a
u
d
i
t
t
r
a
i
l
=
n
o
n
e
s
c
o
p
e
=
s
p
f
i
l
e
;
S
Q
L
>
s
h
o
w
d
o
w
n
i
m
m
e
d
i
a
t
e
;
S
Q
L
>
s
t
a
r
t
u
p
;
四
、
将
A
U
D
表并关闭审计功能(我是使用的这种,效果立竿见影,不过如果不关闭此功能, 需要定期清理此表): SQL> alter system set audit_trail=none scope=spfile; SQL>showdown immediate; SQL>startup; 四、将AUD
表并关闭审计功能(我是使用的这种,效果立竿见影,不过如果不关闭此功能,需要定期清理此表):SQL>altersystemsetaudittrail=nonescope=spfile;SQL>showdownimmediate;SQL>startup;四、将AUD默认表空间由system移出。
如何用sql 语句查询出oracle的cpu使用率
select * from (select sql_text,sql_id,cpu_time from v
s
q
l
o
r
d
e
r
b
y
c
p
u
t
i
m
e
d
e
s
c
)
w
h
e
r
e
r
o
w
n
u
m
<
=
10
o
r
d
e
r
b
y
r
o
w
n
u
m
a
s
c
;
s
e
l
e
c
t
∗
f
r
o
m
(
s
e
l
e
c
t
s
q
l
t
e
x
t
,
s
q
l
i
d
,
c
p
u
t
i
m
e
f
r
o
m
v
sql order by cpu_time desc) where rownum<=10 order by rownum asc ; select * from (select sql_text,sql_id,cpu_time from v
sqlorderbycputimedesc)whererownum<=10orderbyrownumasc;select∗from(selectsqltext,sqlid,cputimefromvsqlarea order by cpu_time desc) where rownum<=10 order by rownum asc ;
这2个语句效果基本一样,一个从v
s
q
l
视
图
查
询
一
个
从
v
sql视图查询一个从v
sql视图查询一个从vsqlarea视图查询。
列出使用频率最高的5个查询:
select sql_text,executions
from (select sql_text,executions, rank() over
(order by executions desc) exec_rank
from v$sql)
where exec_rank <=5;
消耗磁盘读取最多的sql top5:
select disk_reads,sql_text from (select sql_text,disk_reads,
dense_rank() over (order by disk_reads desc) disk_reads_rank
from v$sql) where disk_reads_rank <=5;
找出需要大量缓冲读取(逻辑读)操作的查询:
select buffer_gets,sql_text from (select sql_text,buffer_gets,
dense_rank() over (order by buffer_gets desc) buffer_gets_rank
from v
s
q
l
)
w
h
e
r
e
b
u
f
f
e
r
g
e
t
s
r
a
n
k
<
=
5
;
1
,
使
用
L
i
n
u
x
系
统
t
o
p
命
令
查
出
占
用
c
p
u
最
高
的
进
程
2
,
在
s
q
l
p
l
u
s
中
执
行
如
下
s
q
l
:
S
E
L
E
C
T
s
q
l
t
e
x
t
F
R
O
M
v
sql) where buffer_gets_rank<=5; 1,使用Linux系统top命令查出占用cpu最高的进程 2,在sqlplus中执行如下sql: SELECT sql_text FROM v
sql)wherebuffergetsrank<=5;1,使用Linux系统top命令查出占用cpu最高的进程2,在sqlplus中执行如下sql:SELECTsqltextFROMvsqltext a
WHERE (a.hash_value, a.address) IN
(SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v
s
e
s
s
i
o
n
b
W
H
E
R
E
b
.
p
a
d
d
r
=
(
S
E
L
E
C
T
a
d
d
r
F
R
O
M
v
session b WHERE b.paddr = (SELECT addr FROM v
sessionbWHEREb.paddr=(SELECTaddrFROMvprocess c WHERE c.spid = ‘&pid’))
ORDER BY piece ASC
其中&pid 是使用top 查看系统中进程占用CPU极高的PID
找到SQL语句进行相应的调整优化
3,分析找到的sql语句,如查看sql执行计划。
######查询结果集输出到一个文件里面
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool d:\slow_info.txt
select sql_text,executions
from (select sql_text,executions,
rank() over
(order by executions desc) exec_rank
from vKaTeX parse error: Expected 'EOF', got '#' at position 91: …ect sid, serial#̲, sql_text, exe…sql join v
s
e
s
s
i
o
n
o
n
v
session on v
sessiononvsql.sql_id = v$session.sql_id
where cpu_time > 20000;
##########查出问题的SQL执行杀进程操作:
执行以下语句杀死
alter system kill session ‘sid,serial#’;