Oracle PL/SQL/System Tables Views/sql
Query SQL table
<source lang="sql">
SQL> SQL> select * from dual; D - X SQL> select * from DUAL; D - X SQL> SQL> select sql_text, hash_value from v$sql where upper(sql_text) = "SELECT * FROM DUAL"; SQL_TEXT
HASH_VALUE
select * from DUAL 3649189949 select * from dual
942515969
SQL_TEXT
HASH_VALUE
select * from dual
942515969
3 rows selected. SQL> SQL> alter session set OPTIMIZER_MODE = first_rows; Session altered. SQL> SQL> select * from dual; D - X
</source>
select sql_text, hash_value, parsing_user_id, optimizer_mode from v$sql
<source lang="sql">
SQL> SQL> select * from dual; D - X SQL> SQL> select sql_text, hash_value, parsing_user_id
2 from v$sql 3 where upper(sql_text) = "SELECT * FROM DUAL";
SQL_TEXT
HASH_VALUE PARSING_USER_ID
---------------
select * from DUAL 3649189949 0 select * from dual
942515969 0
SQL_TEXT
HASH_VALUE PARSING_USER_ID
---------------
select * from dual
942515969 0
3 rows selected. SQL> SQL> select sql_text, hash_value, parsing_user_id, optimizer_mode
2 from v$sql 3 where upper(sql_text) = "SELECT * FROM DUAL";
SQL_TEXT
HASH_VALUE PARSING_USER_ID OPTIMIZER_
--------------- ----------
select * from DUAL 3649189949 0 RULE select * from dual
942515969 0 RULE
SQL_TEXT
HASH_VALUE PARSING_USER_ID OPTIMIZER_
--------------- ----------
select * from dual
942515969 0 FIRST_ROWS
3 rows selected. SQL>
</source>