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