Oracle

If you are on an 11g database, you can ignore this post and just use the new ddl_lock_timeout parameter to accomplish what this post explains.

“alter session set ddl_lock_timeout=60” – will effectively allow ddl statements to all wait 60 seconds before encountering the error.

If you are on 10g or lower, here is an option:

If you are an Oracle programmer, then you’ve probably encountered the ORA-00054: resource busy and acquire with NOWAIT specified exception when attempting to truncate a table. When this happens during the day, you can usually just wait a few minutes (or go and commit your other open sessions…), but what about when you are truncating tables as part of a script which runs overnight? Are you tired of being woken up so you can simply rerun the script?

Well check out this script, then! It is a very simple PL/SQL procedure which accepts 1 or 2 arguments.

TABLE_NAME: string containing the table to truncate (include the schema prefix as well).
MAX_WAIT: number which is the maximum amount of minutes you would want to wait (you can ignore this parm and it will default to 10)

The script does the following:

1. Check if it has been running for more than the max wait time – if so, exit with a custom exception.
2. Truncate the table sent as a parameter
2a. If successful – EXIT.
2b. If truncate fails due to ORA-00054 exception, repeat steps 1 & 2 (this is a good place to put dbms_lock.sleep if you desire).
2b. If truncate fails for any other reason, simply pass the exception onto the calling program.

So instead of getting an error when a resource is busy – your code will wait for it to become available, then truncate it as soon as possible. And since you are executing procedures, you can easily use dbms_scheduler to allow your truncates to run concurrently. If you have enough of them, this will be a real time saver. But be careful because if you are doing a truncate and load you will want to have something that checks that the jobs are completed before beginning your load.

Please see the code below:

CREATE OR REPLACE PROCEDURE truncate_tbl(table_name IN VARCHAR2, max_wait IN NUMBER DEFAULT 10)
AS
   ld_start_time   DATE;
BEGIN
   ld_start_time   := SYSDATE;

  <>
   LOOP
      IF (SYSDATE - ld_start_time) < (max_wait/60/24) THEN
         BEGIN
           <>
            EXECUTE IMMEDIATE('TRUNCATE TABLE ' || table_name);

            EXIT truncate_loop;
         EXCEPTION
            WHEN OTHERS THEN
               IF SQLCODE = -54 THEN
                  NULL;
               ELSE
                  RAISE;
               END IF;
         END actual_code;
      ELSE
         RAISE_APPLICATION_ERROR(-20101, max_wait||' Minutes waited for table to be available from NOWAIT status.');
      END IF;
   END LOOP truncate_loop;
END truncate_tbl;

Then you can call it simple with

declare
begin
  truncate_tbl('SCHEMA.TABLE_NAME');
end;
/

If this article doesn’t help you, consider dropping me a note with your concern. I have thousands of lines of code for hundreds of Oracle problems I’ve faced and would publish ones that people told me would be helpful.

Updated November 8, 2013 – I have verified this on Windows 7 Enterprise Edition.

Have you ever wanted to see your Oracle output or log files open in your custom text editor instead of an IE window? I use Notepad++ (FREE) for text editing. Every time I open an output file or log file which is text it opens in IE.

This is undesirable for a couple of reasons: (I’m sure you can name more)

1. This means I have SEVERAL IE (Internet Explorer) windows open for each log I am viewing.
2. I cannot use the advanced features of my desktop application, I’m stuck with CTRL+F in IE, etc.

If you are in my boat, I have the cure for your woes.

Click Start -> Run -> then type “regedit” in the run box and press {ENTER}

You may be prompted to allow access to the application. Click “Yes,” if so.

Navigate to the object HKEY_CLASSES_ROOT\.txt\Content Type. It should have a value of ‘text/plain’. Go ahead and DELETE this object.

Now when you open text files in your browser, including when you click View Log or View Output from the Oracle Concurrent Request screen, text files open properly in your default OS application for text as long as you are using the Viewer: Text profile option set to “Browser” in your site.

Whatever your preferred text editor is, Notepad++, Ultraedit, Textpad, you can use it to view logs, compare files quickly and save them easily in a tabbed application.

If this article doesn’t help you, consider dropping me a note with your concern. I have thousands of lines of code for hundreds of Oracle problems I’ve faced and would publish ones that people told me would be helpful.

How many times have you been told by Oracle Support that you should run a concurrent request…only to realize that you have no idea which responsibility to choose to run the request? Or you want to instruct a user to run a request, and you want to be sure they have access?

The query below will help you. It will provide for you the responsibility name and the request group name which can run a concurrent program. You can search by the user_concurrent_program_name or the “short_name”.

SELECT FRT.RESPONSIBILITY_NAME, FRG.REQUEST_GROUP_NAME, FRG.DESCRIPTION
  FROM FND_REQUEST_GROUPS FRG
      ,FND_REQUEST_GROUP_UNITS FRGU
      ,FND_CONCURRENT_PROGRAMS FCP
      ,FND_CONCURRENT_PROGRAMS_TL FCPT
      ,FND_RESPONSIBILITY_TL FRT
      ,FND_RESPONSIBILITY FR
 WHERE     FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
       AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
       AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
       AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
       AND FCPT.SOURCE_LANG = USERENV('LANG')
       AND FCP.APPLICATION_ID = FCPT.APPLICATION_ID
       AND FCP.CONCURRENT_PROGRAM_ID = FCPT.CONCURRENT_PROGRAM_ID
       AND FR.APPLICATION_ID = FRT.APPLICATION_ID
       AND FR.RESPONSIBILITY_ID = FRT.RESPONSIBILITY_ID
       AND FRT.SOURCE_LANG = USERENV('LANG')
       AND FR.REQUEST_GROUP_ID = FRG.REQUEST_GROUP_ID
       AND FR.APPLICATION_ID = FRG.APPLICATION_ID
       --   AND FCP.CONCURRENT_PROGRAM_NAME = 'OPMTMOPG'  --  YOU CAN PUT THE SHORTNAME HERE
       AND FCPT.USER_CONCURRENT_PROGRAM_NAME LIKE 'Purge OPM Txns and Move Order Lines' --OR THE USER CONC PROGRAM NAME HERE
;

If this article doesn’t help you, consider dropping me a note with your concern. I have thousands of lines of code for hundreds of Oracle problems I’ve faced and would publish ones that people told me would be helpful.