Why setting Default Tablespace on the Database affects some Oracle Internal Users

Just following on from my previous post, I’ve done a bit of investigation to find out why issuing ALTER DATABASE DEFAULT TABLESPACE tsname affects some Oracle Internal users.
Remember, in the case of my standard Oracle XE installation, the offending users were DIP, MDSYS and TSMSYS.

Apparently, this command will only apply to users that have been created without a default tablespace being specified. This is how Oracle creates these users when it creates the XE database.

So, once you’ve changed the default tablespace on the database in this way, it’s advisable to go through the Oracle internal users and set the default tablespace back to what it was previously.


Sociable PL/SQL – associative arrays indexed by VARCHAR2

PL/SQL tables are a really good way of array processing in PL/SQL. In 99% of cases, using a table indexed by PLS_INTEGER works a treat, but what about those times when life would be that bit easier if you could index by VARCHAR2 ?

I was recently presented with one such instance, courtesy of some unconventional table design by now long-departed wise-men.
These gurus had decided to use a synthetic key on a reference table…except the natural key was still in the table….but the actual primary key was half of the natural key plus the synthetic key.

Oh how I would’ve loved to meet these ancients and received the benefit of their wisdom, with the aid of a spot-light, some gaffer tape…and a very sharp stick.

This is a theme I will return to in more detail in a future post ( yep, the 10 things that really wind me up post, coming after the quick ‘n’ dirty tuning post I promised a couple of months ago).

Anyway, back to the PL/SQL table madness.

In this example, we have a table called COUNTRIES – not very original, but hey, the pub’s open so we haven’t got time to hang around.

CREATE TABLE countries(
code VARCHAR2(3),
country VARCHAR2(50))

The COUNTRY table has the following records :-

SQL> select * from countries;
---- -----------------------

9 rows selected.

Now, we have a situation where we want to use the country code for three of those countries, we know which three we want to get. But we then want to use them programatically elsewhere without having to faff about working out which order they are in in our array….

-- associate.sql
-- Script to demonstrate a PL/SQL associative array 
-- indexed by something other  than a number.
set serveroutput on
   TYPE typ_country_codes IS TABLE OF countries.code%TYPE 
      INDEX BY countries.country%TYPE;
   tbl_country_codes typ_country_codes;
   -- In this instance, we want all the English speaking 
   -- countries... except for the USA
   -- To paraphrase the colonel in Apocalypse Now - 
   -- "Yankee don't play cricket !"
   CURSOR c_countries IS
      SELECT code, country
      FROM countries
      WHERE country in ('AUSTRALIA', 'NEW ZEALAND', 
         'UNITED KINGDOM');
   FOR r_countries IN c_countries LOOP
      -- assign the code to the table using the value of 
      -- the country column as the index
      tbl_country_codes(r_countries.country) := 
   DBMS_OUTPUT.PUT_LINE( 'Country code for Australia is '
   DBMS_OUTPUT.PUT_LINE( 'Country code for New Zealand is '
      ||tbl_country_codes('NEW ZEALAND'));
   DBMS_OUTPUT.PUT_LINE( 'Country code for United Kingdom is '
      ||tbl_country_codes('UNITED KINGDOM'));

And that’s stumps.
Please let me know if you find a less tortuous circumstance in which you find this useful.

Des Cartes Must Die – a Killer App for PL/SQL

Text books, manuals, experts and assorted gurus are fairly insistent on the point that it’s probably a good idea to keep your OLTP systems and Data Warehouses in separate instances ( ideally on separate servers).
Meanwhile, back in the real world, you’ve got a bit of a problem.
Not only do you have a Data Warehouse competing with your OLTP App for resources on the same instance, the DW users have access to the database via SQL and are extremely reluctant to give it up.
Your reasoned explanation as to why this is a bad idea and that you can’t guarantee performance of any application on the database as a result has fallen on deaf ears.
The application often grinds to a halt when one of the DW users generates a cartesian product, or just runs a humping great query at a peak time.

The process for killing such rogue sessions has been complicated somewhat by your company’s enthusiastic embracing of outsourcing. Indeed, getting this done involves the user raising a call with the help desk, who pass it to you. You have to raise another call to the DBA’s via a call-management system and hope that someone is up as it’s early/late/the middle of the night wherever they are.
Even if all runs smoothly, it still takes around half-an-hour to get the runaway session terminated and all the while, users are complaining about performance and that clammy feeling on the back of your neck is the laboured breathing of a boss who JUST WANTS IT FIXED.

Having gone through this particular loop several times it becomes apparent that (a) no-one is going to sign-off on the new hardware/Oracle license/time and effort to split out these warring applications and (b) the fact that you can’t guarantee performance for anything running on this setup is accepted without question… right up to the time things grind to a halt. All of which means that desperate measures are called for.

Yep, the users running those rogue queries are going to get the facility to kill their own sessions.

At this point, it’s probably worth outlining a couple of requirements ( if only to stop your DBA hyperventilating after reading that last sentence) :-

  1. Users need to be able to terminate their own sessions WITHOUT being granted the ALTER SYSTEM privilege
  2. Users will only be allowed to kill their own sessions, not anyone elses
  3. Users won’t be able to kill the current session as that will obviously be the result of a user error
  4. Users need to see enough information about their own current sessions to work out which one they want to kill.

Now Baldrick, the raw materials for this cunning plan are :-

  • A Global Temporary Table
  • A stored procedure to populate the GTT
  • A stored procedure to kill the session

Our target audience is proficient enough in SQL that you can point them at the DBMS_APPLICATION_INFO package and get them to add identifiers to their sessions using this package.

Now for our GTT :-

audsid NUMBER,
status VARCHAR2(8),
program VARCHAR2(64),
session_name VARCHAR2(48),
action VARCHAR2(32),
client_info VARCHAR2(64) )

