12C SQL Translation Framework.txt

[20181013]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 --//和前面的能对上.