Skip to content
March 17, 2017 / doganay

BIND DATATYPE MISMATCHED SQLS

  • !!! These sqls do not return all bind datatype mismatches. Just return right and wrong datatype binded at the same time ones.

RECENT:

SELECT COUNT (*),
       sql_id,
       child_number,
       name
  FROM (SELECT DISTINCT sql_id,
                        child_number,
                        name,
                        SUBSTR (datatype_string, 1, 4) dtype
          FROM gv$sql_bind_capture
        GROUP BY sql_id,
                 child_number,
                 name,
                 SUBSTR (datatype_string, 1, 4))
GROUP BY sql_id, child_number, name
HAVING COUNT (*) > 1;


HISTORICAL:

SELECT COUNT (*), sql_id, name
  FROM (SELECT DISTINCT sql_id, name, SUBSTR (datatype_string, 1, 4) dtype
          FROM dba_hist_sqlbind
        GROUP BY sql_id, name, SUBSTR (datatype_string, 1, 4))
GROUP BY sql_id, name
HAVING COUNT (*) > 1;
Advertisements
March 17, 2017 / doganay

AUTOMATE DROPPING OLD PARTITIONS

--Print drop partition script older then 3 months for table owner1.table1
DECLARE
 V_OWNER varchar2(30) := 'OWNER1';
 V_TABLE_NAME varchar2(30) := 'TABLE1';
 V_MONTHS_OLDER number := 3;