Simple enough, create a Global Temporary Table where all the rows are cleared down at the end of a transaction.
Granting Select, Insert, and Delete on this table will enable a user to see their own rows in this table, but only their own.
The GTT consists of columns taken from V$SESSION. You may have noticed that these do not include the sid and serial# that you need to kill the session. Well, you can get this from V$SESSION by including AUDSID in the predicate, so we’ll keep things simple by allowing the user to work off one piece of information rather than two.
NOTE – the module column in V$SESSION has been changed to SESSION_NAME in this table so it’s obvious to users where the session name they set using DBMS_APPLICATION_INFO can be found.
Next step – give the users a mechanism to populate the table.

   -- First cleardown the table without creating a 
   -- transaction boundary i.e. can't do a 
   -- TRUNCATE here
   DELETE FROM tmp_user_sessions;
   -- Now populate the table with details of the 
   -- current sessions
   INSERT INTO tmp_user_sessions
   SELECT audsid, status, program, module, 
      action, client_info
   FROM v$session
   WHERE username = USER;

Nice and simple to call this, no parameters required, simply exec get_session_info or, if they’re TOAD ( or SQLDeveloper) users – begin get_session_info; end;

Now they should have some data to look at in the table.

SQL> exec get_session_info

PL/SQL procedure successfully completed.

SQL> select audsid, session_name
2 from tmp_user_sessions;

---------- --------------------------------
1771 SQL*Plus
1765 SQL Developer
1770 huge query


