oracle常用维护语句

--查看静态空间--
select a.tablespace_name,
used / 1024 / 1024 || 'M' used,
round(100 - b.free / used * 100, 2) || '%' used_pct,
round(free / 1024 / 1024, 2) || 'M' free,
round(max_size / 1024 / 1024) || 'M' max_size,
round(max_size / 1024 / 1024) - used / 1024 / 1024 || 'M' MAX_SIZE__TOTAL
from (select tablespace_name,
sum(bytes) used,
sum(case
when maxbytes = 0 then
bytes
else
maxbytes
end) max_size
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) free
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name;

--空间监控（注意回滚段是循环利用的，仅仅看已占用空间没有实际意义）
select a.a1 表空间名称,
c.c2 类型,
c.c3 区管理,
b.b2 / 1024 / 1024 表空间大小M,
(b.b2 - a.a2) / 1024 / 1024 已使用M,
a.a2 / 1024 / 1024 剩余M,
substr((b.b2 - a.a2) / b.b2 * 100, 1, 5) 利用率
from (select tablespace_name a1, sum(nvl(bytes, 0)) a2
from dba_free_space
group by tablespace_name) a,
(select tablespace_name b1, sum(bytes) b2
from dba_data_files
group by tablespace_name) b,
(select tablespace_name c1, contents c2, extent_management c3
from dba_tablespaces) c
where a.a1 = b.b1
and c.c1 = b.b1;

--当前用户表、索引等占用空间情况，正式机要用bp来跑
Select t.segment_type,
t.tablespace_name,
t.Segment_Name,
Sum(t.bytes) / 1024 / 1024
From User_Extents t
Group By t.segment_type, t.tablespace_name, t.Segment_Name;

--数据库文件占用情况
select b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes / 1024 / 1024 大小M,
(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 已使用M,
substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) 利用率
from dba_free_space a, dba_data_files b
where a.file_id = b.file_id
group by b.tablespace_name, b.file_name, b.bytes
order by b.tablespace_name;

--临时空间
Select file#, status, name from V$tempfile;

--事务正在使用的回滚段监控（貌似结果偏小啊，很大的事务只用了几个kb？）
SELECT r.name rbs,
nvl(s.username, 'None') oracle_user,
s.osuser client_user,
p.username unix_user,
to_char(s.sid) || ',' || to_char(s.serial#) as sid_serial,
p.spid unix_pid,
-- TO_CHAR(s.logon_time, 'mm/dd/yy hh24:mi:ss') as login_time,
-- TO_CHAR(sysdate - (s.last_call_et) / 86400,'mm/dd/yy　　hh24:mi:ss') as last_txn,
t.used_ublk * TO_NUMBER(x.value) / 1024 as undo_kb
FROM v$process p,
　　v$rollname r,
　　v$session s,
　　v$transaction t,
　　v$parameter x
WHERE s.taddr = t.addr
AND s.paddr = p.addr(+)
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size'
ORDER BY r.name;

--我们在操作数据库的时候，有时候会由于操作不当引起数据库表被锁定，这么我们经常不知所措，
--不知怎么给这些表解锁，在pl/sql Developer工具的的菜单“tools”里面的“sessions”可以查询现在存在的会话，
--但是我们很难找到那个会话被锁定了，想找到所以被锁的会话就更难了，下面这叫查询语句可以查询出所以被锁的会话。如下：

SELECT sn.username,
m.SID,
sn.SERIAL#,
m.TYPE,
DECODE(m.lmode,
0,
'None',
1,
'Null',
2,
'Row Share',
3,
'Row Excl.',
4,
'Share',
5,
'S/Row Excl.',
6,
'Exclusive',
lmode,
LTRIM(TO_CHAR(lmode, '990'))) lmode,
DECODE(m.request,
0,
'None',
1,
'Null',
2,
'Row Share',
3,
'Row Excl.',
4,
'Share',
5,
'S/Row Excl.',
6,
'Exclusive',
request,
LTRIM(TO_CHAR(m.request, '990'))) request,
m.id1,
m.id2
FROM v$session sn, v$lock m
WHERE (sn.SID = m.SID AND m.request != 0) --存在锁请求，即被阻塞
OR (sn.SID = m.SID --不存在锁请求，但是锁定的对象被其他会话请求锁定
AND m.request = 0 AND lmode != 4 AND
(id1, id2) IN (SELECT s.id1, s.id2
FROM v$lock s
WHERE request != 0
AND s.id1 = m.id1
AND s.id2 = m.id2))
ORDER BY id1, id2, m.request;

--通过以上查询知道了sid和 SERIAL#就可以开杀了
alter system kill session 'sid,SERIAL#';

--===========================================

--创建表空间及添加数据文件

--1、创建表空间

create tablespace tablespacename datafile ‘ / u02 / oradata / $ORACLE_SID / datafilename’
size128M autoextend on next 2048K maxsize2048M,
‘u02 / oradata / $ORACLE_SID / datafilename’ size128M
autoextend on next 2048K maxsize2048M
logging extent management local autoallocate segment space management auto flashback on;

--2、添加数据文件

alter tablespace tablespacename add datafile ‘u02 / oradata / $ORACLE_SID / datafilename’
size128M autoextend on next 2048K maxsize2048M;