10G取消自动分析怎么这么难了？

显示全部楼层 · 2006-4-17 13:46:34

执行
SQL> alter system set statistics_level = basic scope=both;
alter system set statistics_level = basic scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled
听PUBER说要把sga_target给注销了才可以
只有把sga_target=1536163840给注销了
startup pfile=/u01/app/oracle/admin/prod/pfile/init.ora
ORACLE instance started.
Total System Global Area113246208 bytes
Fixed Size
778032 bytes
Variable Size
61087952 bytes
Database Buffers 50331648 bytes
Redo Buffers
1048576 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile='/u01/app/oracle/admin/prod/pfile/init.ora';
File created.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area113246208 bytes
Fixed Size
778032 bytes
Variable Size
61087952 bytes
Database Buffers 50331648 bytes
Redo Buffers
1048576 bytes
Database mounted.
Database opened.
SQL> connect live/live@prod
ERROR:
ORA-00604: error occurred at recursive SQL level 3
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared
pool","select count(*) from sys.job...","Typecheck heap","kgghteInit&quot

Error accessing package DBMS_APPLICATION_INFO
Error accessing package DBMS_APPLICATION_INFO
请大家多多指点

千问 · 2006-4-17 13:46:34

楼主，你在10g里
conn / as sysdba
这样登陆可以吗？我怎么就不可以？

千问 · 2006-4-17 13:46:34

我说的方法你测试了吗？

千问 · 2006-4-17 13:46:34

当你去掉sga_target=1536163840后
需要在文件中加入一下配置数据
db_cache_size=1258291200
java_pool_size=52428800
large_pool_size=52428800
shared_pool_size=209715200
其它步骤与你写的一致

千问 · 2006-4-17 13:46:34

在系统提示符后输入
$
sqlplus /nolog
SQL> connect / as sysdba

千问 · 2006-4-17 13:46:34

你只是想取消自动分析，没必要statistics_level 啊
conn / as sysdba
exec dbms_scheduler.disable('GATHER_STATS_JOB');

千问 · 2006-4-17 13:46:34

每试过，等LZ测试结果

千问 · 2006-4-17 13:46:34

最初由 sbcel 发布
[B]当你去掉sga_target=1536163840后
需要在文件中加入一下配置数据
db_cache_size=1258291200
java_pool_size=52428800
large_pool_size=52428800
shared_pool_size=209715200
其它步骤与你写的一致 [/B]
不需要配置，默认就有
__db_cache_size=
__java_pool_size=
__large_pool_size=
__shared_pool_size=
__streams_pool_size=