Now all they need to do is pass the relevant audsid value to the killer procedure, which looks like this :-

   p_audsid IN NUMBER) IS
   l_sid v$session.sid%TYPE;
   l_serial v$session.serial#%TYPE;
   l_uname v$session.username%TYPE;

   CURSOR c_target( cp_audsid NUMBER) IS
      SELECT sid, serial#, username
      FROM v$session
      WHERE audsid = cp_audsid;

   l_command VARCHAR2(200);
   e_current_session EXCEPTION;
   e_invalid_session EXCEPTION;
   e_session_owner EXCEPTION;

   -- First, make sure that we're not trying to kill the 
   -- current session
   IF p_audsid = USERENV('sessionid') THEN
      RAISE e_current_session;
   END IF;
   -- Now get the session details
   OPEN c_target( p_audsid);
   FETCH c_target INTO l_sid, l_serial, l_uname;
   IF c_target%NOTFOUND THEN
      CLOSE c_target;
      RAISE e_invalid_session;
   END IF;
   CLOSE c_target;
   -- Check that the user actually owns the session 
   -- we're going to kill
   IF l_uname != USER THEN
      RAISE e_session_owner;
   END IF;
   -- If we're still here, then kill the session
   l_command := 'ALTER SYSTEM KILL SESSION '
      ||''''||l_sid||', '||l_serial||''''||' IMMEDIATE';
   EXECUTE IMMEDIATE( l_command);
   WHEN e_current_session THEN
         'You cannot kill the current session.');
   WHEN e_invalid_session THEN
         'Could not find this session.');
   WHEN e_session_owner THEN
         'Cannot Kill a session you do not own.');

NOTE – this procedure MUST be created under a schema which has ALTER SESSION granted to it directly – i.e. not via a role.

You can then grant execute on this procedure to the relevant users.

So, now the user can kill their own “huge query” by simply entering :-

SQL> exec kill_session(1770)

PL/SQL procedure successfully completed.


The target session will then be terminated and the next time the user interacts with it they should get an ORA-03113 : end-of-file on communication channel.

Whilst this utility isn’t a panacea, it does make the time lag between recognising the approach of Mr Des Cartes, and his ultimate demise, somewhat shorter.

The computer has forgotten my password !

The quote in the title of this entry is from my all-time favourite support call, made to be by a very irate and hassled user.
Taking pity on their plight, I dealt with the call and refrained from pointing out the flaws in this assertion.

Several years later, I had cause to remember this incident, although the circumstances were very different.

The place I was working at had gone in for outsourcing…in a big way. Whilst we still had control of the application owner account, our DBAs were in Madrid; Unix admins in Switzerland and other development teams in Poland.
As the application owners, we were in the process of rolling out a major enhancement to the app.
All was going well, we’d rehearsed the rollout several times previously and most of the 300-odd scripts had been completed without a hitch.
We then came to a small change on one of the other applications on the machine, required to account for the changes we were making to the main application.
At this point, several things became apparent :-
the password we had for the user we needed to run these scripts as wasn’t correct
the person or persons who had this information were definitely unavailable ( it was Saturday afternoon)
changing the password on the account could potentially cause issues with batch jobs connecting as this user ( although we had no idea what these could be as we didn’t know the application in question).
Faced with the prospect of having to rollback the release and come back the following Saturday ( F.A. Cup Final day, no less), we opted to “cheat”.

Utilizing that useful little dodge known as “identified by values” we were able to change the relevant password, connect as the user and run the scripts and then change the password back to it’s original value, without ever having to know what that value was.

For an illustration of this technique, I used an account which had CREATE USER, ALTER USER, and SELECT ANY DICTIONARY privileges.

Let’s start by creating a user :-

SQL> grant connect to mystery identified by secret;
Grant succeeded.

Now, we can look at DBA_USERS to find a hash value for the password :-

SQL> select password
2 from dba_users
3 where username = 'MYSTERY';

To change the password for this user, it’s simply a matter of :-

SQL> alter user mystery identified by iknowurpwd;
User altered.

Once you’ve done what you need to as this user and need to reset the password to it’s original value :-

SQL> alter user mystery identified by values '33CF630B57549B54';
User altered.

And hey presto, off to the pub ( and next weekend is still free to watch the footie !)

You Don’t Have to be Posh to be Privileged

Working in multiple environments, I sometimes get tripped up by the RDBMS telling me a program unit doesn’t exist when I know it does.
What with system privileges either being granted directly, or via roles, I find the following query really useful in working out just what privileges I’ve got on an environment.

SELECT distinct privilege
FROM dba_sys_privs
WHERE grantee = USER
OR grantee IN (
    SELECT granted_role
    FROM dba_role_privs
    START WITH grantee = USER
    CONNECT BY PRIOR granted_role = grantee)

As a SQL Developer user, I drop this into my User Defined Reports folder so it’s always handy.