大家好: 最近在维护一个项目,在用数据结构文档中的表和ORACLE中的表进行比较,发现数据库中多了一个plan_table表;
请问下plan_table表作用是干什么的?
请问下plan_table表作用是干什么的?
解决方案 »
- 通过 pl/sql developer 工具通过DBLINK去查询一个表。为什么commit,rollback按钮会被激活。
- 分别取每个行政区前5条的数据,sql 如何写的呢?
- 求助:此列列表的唯一或主键不匹配
- 求助:把SqlServer 2000的数据库移植到Oracle中!
- 一个ORACLE替换数据库的问题,急...................
- 讨论一下表中状态字段的设计?
- 9i中的Date类型是怎么样的格式?
- 简单的表空间问题,来者有分!
- exp导出数据后,为什么不能用imp直接导入呢?错误提示对象已存在
- 怎么查询 ORACLE 用户最后一次登录的时间。
- Oracle Database Administrator's Guide 问题
- Oracle 提高查询效率
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report初步判断,因为SYSDBA可以打开,但其他用户无法打开,是权限问题,于是查找资料。用以下语句解决SQL> connect sys/pass@xxx as sysdba
Connected.
SQL> create role wwmtrace;Role created.
SQL> grant select on v_$sesstat to wwmtrace;Grant succeeded.
SQL> grant select on v_$statname to wwmtrace;Grant succeeded.
SQL> grant select on v_$mystat to wwmtrace;Grant succeeded.
SQL> grant wwmtrace to wwm;SQL> connect wwm/pass
Connected.SQL> set autotrace on
SQL>无错误提示,测试正确
create global temporary table PLAN_TABLE$
(
STATEMENT_ID VARCHAR2(30),
PLAN_ID NUMBER,
TIMESTAMP DATE,
REMARKS VARCHAR2(4000),
OPERATION VARCHAR2(30),
OPTIONS VARCHAR2(255),
OBJECT_NODE VARCHAR2(128),
OBJECT_OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
OBJECT_ALIAS VARCHAR2(65),
OBJECT_INSTANCE INTEGER,
OBJECT_TYPE VARCHAR2(30),
OPTIMIZER VARCHAR2(255),
SEARCH_COLUMNS NUMBER,
ID INTEGER,
PARENT_ID INTEGER,
DEPTH INTEGER,
POSITION INTEGER,
COST INTEGER,
CARDINALITY INTEGER,
BYTES INTEGER,
OTHER_TAG VARCHAR2(255),
PARTITION_START VARCHAR2(255),
PARTITION_STOP VARCHAR2(255),
PARTITION_ID INTEGER,
OTHER LONG,
OTHER_XML CLOB,
DISTRIBUTION VARCHAR2(30),
CPU_COST INTEGER,
IO_COST INTEGER,
TEMP_SPACE INTEGER,
ACCESS_PREDICATES VARCHAR2(4000),
FILTER_PREDICATES VARCHAR2(4000),
PROJECTION VARCHAR2(4000),
TIME INTEGER,
QBLOCK_NAME VARCHAR2(30)
)
on commit preserve rows;
-- Grant/Revoke object privileges
grant select, insert, update, delete on PLAN_TABLE$ to PUBLIC;
没试过删除,可能会对dmbs_xplan的执行有影响
Table dropped
SQL> explain plan for select 1 from dual;--解析执行计划失败
explain plan for select 1 from dual
ORA-00980: 同义词转换不再有效
SQL> -- Create table
SQL> create global temporary table PLAN_TABLE$--重建表PLAN_TABLE$
2 (
3 STATEMENT_ID VARCHAR2(30),
4 PLAN_ID NUMBER,
5 TIMESTAMP DATE,
6 REMARKS VARCHAR2(4000),
7 OPERATION VARCHAR2(30),
8 OPTIONS VARCHAR2(255),
9 OBJECT_NODE VARCHAR2(128),
10 OBJECT_OWNER VARCHAR2(30),
11 OBJECT_NAME VARCHAR2(30),
12 OBJECT_ALIAS VARCHAR2(65),
13 OBJECT_INSTANCE INTEGER,
14 OBJECT_TYPE VARCHAR2(30),
15 OPTIMIZER VARCHAR2(255),
16 SEARCH_COLUMNS NUMBER,
17 ID INTEGER,
18 PARENT_ID INTEGER,
19 DEPTH INTEGER,
20 POSITION INTEGER,
21 COST INTEGER,
22 CARDINALITY INTEGER,
23 BYTES INTEGER,
24 OTHER_TAG VARCHAR2(255),
25 PARTITION_START VARCHAR2(255),
26 PARTITION_STOP VARCHAR2(255),
27 PARTITION_ID INTEGER,
28 OTHER LONG,
29 OTHER_XML CLOB,
30 DISTRIBUTION VARCHAR2(30),
31 CPU_COST INTEGER,
32 IO_COST INTEGER,
33 TEMP_SPACE INTEGER,
34 ACCESS_PREDICATES VARCHAR2(4000),
35 FILTER_PREDICATES VARCHAR2(4000),
36 PROJECTION VARCHAR2(4000),
37 TIME INTEGER,
38 QBLOCK_NAME VARCHAR2(30)
39 )
40 on commit preserve rows;
Table created
SQL> -- Grant/Revoke object privileges
SQL> grant select, insert, update, delete on PLAN_TABLE$ to PUBLIC;
Grant succeeded
SQL> -- Create the synonym
SQL> create or replace public synonym PLAN_TABLE--重建同义词PLAN_TABLE
2 for SYS.PLAN_TABLE$;
Synonym created
SQL> explain plan for select 1 from dual;--执行计划解析成功
Explained
SQL>