Re: Database creation question
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-lReceived on Tue Jan 29 2013 - 00:03:34 CET