ITPub博客

首页 > 数据库 > Oracle > [20140218]关于SDO_GEORASTER的问题.txt

[20140218]关于SDO_GEORASTER的问题.txt

原创 Oracle 作者:lfree 时间:2014-02-18 10:10:46 0 删除 编辑

[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的应用,应该没有问题,注意避免在生产系统做这样的修改!!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1082941/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2639
  • 访问量
    6398876