#########sample 0
---如何在 PL/SQL Block 端查看执行的SQL.
The SQL statement within the PL/SQL block is actually stored separately, but you cannot see it because:
- every sql statement in a PL/SQL block is stored as capital letters
- every comment and INTO clause are removed
- 因为pl/sql 每个块都是封装的,PL/SQL 块中的每个 sql 语句都存储为大写字母,所以查看PL/SQL 代码可以小写。
- 但是在查询V$SQL 我们应该去掉into 选项,并且要所有字母要大写
How to Determine the SQL_ID of a SQL Statement in a PL/SQL Block
OLUTION
If you have a PL/SQL block such as:
begin
select /* SimpleTest */ sum(sal) into v1 from emp;
end;
/
Then if you try to find the SQL_ID from v$sql then you will see the SQL_ID of the PL/SQL block NOT the SQL itself:
SQL> select sql_id, sql_text from v$sql where sql_text like '%SimpleTest%'; SQL_ID SQL_TEXT ------------- ---------------------------------------------------------------------------------- 77hjjr9qgwtzm declare v1 number; begin select /* SimpleTest */ sum(sal) into v1 from emp; end;
The SQL statement within the PL/SQL block is actually stored separately, but you cannot see it because:
- every sql statement in a PL/SQL block is stored as capital letters
- every comment and INTO clause are removed
- 因为pl/sql 每个块都是封装的
- 但是在V$SQL 我们应该去掉into 选项
Note that optimizer hints are preserved.
In other words,
is stored as
In order to find it's SQL_ID you would need to search on something similar to the following:
SQL> select sql_id, sql_text from v$sql where sql_text like '%SUM(SAL)%EMP%'; SQL_ID SQL_TEXT ------------- ------------------------------- 5mqhh85sm278a SELECT SUM(SAL) FROM EMP
The SQL_ID can also be determined by using the hash_value from a SQL_TRACE. The hash value can be seen in the raw trace file identified by the "hv=" string.
................................................. PARSING IN CURSOR #1 len=24 dep=1 uid=54 oct=3 lid=54 tim=1194298465705687 hv=1899044106 ad='997aa660' SELECT SUM(SAL) FROM EMP END OF STMT ..................
In this case the hash value is 1899044106. To find the SQL_ID using the hash value use the following select:
SQL> SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text FROM v$sql WHERE hash_value = &Hash_Value; SQL_ID HASH_VALUE SQL_TEXT ------------- ---------- ------------------------------- 5mqhh85sm278a 1899044106 SELECT SUM(SAL) FROM EMP
####sample 1
https://www.askmaclean.com/archives/vsqlcommand-sql-opcodes-and-names.html
ash 报告显示 代表都是存储过程封装好的SQL.
V$SQLCOMMAND SQL opcodes and names
47 PL/SQL EXECUTE
ash 报告显示 代表都是存储过程封装好的SQL. 因此无法通过AWR和ASH 报告看出,
因此需要进一步查看里面的查询 有2个方法,
方法1:使用PL/SQL developer 查看源代码,争取从源代码中找到相关的SQL语句以及表,当然这个比较麻烦,具体可以查看sample 2
方法2:
通过方法1 查找到的表,进一步需要通过V$sql进一步查看, 查找到SQL,在根据执行次数排序,排序越在前的SQL语句就越可能是关键语句。
select * from v$sql where sql_text like '%TRAN_AMT%'
###sample 2
https://blog.csdn.net/sinat_35512702/article/details/80937840
->通过在PLSQL Developer 中查看存储过程源码
https://blog.csdn.net/sinat_35512702/article/details/80937840
->.在Package bobodies 中找到存储过程所在的包名,比如TOOL,右键,选择Edit Spec & Body
->.如下图操作,在存储过程名上按住CTRL键 + 右键双击
1.在Package bobodies 中找到存储过程所在的包名,比如TOOL,右键,选择Edit Spec & Body
2.找到你要查看的存储过程,点击一下
3.如下图操作,在存储过程名上按住CTRL键 + 右键双击
4.该存储过程源码就可以看见了
##########sample 3:
https://blog.csdn.net/zjxbllg2008/article/details/82380812
手工查看存储过程的包含的关键字
oracle
##for
select * from all_source t where type = 'PROCEDURE' and text like '%mainacc%'
select * from all_source t where type = 'FUNCTION' and text like '%mainacc%'
select * from all_source t where type = 'PACKAGE BODY' and text like '%mainacc%'
查找存储过程
1 |
|
查找函数
1 |
|
MSSQL
1 2 3 4 |
|
Oracle中,如果关键字是一张表的话,还可以通过表的Referenced by属性找到相关的函数、存储过程和触发器等。
如:
MSSQL类似。
oracle数据库中如何查看已经创建的索引信息
1. 根据表名,查询一张表的索引
1 |
|
2. 根据索引号,查询表索引字段
1 |
|
3.根据索引名,查询创建索引的语句
1 |
|
PS:dbms_metadata.get_ddl还可以得到建表语句,如:
1 2 |
|