Common SQL for Oracle---(2) Check tablespace

-----Check tablespace-----

---Check datafile

Select Tablespace_name "Tablespace",

file_name "DataFile",

ROUND (BYTES/(1024x768)) "File_size (KB)"

From Dba_data_files

WHERE tablespace_name like ' &tablespace '

ORDER by DataFile DESC;

---Add datafile

Alter tablespace Ods_100m1a_dts add datafile '/oradata31/odsf10/ods_100m1a_dp196.dbf ' size 2048064k;

---Check tablespace Usage

SELECT DF. Tablespace_name "Tablespace",

100-round (SUM (FS). BYTES) * 100/df. BYTES) "Used_percent (%)",

ROUND (SUM (FS). BYTES)/(1024x768 * 1024x768)) "Free_size (MB)",

ROUND (DF. BYTES/(1024x768 * 1024x768)) "Total_size (MB)"

From Dba_free_space FS,

(SELECT tablespace_name, SUM (BYTES) BYTES

From Dba_data_files

GROUP by Tablespace_name) DF

WHERE FS. Tablespace_name (+) = DF. Tablespace_name

GROUP by DF. Tablespace_name, DF. BYTES

ORDER by 3;

SELECT *

From (SELECT a.tablespace_name tablespacename,

Round (NVL (a.bytes/1024/1024/1024,0), 2) "TotalSize (G)",

Round (NVL (b.bytes/1024/1024/1024,0), 2) "Freesize (G)",

Round (NVL ((a.bytes-b.bytes)/1024/1024/1024,0), 2) "Usedsize (G)",

Round (NVL ((a.bytes-b.bytes)/a.bytes *, 0), 2) usedpercent

From (SELECT tablespace_name, sum (BYTES) BYTES

From Dba_data_files

GROUP by Tablespace_name) A,

(SELECT tablespace_name, sum (BYTES) BYTES, Max (BYTES) largest

From Dba_free_space

GROUP by Tablespace_name) b

WHERE A.tablespace_name = B.tablespace_name

ORDER by A.tablespace_name)

UNION

SELECT D.tablespace_name Tablespacename,

Round (NVL (a.bytes/1024/1024/1024, 0), 2) "TotalSize (G)",

Round (NVL (a.bytes/1024/1024/1024, 0), 2)-round (NVL (t.bytes, 0)/1024/1024/1024,2) "Freesize (G)",

Round (NVL (t.bytes, 0)/1024/1024/1024,2) Usedsize,

Round (NVL (T.bytes/a.bytes *, 0), 2) "Usedsize (G)"

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) bytes

From V$temp_extent_pool

GROUP by Tablespace_name) t

WHERE D.tablespace_name = a.tablespace_name (+)

and D.tablespace_name = T.tablespace_name (+)

and d.extent_management like ' LOCAL '

and d.contents like ' temporary ';

---check undo usage

SELECT Undo_type,type_size_mb,round (100*ratio_to_report (TYPE_SIZE_MB) over (), 2) type_pct

From (SELECT STATUS undo_type,round (SUM (BYTES)/1024/1024) TYPE_SIZE_MB

From Dba_undo_extents

GROUP by STATUS

UNION All

SELECT ' free ', ROUND (SUM (DFS. BYTES)/1024/1024)

From Dba_free_space dfs,dba_tablespaces TBS

WHERE DFS. Tablespace_name=tbs. Tablespace_name

and TBS. contents= ' UNDO ');

----Undo Usage by session

SELECT s.sid,s.serial#,s.sql_id,s.username,s.osuser,s.machine,s.program,s.event,t.start_date, (SYSDATE-T. start_date) *24*60*60 Undo_seconds,

t.used_ublk/1024/1024* (SELECT block_size from dba_tablespaces WHERE tablespace_name=r.tablespace_name) UNDO_SIZE_MB, T.status

From Dba_rollback_segs r,v$transaction t,v$session S

WHERE R.segment_id=t.xidusn

and T.ADDR=S.TADDR

ORDER by UNDO_SIZE_MB DESC;

---Check temp tablespace by session

SELECT SE. USERNAME,

Su. BLOCKS * 8/1024 "M",

Su. SQL_ID,

Su. Sqlhash,

Sa. Sql_text,

Sa. Sql_fulltext,

Se. Sid

From V$sort_usage SU, V$sqlarea SA, V$session SE

WHERE SU. sql_id = SA. sql_id

and SE. serial# = su. Session_num

ORDER by 2 DESC;

SELECT * from Dba_temp_files;

This article from "on the Road" blog, declined reprint!

Common SQL for Oracle---(2) Check tablespace