How to find Trace File associated with every session the database ?

Following query will list the USER session and trace file Associated with each background session.

set pages 10000
set lines 180
clear columns

COLUMN PROGRAM FORMAT A40 WRAP HEADING 'SCHEMANAME TYPE|PROGRAM'
COLUMN NAME FORMAT A30 WRAP HEADING 'USERNAME|OSUSER'
COLUMN MACHINE FORMAT A20 WRAP HEADING 'MACHINE|TERMINAL'
COLUMN INSTANCE FORMAT A15 WRAP HEADING 'INST_ID|INST_NAME'
COLUMN SID FORMAT A10 WRAP HEADING 'SID|SERIAL#'
COLUMN PID FORMAT A10 WRAP HEADING 'PID|SPID'
COLUMN LOGON_TIME FORMAT A20 WRAP HEADING 'LOGON_TIME|STATUS'
COLUMN TRACEFILE FORMAT A109 WRAP

select
rpad(rtrim(to_char(vses.SID)),10,' ') || rpad(rtrim(to_char(vses.SERIAL#)),10,' ') as SID,
rpad(rtrim(to_char(vproc.PID)),10,' ') || rpad(rtrim(to_char(vproc.SPID)),10,' ') as PID,
rpad(rtrim(vses.USERNAME),30,' ') || rpad(rtrim(vses.OSUSER),30,' ') as NAME,
rpad(trim(vses.SCHEMANAME),25,' ') || ' ' || rpad(rtrim(vses.TYPE),14,' ') || rpad(rtrim(vses.PROGRAM),40,' ') as PROGRAM,
rpad(rtrim(vses.MACHINE),20,' ') || rtrim(vses.TERMINAL) as MACHINE,
rpad(rtrim(TO_CHAR(vses.LOGON_TIME,'DD-MON-YYYY HH24:MI:SS')),20,' ') || rpad(rtrim(vses.STATUS),10,' ') as LOGON_TIME,
rpad(rtrim(to_char(vses.INST_ID)),15,' ') || rpad(rtrim(vinst.INSTANCE_NAME),15,' ') as INSTANCE,
vproc.TRACEFILE,
vproc.PGA_USED_MEM,
vproc.PGA_FREEABLE_MEM,
vproc.PGA_MAX_MEM
from gv$session vses, gv$process vproc, gv$instance vinst
where vses.PADDR=vproc.ADDR
and vses.inst_id = vproc.inst_id
and vses.inst_id = vinst.instance_number
and vproc.background is null -- Uncomment this to get User Process only.
-- and vproc.background = 1 -- Uncomment this to get Background Process only.
order by vses.type,vses.status,vses.inst_id,vses.USERNAME
/

clear columns

Following query will list the BACKGROUND session and trace file Associated with each user session.

set pages 10000
set lines 180
clear columns

COLUMN PROGRAM FORMAT A25 WRAP
COLUMN OSUSER FORMAT A20 WRAP
COLUMN TERMINAL FORMAT A20 WRAP
COLUMN MACHINE FORMAT A20 WRAP
COLUMN DESCRIPTION FORMAT A40 WRAP
COLUMN TRACEID FORMAT A8 WRAP
COLUMN TRACEFILE FORMAT A62 WRAP
COLUMN PID FORMAT 999999 WRAP
COLUMN SPID FORMAT A8 WRAP
COLUMN LOGON_TIME FORMAT A20 WRAP
COLUMN INST_ID FORMAT 999999
COLUMN INSTANCE_NAME FORMAT A10 HEADING INST_NAME

select
vbgproc.NAME,
vses.SID,
vses.SERIAL#,
vses.PROGRAM,
vproc.PID,
vproc.SPID,
vses.OSUSER,
vses.MACHINE,
vses.TERMINAL,
TO_CHAR(vses.LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') LOGON_TIME,
vses.INST_ID,
vbgproc.DESCRIPTION,
vproc.PGA_USED_MEM,
vproc.PGA_FREEABLE_MEM,
vproc.PGA_MAX_MEM,
vproc.TRACEID,
vproc.TRACEFILE,
vinst.INSTANCE_NAME
from gv$session vses, gv$process vproc, gv$bgprocess vbgproc, gv$instance vinst
where vses.PADDR=vproc.ADDR
and vses.PADDR=vbgproc.PADDR
and vses.inst_id = vproc.inst_id
and vses.inst_id = vbgproc.inst_id
and vses.inst_id = vinst.instance_number
and vproc.background = 1
order by vses.inst_id,vbgproc.name
/

clear columns

Oracle Recyclebin

Recyclebin : A new feature of RECYCLEBIN was introduced in Oracle 10g.

Enabled : By Default RECYCBLEBIN is enabled. It can be disabled.
There are two SYS owned views USER_RECYCLEBIN and DBA_RECYCLEBIN.
RECYCLEBIN$ is the table owned by SYS. Query DBA_OBJECTS for RECYCLEBIN and you will find these and others.
Basically, in simple terms it works similar to recyclebin in Windows. When you delete an object it goes to recyclebin, either you can recover it from there or PURGE recyclebin and get rid of it.
PURGE :
1. DROP TABLE PURGE;
· This will drop the table and purge at the same time and will not move it to recyclebin. It is similar to dropping a table in earlier versions (9i and below)

2. PURGE TABLESPACE tablespace_name USER username;
Substitute tablespace_name and username.
· All objects in a particular tablespace for a user that have been dropped and are still in recyclebin can be purged.

3. PURGE TABLESPACE tablespace_name;
Substitute tablespace_name.
· All objects in a particular tablespace that have been dropped and are still in recyclebin can be purged.

4. PURGE RECYCLEBIN;
· This can be used to purge user recyclebin. · Note : USER_RECYCBLEBIN synonym refers to RECYCLEBIN

5. PURGE DBA_RECYCLEBIN;
· As a dba you can clear entire recyclebin and clean (purge) all the dropped objects.
You can also purge a particular object in the recyclebin after it is dropped.
Objects in recycle bin are purged, if there are space constraints (running out of space in the tablesapce)
Some of the constraints and limitations

1. Would not preserve table's integrity constraints in recycble bin.
2. Would not restore the indexes or triggers automatically when table from recycble bin is restored.

How to see contents of Recyclebin : SHOW RECYCLEBIN;
It is not recommended to disable recyblebin.
· Disable recyblebin globally (for the database)
o alter system set recyclebin = off;· Disable recyclebin for a session
o alter session set recycblebin = off;