Purging files older than 5 days using mtime in linux

oracle@servername:dbname> cat /fisc/oracle/purge/purgescript_1.ksh
#!/bin/ksh
echo 'SIZE BEFORE dbname TRACE PURGE' > /fisc/oracle/purge/purgetrace_1.log

ls -l /fisc/dba/traces/dbname_ap01_11336.trm >> /fisc/oracle/purge/purgetrace_1.log

df -h /fisc/dba/traces >> /fisc/oracle/purge/purgetrace_1.log

echo 'NUMBER OF FILES BEFORE dbname TRACE PURGE' >> /fisc/oracle/purge/purgetrace_1.log
ls -1R /fisc/dba/traces | wc -l >> /fisc/oracle/purge/purgetrace_1.log


export ORACLE_HOME=/ercprtp1/oracle/product/11.2.0.2
export ORACLE_SID=dbname
echo $ORACLE_HOME
echo $ORACLE_SID


cat /dev/null > /fisc/dba/traces/dbname_ap01_11336.trm

find /fisc/dba/traces/*.trm -mtime +5 -exec rm {} \;

find /fisc/dba/traces/*.trc -mtime +5 -exec rm {} \;

echo 'SIZE AFTER dbname TRACE PURGE' >> /fisc/oracle/purge/purgetrace_1.log

ls -l /fisc/dba/traces/dbname_ap01_11336.trm >> /fisc/oracle/purge/purgetrace_1.log

df -h /fisc/dba/traces >> /fisc/oracle/purge/purgetrace_1.log


echo 'NUMBER OF FILES AFTER dbname TRACE PURGE' >> /fisc/oracle/purge/purgetrace_1.log
ls -1R /fisc/dba/traces | wc -l >> /fisc/oracle/purge/purgetrace_1.log

cat /fisc/oracle/purge/purgetrace_1.log | mailx -s "dbname Trace Purge Job output" Mail-dl@fmr.com
#cat /fisc/oracle/purge/purgetrace_1.log | mailx -s "dbname Trace Purge Job output" corpid@fmr.com


*****************

chmod 777 purgescript_1.ksh

to run the .ksh

ksh -x purgescript_1.ksh



Scheduled Crontab:
00 22 * * * /fisc/oracle/purge/purgescript_1.ksh > /fisc/oracle/logs/Cron/purgescript_1.log 2>&1

******************

Script to check top 128 sql.

select *
from
(
select
executions,
ROUND((cpu_time/1000000),2) total_cpu,
ROUND((elapsed_time/1000000),2) total_elapsed,
ROUND((cpu_time/1000000)/executions,2) cpu_per_e,
ROUND((elapsed_time/1000000)/executions,2) elapsed_per_e,
length(sql_fulltext),
sql_fulltext
from v$sql
where executions > 0
order by elapsed_per_e desc
)
where rownum <= 128;

Script for Checking disk size on ASM

set lines 9999
col DISKNAME for a20
col DISKGROUP for a20
col PATH for a30
select a.name DiskGroup, b.disk_number Disk#, b.name DiskName, b.total_mb, b.free_mb, b.path,
b.header_status from v$asm_disk b, v$asm_diskgroup a where a.group_number (+) =b.group_number
order by b.group_number, b.disk_number, b.name

Putting Sql file in Background.

index_creation.sh

sqlplus /nolog << EOF conn username/passward@DBname index_creation.sql >>EOF

-----

index_creation.sql

-------------
chmod +x index_creation.sh

Sql Script comments

Multi line comment

*/
DML OR DDL
/*

Single line comment
--DML
--DDL

Errors in exp or imp

Nice script to check TBS usage

SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
NVL(a.bytes / 1024 / 1024, 0) "Size (M)",
(NVL(a.bytes -NVL(f.bytes, 0), 0)/1024/1024) "Used (M)",
TO_CHAR(NVL((a.bytes -NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select
tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select
tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE
d.tablespace_name like '&TABLESPACE%' and d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
NVL(a.bytes / 1024 / 1024, 0) "Size (M)", NVL(t.bytes, 0)/1024/1024 "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
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'
order by 2
/