oracle DBA 札记

--dba 博客
蒙昭良

http://space.itpub.net/12778571



（一）处理等待sql
--1
select sid,username,blocking_session,blocking_session_status,blocking_instance,event,wait_time from

v$session where username='MZ_CRM'
--2
select sql_text from v$session a , v$sqltext_with_newlines b where decode

(a.sql_hash_value,0,prev_hash_value,sql_hash_value)=b.hash_value
and a.sid=&sid order by piece
--3
select t2.username,t2.sid,t2.serial#,t2.logon_time  from v$locked_object t1,v$session t2 
where t1.session_id=t2.sid order by t2.logon_time
--4
alter session kill '&sid,&serial#';


（二）解决数据文件达到最大值
--1
select tablespace_name 表空间,file_name 数据文件名,round(bytes/1024/1024/1024) 实际大小GB,round

(maxbytes/1024/1024/1024) 最大值GB
from dba_data_files
--2
select * from dba_tablespace_usage_metrics
--3 添加数据文件
alter tablespace users add datafile '/u01/LSDBNEW/LSDBNEW/datafile/o1_mf_macc_100ycfby1_.dbf'
size 20G autoextend on maxsize unlimited;
--4 修改最大值为不限制
alter database datafile '/u01/LSDBNEW/LSDBNEW/datafile/o1_mf_macc_100ycfby1_.dbf'
autoextend on maxsize unlimited;



（三）临时表空间过大导致磁盘空间不足
--1 查询当前默认临时空间
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
--2 查看temp是否达到最大值
select file_name,tablespace_name,bytes/1024/1024/1024,autoextensible from dba_temp_files
select * from dba_tablespace_usage_metrics
--3 linux 查看磁盘空间情况
$df -lh
--4 新建临时表空间
create temporary tablespace temp02
tempfile '/u01/LSDBNEW/LSDBNEW/datafile/temp02.dbf'
size 20G autoextend on maxsize unlimited;
--5 替换原临时空间 temp
alter database default temporary tablespace temp02;
--6 查询是否已经更改过来
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
--7 删除temp 之前，先kill 掉运行在temp 中的sql ，这样的sql 大多为排序语句 如：
select se.username,se.sid,se.serial#,su.extents,su.blocks*to_number(rtrim(p.value)) as space,
       tablespace,segtype,sql_text
       from v$sort_usage su , v$parameter p , v$session se, v$sql s
where p.name='db_block_size'
and su.session_addr=se.saddr
and s.hash_value=su.sqlhash
and s.address=su.SQLADDR
order by se.username,se.sid
--8 kill
alter system kill session 'sid,serial#';
--9 删除 temp
drop tablespace temp including contens and datafiles;
-- 提示这样操作不需要重启就能解决临时表空间写满的问题



（四）还原表空间过大导致磁盘空间不足
--原因
a.有较大的事物量让oracle Undo 自动扩展，产生过度占用磁盘空间的情况
b.有较大的事物没有收缩或者没有提交所致
c.还原空间用户存放数据库历史数据 ， 当执行 DML 是执行的旧数据会写入还原空间 10已经丢弃回滚段，完全使用还

原表空间
--1 查磁盘空间
$df -lh
--2 查看所有表空间占用率
select * from dba_tablespace_usage_metrics

select a.tablespace_name,
       round((a.maxbytes/1024/1024),2) "sum MB",
       round((a.bytes/1024/1024),2) "datafile MB",
       round(((a.bytes-b.bytes)/1024/1024),2) "used MB",
       round(((a.maxbytes-a.bytes+b.bytes)/1024/1024),2) "free MB",
       round(((a.bytes-b.bytes)/a.maxbytes)*100,2) "percent_used"
       from (select tablespace_name,sum(bytes) bytes,sum(maxbytes) maxbytes
                    from dba_data_files where maxbytes!=0
                         group by tablespace_name) a,
            (select tab