BEGIN
    FOR c
        IN (SELECT table_owner, table_name, partition_name, high_value
              FROM dba_tab_partitions
             WHERE     table_owner = V_OWNER
                   AND table_name  = V_TABLE_NAME)
    LOOP
        EXECUTE IMMEDIATE
               'BEGIN
             IF sysdate >= ADD_MONTHS('
            || c.high_value
            || ', V_MONTHS_OLDER) THEN
                dbms_output.put_line(''ALTER TABLE '
            || c.table_owner
            || '.'
            || c.table_name
            || ' DROP PARTITION '
            || c.partition_name
            || ';'');
                  END IF;
          END;';
    END LOOP;
END;
/
March 17, 2017 / doganay

expdp FLASHBACK_TIME

$ expdp FLASHBACK_TIME=TO_TIMESTAMP('2017/03/01 14:41:01', 'YYYY/MM/DD HH24:MI:SS') schemas=doganay directory=tmpdir dumpfile=doganay.dmp

-bash: syntax error near unexpected token `('

$ expdp FLASHBACK_TIME="TO_TIMESTAMP('2017/03/01 14:41:01', 'YYYY/MM/DD HH24:MI:SS')" schemas=doganay directory=tmpdir dumpfile=doganay.dmp

LRM-00116: syntax error at ')' following 'YYYY/MM/DD HH24:'

$ expdp FLASHBACK_TIME=\"TO_TIMESTAMP\('2017/03/0114:41:01', 'YYYY/MM/DDHH24:MI:SS'\)\" schemas=doganay directory=tmpdir dumpfile=doganay.dmp

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option
ORA-39001: invalid argument value
ORA-39150: bad flashback time
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
ORA-00907: missing right parenthesis

$ expdp FLASHBACK_TIME="2017/03/17 15:34:01" schemas=doganay directory=tmpdir dumpfile=doganay.dmp

LRM-00112: multiple values not allowed for parameter 'flashback_time'


Solution:

$ expdp FLASHBACK_TIME="2017/03/1715:34:01" schemas=doganay directory=tmpdir dumpfile=doganay.dmp

Export: Release 11.2.0.4.0 - Production on Fri Mar 17 15:35:22 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA FLASHBACK_TIME=2017/03/1715:34:01 schemas=doganay directory=tmpdir dumpfile=doganay.dmp 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.125 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
...
February 13, 2017 / doganay

SQL TUNING USING DBMS_SQLTUNE

--sqlid
--exec DBMS_SQLTUNE.DROP_TUNING_TASK('STT_TMP_1');
DEFINE v_sql_id='30nf3tqcuj758'

DECLARE
    v_sql_id       VARCHAR2 (13) := '&v_sql_id';
    my_task_name   VARCHAR2 (30);
BEGIN
    my_task_name :=
        DBMS_SQLTUNE.create_tuning_task (sql_id      => '&v_sql_id',
                                         task_name   => 'STT_TMP_1');

    DBMS_SQLTUNE.execute_tuning_task (task_name => my_task_name);
END;
/






--sqltext
--EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('STT_TMP_1');
DECLARE
    my_task_name   VARCHAR2 (30);
    my_sqltext     CLOB;
BEGIN
    my_sqltext := 'select 1 from dual';

    my_task_name :=
        DBMS_SQLTUNE.create_tuning_task (sql_text    => my_sqltext,
                                         task_name   => 'STT_TMP_1');

    DBMS_SQLTUNE.execute_tuning_task (task_name => my_task_name);
END;
/






--historic
SELECT *
  FROM (SELECT snap_id, begin_interval_time, end_interval_time
          FROM dba_hist_snapshot
        ORDER BY snap_id DESC)
 WHERE ROWNUM <= 15;

--EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('STT_TMP_1');

DEFINE v_sql_id='bjf05cwcj5s6p'
DECLARE     my_task_name   VARCHAR2 (30);
     my_sqltext     CLOB;
BEGIN
     my_task_name := DBMS_SQLTUNE.create_tuning_task
                                        (begin_snap   => 62478,
                                         end_snap     => 62479,
                                         sql_id       => '&v_sql_id');

    DBMS_SQLTUNE.execute_tuning_task (task_name => my_task_name);
END;
/



select * from DBA_ADVISOR_TASKS;


--print tuning advice
SET PAGES 1000
SET SERVEROUTPUT ON
ALTER SESSION SET nls_language=american;

DECLARE
    xml_out   XMLTYPE;

    PROCEDURE print_clob (p_clob IN CLOB)
    IS
        v_offset       NUMBER := 1;
        v_chunk_size   NUMBER := 4000;
        v_length       NUMBER;
    BEGIN
        v_length := DBMS_LOB.getlength (p_clob);

        LOOP
            EXIT WHEN v_offset > v_length;
            DBMS_OUTPUT.put_line (
                DBMS_LOB.SUBSTR (p_clob, v_chunk_size, v_offset));
            v_offset := v_offset + v_chunk_size;
        END LOOP;
    END print_clob;
BEGIN
    DBMS_OUTPUT.enable (NULL);
    print_clob (DBMS_SQLTUNE.report_tuning_task ('STT_TMP_1'));
END;
/

February 10, 2017 / doganay

HOW TO DBMS OUT HUGE CLOBS

thx ronald:

https://ronaldsoracle.wordpress.com/2015/04/30/printing-a-large-xmltype-value-using-dbms_output/

 

February 10, 2017 / doganay

ORA-31603: object “SP1” of type PROCEDURE not found in schema “USER1”

add:
Use uppercase, not lowercase.

Mustafa DOGANAY's Blog


Problem:

SQL> select dbms_metadata.get_ddl('PROCEDURE','SP1','USER1') from dual;
ERROR:
ORA-31603: object "SP1" of type PROCEDURE not found in schema "USER1"
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344
ORA-06512: at line 1


Solution:

Use uppercase, not lowercase.

AND

grant select_catalog_role to user1;

View original post

January 13, 2017 / doganay

PL/SQL PROFILER

--If needed, create the PL/SQL Profiler Tables under your application schema:
--@?/rdbms/admin/proftab
--If needed, install the DBMS_PROFILER API, connected as SYS:
--@?/rdbms/admin/profload
exec dbms_profiler.start_profiler ('Test of raise procedure by mdoganay');
--do your work here
exec dbms_profiler.stop_profiler();
@profiler


For profiler.sql read this MOS note:

Customer RecommendedScript to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data (Doc ID 243755.1)

January 13, 2017 / doganay

CTAS TEXT TRUNCATED

You can see full text sql using 10053 trace:

oradebug setospid <OSPID>
oradebug unlimit
oradebug Event 10053 trace name context forever, level 12
oradebug tracefile_name
--wait some time
oradebug Event 10046 trace name context off
January 13, 2017 / doganay

EXCEPTION USAGE IN LOOPS

BEGIN
    FOR i IN (...)
    LOOP
        --BEGIN needed
        BEGIN
            --Do your work here..
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.put_line (i.col1);
                DBMS_OUTPUT.put_line ('SQLCODE: ' || SQLCODE);
                DBMS_OUTPUT.put_line ('SQLERRM: ' || SQLERRM);
        --END needed
        END;
    END LOOP;
END;
/
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);