ITRemote

Finding Large Directories

look for large files with this command:

find . -type f -size +100000k -exec ls -lh {} \; | awk ‘{ print $9 “: ” $5 }’

This looks for files over 100MB, but other than datafiles there were very few files found. I knew I needed to look for large directories. Directories that had a large amount of small files. This command worked nicely:

du -h / | grep ^[1-9][0-9][0-9.]*G | sort -rn

From there I was able to find the directories that I needed to clear out.

Find and zip files newer than X minutes

find . -mmin -1620 -type f -name \*_lm[d,s]\*trc -print -exec zip /tmp/`hostname`_lm_files.zip {} \;

set lines 100 pages 999
col username format a20
col sess_id format a10
col object format a25
col mode_held format a10
select oracle_username || ' (' || s.osuser || ')' username,
       s.sid || ',' || s.serial# sess_id,
       owner || '.' || object_name object,
       object_type,
       decode( l.block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') status,
       decode(v.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X SX)'
 , 4, 'Share'
 , 5, 'S/Row-X (SSX)'
 , 6, 'Exclusive', TO_CHAR(lmode)) mode_held
    from v\$locked_object v,
         dba_objects d,
         v\$lock l,
         v\$session s
   where v.object_id = d.object_id and
         v.object_id = l.id1 and
         v.session_id = s.sid
   order by oracle_username, session_id;

/**************************************************************************
 * Description:
 *      SQL*Plus script to start the SQL Trace for another session.  This
 *      script displays the trace file name and location.  This script will
 *      display the user sessions and prompt for information it needs.
 *
 **************************************************************************/
SET TERMOUT OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET ECHO OFF

column in_db_namet new_value out_db_namet noprint
column in_spidt new_value out_spidt noprint
column in_usert new_value out_usert noprint
column in_patht new_value out_patht noprint

select value in_db_namet,
       user in_usert
  from v\$parameter
 where name = 'db_name';

-- Find the path of the trace file directory
select value in_patht
  from v\$parameter
 where name = 'user_dump_dest';

-- This will put identifiable characters into the trace file and make it easier to find
alter session set tracefile_identifier = &out_usert;

SET TERMOUT ON
column program format a20

select username,
       sid,
       serial#,
       program
  from v\$session
 where program NOT LIKE 'ORACLE%';

ACCEPT in_user PROMPT 'Enter User ID from list above : '
ACCEPT in_sid PROMPT 'Enter SID from list above : '
ACCEPT in_serial PROMPT 'Enter SERIAL from list above : '

PROMPT
PROMPT 'Write the SID and SERIAL down...will need them to stop the trace'
PROMPT
SET TERMOUT OFF

select p.spid in_spidt
  from v\$process p, 
       v\$session s
 where p.addr = s.paddr 
   and s.username = upper('&in_user') 
   and s.sid = &in_sid;

SET TERMOUT ON
PROMPT
PROMPT Started SQL Trace in session
SELECT '&out_path' "Trace File Path" from dual;
SELECT '&out_db_namet' || '_ora_' || '&out_spidt' || '.trc' "Trace File Name"
  from dual;

PROMPT
rem exec dbms_system.set_ev(&in_sid, &in_serial, 10046, 12, '');
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => &in_sid,
                                          serial_num => &in_serial,
                                               waits => TRUE,
                                               binds => TRUE);
SET FEEDBACK ON
SET VERIFY ON
SET ECHO ON

This will start a trace in another session
exec dbms_system.set_sql_trace_in_session(3,5023,true);
exec dbms_system.set_sql_trace_in_session(3,5023,false);