Skip to content
January 13, 2017 / doganay

DBMS_LOCK.SLEEP ALNERNATIVES


1) JAVA(milisecond):

SQL> create or replace procedure sleep(x_ms in number) as language java
2 name 'java.lang.Thread.sleep(int)';
3 /

Procedure created.

SQL> set timing on
SQL> exec sleep(10000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.02


2) USERLOCK

@?/rdbms/admin/userlock
--use 1 for 10 ms, 100 for 1 sec
exec user_lock.sleep(100);
January 13, 2017 / doganay

SHOW VIEW NAMES IN SQL PLANS

alter session set "_simple_view_merging"=false;
alter session set "_dump_qbc_tree"=1;

Many thanks to Tanel Poder 🙂

http://blog.tanelpoder.com/2007/09/16/how-to-resolve-sql-object-and-column-names-all-the-way-to-base-tables-and-columns-in-oracle/

January 13, 2017 / doganay

LOG MINER SCRIPT CREATOR


undefine V_START V_END
set pages 1000 lines 1000 feed off
accept V_START prompt "START TIME (dd.mm.yyyy hh24:mi:ss): "
accept V_END     prompt "END TIME (dd.mm.yyyy hh24:mi:ss): "

SELECT    'exec dbms_logmnr.add_logfile(LogFileName => '''
       || name
       || ''',Options => dbms_logmnr.ADDFILE);'
  FROM gv$archived_log
 WHERE     first_time >= TO_DATE ('&&V_START', 'dd.mm.yyyy hh24:mi:ss')
       AND first_time <= TO_DATE ('&&V_END', 'dd.mm.yyyy hh24:mi:ss') UNION ALL SELECT    'exec dbms_logmnr.add_logfile(LogFileName => '''
       || MIN (MEMBER)
       || ''',Options => dbms_logmnr.ADDFILE);'
  FROM gv$log a, gv$logfile b
 WHERE     a.inst_id = b.inst_id
       AND a.group# = b.group#
       AND a.first_time >= TO_DATE ('&&V_START', 'dd.mm.yyyy hh24:mi:ss')
       AND a.first_time <= TO_DATE ('&&V_END', 'dd.mm.yyyy hh24:mi:ss') GROUP BY a.group# UNION ALL SELECT 'exec dbms_logmnr.start_logmnr(options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);'
  FROM DUAL
UNION ALL
SELECT '--select * from gv$logmnr_contents;' FROM DUAL
UNION ALL
SELECT '--exec DBMS_LOGMNR.END_LOGMNR;' FROM DUAL;

prompt
set feed on
undefine V_START V_END
January 13, 2017 / doganay

SQL PATCH

If sql can not be edited (embedded in a tool etc.) and if you can use hint, you can use sql patch.
Sql patch applies hint to your sql.
But you must use only one hint. (After first select)
So, if you sql consists of multiple inner sqls, use @SEL$ syntax.
For example:

select /*+ index(@SEL$2 table2) */ * from table 1
where col1 in (select * from table2);

So, your hint is

index(@SEL$2 table2)

for this sql.

Easily you can use this anon.block to create your sql patch:

DECLARE
    v_sql_fulltext   CLOB;
    v_sql_id         VARCHAR2 (13)   := 'az6f9s2pkj7av';
    v_hint           VARCHAR2 (4096) := 'GATHER_PLAN_STATISTICS';
BEGIN
    SELECT sql_fulltext
      INTO v_sql_fulltext
      FROM gv$sql
     WHERE sql_id = v_sql_id AND ROWNUM = 1;

    sys.dbms_sqldiag_internal.i_create_patch (
        sql_text    => v_sql_fulltext,
        hint_text   => v_hint,
        name        => 'SQL_PATCH_' || v_sql_id);
END;
/

Of course you can give whatever name you want to your sql patch.
But our sql patch name is SQL_PATCH_az6f9s2pkj7av
and there is a reason for that:
After creating sql patch, to get the sql text you must know the sql_id.
Use this:

SELECT CAST (EXTRACTVALUE (VALUE (x), '/hint') AS VARCHAR2 (500))
           AS outline_hints
  FROM XMLTABLE (
           '/outline_data/hint'
           PASSING (SELECT xmltype (comp_data) xml
                      FROM sys.sqlobj$data
                     WHERE signature =
                               (SELECT signature
                                  FROM dba_sql_patches
                                 WHERE SUBSTR (name, -13) = 'SQL_PATCH_az6f9s2pkj7av'))) x;

You can alter it:

EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH('SQL_PATCH_az6f9s2pkj7av', 'STATUS', 'DISABLED');

And finally you can delete it:

DECLARE
  PATCHNOTFND EXCEPTION;
  PRAGMA EXCEPTION_INIT(PATCHNOTFND, -13833);
BEGIN
  DBMS_SQLDIAG.DROP_SQL_PATCH('SQL_PATCH_az6f9s2pkj7av');
EXCEPTION
  WHEN PATCHNOTFND THEN
    dbms_output.put_line('SQL Patch Not Found.');
END;
/
December 20, 2016 / doganay

BLOCKING-BLOCKED SESSIONS TREE SQL

--Session is displayed as -
with lk as (select blocking_instance||'-'||blocking_session blocker, inst_id||'-'||sid waiter 
from gv$session where blocking_instance is not null and blocking_session is not null)
select lpad(' ',2*(level-1))||waiter "Displayed as -" from
(select * from lk
union all
select distinct 'root', blocker from lk
where blocker not in (select waiter from lk))
connect by prior waiter=blocker start with blocker='root';
prompt

sample output:

Displayed as -
--------------------------------
1-855
  1-619
    1-548
November 28, 2016 / doganay

WHERE ARE BIND VALUES

1) GV$SQL_BIND_CAPTURE and/or DBA_HIST_SQLBIND views
2) GV$SQL_MONITOR
3) Use the DBMS_XPLAN package to list bind values:
select * from table ( dbms_xplan.display_cursor (‘my_sql_id’,0, ‘ADVANCED’));
4) Use the TRCANLZR tool

November 28, 2016 / doganay

NEGATIVE BYTES and BLOCKS in DBA_SEGMENTS

Re-calculate dba_segments data using dba_extents.
In order to do that, use:

exec DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_EXTBLKS('TABLESPACE_NAME');
November 10, 2016 / doganay

REPLACE BIND VARIABLES in v$sql

Mustafa DOGANAY's Blog

undefine sql_id accept sql_id prompt "SQL_ID......: " set serveroutput on declare v_sql_id varchar2(13):=trim('&&sql_id'); TYPE varchar2_array IS VARRAY(1000) of varchar2(32767); v_sql_fulltext varchar2_array := varchar2_array(); j number:=1; begin v_sql_fulltext.extend(1000); for i in ( select substr(sql_fulltext,1,32767) vc_sql_fulltext from gv$sql where sql_id=v_sql_id ) loop v_sql_fulltext(j):=i.vc_sql_fulltext; j:=j+1; end loop; for i in ( SELECT name, NVL ( DECODE ( SUBSTR (datatype_string, 1, 4), 'NUMB', value_string, 'VARC', '''' || value_string || '''', 'NVAR', '''' || value_string || '''', 'CHAR', '''' || value_string || '''', 'DATE', 'to_date(''' || value_string || ''',''MM/DD/YY HH24:MI:SS'')', ''), 'NULL') AS "VALUE" FROM gv$sql_bind_capture WHERE sql_id = v_sql_id AND child_address = (SELECT child_address FROM (SELECT child_address FROM gv$sql WHERE sql_id = v_sql_id ORDER BY elapsed_time / (executions + 1) DESC) WHERE ROWNUM = 1) GROUP BY name, DECODE ( SUBSTR (datatype_string, 1, 4), 'NUMB', value_string, 'VARC', '''' || value_string || '''', 'NVAR', '''' || value_string || '''', 'CHAR', '''' || value_string || ''''…

View original post 34 more words

November 9, 2016 / doganay

CODE SEARCH

select owner||'.'||name from dba_source where lower(text) like '%pattern1%' intersect
select owner||'.'||name from dba_source where lower(text) like '%pattern2%' intersect
select owner||'.'||name from dba_dependencies where referenced_name='TABLE1'
November 9, 2016 / doganay

LINE NUMBERS OF STORED OBJECTS

SELECT owner,
       name,
       DECODE (TYPE, 'PACKAGE BODY', 'PACKAGE', TYPE),
       SUM (max_line)
  FROM (SELECT owner,
               name,
               TYPE,
               MAX (line) max_line
          FROM dba_source
         WHERE owner NOT IN (SELECT owner
                               FROM dba_logstdby_skip
                              WHERE statement_opt = 'INTERNAL SCHEMA')
        GROUP BY owner, name, TYPE)
GROUP BY owner, name, DECODE (TYPE, 'PACKAGE BODY', 'PACKAGE', TYPE)
ORDER BY 4 DESC