Some Useful Unix commands and sql queries

Killing stubborn Unix Processes



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


cat /dev/null > /tmp/ kill -9


+++++++++++++++++++++++++++



Get the Total number of files


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


select count(*) from dba_data_files union


select count(*) from dba_temp_files union


select count(*) from v$logfile union


select count(*) from v$controlfile;


+++++++++++++++++++++++++



Get Mountpoint hosting the DBFs


-------------------------------


select distinct substr(name,1,instr(NAME,'/',-1,1)-9) fsname from v$datafile;
 

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

To delete files based on date and file extensions

----------------------------------------------

ls -l *.dat grep "Jul" tr -s " " cut -d " " -f9 > zzjul.sh


ps -ef|grep "" sort grep -v grep awk '{print $2'}


Generate Compression script for cold backup on source database


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

sqlplus /nolog

conn /as sysdba

set lines 132

set head off

set pages 0

select '/usr/bin/compress < ' FILE_NAME 
' > (/DEST_BACKUP_DIR/)' 
substr(FILE_NAME,30)
'.Z'
from dba_data_files
order by substr(FILE_NAME,30)
spool ColdBackup..ksh
/
spool off
 
+++++++++++++++++++++++++++++++++++++++++++++
 
Generate Uncompression script for cold backup on source database


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

select '/usr/bin/uncompress < /mnt2/oradata/backup/gpses117'
substr(FILE_NAME, instr(FILE_NAME,'/',-1,1)+1)
'.Z > '
FILE_NAME
from dba_data_files
order by substr(FILE_NAME, instr(FILE_NAME,'/',-1,1)+1)
spool RestoreBackup_gpser117.ksh
/
spool off

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

All Scheduled Jobs


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

set wrap on
set lines 140
set veri off
col PROGRAM for a20
col UnitCode for a8
col TypeCode for a8

select b.program, a.request_id, resubmit_interval "Interval", resubmit_interval_unit_code "UnitCode"

,resubmit_interval_type_code "TypeCode", resubmit_time "ResubTime", resubmit_end_date "EndDate"
,resubmitted, a.request_type, request_limit ,increment_dates ,resub_count,
exp_date, root_request_id "RootReqID", origin
from fnd_concurrent_requests a ,fnd_conc_req_summary_v b
where root_request_id is not null
and a.request_id = b.request_id;
 
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 
Get the SID for a given SPID


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

col program for a30
col username for a10

col spid for a9

select A.PID, A.SPID, A.USERNAME, A.SERIAL#,
B.SID, B.COMMAND, B.PROGRAM
from v$process A, v$session B

where A.spid = &spid

and B.PADDR = A.ADDR

/
 
+++++++++++++++++++++++++++++++++++++++++
 
List of Users with SYSADMIN Responsibility


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

set pages 45
set lines 132
col RESPONSIBILITY_NAME for a30
col SECURITY_GROUP_NAME for a30
col APPLICATION_NAME for a30
col USER_NAME for a15

select user_name, application_name, responsibility_name, u.END_DATE, ur.END_DATE
from apps.fnd_user u, apps.fnd_user_resp_groups ur,
apps.fnd_responsibility_vl r, apps.fnd_application_vl a,
apps.fnd_security_groups_vl s
where a.application_id = r.application_id
and u.user_id = ur.user_id
and r.application_id = ur.responsibility_application_id
and r.responsibility_id = ur.responsibility_id
and ur.start_date <= sysdate
and r.RESPONSIBILITY_NAME = 'System Administrator'
and ur.security_group_id = s.security_group_id
order by 1
/