Oracle 常用指令碼整理

標籤：查看   cascade   nbsp   資料   容量   to_char   進程   like   text   

一、統計表所佔空間大小（表容量）

/*一般情況下，表所佔空間分為三部分：表資料、表索引、表blob欄位資料*/
--1 統計含(blob欄位)的單表所佔用的空間

SELECT TABLE_NAME, SUM(SIZE_MB)
  FROM (SELECT A.SEGMENT_NAME AS TABLE_NAME,
               SUM(BYTES) / 1024 / 1024 SIZE_MB
          FROM USER_SEGMENTS A
         GROUP BY A.SEGMENT_NAME  --文本資訊容量
        UNION ALL
        SELECT A.TABLE_NAME, SUM(B.BYTES) / 1024 / 1024 SIZE_MB
          FROM USER_LOBS A, USER_SEGMENTS B
         WHERE A.SEGMENT_NAME = B.SEGMENT_NAME
         GROUP BY A.TABLE_NAME)   --lob欄位容量
 WHERE TABLE_NAME = ‘&table_name‘
 GROUP BY TABLE_NAME
 ORDER BY 2 DESC;

--2 統計含(blob欄位)的表所佔用的空間

SELECT TABLE_NAME, SUM(SIZE_MB)
  FROM (SELECT A.SEGMENT_NAME AS TABLE_NAME,
               SUM(BYTES) / 1024 / 1024 SIZE_MB
          FROM USER_SEGMENTS A
         GROUP BY A.SEGMENT_NAME  --文本資訊容量
        UNION ALL
        SELECT A.TABLE_NAME, SUM(B.BYTES) / 1024 / 1024 SIZE_MB
          FROM USER_LOBS A, USER_SEGMENTS B
         WHERE A.SEGMENT_NAME = B.SEGMENT_NAME
         GROUP BY A.TABLE_NAME)   --lob欄位容量
 GROUP BY TABLE_NAME
 ORDER BY 2 DESC;
 
--3 統計不含lob欄位的表所佔空間

SELECT A.SEGMENT_NAME AS TABLE_NAME,
       SUM(BYTES) / 1024 / 1024 SIZE_MB
FROM USER_SEGMENTS A
WHERE A.segment_type = ‘TABLE‘
GROUP BY A.SEGMENT_NAME ;

二、統計資料檔案使用率
select b.file_name 物理檔案名稱,
          b.tablespace_name 資料表空間,
          b.AUTOEXTENSIBLE,
          b.MAXBYTES,
          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
 --and b.tablespace_name in(‘TBS_CRJ_SQ‘,‘TBS_CRJ_RZ_INDEX‘)
 group by b.tablespace_name, b.AUTOEXTENSIBLE,b.MAXBYTES, b.file_name, b.bytes
 order by b.tablespace_name

三、查看錶空間是否已滿
select
  a.tablespace_name,trunc(sum(a.bytes)/1024/1024) total,
  trunc(sum(a.bytes)/1024/1024 - sum(b.bytes)/1024/1024) used,
  trunc(sum(b.bytes)/1024/1024) free,
  to_char(trunc((sum(a.bytes)/1024/1024 - sum(b.bytes)/1024/1024)/(sum(a.bytes)/1024/1024),4)*100)||‘%‘ pused,
  to_char(trunc((sum(b.bytes)/1024/1024)/(sum(a.bytes)/1024/1024),4)*100)||‘%‘ pfree
from
  (select sum(bytes) bytes,tablespace_name from dba_data_files group by tablespace_name) a,
  (select sum(bytes) bytes,tablespace_name from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name(+)
group by a.tablespace_name
order by to_number(rtrim(pused,‘%‘)) desc;

四、殺進程

select a.MACHINE,a.PROGRAM,a.SID,a.SERIAL#,a.STATUS,b.SQL_TEXT,
‘alter system kill session‘||‘‘‘‘||a.SID||‘,‘||a.SERIAL#||‘‘‘immediate ;‘kill_sql
from v$session a , v$sql b
where a.USERNAME is not null
and a.SQL_ID=b.SQL_ID;

五、SCN查詢
select version,
       to_char(SYSDATE, ‘YYYY/MM/DD HH24:MI:SS‘) DATE_TIME,
       ((((((to_number(to_char(sysdate,‘YYYY‘))-1988)*12*31*24*60*60)+
       ((to_number(to_char(sysdate, ‘MM‘)) - 1) * 31 * 24 * 60 * 60) +
       (((to_number(to_char(sysdate, ‘DD‘)) - 1)) * 24 * 60 * 60) +
       (to_number(to_char(sysdate, ‘HH24‘)) * 60 * 60) +
       (to_number(to_char(sysdate, ‘MI‘)) * 60) +
       (to_number(to_char(sysdate, ‘SS‘)))) * (16 * 1024)) -
       dbms_flashback.get_system_change_number) /
       (16 * 1024 * 60 * 60 * 24)) headroom
  from v$instance;

--SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND x.ksppinm LIKE ‘%_external_scn_rejection_threshold_hours%‘;
--alter system set  "_external_scn_rejection_threshold_hours"=8;

六、資料庫使用者

--1、使用者掛起（鎖定使用者）
alter user crjapp  account lock;

--2、使用者解鎖
alter user crjapp  account unlock;

--3、刪除使用者
drop user user_name cascade ;

 

Oracle 常用指令碼整理