PL/SQL: Sleep without using DBMS_LOCK

If you want to wait in your PL/SQL program (“sleep”) you may want to use the procedure “SLEEP” from the package “DBMS_LOCK“. While this function does exactly what you want, your DBA may not grant you access to this package, because it contains some other mighty and dangerous procedures.

But there is help! Here are some valid alternatives:

Option 1: APEX_UTIL.sleep

If APEX is installed you can use the procedure “PAUSE” from the publicly available package APEX_UTIL.

Example – “Wait 5 seconds”:

SET SERVEROUTPUT ON ;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Start ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
    APEX_UTIL.PAUSE(5);
    DBMS_OUTPUT.PUT_LINE('End   ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
/

Option 2: java.lang.Thread.sleep

An other option is the use of the method “sleep” from the Java class “Thread”, which you can easily use through providing a simple PL/SQL wrapper procedure:

CREATE OR REPLACE PROCEDURE SLEEP (P_MILLI_SECONDS IN NUMBER) 
AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)';

Note: Please remember, that “Thread.sleep” uses milli seconds!

Example – “Wait 5 seconds”:

SET SERVEROUTPUT ON ;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Start ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
    SLEEP(5 * 1000);
    DBMS_OUTPUT.PUT_LINE('End   ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
/

 

I will speak at the “DOAG 2015 Conference + Exhibition” (“DOAG 2015 Konferenz + Ausstellung”)

I will be on the “DOAG 2015 Conference + Exhibition” in Nürnberg (Nuremberg), Germany from November 17th to 19th.

I will speak at November 18th, at 10 am, room “Konferenzraum EG”:
Was Sie bei modernen Datenbank-Systemen anders machen müssen
(English: “What you need to do differently in modern database systems”)

The DOAG is the largest German Oracle user group with over 6000 members.

DOAG 2015 Konferenz + Ausstellung

DOAG 2015 Conference + Exhibition

SQL-Developer: How to prevent the “Java Heap Space” error

When you are working with a lot of worksheets and reports or try to open large SQL files, you may run into “Java Heap Space” errors.

To fix this problem you need to increase the Xmx (maximum heap size).

Here is how:

  1. Close all open SQL-Developer instances.
  2. Find the “sqldeveloper.conf” file
    1. Windows:
      1. goto the directory where the SQL-Developer program is installed.
      2. goto the subdirectory “sqldeveloper\bin”
    2. Mac OS X:
      1. goto your applications folder (“/Applications”)
      2. open the SQLDeveloper-App via context menu (“right mouse click”)
      3. goto the subdirectory “Contents/Resources/sqldeveloper/sqldeveloper/bin”
  3. Open the file “sqldeveloper.conf” with the text editor of your choise.
  4. Add a new line with the following text to the file:
    AddVMOption -Xmx1024M
  5. Save the file.

Oracle Advanced Queuing Series: 1. Create Message Type, AQ Table and Queue

Welcome to the first part of the series “Oracle Advanced Queuing”.

We start with the basic elements of Oracle Advanced Queuing:
the message type, the queue table and finally the queue.

  1. Create a Type for your messages:
    CREATE TYPE my_queue_typ  AS OBJECT(message_text VARCHAR2(4000));
  2. Create an AQ Table based on your type:
    EXEC DBMS_AQADM.CREATE_QUEUE_TABLE('my_queue_table', 'my_queue_type');
  3. Create a Message Queue with your table:
    EXEC DBMS_AQADM.CREATE_QUEUE('my_queue', 'my_queue_table');
  4. Start the queue:
    EXEC DBMS_AQADM.START_QUEUE('my_queue');

Missing statements in SQL Monitoring

Since Oracle 11g, the database offers real-time SQL Monitoring, a great feature for insights of currently and previously running queries.
In most cases this works great without effort, but sometimes your statements won’t show.
This can be caused by statements with very large execution plans or statements with short execution times.

Monitor statements with very large execution plans

To include statements with very large execution plans in SQL Monitoring, we need to increase two thresholds. The following values served me well, but your millage may vary:

ALTER SESSION SET "_SQLMON_MAX_PLAN"=4020;
ALTER SESSION SET "_SQLMON_MAX_PLANLINES"=4000;

Remember to execute these commands in the same session and before the statements you want to monitor!

Monitor short running statements

To include short running statements into SQL Monitoring, we can use the MONITOR hint.

example:
SELECT /*+ MONITOR*/ * FROM DUAL;

Remeber that this hint is only valid when the parameter CONTROL_MANAGEMENT_PACK_ACCESS is set to DIAGNOSTIC+TUNING.

Execlude statements from SQL Monitoring

To exclude statements from SQL Monitoring, we can use the NO_MONITOR hint.

example:
SELECT /*+ NO_MONITOR*/ count(*) FROM DBA_OBJECTS CROSS JOIN DBA_OBJECTS;

Access SQL Monitoring

The SQL Monitoring informations can be accessed through

  • the tables V$SQL_MONITOR and V$SQL_PLAN_MONITOR
    (RAC:  GV$SQL_MONITOR and GV$SQL_PLAN_MONITOR)
  • a PL/SQL procedure:
    variable l_rep CLOB;
    exec :l_rep := DBMS_SQLTUNE.REPORT_SQL_MONITOR();
    print :l_rep
  • or with Oracle Enterprise Manager

SQL-Developer: Migrate settings files (settings, code templates, snippets, connections etc.)

If you, like me, have a lot of personal settings, code templates, snippets, connections etc. in SQL-Developer, you may want to migrate them to a new version or another computer from time to time. Here are the most important configuration files of SQL-Developer for migrations and backup:

User dependent files

  • Code templates:
    %USERPROFILE%\Application Data\SQL Developer\CodeTemplate.xml
  • Snippets:
    %USERPROFILE%\Application Data\SQL Developer\UserSnippets.xml
  • SQL history:
    %USERPROFILE%\Application Data\SQL Developer\SqlHistory.xml

SQL-Developer version dependent files

The numbers in the following paths vary with the version of SQL-Developer you have installed!
Please look for the appropriate directories of your installation.

  • Accelerators:
    %USERPROFILE%\Application Data\SQL Developer\system3.0.04.34\o.ide.11.1.1.4.37.59.31\settings.xml
  • Connections:
    %USERPROFILE%\Application Data\SQL Developer\system3.0.04.34\o.jdeveloper.db.connection.11.1.1.4.37.59.31\connections.xml
  • Syntax highlights:
    %USERPROFILE%\Application Data\SQL Developer\system3.0.04.34\o.sqldeveloper.11.1.2.4.34\product-preferences.xml

SQL-Developer: Change GUI language to english

SQL-Developer uses the same language for its GUI that you have set for your operation system. But when the GUI has another language than English, it’s hard to match it with help texts and the documentation!

It gets even worse! Some languages, e.g. German, don’t have all the menu entries the English GUI has!

So, if don’t like that you would have to change your operation system language, because you can’t set the GUI language in the preferences of SQL-Developer.

But there is a remedy for that – all you have to do is to add single line of text to a SQL-Developer configuration file.

Here is how:

  1. Close all open SQL-Developer instances.
  2. Find the “sqldeveloper.conf” file
    1. Windows:
      1. goto the directory where the SQL-Developer program is installed.
      2. goto the subdirectory “sqldeveloper\bin”
    2. Mac OS X:
      1. goto your applications folder (“/Applications”)
      2. open the SQLDeveloper-App via context menu (“right mouse click”)
      3. goto the subdirectory “Contents/Resources/sqldeveloper/sqldeveloper/bin”
  3. Open the file “sqldeveloper.conf” with the text editor of your choise.
  4. Add a new line with the following text to the file:
    AddVMOption -Duser.language=en
  5. Save the file.

The next time you start SQL-Developer, you will see that the GUI language is now english.