Monday, November 28, 2011

Password_Verify_Function (Part 1)

This example was tested on Oracle 10 on Linux. It shows you how to create a simple password verify function (PVF). First connect as the SYS user because a PVF must be owned by SYS:

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> 


Create a profile to use the PVF:

SQL> create profile andrews_profile
  2  limit password_life_time 60
  3  /

Profile created.

SQL>

Try to give my new profile a PVF called my_verify_function. This fails as it has not been created yet:

SQL> alter profile andrews_profile limit
  2  password_verify_function my_verify_function
  3  /
alter profile andrews_profile limit
*
ERROR at line 1:
ORA-07443: function MY_VERIFY_FUNCTION not found

SQL>

Create a function with the required name. A PVF must have the parameters shown. It should be obvious what this PVF is supposed to do:

SQL> create or replace function my_verify_function (
  2  username     varchar2,
  3  password     varchar2,
  4  old_password varchar2)
  5  return boolean as
  6  begin
  7  if length(password) < 4 then
  8    return false;
  9  else
 10    return true;
 11  end if;
 12  end my_verify_function;
 13  /

Function created.

SQL>

Change the new profile to use this PVF:

SQL> alter profile andrews_profile limit
  2  password_verify_function my_verify_function
  3  /

Profile altered.

SQL>

Create a user and assign it the new profile:

SQL> create user andrew identified by reid1
  2  profile andrews_profile
  3  /

User created.

SQL>

Change the user’s password. The new value is passed to the PVF and rejected:

SQL> alter user andrew identified by rei
  2  /
alter user andrew identified by rei
*
ERROR at line 1:
ORA-28003: password verification for the specified
password failed
ORA-28003: password verification for the specified
password failed

SQL>

The error message was not very helpful so change the PVF accordingly:

SQL> create or replace function my_verify_function (
  2  username     varchar2,
  3  password     varchar2,
  4  old_password varchar2)
  5  return boolean as
  6  begin
  7  if length(password) < 4 then
  8   raise_application_error
  9   (-20000, 'Password < 4 characters long');
 10  else
 11   return true;
 12  end if;
 13  end my_verify_function;
 14  /

Function created.

SQL>

Try to change the password again. This time the reason for rejection is clear:

SQL> alter user andrew identified by rei
  2  /
alter user andrew identified by rei
*
ERROR at line 1:
ORA-28003: password verification for the specified
password failed
ORA-20000: Password < 4 characters long

SQL>

Fix the problem and try again:

SQL> alter user andrew identified by reid2
  2  /

User altered.

SQL>

Tuesday, November 15, 2011

Global Temporary Tables (Part 2)

You can use a global temporary table (GTT) simultaneously in multiple sessions. Each of these sessions can delete, insert, select and update data in the GTT but other sessions cannot see that data. To put it in other words, each session can only see its own data. This example demonstrates this and also looks more closely at ORA-14452. It uses two sessions: session 1 (in red) and session 2 (in blue) which run concurrently. First create a GTT, which retains data after a commit statement, in session 1:

SQL> prompt Starting session 1
Starting session 1
SQL> conn /
Connected.
SQL> show user
USER is "ORACLE"
SQL> create global temporary table gtt1
  2  (session_name varchar2(12))
  3  on commit preserve rows
  4  /

Table created.
 SQL>

Add a single row to the GTT, do a commit then check that the row is still there:

SQL> insert into gtt1 values('Session 1')
  2  /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from gtt1
  2  /

SESSION_NAME
------------
Session 1

SQL>

Check the GTT's object_id for future reference reference:

SQL> select object_id  from dba_objects
  2  where object_name = 'GTT1'
  3  /

 OBJECT_ID
----------
    235348

SQL> accept stop prompt 'Go to session 2'
Go to session 2

Now go to session 2 as instructed by the SQL prompt. Check that you can see the GTT and that it has the same object_id:

SQL> prompt Starting session 2
Starting session 2
SQL> conn /
Connected.
SQL> show user
USER is "ORACLE"
SQL> desc gtt1
Name                       Null?    Type
-------------------------- -------- ------------------
SESSION_NAME                        VARCHAR2(12)

SQL> select object_id from dba_objects
  2  where object_name = 'GTT1'
  3  /

 OBJECT_ID
----------
    235348

SQL>

Confirm that you cannot see the data from session 1, insert a row for session 2 and check that you can see it:

