[20111213]查看v$sql_shared_cursor视图获取sql语句为什么不能共享?
查询v$sql_shared_cursor视图获取sql语句为什么不能共享，在GUI界面下，由于不能共享的原因很多，视图字段N多，大部分都是'N'的数值，检查这个是一个很费时间的操作。
想到了TOM大师的print_table过程，应该修改一下很容易实现这个功能。
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1035431863958
修改一下，很容易实现这个功能：
CREATE OR REPLACE PROCEDURE print_shared_cursor (p_query IN VARCHAR2)
AUTHID CURRENT_USER
IS
l_thecursor INTEGER DEFAULT DBMS_SQL.open_cursor;
l_columnvalue VARCHAR2 (4000);
l_query VARCHAR2 (4000);
l_status INTEGER;
l_desctbl DBMS_SQL.desc_tab;
l_colcnt NUMBER;
BEGIN
--EXECUTE IMMEDIATE 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss'' ';
l_query := 'select * from v$sql_shared_cursor where sql_id=' || CHR (39) || p_query || CHR (39);
DBMS_SQL.parse (l_thecursor, l_query, DBMS_SQL.native);
DBMS_SQL.describe_columns (l_thecursor, l_colcnt, l_desctbl);
FOR i IN 1 .. l_colcnt
LOOP
DBMS_SQL.define_column (l_thecursor, i, l_columnvalue, 4000);
END LOOP;
l_status := DBMS_SQL.EXECUTE (l_thecursor);
DBMS_OUTPUT.put_line ('-------------------------------------------------------');
WHILE (DBMS_SQL.fetch_rows (l_thecursor) > 0)
LOOP
FOR i IN 1 .. l_colcnt
LOOP
DBMS_SQL.COLUMN_VALUE (l_thecursor, i, l_columnvalue);
IF (l_columnvalue 'N')
THEN
DBMS_OUTPUT.put_line (RPAD (l_desctbl (i).col_name, 30) || ': ' || l_columnvalue);
END IF;
END LOOP;
DBMS_OUTPUT.put_line ('-------------------------------------------------------');
END LOOP;
-- execute immediate 'alter session set nls_date_format=''dd-MON-rr'' ';
EXCEPTION
WHEN OTHERS
THEN
NULL;
-- execute immediate 'alter session set nls_date_format=''dd-MON-rr'' ';
RAISE;
END;
/
实际上我还发现别人已经写了一个sql语句实现一样的操作，只不过他查询的是sql语句，很容易修改为查询sql_id的语句，我修改如下：
http://dioncho.wordpress.com/2009/03/05/vsql_shared_cursor/
$ cat shared_cursor.sql
SET serveroutput on size 100000;
DECLARE
c NUMBER;
col_cnt NUMBER;
col_rec DBMS_SQL.desc_tab;
col_value VARCHAR2 (4000);
ret_val NUMBER;
BEGIN
c := DBMS_SQL.open_cursor;
DBMS_SQL.parse
(c,
'select q.sql_text, s.*
from v$sql_shared_cursor s, v$sql q
where s.sql_id = q.sql_id
and s.child_number = q.child_number
and q.sql_id like ''&1''',
DBMS_SQL.native
);
DBMS_SQL.describe_columns (c, col_cnt, col_rec);
FOR idx IN 1 .. col_cnt
LOOP
DBMS_SQL.define_column (c, idx, col_value, 4000);
END LOOP;
ret_val := DBMS_SQL.EXECUTE (c);
WHILE (DBMS_SQL.fetch_rows (c) > 0)
LOOP
FOR idx IN 1 .. col_cnt
LOOP
DBMS_SQL.COLUMN_VALUE (c, idx, col_value);
IF col_rec (idx).col_name IN ('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS', 'CHILD_NUMBER', 'SQL_TEXT')
THEN
DBMS_OUTPUT.put_line (RPAD (col_rec (idx).col_name, 30) || ' = ' || col_value);
ELSIF col_value = 'Y'
THEN
DBMS_OUTPUT.put_line (RPAD (col_rec (idx).col_name, 30) || ' = ' || col_value);
END IF;
END LOOP;
DBMS_OUTPUT.put_line ('--------------------------------------------------');
END LOOP;
DBMS_SQL.close_cursor (c);
END;
/
SET serveroutput off;
做一个简单测试：
SQL> var a number;
SQL> exec :a := 10;
PL/SQL procedure successfully completed.
SQL> select * from dept where deptno=:a;
查询获得sql_id='6a2y9jbwu5fz9'.
SQL> alter session set optimizer_mode =first_rows;
SQL> select * from dept where deptno=:a;
SQL> set serveroutput on
SQL> exec print_shared_cursor('6a2y9jbwu5fz9');
-------------------------------------------------------
SQL_ID : 6a2y9jbwu5fz9
ADDRESS : 00000000BF56C328
CHILD_ADDRESS : 00000000BF56BFC8
CHILD_NUMBER : 0
-------------------------------------------------------
SQL_ID : 6a2y9jbwu5fz9
ADDRESS : 00000000BF56C328
CHILD_ADDRESS : 00000000B5ED9E80
CHILD_NUMBER : 1
OPTIMIZER_MODE_MISMATCH : Y
-------------------------------------------------------
PL/SQL procedure successfully completed.
@shared_cursor.sql 6a2y9jbwu5fz9
old 15: and q.sql_id like ''&1''',
new 15: and q.sql_id like ''6a2y9jbwu5fz9''',
SQL_TEXT = select * from dept where deptno=:a
SQL_ID = 6a2y9jbwu5fz9
ADDRESS = 00000000BF56C328
CHILD_ADDRESS = 00000000BF56BFC8
CHILD_NUMBER = 0
--------------------------------------------------
SQL_TEXT = select * from dept where deptno=:a
SQL_ID = 6a2y9jbwu5fz9
ADDRESS = 00000000BF56C328
CHILD_ADDRESS = 00000000B5ED9E80
CHILD_NUMBER = 1
OPTIMIZER_MODE_MISMATCH = Y
--------------------------------------------------
PL/SQL procedure successfully completed.