linux查看视图sql,查看v$sql_shared_cursor视图获取sql语句为什么不能共享?

[20111213]查看v$sql_shared_cursor视图获取sql语句为什么不能共享?

查询v$sql_shared_cursor视图获取sql语句为什么不能共享，在GUI界面下，由于不能共享的原因很多，视图字段N多，大部分都是'N'的数值，检查这个是一个很费时间的操作。

想到了TOM大师的print_table过程，应该修改一下很容易实现这个功能。

修改一下，很容易实现这个功能：

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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-713219/，如需转载，请注明出处，否则将追究法律责任。