Oracle hidden parameters

COLUMN parameter           FORMAT a37 COLUMN description         FORMAT a30 WORD_WRAPPED COLUMN “Session Value”     FORMAT a10 COLUMN “Instance Value”    FORMAT a10 SET LINES 100 SET PAGES 0 SPOOL undoc.lis SELECT      a.ksppinm  “Parameter”,      a.ksppdesc “Description”,      b.ksppstvl “Session Value”,      c.ksppstvl “Instance Value” FROM      x$ksppi a,      x$ksppcv b,      x$ksppsv c WHERE      a.indx…

SQL script to find where is a certain object used

SELECT OWNER,        NAME,        TYPE   FROM DBA_DEPENDENCIES  WHERE REFERENCED_OWNER = upper(‘&OWNER’)    AND REFERENCED_NAME = upper(‘&object’)    AND REFERENCED_TYPE IN (‘PACKAGE’, ‘PROCEDURE’, ‘FUNCTION’); select    x.sid from    v$session x, v$sqltext y where    x.sql_address = y.address and    upper(y.sql_text) like upper(‘%object%’);

Find my current session id

There are multiple approaches: SQL> SELECT sys_context(‘userenv’,’sessionid’) Session_ID from dual; SQL> SELECT sid FROM v$mystat WHERE rownum = 1; SID ———- 145 SQL> SELECT sid FROM V$SESSION WHERE audsid = userenv(‘sessionid’); SID ———- 145 SQL> SELECT sid FROM V$SESSION WHERE audsid = SYS_CONTEXT(‘userenv’,’sessionid’); SID ———- 145

BIND variables

col sid format 9999999999 col username format a8 col sql_hash_value format 999999999999 col sqlid format a14 col sql_child_number format 9 col name format a4 col value_string format a15 col last_captured format a25 select s.sid, s.username, s.sql_hash_value, s.sql_id, s.sql_child_number, spc.name, spc.value_string, last_captured from v$sql_bind_capture spc, v$session s,v$sql sq where s.sql_hash_value = spc.hash_value and s.sql_address = spc.address…

Find the last JOB run status

set lin 300 col JOB_NAME format a30 col STATUS format a15 col ADDITIONAL_INFO format a100 select JOB_NAME,STATUS,ADDITIONAL_INFO from dba_scheduler_job_run_details where log_id in(select max(log_id) from dba_scheduler_job_run_details where job_name like upper(‘&job_name’));