Error Cannot Fetch Last Explain Plan From Plan_table 11g
resolution for the EXPLAIN PLAN command in comparison to everything else I'm aware of. Wth error cannot fetch plan for statement_id autotrace the usual disclaimer of: I might have missed something…聽EXPLAIN PLAN oracle create plan table is not affected by the "ALTER SESSION SET CURRENT_SCHEMA" command. This little investigation all started with a report of "explain plan" not working in PL/SQL Developer. On digging into it we discovered 3 PLAN_TABLE tables in the database plus a couple of synonyms. The names have been changed to protect the innocent, so to speak. Anyway it looked something like this: SQL> select owner, object_name, object_type from dba_objects where object_name = 'PLAN_TABLE'; OWNER OBJECT_NAME OBJECT_TYPE ------------------------------ ----------- ------------------- PUBLIC PLAN_TABLE SYNONYM APP_SCHEMA PLAN_TABLE TABLE USER01 PLAN_TABLE SYNONYM SYSTEM PLAN_TABLE TABLE SYS PLAN_TABLE TABLE With the PUBLIC synonym pointing to SYSTEM.PLAN_TABLE and the synonym owned by USER01 pointing to APP_SCHEMA.PLAN_TABLE. Just to spice things up, a logon trigger used ALTER SESSION SET CURRENT_SCHEMA to APP_SCHEMA for pretty much all database users. After a quick test I confirmed that things were definitely not working as expected via SQL*Plus as shown below: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining op JServer Release 9.2.0.8.0 - Production MARTINNASH@orcl> explain plan for 2 select * from dual; Explained. MARTINNASH@orcl> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | -------------------------------------------------------------------------------------------------------- |-------------------------------------- NOT THE QUERY I JUST RAN --------------------------------------| -------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- <------------ TEXT REMOVED ------------> Note: cpu costing is off Remember the logon trigger changes my current schema t
比特币 小额贷款 贷款 crm ORACLE 项目管理系统 thread https://oraganism.wordpress.com/2010/01/08/explain-plan-for-anomaly/ android python java 文章帖子用户 抱歉,指定的主题不存在或已被删除或正在被审核 TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 http://www.itpub.net/forum.php?mod=viewthread&action=printable&tid=1465774 文本模式 帮助 ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客 ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统 CopyRight 1999-2011 itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明 京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001 广播电视节目制作经营许可证:编号(京)字第1149号 返回顶部
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the http://stackoverflow.com/questions/25613444/how-to-create-explain-plan-table-on-amazon-rds-database workings and policies of this site About Us Learn more about http://forums.allroundautomations.com/ubb/ubbthreads.php?ubb=showflat&Number=48748 Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. error cannot Join them; it only takes a minute: Sign up How to create EXPLAIN_PLAN table on Amazon RDS database? up vote 0 down vote favorite How to create EXPLAIN_PLAN table having no access to console? Is there an option to tick somewhere? oracle amazon-rds share|improve this question asked Sep 1 '14 at 22:10 gumol 497 are you able error cannot fetch to connect to your rds from local computer? –zaratustra Sep 1 '14 at 22:26 yes, using python+cx_Oracle and sqlplus –gumol Sep 1 '14 at 22:33 Have you tried set autotrace traceonly? –zaratustra Sep 1 '14 at 22:35 I don't think it works outside sql*plus –gumol Sep 2 '14 at 1:06 But you told me that you could connect to your database through the SQL*Plus tool. Next time express yourself clearly. –zaratustra Sep 2 '14 at 8:09 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote accepted I believe you are referring to PLAN_TABLE used in "explain plan" commands. In Oracle 11.2 it's already created by default as a temporary table in the SYS schema (PLAN_TABLE$) with public synonym and privileges to all users. So "explain plan" should work right out of the box and then you can read the results from the table: explain plan for select * from dual; select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------- Plan hash value: 272002086 ---------------------------------------------------------------------
back to return to the previous page. Enter your Username and Password to log in. If you have not yet registered, you can register here. Username: Password: Have you forgotten your login information? Mark all read Contact Us · · Top Generated in 0.008 seconds in which 0.001 seconds were spent on a total of 3 queries. Zlib compression disabled.Powered by UBB.threads™ PHP Forum Software 7.4.1p2