SQL> select * from gtt1
  2  /

no rows selected

SQL> insert into gtt1 values('Session 2')
  2  /

1 row created.

SQL> select * from gtt1
  2  /

SESSION_NAME
------------
Session 2

SQL>

Truncate the GTT. Check that the row belonging to session 2 has gone. We will check later to see that the row from session 1 has not been affected:

SQL> truncate table gtt1
  2  /

Table truncated.

SQL> select * from gtt1
  2  /

no rows selected

SQL>

Try to drop the table. This fails as session 1 is still using it:
  
SQL> drop table gtt1
  2  /
drop table gtt1
          *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index
on temporary table already in use

SQL> prompt Go back to session 1
Go back to session 1

Now go back to session 1 as instructed. Check that the truncate, which session 2 did, has not removed the row for session 1:

SQL> prompt Resuming session 1
Resuming session 1
SQL> select * from gtt1
  2  /

SESSION_NAME
------------
Session 1

SQL>

Try to drop the GTT again as session 1 is the only one using it:

SQL> drop table gtt1
  2  /
drop table gtt1
          *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index
on temporary table already in use

SQL>

That failed so look up the error message at the UNIX prompt (sorry the font is so small):

TEST9 > oerr ora 14452
14452, 00000, "attempt to create, alter or drop an index on temporary table already in use"
// *Cause:  An attempt was made to create, alter or drop an index on temporary
//          table which is already in use.
// *Action: All the sessions using the session-specific temporary table have
//          to truncate table and all the transactions using transaction
//          specific temporary table have to end their transactions.
TEST9 >

Here is the key point relating to this example: 

sessions using the session-specific temporary table have to truncate table
Session 2 has already done a truncate so we need to stay in session 1 and do a truncate there:


SQL> truncate table gtt1
  2  /

Table truncated.

SQL>

Now we can drop the GTT:

SQL> drop table gtt1
  2  /

Table dropped.

SQL>

Monday, November 14, 2011

Global Temporary Tables (Part 1)

You can use these to store data temporarily. I had never used them before and decided to try them out. I hit a problem straight away:

SQL> conn /
Connected.
SQL> show user
USER is "ORACLE"
SQL> create global temporary table gtt
  2  as select * from dba_synonyms
  3  where 1 = 2
  4  on commit delete rows
  5  /
on commit delete rows
*
ERROR at line 4:
ORA-00933: SQL command not properly ended


SQL>

I couldn't find an example in my documentation using CTAS (create table as select) with a global temporary  table. I had a look on the Internet and it seems that several people have already had the same problem. The correct syntax is as follows:

SQL> create global temporary table gtt
  2  on commit delete rows
  3  as select * from dba_synonyms
  4  where 1 = 2
  5  /

Table created.

SQL> desc gtt
Name                       Null?    Type
-------------------------- -------- ------------------
OWNER                      NOT NULL VARCHAR2(30)
SYNONYM_NAME               NOT NULL VARCHAR2(30)
TABLE_OWNER                         VARCHAR2(30)
TABLE_NAME                 NOT NULL VARCHAR2(30)
DB_LINK                             VARCHAR2(128)

SQL>


As the name suggests, you can use these tables for temporary storage. I'm not sure why you might want to save the contents of dba_synonyms but it's only an example:


SQL> insert into gtt select * from dba_synonyms
  2  /

12401 rows created.

SQL> select count(*) from gtt
  2  /

  COUNT(*)
----------
    12401

SQL>


If you specify on commit delete rows when you create the temporary table, the rows disappear when you do a commit, as you might expect:

SQL> commit;

Commit complete.

SQL> select count(*) from gtt
  2  /

  COUNT(*)
----------
         0

SQL>


The same thing happens after an implied commit:

SQL> insert into gtt select * from dba_synonyms
  2  /

12401 rows created.

SQL> select count(*) from gtt
  2  /

  COUNT(*)
----------
    12401

SQL> grant select on gtt to system
  2  /

Grant succeeded.

SQL> select count(*) from gtt
  2  /

  COUNT(*)
----------
        0

SQL>


To stop this happening, you have to specify on commit preserve rows instead when you create the table:

SQL> drop table gtt
  2  /

Table dropped.

SQL> create global temporary table gtt
  2  on commit preserve rows
  3  as select * from dba_synonyms
  4  where 1 = 2
  5  /

Table created.

