Oracle数据库常用脚本（二）

selecta.tablespace_name,case when g.auto_free <0 then round((f.free_MB/a.total_MB)*100) else round(((f.free_MB+g.auto_free)/b.r_total_mb)*100) end "Free"from(select tablespace_name, sum(bytes/(1024*1024)) total_MB from dba_data_files group by tablespace_name) a,(select tablespace_name, round(sum(bytes/(1024*1024))) free_MB from dba_free_space group by tablespace_name) f,(select tablespace_name,trunc(sum(maxbytes)/1024/1024,2) as r_total_mb from dba_data_files group by tablespace_name) b,(select sum(free_MB) auto_free,tablespace_name from (select tablespace_name,trunc(maxbytes / 1024 / 1024 , 2) as MAX_MB,trunc((maxbytes-bytes)/1024/1024,2) FREE_MB from dba_data_files) a group by tablespace_name) gWHEREa.tablespace_name = f.tablespace_name(+)andg.tablespace_name = a.tablespace_nameandb.tablespace_name = a.tablespace_name;

select a.TABLESPACE_NAME,ROUND((1 - (a.Free_MB_1+ nvl(b.FREE_MB_2,0)) / a.total_mb) * 100, 2) Used_Prcfrom (select TABLESPACE_NAME,ROUND(sum(casewhen autoextensible = 'NO' thenBYTESwhen autoextensible = 'YES' thenMAXBYTESend) / 1024 / 1024,2) TOTAL_MB,ROUND(sum(casewhen MAXBYTES - BYTES >= 0 thenMAXBYTES - BYTESwhen MAXBYTES - BYTES < 0 then0end) / 1024 / 1024,2) Free_MB_1from dba_data_filesgroup by TABLESPACE_NAME) ainner join (SELECT dfs.TABLESPACE_NAME,SUM(dfs.bytes / 1024 / 1024) FREE_MB_2FROM dba_free_space dfsGROUP BY dfs.TABLESPACE_NAME) bon a.TABLESPACE_NAME = b.TABLESPACE_NAME(+);

set line 300col tablespace_name for a20col file_name for a45select tablespace_name,file_name,file_id,status,trunc(bytes / 1024 / 1024 / 1024, 2) as FILE_GB,autoextensible,trunc(maxbytes / 1024 / 1024 / 1024, 2) as MAX_GBfrom dba_data_filesorder by file_id;

select t.status,sum(t.blocks)*8/1024||'M' size_Mfrom dba_undo_extents tgroup by t.status;

select * from v$rollname;

set line 150col tablespace_name for a10set pagesize 100select owner, tablespace_name, segment_id, segment_name, statusfrom dba_rollback_segs order by 2,3;

set line 300col tablespace_name for a20col file_name for a40select tablespace_name,file_name,file_id,status,trunc(bytes / 1024 / 1024 / 1024, 2) as FILE_GB,autoextensible,trunc(maxbytes / 1024 / 1024 / 1024, 2) as MAX_GBfrom dba_temp_filesorder by file_id;

SET PAGESIZE 400SET LINES 300COL D.TABLESPACE_NAME FORMAT A15COL D.TOT_GROOTTE_MB FORMAT A10COL TS-PER FORMAT A15SELECT d.tablespace_name "Name",TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999') "HWM (M)",TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)",TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"FROM sys.dba_tablespaces d,(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,(select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) tWHERE d.tablespace_name = a.tablespace_name(+)AND d.tablespace_name = t.tablespace_name(+)AND d.extent_management like 'LOCAL'AND d.contents like 'TEMPORARY';

col name for a50select status,name from v$controlfile;

SELECT * FROM v$CONTROLFILE_RECORD_SECTION;

set line 300col member for a50select a.group#,THREAD#,b.member,a.members,a.status,a.sequence#,bytes / 1024 / 1024 as file_mbfrom v$log a, v$logfile bwhere a.group# = b.group#order by 2,1;