0

I would like to obtain the query before tablespace creation using trigger. Searching through web didn't provide enough info. So, it will be a great help if someone throw some light on this.

I have a procedure xyz which i want to be invoked before creating a tablespace.

bprasanna
  • 2,423
  • 3
  • 27
  • 39
  • Tablespace creation from inside a trigger? What exactly are you trying to accomplish? – Rene Apr 09 '13 at 13:37
  • @Rene It is not tablespace creation inside trigger. It is capturing the tablespace creation event before the tablespace is created. Also capturing the query, used to create the tablespace, inside the trigger. – bprasanna Apr 09 '13 at 18:02

3 Answers3

1

I could be wrong but I don't think you can specifically capture CREATE TABLESPACE: You can create a DDL trigger which will trap all CREATE events but you would then have to do some checking to ensure it is a tablespace creation. Remember triggers are executed for every user every time the event occurs for which they are created so be very careful with them.

There's a few examples of DDL triggers here

Steve Pettifer
  • 1,975
  • 1
  • 19
  • 34
1

The 11.2 documentation doesn't specifically disallow DDL triggers on create tablespace, but does on create database or controlfile: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#CIHGCJHC.

So there's nothing to suggest that it can't be done.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
1

Finally came up with a trigger which works:

set serveroutput on;
CREATE OR REPLACE TRIGGER ddl_trig
BEFORE CREATE
ON DATABASE
DECLARE
    sql_text   DBMS_STANDARD.ora_name_list_t;
    v_stmt VARCHAR2(2000);
    n   PLS_INTEGER;
BEGIN
    IF (ora_dict_obj_type = 'TABLESPACE' ) AND (ora_sysevent = 'CREATE') THEN 
        n := ora_sql_txt(sql_text);
        FOR i IN 1..n LOOP
            v_stmt := v_stmt || sql_text(i);
        END LOOP;
        dbms_output.put_line('------------Query Used---------------');
        dbms_output.put_line(v_stmt);
    END IF;
END ddl_trig;
/

show errors;

Got the list of events from Oracle Doc. My sincere thanks to @David @Steve for the pointers.

bprasanna
  • 2,423
  • 3
  • 27
  • 39
  • 1
    No problem. Only thing I would say is that I think that trigger will fire every single time any user executes any DDL statement on the database. I suspect that if you changed it to BEFORE CREATE that would help. It's always good practice to restrict the running of anything to only when it is needed as far as possible for the sake of performance. – Steve Pettifer Apr 10 '13 at 08:31
  • @StevePettifer Thank you, before create suits the need and your point on performance is an apt suggestion. – bprasanna Apr 10 '13 at 09:29