Monday, January 5, 2015

Changing session settings automatically for a user when they login

We needed to set some parameters for specific users when they logged in since the client didn't want them changed at database level.

This was achieved by using login triggers to run commands when the specific user logged in.

Hopefully this won't need much explanation:

create or replace TRIGGER set_params_at_login
after logon on database

DECLARE
  lv_username VARCHAR2(100) := NULL;
begin
  lv_username := UPPER(SUBSTR(SYS_CONTEXT('USERENV', 'SESSION_USER'),
                              1,
                              100));

   /* Oracle Parameters for USER1 to reduce CPU */

   /* USER1 */
    if NVL(lv_username, 'X') IN ('USER1' ) then
      execute immediate 'alter session set optimizer_features_enable=''10.2.0.5''';
      execute immediate 'alter session set "_unnest_subquery"=true';
      execute immediate 'alter session set query_rewrite_enabled=false';
      execute immediate 'alter session set optimizer_index_cost_adj=100';
      execute immediate 'alter session set optimizer_index_caching=50';
    end if;

  exception
  when others then
    null;
end;

Monday, December 15, 2014

Patching APEX

I noticed my version of APEX was a bit out of date, so decided to patch it to the latest version.

It took me a bit of time to do it, because I was looking in the wrong place. If you go to the APEX download page -

http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

You can download the latest version - but don't, because if you already have V4 installed, it won't work.

If you download it and try to run @apexins sysaux sysaux temp /i/ , you will get this:

Error: This version of Application Express is already installed (APEX_040200).
declare
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 9



You actually need to download a patch and apply that. But don't go to the Oracle site and try to search for the latest APEX patch there, you'll just end up going round in circles.

Instead, open the Release Notes from the download page, and in there you will find a reference to the patch number:

2.2 Downloading and Extracting the Installation Software

Download the patch set to a computer with access to the Oracle Database. You will also need access to the Oracle home.

To download and extract the patch set installation software:

    Download the p19595896_426_Generic.zip patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.


So go to the Oracle Support site and search for this number in the patches tab.

Once downloaded, just read the instructions in the notes.

Short version - cd to the directory the patch was extracted.

cd /tmp/apex/patch



You might want to take a database backup here, just in case.

Run the script to apply the patch (go and get a coffee, this takes a while):

sqlplus> @apxpatch.sql

Then

sqlplus> @apxldimg.sql /tmp/apex/patch

Log into APEX and the new version number should be shown in the bottom right of the page.