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 -rnFrom 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);