Oracle PL/SQL/System Tables Views/process

Get oracle installation path

 
SQL>
SQL>
SQL>      select c.value || "\ORA" || to_char(a.spid,"fm00000") || ".trc"
  2        from v$process a, v$session b, v$parameter c
  3       where a.addr = b.paddr
  4         and b.audsid = userenv("sessionid")
  5         and c.name = "user_dump_dest"
  6      /
C.VALUE||"\ORA"||TO_CHAR(A.SPID,"FM00000")||".TRC"
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
C:\ORACLEXE\APP\ORACLE\ADMIN\XE\UDUMP\ORA00604.trc

Query process for The Current Usage of the PGA Memory

 
SQL> --
SQL>
SQL> select
  2  program ,pga_used_mem ,pga_alloc_mem ,pga_max_mem
  3  from
  4  v$process
  5  order by pga_used_mem desc;
PROGRAM
--------------------------------------------------
PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM
------------ ------------- -----------
ORACLE.EXE (SHAD)
     5774313      10843381    25916661
ORACLE.EXE (LGWR)
     4427613       9271617     9926977
ORACLE.EXE (SHAD)
     1220113       1315025     3127161
ORACLE.EXE (MMON)
     1136145       1538885     2652997
ORACLE.EXE (D000)
      653677        659665     1211217
ORACLE.EXE (CJQ0)
      524517        752465     1735505
ORACLE.EXE (SMON)
      519985       1014609     1801041
ORACLE.EXE (RECO)
      488969        883537     1080145
ORACLE.EXE (DBW0)
      362765       1521749     1669849
ORACLE.EXE (q000)
      302965        555857      555857
ORACLE.EXE (CKPT)
PROGRAM
--------------------------------------------------
PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM
------------ ------------- -----------
      295405       1651229     1651229
ORACLE.EXE (q001)
      223941        359249      359249
ORACLE.EXE (MMNL)
      216201        424785      424785
ORACLE.EXE (QMNC)
      207757        359249      359249
ORACLE.EXE (PMON)
      207373        359249      359249
ORACLE.EXE (MMAN)
      206901        359249      359249
ORACLE.EXE (PSP0)
      206581        359249      359249
ORACLE.EXE (S003)
      200913        200913      293713
ORACLE.EXE (S002)
      200913        200913      293713
ORACLE.EXE (S000)
      200913        200913      293713
ORACLE.EXE (S001)
      200913        200913      293713
ORACLE.EXE (J003)
       86048        621393      621393
ORACLE.EXE (J001)
       86048        856273     1473361
ORACLE.EXE (J000)
       86048        921809     1511633
ORACLE.EXE (J002)
       86048        621393      621393
PSEUDO
           0             0           0

26 rows selected.
SQL> --

Query v$process a, v$session

 
SQL>
SQL> select a.spid
  2      from v$process a, v$session b
  3     where a.addr = b.paddr
  4       and b.audsid = userenv("sessionid")
  5  /
SPID
------------
972
SQL>