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: http://www.oracle.com/technet 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.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…
