Installing SQLcl

SQLcl is the new command line tool from Oracle, more specifically from the SQL Developer team. It is currently an Early Adopter (EA) release and you can download it from: Source: Installing SQLcl  

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.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…