Creating Trigger for SQL Trace / Session Trace

Creating Trigger for SQL Trace / Session Trace

CREATE OR REPLACE TRIGGER _sql_trace_trigger
AFTER LOGON
ON .SCHEMA
BEGIN
execute immediate 'alter session set TRACEFILE_IDENTIFIER="xxxxxx"';
execute immediate 'alter session set MAX_DUMP_FILE_SIZE=1000000';
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
execute immediate 'alter session set STATISTICS_LEVEL=ALL';
END;
/

Parallel queries

set pagesize 500 linesize 500
set serveroutput on
set feedback off echo off
col username format a10
col osuser format a10
col machine format a10
col program format a30
col schemaname format a10

SELECT qcsid, username, status, schemaname, machine, logon_time, program, degree, req_degree
FROM SYS.V_$PX_SESSION p, V$SESSION s
WHERE p.saddr=s.saddr
ORDER BY qcsid, status, logon_time;

SELECT username, schemaname, qcsid, count(*) as num_parallel_proc
FROM SYS.V_$PX_SESSION p, V$SESSION s
WHERE p.saddr=s.saddr
GROUP BY username, schemaname, qcsid;

Temp Usage

set serveroutput off
col TABLESPACE for a15
col SIZE for a10
col sid_serial for a15
col program for a45
col username for a15
col SID_SERIAL for a15
col osuser for a15

set line 200
set pagesize 200
SELECT ta.TABLESPACE_NAME,
ta.total_space "TOTAL SPACE(MB)" ,
tb.used_space "USED SPACE(MB)",
ta.total_space - tb.used_space " FREE SPACE(MB)",
((tb.used_space/ta.total_space)*100)||'%' "USED%"
FROM
(select TABLESPACE_NAME,
sum(BYTES)/1048576 total_space
from dba_temp_files
group by TABLESPACE_NAME ) ta,
(SELECT tablespace,
sum(u.blocks*p.value)/(1024 * 1024) used_space
from v$sort_usage u, v$parameter p
where p.name='db_block_size'
group by tablespace) tb
where ta.tablespace_name=tb.tablespace(+)
/
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||' MB ' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.osuser,
a.status,
to_char(a.logon_time,'DD-MM-YYYY:HH24:MI') " LOGON TIME ",
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks
/

Find Blocking in Database and related session information

Blocking in the Database

select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;

SQL related to session and object and row wait

select s.sid, q.sql_text from v$sqltext q, v$session s
where q.address = s.sql_address
and s.sid = &sid
order by q.piece;


select do.object_name,
row_wait_obj#, do.data_object_id, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, do.data_object_id, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=&sid
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

select * from dba_dml_locks;

select * from dba_ddl_locks;

Shrink datafiles in all tablespace below HWM

Shrink Data files in all tablespaces

Spool /tmp/shrink_tablespaces.sql

select 'alter database datafile ''' || file_name || ''' resize ' ||
ceil(ROUND((((nvl(hwm, 1) * &&BlockSize) / 1024 / 1024) + 50), -2)) || 'm' cmd
from dba_data_files a,
(select file_id, max(block_id + blocks - 1) hwm
from dba_extents
group by file_id) b
where a.file_id = b.file_id(+)
and ceil(blocks * &BlockSize / 1024 / 1024) -
ceil((nvl(hwm, 1) * &&BlockSize) / 1024 / 1024) > 0
and a.tablespace_name not in
('SYSTEM', 'TEMP', 'UNDO_SDATA01', 'UNDO_SDATA02')
/

spool off;

@/tmp/shrink_tablespaces.sql


Schema Size

select 'TABLE' "SEGMENT_TYPE",round(sum(bytes)/1024/1024/1024) "SIZE in GB" from dba_segments where owner='&OWNER'
and segment_type in('TABLE','TABLE PARTITION','LOBSEGMENT') group by 'TABLE'
UNION
select 'INDEX' "SEGMENT_TYPE",round(sum(bytes)/1024/1024/1024) "SIZE in GB" from dba_segments where owner='&OWNER'
and segment_type in('INDEX','INDEX PARTITION','LOBINDEX') group by 'INDEX'
/