前提：
当执行了alter system set sga_target=700 scope=700m退出数据库重启时发现错误：
ORA-00823: Specified value of sga_target greater than sga_max_size
这说明：设置的sga_target值大于了sga_max_size.
解决步骤：
1． 更改spfileSID.ora文件中的sga_target 为0；
2． 另启动一个会话并重启数据库，问题解决.
建议：
a.在设置sga_target是要先查看一下sga_max_size的大小。命令如下：
sql>select pool,sum(bytes) from v_$sgastat group by pool;
或：
sql>show parameter sga_max_size;
b.备份参数文件spfileSID.ora
本文链接
[oracle@oracle111 bin]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 3 09:36:07 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> select instance_name from v$instance;
select instance_name from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/app/product/10.2.0/db_1/dbs/initCMS.ora'
SQL> exit
Disconnected
[oracle@oracle111 dbs]$ cp /oracle/app/admin/cms/pfile/init.ora.229201284028 /oracle/app/product/10.2.0/db_1/dbs/initCMS.ora
[oracle@oracle111 dbs]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 3 09:42:45 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> select instance_name from v$instance;
select instance_name from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
SQL> startup;
ORACLE instance started.
Total System Global Area 599785472 bytes
Fixed Size 2022600 bytes
Variable Size 163578680 bytes
Database Buffers 427819008 bytes
Redo Buffers 6365184 bytes
Database mounted.
Database opened.
本文链接
这是我的写法,欢迎批评指正:
假设,我们要找出table_name这张表下字段field1的最大值,然后取到这一行的field2字段的值,可以采取以下方法----
select decode(count(*),0,default_value,substr(max(field1||field2),field1_length)) into String_variable from table_name where ...
注:
1.如果field1的长度不固定,我们可以采取补齐位数或者添加分隔符的方法截取到field2.
2.如果根据筛选条件查找不到数据,我们可以通过指定default_value给String_variable一个默认值.
本文链接