Oracle Tablespaces, Segments Datafiles, Temporary Tablespace, and Linux space usage. SQL and bash

================================
General
================================
Database
Tablespaces report
Segments report
Datafiles report
Temporary tablespace report

Increase space to tablespace.

Linux
Report space usage
Increase space to mount point


Scripts
Delete oracle old trace files
Delete oracle large trace files

Delete oracle listener trace file

Delete old data from a table

Truncate table

================================
Tablespace
================================
SET LINESIZE 120
SET PAGESIZE 200
spool tbs.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 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;

spool off

================================
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

spool segments.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 LIKE '%SYSTEM%' 
  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;

spool off

================================

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

spool datafile.txt
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;  
spool off

================================
Partitions
================================

SELECT owner, 

       segment_name, 
       partition_name AS PARTITION, 
       segment_type, 
       tablespace_name, 
       ROUND(bytes/1024/1024,1) AS MB, 
       ROUND(max_size/1024/1024,1) AS MAX_MS
FROM DBA_SEGMENTS 
WHERE SEGMENT_NAME = 'FRS_ID_IDX'
ORDER BY MB DESC

================================
Temporary Tablespace and Datafile
================================
SET LINESIZE 120
SET PAGESIZE 120
COL tablespace_name FOR A30

COL file_name FOR A50

spool temp_usage.txt

SELECT tablespace_name, 
       ROUND(bytes_used/1024/1024) AS used_mb,
       ROUND(bytes_free/1024/1024) AS free_mb 
FROM V$TEMP_SPACE_HEADER;

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

       status
FROM DBA_TEMP_FILES;

spool off

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

================================
Add space to Datafile

================================

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_igt_table_01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 30000M;

================================
Add Datafile to Tablespace
================================

ALTER TABLESPACE IGT_TABLE ADD DATAFILE '/oracle_db/db1/db_igt/ora_igt_table_02.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 30000M;

================================
Linux Biggest Files
================================
Get space usage by file
#!/bin/bash
find /software/oracle -type f -printf '%s %p\n'|sort -nr | head -20

Get space usage by folder

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

================================
Linux Add Space
================================

df -hP | grep LogVol07
/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@rus-bki-3-glr-2:~>% 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
resize2fs 1.39 (29-May-2006)
Filesystem at /dev/Volume00/LogVol07 is mounted on /software/oracle; on-line resizing required
Performing an on-line resize of /dev/Volume00/LogVol07 to 3145728 (4k) blocks.
The filesystem on /dev/Volume00/LogVol07 is now 3145728 blocks long.

df -hP | grep LogVol07  
/dev/mapper/Volume00-LogVol07
                       12G  7.2G  3.9G  65% /software/oracle

================================
scripts
================================
- Delete Oracle old trace files
- Delete large Oracle trace files
- Delete Oracle listener logs
- Delete old Archive files  
- SQL script to delete old data from table


crontab example
crontab -l

0 4 * * * /software/oracle/oracle/scripts/delete_old_trace_files.sh

Delete Oracle old trace files

delete_old_trace_files.sh
#!/bin/bash

ORA_INST=igt
DAYS_TO_KEEP=7

find /software/oracle/diag/rdbms/${ORA_INST}/${ORA_INST}/trace -type f -name "*.trc" -mtime +${DAYS_TO_KEEP} -exec rm {} \;
find /software/oracle/diag/rdbms/${ORA_INST}/${ORA_INST}/trace -type f -name "*.trm" -mtime +${DAYS_TO_KEEP} -exec rm {} \;


Delete large Oracle trace files

delete_large_trace_files.sh
#!/bin/bash

ORA_INST=igt

find /software/oracle/diag/rdbms/${ORA_INST}/${ORA_INST}/trace -type f -size +1000M -exec rm {} \;

Delete Oracle Listener logs

delete_listener_files.sh
#!/bin/bash

LISTENER_ROOT=/software/oracle/diag/tnslsnr/${SERVER_NAME}/lsnr_igt/trace
SERVER_NAME=`hostname`