SQL> insert into gtt select * from dba_synonyms
  2  /

12401 rows created.

SQL>


Oracle allows you to use commit work instead of commit to keep in line with standard SQL syntax. Both statements work in the same way:

SQL> commit work;

Commit complete.

SQL>


This time the rows remain in the table:

SQL> select count(*) from gtt
  2  /

  COUNT(*)
----------
     12401

SQL>


And they will stay there for the duration of the session (unless you delete them on purpose). So you cannot drop the table at this point:

SQL> drop table gtt
  2  /
drop table gtt
          *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index
on temporary table already in use

SQL>


However, if you end the session by logging into a new one:

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL>


Then log back in as the original user, the table is there but the contents have gone. (It is the table's contents which are temporary, not the table itself.)

SQL> conn /
Connected.
SQL> show user
USER is "ORACLE"
SQL> select count(*) from gtt
  2  /

  COUNT(*)
----------
        0

SQL>


And now you can drop the table:

SQL> drop table gtt
  2  /

Table dropped.

SQL>

ORA-25153

If you try to sort anything other than a small amount of data, and the sort has to use disk space, you may see the following error message:
 
SQL> select a.table_name, b.table_name
  2  from dba_tables a, dba_tables b
  3  order by 1;
from dba_tables a, dba_tables b
     *
ERROR at line 2:
ORA-25153: Temporary Tablespace is Empty
 
SQL>
 
This will happen if you:
 
(1)    Copy the datafiles from a source to a target database.
(2)    Recreate the target database’s control file.
(3)    Forget to add a file to its temporary tablespace.
 
You can diagnose this fault as follows. First you have to find the name(s) of the target database’s temporary tablespace(s). In this example there is only one:
 
SQL> select distinct temporary_tablespace
  2  from dba_users
  3  /
 
TEMPORARY_TABLESPACE
------------------------------
TEMP
 
SQL>
 
Then you have to see if it contains any temp files:
 
SQL> select file_name from dba_temp_files
  2  where tablespace_name = 'TEMP'
  3  /
 
no rows selected
 
SQL>
 
If it doesn’t, you have to add one. On this occasion, there was still an old tempfile so I was able to reuse it:
 
  1  alter tablespace temp add tempfile
  2  'test10/andrew/temp_files/temp01.dbf'
  3* reuse
SQL> /
 
Tablespace altered.
 
SQL>
 
Then the sort should work:
 
SQL> col table_name format a25
SQL> l
  1  select a.table_name, b.table_name
  2  from dba_tables a, dba_tables b
  3* order by 1
SQL> /
 
TABLE_NAME                TABLE_NAME
------------------------- -------------------------
ACCESS$                   AQ$_ALERT_QT_H
ACCESS$                   WRI$_DBU_FEATURE_USAGE
ACCESS$                   WRI$_ALERT_HISTORY
ACCESS$                   AQ$_ALERT_QT_T
ACCESS$                   WRH$_FILESTATXS_BL
ACCESS$                   AQ$_ALERT_QT_G
ACCESS$                   AQ$_ALERT_QT_I
ACCESS$                   WRH$_FILESTATXS
ACCESS$                   WRH$_WAITSTAT
ACCESS$                   WRH$_TEMPSTATXS
ACCESS$                   WRH$_SQLSTAT
Etc

Saturday, November 12, 2011

Mutating Triggers

This post gives an introduction to mutating triggers and was tested on an Oracle 9 database. It contains points which I do not fully understand yet. When I do, I will come back and update it. First, create a table and add some data to it:
 
SQL> CREATE TABLE andrews_table
  2   (employee VARCHAR2(10))
  3  /
 
Table created.
 
SQL> INSERT INTO andrews_table
  2   VALUES ('SMITH')
  3  /
 
1 row created.
 
SQL> INSERT INTO andrews_table
  2   VALUES ('JONES')
  3  /
 
1 row created.
 
SQL> SELECT * FROM andrews_table
  2  /
 
EMPLOYEE
----------
SMITH
JONES
 
Now create a mutating trigger. It fires for each row while the table is being modified so Oracle does not know what value to assign to employee:
 
SQL> CREATE TRIGGER andrews_trigger
  2   AFTER UPDATE ON andrews_table
  3   FOR EACH ROW
  4   BEGIN
  5   UPDATE andrews_table
  6   SET employee = 'BLOGGS';
  7   END;
  8  /
 
Trigger created.
 
SQL> UPDATE andrews_table
  2   SET employee = 'BROWN'
  3  /
UPDATE andrews_table
       *
ERROR at line 1:
ORA-04091: table SYSTEM.ANDREWS_TABLE is
mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2
ORA-04088: error during execution of trigger
'SYSTEM.ANDREWS_TRIGGER'
 
SQL>
 
So when you look at the data, it has not changed:
 
SQL> SELECT * FROM andrews_table
  2  /
 
EMPLOYEE
----------
SMITH
JONES
 
SQL>
 
If you remove FOR EACH ROW, the trigger appears to fire after the update and sets up an infinite loop until Oracle notices what has happened:
 
SQL> CREATE OR REPLACE TRIGGER andrews_trigger
  2   AFTER UPDATE ON andrews_table
  3   BEGIN
  4   UPDATE andrews_table
  5   SET employee = 'GREEN';
  6   END;
  7  /
 
Trigger created.
 
SQL> UPDATE andrews_table
  2   SET employee = 'BLACK'
  3  /
UPDATE andrews_table
       *
ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels
(50) exceeded
ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2
ORA-04088: error during execution of trigger
'SYSTEM.ANDREWS_TRIGGER'
ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2
ORA-04088: error during execution of trigger
'SYSTEM.ANDREWS_TRIGGER'
ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2
ORA-04088: error during execution of trigger
'SYSTEM.ANDREWS_TRIGGER'
ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2
ORA-04088: error during execution of trigger
'SYSTEM.ANDREWS_TRIGGER'
ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2
ORA-04088: error during execution of trigger
'SYSTEM.ANDREWS_TRIGGER'
ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2
ORA-04088: error during execution of trigger
'SYSTEM.ANDREWS_TRIGGER'
ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2
ORA-04088: error during execution of trigger
'SYSTEM.ANDREWS_TRIGGER'
ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2
ORA-04088: error during execution of trigger
'SYSTEM.ANDREWS_TRIGGER'
ORA-06512: at "SYSTEM.A
 
SQL>
 
And again, the data has not changed:
 
SQL> SELECT * FROM andrews_table
  2  /
 
EMPLOYEE
----------
SMITH
JONES
 
SQL>
 
Even if the trigger only reads the table, Oracle still says it is mutating:
 
SQL> CREATE OR REPLACE TRIGGER andrews_trigger
  2   AFTER UPDATE ON andrews_table
  3   FOR EACH ROW
  4   DECLARE counter NUMBER;
  5   BEGIN
  6   SELECT COUNT(1) INTO counter FROM andrews_table;
  7   END;
  8  /
 
Trigger created.
 
SQL> UPDATE andrews_table
  2   SET employee = 'WHITE'
  3  /
UPDATE andrews_table
       *
ERROR at line 1:
ORA-04091: table SYSTEM.ANDREWS_TABLE is
mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 3
ORA-04088: error during execution of trigger
'SYSTEM.ANDREWS_TRIGGER'
 
SQL>
 
If you run oerr ora 04091 on the server, the output makes it clear that this is supposed to happen:
 
TEST9 > oerr ora 04091
04091, 00000, "table %s.%s is mutating, trigger/function may not see it"
// *Cause: A trigger (or a user defined plsql function that is referenced in
//         this statement) attempted to look at (or modify) a table that was
//         in the middle of being modified by the statement which fired it.
// *Action: Rewrite the trigger (or function) so it does not read that table.
TEST9 >
 
And the data still has not changed:
 
SQL> SELECT * FROM andrews_table
  2  /
 
EMPLOYEE
----------
SMITH
JONES
 
SQL>
 
But if the trigger only reads the table and you remove FOR EACH ROW, Oracle fires the trigger once after the update, you do not set up an infinite loop and the update works:
 
SQL> CREATE OR REPLACE TRIGGER andrews_trigger
  2   AFTER UPDATE ON andrews_table
  3   DECLARE counter NUMBER;
  4   BEGIN
  5   SELECT COUNT(1) INTO counter FROM andrews_table;
  6   END;
  7  /
 
Trigger created.
 
SQL> UPDATE andrews_table
  2   SET employee = 'KING'
  3  /
 
2 rows updated.
 
SQL>
 
And finally, the data has changed:
 
SQL> SELECT * FROM andrews_table;
 
EMPLOYEE
----------
KING
KING
 
SQL>