Space usage, in Database, In Linux

=========================
General
=========================
Useful way to find space usage + add more space in Database and in Linux

=========================
Database
=========================

Tablespace

SET LINESIZE 120
SET PAGESIZE 200

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 1=1
   --AND 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 1=1
   --AND 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 1=1
   --AND 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;

Segments
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;


Datafiles
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;  

Add space

ALTER TABLESPACE IGT_TABLE ADD DATAFILE '/oracle_db/db1/db_igt/ora_igt_table_02.dbf' SIZE 1000M AUTOEXTEND ON MAXSIZE 10000M;

--Increase MAXSIZE

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_igt_index_01.dbf' AUTOEXTEND ON MAXSIZE 20000M;

--Resize

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_igt_index_01.dbf' RESIZE 20000M;

=========================
Linux
=========================

Get top files

find /software/oracle -type f -printf '%s %p\n'|sort -nr | head -10

Get top folders

du  -sh * |sort -nr | head -10

Add space

df -hP | grep oracle 

/dev/mapper/Volume00-LogVol07   7.8G  7.2G  194M  98% /software/oracle

cat /etc/fstab | grep LogVol07

/dev/Volume00/LogVol07  /software/oracle        ext3    defaults,acl        1 2

root@my_server:~>% vgs

  VG       #PV #LV #SN Attr   VSize   VFree 

  Volume00   1  17   0 wz--n- 135.75G 13.19G

  Volume01   1   1   0 wz--n- 136.70G 33.70G

lvextend -L +4G /dev/Volume00/LogVol07 && resize2fs /dev/Volume00/LogVol07

  Extending logical volume LogVol07 to 12.00 GB

  Logical volume LogVol07 successfully resized