存储大纲的疑问,谢谢!

显示全部楼层 · 2007-3-1 11:03:29

我生成了存储大纲后,比如
SQL> select category,sql_text fromoutln.ol$;
CATEGORY
------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
DEFAULT
SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,'integ
DEFAULT
SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,'integ
然后我退出当前会话,再登陆为什么ol$表里边不保存这些存储大纲,那怎么才能自动保存下来呢?

千问 · 2007-3-1 11:03:29

谁能说说呢!

千问 · 2007-3-1 11:03:29

DBMS_OUTLN
Oracle creates stored outlines automatically when you set the initialization parameter CREATE_STORED_OUTLINES to true.
[ 本帖最后由 jidongzheng 于 2009-3-2 16:30 编辑 ]

千问 · 2007-3-1 11:03:29

举个例子,谢谢!

千问 · 2007-3-1 11:03:29

SQL> select * from user_outlines;

NAME
CATEGORY
USEDTIMESTAMP VERSION
SQL_TEXT
SIGNATURE
------------------------------ ------------------------------ --------- ----------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------
这时为空。

SQL> alter session set CREATE_STORED_OUTLINES = true;

Session altered

SQL> select * from user_outlines ;

NAME
CATEGORY
USEDTIMESTAMP VERSION
SQL_TEXT
SIGNATURE
------------------------------ ------------------------------ --------- ----------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------
SYS_OUTLINE_090302163524718DEFAULT
UNUSED2009-3-2 16 9.2.0.1.0
select 'x' from dual
45A6574D93A8CB5E266B78AA371B0908
SYS_OUTLINE_090302163528953DEFAULT
UNUSED2009-3-2 16 9.2.0.1.0
这时，可以存储了。
disc
再重新连接
SQL> conn db_test/anyun
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as db_test

SQL> select * from user_outlines;

NAME
CATEGORY
USEDTIMESTAMP VERSION
SQL_TEXT
SIGNATURE
------------------------------ ------------------------------ --------- ----------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------
SYS_OUTLINE_090302163524718DEFAULT
UNUSED2009-3-2 16 9.2.0.1.0
select 'x' from dual
45A6574D93A8CB5E266B78AA371B0908
SYS_OUTLINE_090302163528953DEFAULT
UNUSED2009-3-2 16 9.2.0.1.0
4D75DE5F82ECB9478C59328D5F36C594

select * from user_outlines

SYS_OUTLINE_090302163542765DEFAULT
UNUSED2009-3-2 16 9.2.0.1.0
C71955C849844FE06164621B694F7E35

select * from newuser

SYS_OUTLINE_090302163610937DEFAULT
UNUSED2009-3-2 16 9.2.0.1.0
select * from plan_table
E7F79B9BAD2B2B738CE81C2AC0319C99
SYS_OUTLINE_090302163610968DEFAULT
UNUSED2009-3-2 16 9.2.0.1.0
delete plan_table where statement_id = 'DB_TEST'
2B06255C557DCEB677C2EDD593208295
SYS_OUTLINE_090302163611000DEFAULT
UNUSED2009-3-2 16 9.2.0.1.0
select level, plan_table.* from plan_table where statement_id = 'DB_TEST'AD8D5A81BDBF14FC36309CBF149E6829

conne

SYS_OUTLINE_090302163623500DEFAULT
UNUSED2009-3-2 16 9.2.0.1.0
select name from v$statname order by statistic#
7FB720F0C6FA3CA54DC6B6CA0B18AD0F
SYS_OUTLINE_090302163644828DEFAULT
UNUSED2009-3-2 16 9.2.0.1.0
select null from all_synonyms where 1=0
4CAD636420E6A151B7B734E6DB9D2299
SYS_OUTLINE_090302163644890DEFAULT
UNUSED2009-3-2 16 9.2.0.1.0
select null from dba_synonyms where 1=0
751A27DFF4C2629E347482C7A113BE5E

9 rows selected

千问 · 2007-3-1 11:03:29

对啊,那我就郁闷了,我也是在这么试的,难道是因为我用的是system,我换个普通用户试下,谢谢!

千问 · 2007-3-1 11:03:29

那能看看我的出在什么问题上了吗?
SQL> select * from user_outlines;
未选定行
SQL> show parameter outline
NAME
TYPE
------------------------------------ ----------------------
VALUE
------------------------------
create_stored_outlines
string
MYCAT
SQL> select tname from tab;
TNAME
------------------------------------------------------------
T
SALES_V
T6
T8
T7
T9
T10
T3
T4
T5
T1
TNAME
------------------------------------------------------------
T2
BIN$8EG4B8BpS7uLizNRtIFAbg==$0
已选择13行。
SQL> select count(*) from user_outlines;
COUNT(*)
----------
1
C:\>sqlplus g/g
SQL*Plus: Release 10.2.0.3.0 - Production on 星期一 3月 2 20:36:54 2009
Copyright (c) 1982, 2006, Oracle.All Rights Reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select count(*) from user_outlines;
COUNT(*)
----------
0
SQL> show parameter outline
NAME
TYPE
------------------------------------ ----------------------
VALUE
------------------------------
create_stored_outlines
string
MYCAT

千问 · 2007-3-1 11:03:29

估计版本或者什么别的问题导致的吧!换了台机器是可以的.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@netdb oracle]$ sqlplus g/g
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 2 21:44:30 2009
Copyright (c) 1982, 2005, Oracle.All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select name,category,used,sql_text from user_outlines;
NAME
CATEGORY
USED
------------------------------ ------------------------------ ------
SQL_TEXT
--------------------------------------------------------------------------------
SYS_OUTLINE_09030221405975701DEFAULT
UNUSED
select tname from tab
SYS_OUTLINE_09030221412629202DEFAULT
UNUSED
select count(*) from ol$
SYS_OUTLINE_09030221414623403DEFAULT
UNUSED
select count(*) from user_outlines

SQL> select name,category,used,sql_text from user_outlines;
NAME
CATEGORY
USED
------------------------------ ------------------------------ ------
SQL_TEXT
--------------------------------------------------------------------------------
SYS_OUTLINE_09030221405975701DEFAULT
UNUSED
select tname from tab
SYS_OUTLINE_09030221412629202DEFAULT
UNUSED
select count(*) from ol$
SYS_OUTLINE_09030221414623403DEFAULT
UNUSED
select count(*) from user_outlines