SELECT a.VALUE|| b.symbol|| c.instance_name|| '_ora_' || d.spid|| '.trc' trace_file
fROM (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') a,
     (SELECT SUBSTR (VALUE, -6, 1) symbol
      FROM   v$parameter WHERE NAME = 'user_dump_dest') b,
      (SELECT instance_name FROM v$instance) c,
      (SELECT spid FROM v$session s, v$process p, v$mystat m
      WHERE s.paddr= p.addr AND s.SID = m.SID AND m.statistic# = 0) d ;

select * from v$mystat;
select * from v$process;
select * from v$session;

SQL> desc v$session
Name                    Type         Nullable Default Comments
----------------------- ------------ -------- ------- --------
SADDR                   RAW(4)       Y                        
SID                     NUMBER       Y                        
SERIAL#                 NUMBER       Y                        
AUDSID                  NUMBER       Y                        
PADDR                   RAW(4)       Y                        
USER#                   NUMBER       Y                        
USERNAME                VARCHAR2(30) Y                        
COMMAND                 NUMBER       Y                        
OWNERID                 NUMBER       Y                        
TADDR                   VARCHAR2(8)  Y                        
LOCKWAIT                VARCHAR2(8)  Y                        
STATUS                  VARCHAR2(8)  Y                        
SERVER                  VARCHAR2(9)  Y                        
SCHEMA#                 NUMBER       Y                        
SCHEMANAME              VARCHAR2(30) Y                        
OSUSER                  VARCHAR2(30) Y                        
PROCESS                 VARCHAR2(12) Y                        
MACHINE                 VARCHAR2(64) Y                        
TERMINAL                VARCHAR2(16) Y                        
PROGRAM                 VARCHAR2(64) Y                        
TYPE                    VARCHAR2(10) Y                        
SQL_ADDRESS             RAW(4)       Y                        
SQL_HASH_VALUE          NUMBER       Y                        
SQL_ID                  VARCHAR2(13) Y                        
SQL_CHILD_NUMBER        NUMBER       Y                        
PREV_SQL_ADDR           RAW(4)       Y                        
PREV_HASH_VALUE         NUMBER       Y                        
PREV_SQL_ID             VARCHAR2(13) Y                        
PREV_CHILD_NUMBER       NUMBER       Y                        
MODULE                  VARCHAR2(48) Y                        
MODULE_HASH             NUMBER       Y                        
ACTION                  VARCHAR2(32) Y                        
ACTION_HASH             NUMBER       Y                        
CLIENT_INFO             VARCHAR2(64) Y                        
FIXED_TABLE_SEQUENCE    NUMBER       Y                        
ROW_WAIT_OBJ#           NUMBER       Y                        
ROW_WAIT_FILE#          NUMBER       Y                        
ROW_WAIT_BLOCK#         NUMBER       Y                        
ROW_WAIT_ROW#           NUMBER       Y                        
LOGON_TIME              DATE         Y                        
LAST_CALL_ET            NUMBER       Y                        
PDML_ENABLED            VARCHAR2(3)  Y                        
FAILOVER_TYPE           VARCHAR2(13) Y                        
FAILOVER_METHOD         VARCHAR2(10) Y                        
FAILED_OVER             VARCHAR2(3)  Y                        
RESOURCE_CONSUMER_GROUP VARCHAR2(32) Y                        
PDML_STATUS             VARCHAR2(8)  Y                        
PDDL_STATUS             VARCHAR2(8)  Y                        
PQ_STATUS               VARCHAR2(8)  Y                        
CURRENT_QUEUE_DURATION  NUMBER       Y                        
CLIENT_IDENTIFIER       VARCHAR2(64) Y                        
BLOCKING_SESSION_STATUS VARCHAR2(11) Y                        
BLOCKING_INSTANCE       NUMBER       Y                        
BLOCKING_SESSION        NUMBER       Y                        
SEQ#                    NUMBER       Y                        
EVENT#                  NUMBER       Y                        
EVENT                   VARCHAR2(64) Y                        
P1TEXT                  VARCHAR2(64) Y                        
P1                      NUMBER       Y                        
P1RAW                   RAW(4)       Y                        
P2TEXT                  VARCHAR2(64) Y                        
P2                      NUMBER       Y                        
P2RAW                   RAW(4)       Y                        
P3TEXT                  VARCHAR2(64) Y                        
P3                      NUMBER       Y                        
P3RAW                   RAW(4)       Y                        
WAIT_CLASS_ID           NUMBER       Y                        
WAIT_CLASS#             NUMBER       Y                        
WAIT_CLASS              VARCHAR2(64) Y                        
WAIT_TIME               NUMBER       Y                        
SECONDS_IN_WAIT         NUMBER       Y                        
STATE                   VARCHAR2(19) Y                        
SERVICE_NAME            VARCHAR2(64) Y                        
SQL_TRACE               VARCHAR2(8)  Y                        
SQL_TRACE_WAITS         VARCHAR2(5)  Y                        
SQL_TRACE_BINDS         VARCHAR2(5)  Y                        
 
SQL> desc v$process;
Name             Type          Nullable Default Comments
---------------- ------------- -------- ------- --------
ADDR             RAW(4)        Y                        
PID              NUMBER        Y                        
SPID             VARCHAR2(12)  Y                        
USERNAME         VARCHAR2(15)  Y                        
SERIAL#          NUMBER        Y                        
TERMINAL         VARCHAR2(16)  Y                        
PROGRAM          VARCHAR2(64)  Y                        
TRACEID          VARCHAR2(255) Y                        
BACKGROUND       VARCHAR2(1)   Y                        
LATCHWAIT        VARCHAR2(8)   Y                        
LATCHSPIN        VARCHAR2(8)   Y                        
PGA_USED_MEM     NUMBER        Y                        
PGA_ALLOC_MEM    NUMBER        Y                        
PGA_FREEABLE_MEM NUMBER        Y                        
PGA_MAX_MEM      NUMBER        Y                        
 
SQL> desc v$mystat
Name       Type   Nullable Default Comments
---------- ------ -------- ------- --------
SID        NUMBER Y                        
STATISTIC# NUMBER Y                        
VALUE      NUMBER Y                        
 
SQL> desc v$process_memory
Name          Type         Nullable Default Comments
------------- ------------ -------- ------- --------
PID           NUMBER       Y                        
SERIAL#       NUMBER       Y                        
CATEGORY      VARCHAR2(15) Y                        
ALLOCATED     NUMBER       Y                        
USED          NUMBER       Y                        
MAX_ALLOCATED NUMBER       Y                        
 
SQL>