[20140218]关于SDO_GEORASTER的问题.txt
如果你跟踪一个DDL建表的过程,使用10046，如果看到跟踪文件包含如下内容：
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> @10046on 12
old 1: alter session set events '10046 trace name context forever, level &1'
new 1: alter session set events '10046 trace name context forever, level 12'
Session altered.
SCOTT@test> create table t (id number);
Table created.
SCOTT@test> @10046off
Session altered.
--如果看跟踪文件可以发现如下内容：
declare
TYPE attrs_cur IS REF CURSOR;
m_cur attrs_cur;
m_event varchar2(512);
m_user varchar2(512);
m_owner varchar2(512);
m_user1 varchar2(512);
m_type varchar2(512);
m_stmt varchar2(512);
m_name varchar2(5120);
m_column varchar2(5120);
m_cnt NUMBER;
m_stmt1 varchar2(512);
m_var varchar2(512);
m_o_stmt VARCHAR2(5120);
PRAGMA AUTONOMOUS_TRANSACTION;
begin
m_stmt:='select sys.dbms_standard.dictionary_obj_type from dual';
execute immediate m_stmt into m_type;
if(not (m_type='TABLE' or m_type='TRIGGER' or m_type='USER' or m_type='TABLESPACE'))
then
return;
end if;
m_stmt:='select sys.dbms_standard.sysevent from dual';
execute immediate m_stmt into m_event;
m_stmt:='select SYS_CONTEXT(''USERENV'',''SESSION_USER'') from dual';
execute immediate m_stmt into m_user;
m_stmt:='select SYS_CONTEXT(''USERENV'',''CURRENT_USER'') from dual';
execute immediate m_stmt into m_user1;
m_stmt:='select sys.dbms_standard.dictionary_obj_owner from dual';
execute immediate m_stmt into m_owner;
m_stmt:='select sys.dbms_standard.dictionary_obj_name from dual';
execute immediate m_stmt into m_name;
m_stmt:='select sdo_geor_def.getSqlText from dual';
execute immediate m_stmt into m_o_stmt;
if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='ALTER')
then
m_stmt:='select column_name from dba_tab_columns where owner=:1 and table_name=:2';
open m_cur for m_stmt using m_owner,m_name;
loop
fetch m_cur into m_column;
exit when m_cur%NOTFOUND;
m_stmt:='select sdo_geor_def.isDropColumn(:1) from dual';
execute immediate m_stmt into m_stmt1 using SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_column);
if (trim(m_stmt1)='TRUE')
then
m_stmt:='begin sdo_geor_def.doAlterDropColumn(:1,:2,:3); end;';
execute immediate m_stmt using SYS.DBMS_ASSERT.SCHEMA_NAME(m_owner),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_name),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_column);
end if;
end loop;
end if;
if ((m_event='DROP' and m_type='USER') or (m_event='DROP' and m_type='TABLESPACE'))
then
m_stmt:='insert into sdo_geor_ddl__table$$ values (1)';
EXECUTE IMMEDIATE m_stmt;
commit;
end if;
.....
--很长截取其中一段。为什么要执行这些？查询dba_source看看。
SELECT * FROM DBA_SOURCE WHERE LOWER (text) LIKE
LOWER ('%execute immediate m_stmt using SYS.DBMS_ASSERT.SCHEMA_NAME(m_owner),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_name),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_column)%');
SCOTT@test> column text format a100
SCOTT@test> /
OWNER NAME TYPE LINE TEXT
------ --------------------- ---------- ---------- ----------------------------------------------------------------------------------------------------
MDSYS SDO_GEOR_BDDL_TRIGGER TRIGGER 64 execute immediate m_stmt using SYS.DBMS_ASSERT.SCHEMA_NAME(m_owner),SYS.DBMS_ASSERT.SIMPLE_S
QL_NAME(m_name),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_column);
SELECT text FROM DBA_SOURCE WHERE owner='MDSYS' and name='SDO_GEOR_BDDL_TRIGGER' order by line;
TEXT
----------------------------------------------------------------------------------------------------
trigger sdo_geor_bddl_trigger
before ddl on database
--可以发现触发器MDSYS.SDO_GEOR_BDDL_TRIGGER是在执行ddl前触发。SDO_GEOR_开头的表示什么。
--做一个google，很容易发现这些东西与spatial有关，我不熟悉这些东西。
--很明显这些与安装时选择spatial有关，对于这些我从来不熟悉也不使用。建一个简单的表spatial table看看。
SCOTT@test> @10046on 12
old 1: alter session set events '10046 trace name context forever, level &1'
new 1: alter session set events '10046 trace name context forever, level 12'
Session altered.
SCOTT@test> create table t1 (id number(6,0),v1 SDO_GEORASTER) ;
Table created.
SCOTT@test> @10046off
Session altered.
--时间比一些普通表有点长。
SCOTT@test> desc t1
Name Null? Type
----- -------- ---------------------
ID NUMBER(6)
V1 PUBLIC.SDO_GEORASTER
SCOTT@test> column data_type format a30
SCOTT@test> select table_name,column_name,data_type,column_id,segment_column_id,internal_column_id ,qualified_col_name
from dba_tab_cols where owner=user and table_name='T1';
TABLE_NAME COLUMN_NAME DATA_TYPE COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID QUALIFIED_COL_NAME
---------- ------------- -------------------- ---------- ----------------- ------------------ ----------------------------------------
T1 ID NUMBER 1 1 1 ID
T1 V1 SDO_GEORASTER 2 2 2 V1
T1 SYS_NC00003$ NUMBER 2 3 3 "V1"."RASTERTYPE"
T1 SYS_NC00004$ NUMBER 2 4 4 "V1"."SPATIALEXTENT"."SDO_GTYPE"
T1 SYS_NC00005$ NUMBER 2 5 5 "V1"."SPATIALEXTENT"."SDO_SRID"
T1 SYS_NC00006$ NUMBER 2 6 6 "V1"."SPATIALEXTENT"."SDO_POINT"."X"
T1 SYS_NC00007$ NUMBER 2 7 7 "V1"."SPATIALEXTENT"."SDO_POINT"."Y"
T1 SYS_NC00008$ NUMBER 2 8 8 "V1"."SPATIALEXTENT"."SDO_POINT"."Z"
T1 SYS_NC00009$ SDO_ELEM_INFO_ARRAY 2 9 9 "V1"."SPATIALEXTENT"."SDO_ELEM_INFO"
T1 SYS_NC00010$ SDO_ORDINATE_ARRAY 2 10 10 "V1"."SPATIALEXTENT"."SDO_ORDINATES"
T1 SYS_NC00011$ VARCHAR2 2 11 11 "V1"."RASTERDATATABLE"
T1 SYS_NC00012$ NUMBER 2 12 12 "V1"."RASTERID"
T1 SYS_NC00013$ XMLTYPE 2 13 "V1"."METADATA"
T1 SYS_NC00014$ BLOB 2 13 14 SYS_NC00014$
14 rows selected.
--我个人不熟悉这些spatial 的应用。理论讲如果你应用没有spatial有关的东西，完全可以禁用这些触发器。
SCOTT@test> select owner,trigger_name,trigger_type,triggering_event,dump(triggering_event) c30 from dba_triggers
where owner='MDSYS' and (triggering_event like 'DDL%' or triggering_event like 'DROP%');
OWNER TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT C30
------ ---------------------- ---------------- ----------------- ------------------------------
MDSYS SDO_TOPO_DROP_FTBL BEFORE EVENT DROP Typ=1 Len=5: 68,82,79,80,32
MDSYS SDO_NETWORK_DROP_USER AFTER EVENT DROP Typ=1 Len=5: 68,82,79,80,32
MDSYS SDO_GEOR_ADDL_TRIGGER AFTER EVENT DDL Typ=1 Len=4: 68,68,76,32
MDSYS SDO_DROP_USER AFTER EVENT DROP Typ=1 Len=5: 68,82,79,80,32
MDSYS SDO_GEOR_BDDL_TRIGGER BEFORE EVENT DDL Typ=1 Len=4: 68,68,76,32
--真不知道oracle什么搞的，TRIGGERING_EVENT字段串后面都有1个空格。
--我估计许多人跟我一样，在安装oracle时会选择不需要的包，这样导致不必要的操作。
drop table t1 purge ;
drop table t purge ;
alter trigger mdsys.sdo_geor_addl_trigger disable;
alter trigger mdsys.sdo_geor_bddl_trigger disable;
alter trigger mdsys.sdo_topo_drop_ftbl disable;
alter trigger mdsys.sdo_st_syn_create disable;
这样建立表再看跟踪文件：
$ ./bin/trimsql.sh /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_14738_127_0_0_1.trc
0001 0 #182926955040>>>> create table t (id number)
0002 1 #182926948304>>>> select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
0003 1 #182926938296>>>> update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14 where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
0004 1 #182926931424>>>> select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1
0005 1 #182926926232>>>> select obj# from objerror$
0006 1 #182926925056>>>> select obj#, owner, node from syn$ where name=:1
0007 1 #182926923304>>>> insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2,spare3) values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18)
0008 1 #182926907040>>>> insert into deferred_stg$ (obj#, pctfree_stg, pctused_stg, size_stg,initial_stg, next_stg, minext_stg, maxext_stg, maxsiz_stg, lobret_stg,mintim_stg, pctinc_stg, initra_stg, maxtra_stg, optimal_stg, maxins_stg,frlins_stg, flags_stg, bfp_stg, enc_stg, cmpflag_stg, cmplvl_stg) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15, :16,:17,:18,:19,:20,:21,:22)
0009 1 #182927166376>>>> insert into tab$(obj#,ts#,file#,block#,bobj#,tab#,intcols,kernelcols,clucols,audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime,samplesize,cols,property,degree,instances,dataobj#,avgspc_flb,flbcnt,trigflag,spare1,spare6)values(:1,:2,:3,:4,decode(:5,0,null,:5),decode(:6,0,null,:6),:7,:8,decode(:9,0,null,:9),:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,decode(:26,1,null,:26),decode(:27,1,null,:27),:28,:29,:30,:31,:32,:33)
0010 1 #182926940840>>>> insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
0011 1 #182926939968>>>> select 1 from sys.streams$_prepare_ddl p where ((p.global_flag = 1 and :1 is null) or (p.global_flag = 0 and p.usrid = :2)) and rownum = 1
0012 0 #182926955040>>>> alter session set events '10046 trace name context off'
--这样再看跟踪文件要少许多内容。只要你不使用spatial的应用,应该没有问题,注意避免在生产系统做这样的修改!!
