SQL for Space usage for Tablespaces and Segments

================================
SQLs
================================

View per Tablespace. Free space, Used space, Total space.
SET LINESIZE 120
SET PAGESIZE 200
spool tbs_report.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_MORE_SPACE 
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'  
 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' 
 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' 
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_SPACE
-------------------- ----------------- ------------- ------------ ---------- ---------
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

View Segments per Tablespace.

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

spool segments_report.txt 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 NOT LIKE '%TABLE%' 
     AND tablespace_name NOT LIKE '%INDEX%' 
  GROUP BY owner, tablespace_name,segment_name 
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC 

) WHERE ROWNUM < 21;

spool off

OWNER         TABLESPACE_NAME      SEGMENT_NAME                          USED_MB
------------- -------------------- ------------------------------ --------------
USER_A        DWH_TABLE            FACT_ROAMER_CAMPAIGNS                   71428
USER_C        DWH_TABLE            FACT_ROAMER_SCENARIO                    12890
USER_A        DWH_TABLE            FACT_ROAMER_CAMPAIGNS                    9872
USER_C        DWH_TABLE            FACT_ROAMER_CAMPAIGNS                    2764
USER_A        IGT_TABLE            SFI_CUSTOMER_PROFILE                     2624
USER_E        DWH_TABLE            FACT_ROAMER_SCENARIO                     1880
USER_B        IGT_TABLE            SFI_CUSTOMER_PROFILE_PK                  1849
USER_A        DWH_TABLE            FACT_ROAMER_SCENARIO                      792
USER_A        DWH_TABLE            SFI_CUSTOMER_OPTIONS                      768
USER_A        IGT_TABLE            SFI_CUSTOMER_OPTION_20131010              363

10 rows selected.


View Datafiles Usage.
COL FILE_NAME FOR A50

SELECT FILE_NAME, TABLESPACE_NAME, ROUND(BYTES/1024/1024) AS Mb, ROUND(MAXBYTES/1024/1024) AS Max_Mb , ROUND(USER_BYTES/1024/1024) USER_Mb
FROM DBA_DATA_FILES


FILE_NAME                                   TABLESPACE_NAME        MB   MAX_MB  USER_MB
------------------------------------------- ---------------- -------- -------- --------
/oracle_db/db1/db_igt/ora_dwh_table_01.dbf  DWH_TABLE           12000    12000    11999
/oracle_db/db1/db_igt/ora_dwh_table_02.dbf  DWH_TABLE           30000    30000    29999
/oracle_db/db1/db_igt/ora_dwh_table_03.dbf  DWH_TABLE           30000    30000    29999
/oracle_db/db1/db_igt/ora_dwh_table_04.dbf  DWH_TABLE           30000    30000    29999



ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_dwh_table_01.dbf' RESIZE 30000M;


View Tempfiles Usage.

SET LINESIZE 120
SET PAGESIZE 120
COL file_name FOR A50

SELECT file_name, ROUND(BYTES/1024/1024) AS mb, ROUND(MAXBYTES/1024/1024) AS max_mb, ROUND(USER_BYTES/1024/1024) AS user_mb 
FROM DBA_TEMP_FILES;

FILE_NAME                                                  MB     MAX_MB    USER_MB
-------------------------------------------------- ---------- ---------- ----------
/oracle_db/db1/db_igt/ora_temporary_01.dbf              30000      20000      29999

/oracle_db/db1/db_igt/ora_temporary_02.dbf               1024      10240       1023


================================
Solutions
================================

1. Increase Tablespace

A. Resize Datafile

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_table_01.dbf' RESIZE 30720M;

B. Add Datafile

ALTER TABLESPACE TABLE_IGT ADD DATAFILE '/oracle_db/db1/db_igt/ora_table_02.dbf' SIZE 1024M AUTOEXTEND ON MAXSIZE 10240M;

2. Increase TEMP Tablespace

A. Resize Datafile

--Resize to 30Gb

ALTER DATABASE TEMPFILE '/oracle_db/db1/db_igt/ora_temporary_01.dbf' RESIZE 30720M;

B. Add Datafile

--Add 1Gb

ALTER TABLESPACE TEMPORARY ADD TEMPFILE '/oracle_db/db1/db_igt/ora_temporary_02.dbf' SIZE 1024M AUTOEXTEND ON MAXSIZE 10240M;

3. Add Tablespace

CREATE TABLESPACE IGT_TABLE_TEMP DATAFILE '/oracle_db/db2/db_igt/datafiles/ora_igt_table_temp_01.dbf' SIZE 20M AUTOEXTEND ON MAXSIZE 30000M EXTENT MANAGEMENT LOCAL;