Tablespace Fast Reference

====================
Get Tablespace Usage
====================

SET LINESIZE 120
SET PAGESIZE 200
SPOOL tablespace.txt

SELECT TABLESPACE_NAME, 
 (MAX(MAX_SPACE)-MAX(USED_SPACE)  )AS DBA_FREE_SPACE_MB, 
 MAX(USED_SPACE) AS USED_SPACE_MB, 
 MAX(MAX_SPACE) AS MAX_SPACE_MB, 
 ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100) as FREE_PCT, 
 CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100)<15)  THEN 'Y' ELSE 'N' END AS ADD_IND
FROM ( 
SELECT tablespace_name,  
  ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE, 
  0 AS MAX_SPACE, 
  0 AS USED_SPACE   
 FROM DBA_FREE_SPACE
 WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'  OR tablespace_name = 'SYSAUX' 
 GROUP BY tablespace_name  
 UNION ALL 
 SELECT tablespace_name, 
  0 AS FREE_SPACE, 
  ROUND( SUM(CASE WHEN (bytes>maxbytes)  THEN bytes ELSE maxbytes END)/1024/1024) AS MAX_SPACE, 
     0 AS USED_SPACE 
 FROM DBA_DATA_FILES
 WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM' OR tablespace_name = 'SYSAUX'
 GROUP BY tablespace_name 
 UNION ALL 
 SELECT tablespace_name, 
     0 AS FREE_SPACE, 
     0 AS MAX_SPACE, 
     ROUND(SUM(bytes/1024/1024)) AS USED_SPACE 
  FROM DBA_SEGMENTS
 WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM' OR tablespace_name = 'SYSAUX'
GROUP BY tablespace_name 
) 
GROUP BY tablespace_name;
spool off


TABLESPACE_NAME     DBA_FREE_SPACE_MB USED_SPACE_MB MAX_SPACE_MB   FREE_PCT ADD_IND
------------------- ----------------- ------------- ------------ ---------- -------
DWH_INDEX                      179149         96851       276000         65 N
IGT_TABLE                      144946          5054       150000         97 N
SYSTEM                           1691           309         2000         85 N
IGT_INDEX                       89590           410        90000        100 N
DWH_TABLE                         519        101481       102000          1 Y


====================
Get Segments Usage
====================

SET LINESIZE 120
SET PAGESIZE 200
COL tablespace_name FOR A30
COL owner FOR A30
COL segment_name FOR A30
COL USED_MB FOR 9999999999999


SELECT owner, tablespace_name, segment_name, USED_MB 
   FROM ( 
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%TABLE%' 
  GROUP BY owner, tablespace_name,segment_name 
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC 
) WHERE ROWNUM < 11;


SELECT owner, tablespace_name, segment_name, USED_MB 
   FROM ( 
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%INDEX%' 
  GROUP BY owner, tablespace_name,segment_name 
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC 
) WHERE ROWNUM < 11;


SELECT owner, tablespace_name, segment_name, USED_MB 
   FROM ( 
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%SYSAUX%' 
  GROUP BY owner, tablespace_name,segment_name 
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC 
) WHERE ROWNUM < 11;

SELECT owner, tablespace_name, segment_name, USED_MB 
   FROM ( 
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%SYSTEM%' 
  GROUP BY owner, tablespace_name,segment_name 
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC 
) WHERE ROWNUM < 31;

Example of output:

OWNER                          TABLESPACE_NAME SEGMENT_NAME              USED_MB
------------------------------ --------------- ------------------------- ----------
RUS_MTSQQ_SPARX_REPORTS        DWH_TABLE       FACT_ROAMER_CAMPAIGNS          71428
RUS_MTSQQ_SPARX_REPORTS        DWH_TABLE       FACT_ROAMER_SCENARIO           12890
UKR_MTSQQ_SPARX_REPORTS        DWH_TABLE       FACT_ROAMER_CAMPAIGNS           9872
ARM_MTSQQ_SPARX_REPORTS        DWH_TABLE       FACT_ROAMER_CAMPAIGNS           2764
RUS_MTSQQ_SPARX_REPORTS        IGT_TABLE       SFI_CUSTOMER_PROFILE            2624
UKR_MTSQQ_SPARX_REPORTS        DWH_TABLE       FACT_ROAMER_SCENARIO            1880
RUS_MTSQQ_SPARX_REPORTS        IGT_TABLE       SFI_CUSTOMER_PROFILE_PK         1849
ARM_MTSQQ_SPARX_REPORTS        DWH_TABLE       FACT_ROAMER_SCENARIO             792
UKR_MTSQQ_SPARX_REPORTS        DWH_TABLE       SFI_CUSTOMER_OPTIONS             768
RUS_MTSQQ_SPARX_REPORTS        IGT_TABLE       SFI_CUSTOMER_OPTION_20131010     363

10 rows selected.

====================
Get Datafiles Usage
====================

SET LINESIZE 140
SET PAGESIZE 200
COL tablespace_name FOR A30
COL file_name FOR A60
COL Mb FOR 9999999999999
COL MAX_MB FOR 9999999999999


SELECT tablespace_name, file_name, ROUND(bytes/1024/1024) AS Mb, ROUND(maxbytes/1024/1024) AS MAX_MB
  FROM DBA_DATA_FILES
ORDER BY tablespace_name, file_name;  


TABLESPACE_NAME   FILE_NAME                                           MB     MAX_MB
----------------- -------------------------------------------- --------- ----------
DWH_INDEX         /oracle_db/db1/db_igt/ora_dwh_index_01.dbf         100       6000
DWH_TABLE         /oracle_db/db1/db_igt/ora_dwh_table_01.dbf         200      12000
GIN               /oracle_db/db1/db_igt/ora_gin_01.dbf               500       6000
GININDEX          /oracle_db/db1/db_igt/ora_ginindex_01.dbf          200       3000
IGT_INDEX         /oracle_db/db1/db_igt/ora_igt_index_01.dbf        6400       8000
IGT_TABLE         /oracle_db/db1/db_igt/ora_igt_table_01.dbf       14000      14000
SYSAUX            /oracle_db/db1/db_igt/ora_sysaux_01.dbf            950       1000
SYSTEM            /oracle_db/db1/db_igt/ora_system_01.dbf           1000       1000
UNDOTBS           /oracle_db/db1/db_igt/ora_undotbs_01.dbf          1600       2000
WORKAREA          /oracle_db/db1/db_igt/ora_workarea_01.dbf         1000       1000

====================
Get Datafiles Usage
====================
Resize Datafile
ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_system_01.dbf' AUTOEXTEND ON MAXSIZE 2000M;

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_system_01.dbf' RESIZE 2000M;

Add Datafile
ALTER TABLESPACE TABLE_IGT ADD DATAFILE '/oracle_db/db1/db_igt/ora_table_02.dbf' SIZE 1000M AUTOEXTEND ON MAXSIZE 10000M;

Add Tablespace

ADD TABLESPACE TABLE_IGT_02 DATAFILE '/oracle_db/db1/db_igt/ora_table_02.dbf' SIZE 1000M AUTOEXTEND ON MAXSIZE 10000M;