[20181015]12C SQL Translation Framework.txt

[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.