mv -f  ${LISTENER_ROOT}/lsnr_igt.log_6  ${LISTENER_ROOT}/lsnr_igt.log_7
mv -f  ${LISTENER_ROOT}/lsnr_igt.log_5  ${LISTENER_ROOT}/lsnr_igt.log_6
mv -f  ${LISTENER_ROOT}/lsnr_igt.log_4  ${LISTENER_ROOT}/lsnr_igt.log_5
mv -f  ${LISTENER_ROOT}/lsnr_igt.log_3  ${LISTENER_ROOT}/lsnr_igt.log_4
mv -f  ${LISTENER_ROOT}/lsnr_igt.log_2  ${LISTENER_ROOT}/lsnr_igt.log_3
mv -f  ${LISTENER_ROOT}/lsnr_igt.log_1  ${LISTENER_ROOT}/lsnr_igt.log_2
mv -f  ${LISTENER_ROOT}/lsnr_igt.log    ${LISTENER_ROOT}/lsnr_igt.log_1

Delete old Archive files
#!/bin/bash

DAYS_TO_KEEP=3
ARCH_DIR=/oracle_db/db2/db_igt/arch

find ${ARCH_DIR} -type f -name "*.arc" -mtime +${DAYS_TO_KEEP} -exec rm {} \;


SQL script to delete old data from table
ALTER SESSION SET DDL_LOCK_TIMEOUT = 600;
CREATE TABLE MY_TABLE_BAK AS SELECT * FROM MY_TABLE WHERE 1=2;
ALTER TABLE MY_TABLE_BAK NOLOGGING;
INSERT /*+ APPEND */ INTO MY_TABLE_BAK SELECT * FROM MY_TABLE 
WHERE MY_TABLE.ts_last_modified > SYSDATE - 7;
COMMIT;
TRUNCATE TABLE MY_TABLE;
INSERT /*+ APPEND */ INTO MY_TABLE SELECT * FROM MY_TABLE_BAK;
COMMIT;
DROP TABLE MY_TABLE_BAK;



bash script to truncate table
truncate_table.sh #!/bin/bash HOME_DIR=/software/oracle/oracle/scripts cd $HOME_DIR . $HOME_DIR/.set_profile export LOG_FILE=${HOME_DIR}/truncate_table.log touch $LOG_FILE export RUN_TIME=`date "+%Y%m%d"_"%H%M%S"` echo "----------------------------------------------" >> $LOG_FILE echo "Starting Truncate at $RUN_TIME" >> $LOG_FILE echo "----------------------------------------------" >> $LOG_FILE sqlplus system/xen86pga@igt @truncate_table.sql less TRUNCATE_TABLE_SQL.sql >> $LOG_FILE echo "Done " >> $LOG_FILE echo "----------------------------------------------" >> $LOG_FILE rm TRUNCATE_TABLE_SQL.sql truncate_table.sql SET HEADING OFF SET VERIFY OFF SET TERMOUT OFF SET PAGESIZE 0 SET FEEDBACK OFF spool TRUNCATE_TABLE_SQL.sql SELECT 'TRUNCATE TABLE '||OWNER||'.'||SEGMENT_NAME||';' FROM DBA_SEGMENTS WHERE segment_name = 'DEBUG_GEN_W_SDR' HAVING ROUND(SUM(bytes)/1024/1024) > 500 GROUP BY OWNER,SEGMENT_NAME; spool off @TRUNCATE_TABLE.sql exit; generated TRUNCATE_TABLE.sql
TRUNCATE TABLE OWNER1.SGA_W_EVENTS; TRUNCATE TABLE OWNER2.SGA_W_EVENTS; TRUNCATE TABLE OWNER3.SGA_W_EVENTS; TRUNCATE TABLE OWNER4.SGA_W_EVENTS; generated truncate_table.log ---------------------------------------------- Starting Truncate at 20161124_122549 ---------------------------------------------- TRUNCATE TABLE ALB_VODAF_SPARX.SGA_W_EVENTS; TRUNCATE TABLE MLT_VODAF_SPARX.SGA_W_EVENTS; TRUNCATE TABLE ROM_VODAF_SPARX.SGA_W_EVENTS; TRUNCATE TABLE ZAF_VODAC_SPARX.SGA_W_EVENTS; TRUNCATE TABLE NZL_VODAF_SPARX.SGA_W_EVENTS; TRUNCATE TABLE GRC_VODAF_SPARX.SGA_W_EVENTS; TRUNCATE TABLE GHA_VODAF_SPARX.SGA_W_EVENTS; Done ----------------------------------------------
Drop Tablespace with Datafile Example
DROP TABLESPACE COLLECT_CDR_TABLE_201906 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;