Re: Database creation question

From: Yong Huang <yong321_at_yahoo.com>
Date: Mon, 28 Jan 2013 15:03:34 -0800 (PST)
Message-ID: <1359414214.11769.YahooMailClassic_at_web184803.mail.gq1.yahoo.com>

Jonathan Lewis wrote:
/*
The one that I particularly dislike is Spatial - which installs by default unless you go to the "secondary" installation screen and deselect it.

This introduces a number of database-level triggers that fire for reasons related to Spatial temporary tables et. al. In many cases people don't notice that this is happening because the causes are quite rare, but some applications do a lot of DDL and can end up with significant contention as a side effect of installing an application that they didn't realised was going to be installed.
*/

I noticed that too. It's related to

Unexpected Oracle Spatial Audit Entries on AUD$ [ID 1338587.1] and even
Bug 12552986: ERROR CODE CHANGE WITH 11G

But the scope of "intrusion" is not limited to audit. The suggested workaround is to disable 4 Spatial triggers (I only find 2 in my 11.2.0.3).

I think the real cause is that some Spatial triggers are created to fire on the database, while they really should be fired on schema.

SQL> select trigger_name, trigger_type, triggering_event from dba_triggers where owner = 'MDSYS' and base_object_type like 'DATABASE%' order by 1;

TRIGGER_NAME                   TRIGGER_TYPE     TRIGGERING_EVENT
------------------------------ ---------------- ------------------------------
SDO_DROP_USER                  AFTER EVENT      DROP
SDO_GEOR_ADDL_TRIGGER          AFTER EVENT      DDL
SDO_GEOR_BDDL_TRIGGER          BEFORE EVENT     DDL
SDO_NETWORK_DROP_USER          AFTER EVENT      DROP
SDO_ST_SYN_CREATE              BEFORE EVENT     CREATE
SDO_TOPO_DROP_FTBL             BEFORE EVENT     DROP

The code to create the triggers shown above should NOT be before|after event|ddl on *database*, but instead on <user>.schema, where <user> is whoever wants to use Spatial. "Database" means any logged on user. "The causes are quite rare" probably because most production databases rarely have DDLs. But as Jonathan stated, a query could dynamically create a GTT, which is a DDL.

Yong Huang

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 29 2013 - 00:03:34 CET