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;