[20181015]12c sql translation framework.txt
–//12c提供一个dba改写sql语句的可能性,实际上10g,11g之前也有一个包dbms_advanced_rewrite能实现类似的功能.
–//这种功能实在是一种旁门左道,还是测试看看.
–//不过如果程序存在大量的执行错误,一样会影响性能,导致出现sql*net break/reset to client.
–//参考连接:0624使用10035事件跟踪无法执行的sql语句 =>http://blog.itpub.net/267265/viewspace-2120884/
–//http://www.itpub.net/thread-2061952-1-1.html
1.环境:
scott@test01p> @ ver1
port_string version banner con_id
—————————— ————– ——————————————————————————– ———-
ibmpc/win_nt64-9.1.0 12.2.0.1.0 oracle database 12c enterprise edition release 12.2.0.1.0 – 64bit production 0
2.测试:
begin
dbms_sql_translator.create_profile(‘test_profile’);
dbms_sql_translator.register_sql_translation( profile_name => ‘test_profile’,
sql_text => ‘select sysdate’,
translated_text => ‘select sysdate from dual’);
end;
/
–//注sql_text可以写不对,但是前面一定开始是select,不然sqlplus无法识别是sql语句.
scott@test01p> alter session set sql_translation_profile=test_profile;
session altered.
scott@test01p> alter session set events = ‘10601 trace name context forever, level 32’;
session altered.
d:\tools\rlwrap>oerr ora 10601
10601, 00000, “turn on debugging for cursor_sharing (literal replacement)”
// *cause:
// *action:
scott@test01p> select sysdate;
sysdate
——————-
2018-10-15 20:24:21
scott@test01p> select sysdate;
select sysdate
*
error at line 1:
ora-00923: from keyword not found where expected
–//一定要与原来文本一样.
–//刷新共享池问题:
scott@test01p> alter system flush shared_pool;
system altered.
scott@test01p> select sysdate;
sysdate
——————-
2018-10-15 20:27:07
scott@test01p> select invalid select statement to force odbc driver to unprepared state;
d
–
x
sys@test> alter system flush shared_pool;
system altered.
scott@test01p> select sysdate;
sysdate
——————-
2018-10-15 20:27:50
–//我记忆里早期12.1.0.1版本刷新共享池后执行会报错.12cr2版本修复这个错误.
3.看看记录在那些表中,如何删除等等操作.
–//涉及视图:
dba_error_translations
dba_sql_translation_profiles
dba_sql_translations
scott@test01p> @ pt2 ‘select * from dba_sql_translations where profile_name=”test_profile”’;
row_num col_num col_name col_value
———- ———- ——————– ————————————————————-
1 1 owner scott
2 profile_name test_profile
3 sql_text select sysdate
4 translated_text select sysdate from dual
5 sql_id bw2c1d6sqyjpy
6 hash_value 2976859838
7 enabled true
8 registration_time 2018-10-15 20:23:15.415000
8 rows selected.
sys@test> @ sharepool/shp4 bw2c1d6sqyjpy 0
text kglhdadr kglhdpar c40 kglhdlmd kglhdpmd kglhdivc kglobhd0 kglobhd6 kglobhs0 kglobhs6 kglobt16 n0_6_16 n20 kglnahsh kglobt03 kglobt09
————– —————- —————- ————– ——– ———- ———- —————- —————- ———- ———- ———- ——— ———- ———- ————- ———-
父游标句柄地址 000007ff130dcbc8 000007ff130dcbc8 select sysdate 1 0 0 00 00 0 0 0 0 0 2327677740 bw2c1d6sqyjpy 0
–//看到一个很奇怪的父游标句柄,没有子游标,而且父游标的堆0是0.
–//如果还有一些语句还可以加入:
begin
dbms_sql_translator.register_sql_translation
(
profile_name => ‘test_profile’
,sql_text => ‘select user’
,translated_text => ‘select usera from dual’
);
end;
/
scott@test01p> select user;
user
——————–
scott
–//删除执行如下:
scott@test01p> exec dbms_sql_translator.drop_profile(profile_name => ‘test_profile’);
pl/sql procedure successfully completed.
scott@test01p> select sysdate;
select sysdate
*
error at line 1:
ora-00923: from keyword not found where expected
scott@test01p> @ pt2 ‘select * from dba_sql_translations where profile_name=”test_profile”’;
no rows selected
4.顺便看看这个包dbms_sql_translator的其它功能:
–//可以使用它计算sql_id:
scott@test01p> select dbms_sql_translator.sql_id(‘select sysdate’) c20 from dual ;
c20
——————–
bw2c1d6sqyjpy
–//和前面的能对上.
scott@test01p> set linesize 100
scott@test01p> declare
2 content clob;
3 begin
4 dbms_sql_translator.export_profile(
5 profile_name => ‘odbc_profile’,
6 content => content);
7 dbms_output.put_line(content);
8 end;
9 /
<sqltranslationprofile foreignsqlsyntax=”true” translatenewsql=”true” raisetranslationerror=”false”
logtranslationerror=”false” tracetranslation=”false” logerrors=”false”
editionable=”true”><sqltranslations><sqltranslation enabled=”true”><sqltext>select invalid select
statement to force odbc driver to unprepared state</sqltext><translatedtext>select dummy from
dual</translatedtext><registrationtime>2018-10-13t21:02:21.964000</registrationtime></sqltranslation
></sqltranslations><errortranslations></errortranslations></sqltranslationprofile>
pl/sql procedure successfully completed.