Getting ORA-01403 in dba_segments query during a trigger

JP Kreps

Hi, Everyone --

I need to set up a process which will automatically start Oracle auditing on a table when it's created on a particular tablespace.

I've created an "after create on database" DDL trigger which will fire when (ORA_DICT_OBJ_TYPE = 'TABLE').

In this trigger, I use the DBMS_STANDARD package trigger attribute functions in a query against the dba_segments view to check if the new
table has been created in the targeted tablespace. Here' the query:

SELECT DISTINCT s.tablespace_name
INTO tablespace_name
FROM sys.dba_segments s
WHERE s.owner = ORA_DICT_OBJ_OWNER
AND s.segment_name = ORA_DICT_OBJ_NAME
AND s.segment_type IN ('TABLE', 'TABLE PARTITION')
AND s.tablespace_name = target_tablespace_name;

The only purpose of this query is to make sure that that table is contained in the targeted tablespace identified by the "target_tablespace_name" constant variable used in the WHERE clause. If the query runs without error, then my trigger creates a DBMS_SCHEDULER job that executes an "audit" command to start Oracle auditing on the new table.

To test the trigger, within the trigger code, I put the query inside a PL/SQL block with a "WHEN OTHERS" exception clause that will send formatted error detail to the alert log. Then I successfully create a test table on the targeted tablespace. The trigger fires, but the output shows that the query in my trigger failed with an "ORA-01403: no data found" error, indicating that the dba_segments view contains no records showing that the new table is on the targeted tablespace.

But when, in an already opened PL/SQL session, I immediately run the same query (with the appropriate literals in place of the trigger attribute functions) then the query works with the name of the tablespace returned as expected!

I tried an experiment with the trigger running the same query against the dba_tables view instead of the dba_segments view. It worked without error. The only problem is that if your create a partitioned table, the "tablespace_name" column in the dba_table view is NULL (which makes sense when the various table partitions can be contained in different tablepsaces). So, unfortunately, in the case of creating partitioned tables, the purpose of the query would be defeated.

Why does the trigger work when it queries the dba_tables view, but fails when it queries the dba_segments view? Is there a timing issue which causes a lag between the time the table is created and the time that the dba_segments view is updated? Would the trigger fire inside this time lag, thus causing the dba_segments query to fail? Or is there another explanation?

Thanks in advance for any advice you can give me!

Solomon Yakobson

Yes, you are right, I just tested it on 10.2.0.4.0:

SQL> create or replace
  2  trigger after_create
  3  after create
  4  on database
  5  declare
  6      v_cnt number;
  7  begin
  8      if ORA_DICT_OBJ_TYPE = 'TABLE'
  9        then
 10          select count(*) into v_cnt from dba_tables where owner = ORA_DICT_OBJ_OWNER and table_name = ORA_DICT_OBJ_NAME;
 11          dbms_output.put_line('TABLE CNT = ' || v_cnt);
 12          select count(*) into v_cnt from dba_segments where owner = ORA_DICT_OBJ_OWNER and segment_name = ORA_DICT_OBJ_NAME
 13          and segment_type IN ('TABLE', 'TABLE PARTITION');
 14          dbms_output.put_line('SEGMENT CNT = ' || v_cnt);
 15      end if;
 16  end;
 17  /

Trigger created.

SQL> set serveroutput on
SQL> create table tbl(x number);
TABLE CNT = 1
SEGMENT CNT = 0

Table created.

SQL> 

SY.

JP Kreps

Hi, Solomon --

Thanks for the confirmation! I forgot to mention in my original post that I'm running this on 11.1.0.7.

To test the hypothesis that it's a timing issue, I put a hefty "for loop" before the dba_segments query in the code of the trigger. It caused the trigger to spin its wheels for a few seconds before it ran the query. But this didn't help, because I got the ORA-01403 error again.

So it must not be a timing issue after alll. Perhaps the dba_segments query isn't updated until all triggers associated with the table creation have completed. Just guessing.

Anybody know the reason for this?

Cheers!

--JP

561825

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:432600400346770361

This link might help you.

Regards

Raj

JP Kreps

Hi, R.Subramanian --

Thanks for the link! That answers my question.

Tom frowns on using DDL triggers this way. He suggests removing from the trigger the code that performs the desired post-DDL actions and putting the code in some prefabricated standard procedure in which to execute DDL commands.

But the purpose of my trigger is to automatically and immediately start Oracle auditing on a table created on an encrypted tablespace on which confidential information is to be stored. The employees who will be creating the tables and loading the confidential data will need to do so in various ways, (i.e., datapump import, sql*loader, etc.). They have enough technical savvy to get around having to run a prefabricated procedure containing DDL. They could just run the DDL commands directly, which would then defeat the whole purpose of putting audit commands in a prefabricated procedure.

I thought about restricting access to the tablespace by creating only one user with quota on the tablespace and forcing the employees to run their data loading procedures connected as only that user. But then I run into a whole other class of security problems. Employees would have to share the password to this user. Plus, if all DDL/DML were run by this user, then auditing would be pointless, because it would be impossible to determine which employee is responsible for which DML operation the audit trail would detect.

Using a DDL trigger seems to be the most convenient and reliable way to make sure tables created on this encrypted tablespace are audited.

Thanks again!

-- JP

Edited by: JP Kreps on Aug 20, 2009 12:33 PM