根据表名搜索SQL语句

作者: AnySQL.net, 转载时请务必以超链接形式标明文章原始出处和作者信息.
链接: http://www.anysql.net/dba/search_sql_by_tablename.html

用全文索引做了一个根据表名查找SQL语句的功能. 在Statspack中有一个表存放了数据库中执行过的SQL, 虽然不是全部, 但也有差不多99.9%了. 只是由于它是分行存贮的, 不能直接用普通的SQL语句(like)来查找, 表名有可能被折分存放在两行中. 解决的方法有两种, 一是写过程, 将多行的串在一起. 二是用全文索引中的主从存贮方式, 建立全文索引来查询. 我选择的是全文索引的方式.

先将这里面的数据拷出来, 分成两个表, 主表和从表.

CREATE TABLE SQLS AS
SELECT DISTINCT HASH_VALUE,'X' BODY FROM STATS$SQLTEXT;
ALTER TABLE SQLS ADD PRIMARY KEY (HASH_VALUE);
CREATE TABLE SQL_DETAILS AS
SELECT HASH_VALUE,PIECE,SQL_TEXT FROM STATS$SQLTEXT;
ALTER TABLE SQL_DETAILS ADD PRIMARY KEY (HASH_VALUE, PIECE);

创建全文索引的存贮方式及Lexer属性.

begin
ctx_ddl.create_preference('sqltext_pref', 'DETAIL_DATASTORE');
ctx_ddl.set_attribute('sqltext_pref', 'binary', 'true');
ctx_ddl.set_attribute('sqltext_pref', 'detail_table', 'sql_details');
ctx_ddl.set_attribute('sqltext_pref', 'detail_key', 'hash_value');
ctx_ddl.set_attribute('sqltext_pref', 'detail_lineno', 'piece');
ctx_ddl.set_attribute('sqltext_pref', 'detail_text', 'sql_text');
end;
/
begin
ctx_ddl.create_preference('sqltext_lex', 'BASIC_LEXER');
ctx_ddl.set_attribute ('sqltext_lex', 'continuation', '_');
ctx_ddl.set_attribute ('sqltext_lex', 'printjoins', '_');
ctx_ddl.set_attribute ('sqltext_lex', 'index_themes', 'NO');
ctx_ddl.set_attribute ('sqltext_lex', 'index_text', 'YES');
end;
/

接下来创建全文索引.

CREATE INDEX SQL_CTX on SQLS(body) indextype is ctxsys.context
parameters('datastore sqltext_pref LEXER sqltext_lex');

接下来就可以查询了.

SELECT HASH_VALUE FROM SQLS WHERE CONTAINS(BODY,'tablename and ...') > 0

接下来要好好用用这个功能了.