[20140218]關於SDO_GEORASTER的問題.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20181229]關於字串的分配問題.txt字串
- [20200416]關於軟軟解析的問題.txt
- 關於SQLServerDriver的問題SQLServer
- 關於 JavaMail 的問題JavaAI
- 關於session的問題Session
- [20161108]關於資料檔案的問題.txt
- [20171228]關於資料塊轉儲的問題.txt
- [20121108]關於克隆資料庫的問題.txt資料庫
- [20190918]關於函式索引問題.txt函式索引
- [20150304]關於sql格式化問題.txtSQL
- 關於javascript的this指向問題JavaScript
- 關於跨域的問題跨域
- 關於bit code的問題
- 關於序列同步的問題
- 關於IP地址的問題
- 關於橋模式的問題模式
- 求救 關於parallel的問題Parallel
- 關於web start的問題Web
- 關於action的error問題Error
- 關於ADAPTER的問題APT
- 關於session的奇怪問題Session
- php關於session的問題PHPSession
- 關於diag程式的問題
- 關於SimpleJdonFrameworkTest的問題Framework
- 關於 Puerts 的效能問題
- 關於盒模型相關的問題模型
- 關於FastHashMap問題ASTHashMap
- [20211018]運維中關於history的問題.txt運維
- [20230317]關於TIME_WAIT問題.txtAI
- [20170711]關於tmux共享會話問題.txtUX會話
- [20171120]關於find 軟連線問題.txt
- 關於PHP佇列的問題PHP佇列
- 關於 Laravel 分頁的問題？Laravel
- 關於css權值的問題CSS
- 關於搜尋地址的問題
- 關於PWA落地問題的思考
- 關於 API 合併的問題API
- 關於excelize庫的使用問題Excelize