sga手动改自动，以及参数文件管理

sga手动改自动：

1：If you are migrating from a manual management scheme, execute the following query on the instance running in manual mode to get a value for SGA_TARGET:    

SELECT (  
   (SELECT SUM(value) FROM V$SGA) -  
   (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)  
   ) "SGA_TARGET"  
FROM DUAL;

2：ALTER SYSTEM SET SGA_TARGET=value [SCOPE={SPFILE|MEMORY|BOTH}]

When SGA_TARGET is not set, the automatic shared memory management feature is not enabled.

下面是sga相关的视图及说明：

View	Description
V$SGA	Displays summary information about the system global area (SGA).
V$SGAINFO	Displays size information about the SGA, including the sizes of different SGA components, the granule size, and free memory.
V$SGASTAT	Displays detailed information about the SGA.
V$SGA_DYNAMIC_COMPONENTS	Displays information about the dynamic SGA components. This view summarizes information based on all completed SGA resize operations since instance startup.
V$SGA_DYNAMIC_FREE_MEMORY	Displays information about the amount of SGA memory available for future dynamic SGA resize operations.
V$SGA_RESIZE_OPS	Displays information about the last 400 completed SGA resize operations.
V$SGA_CURRENT_RESIZE_OPS	Displays information about SGA resize operations that are currently in progress. A resize operation is an enlargement or reduction of a dynamic SGA component.
V$SGA_TARGET_ADVICE	Displays information that helps you tuneSGA_TARGET. For more information, seeOracle Database Performance Tuning Guide.

参数文件管理：

CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora'
       FROM PFILE='/u01/oracle/dbs/test_init.ora';

Note

If you create a server parameter file in a location other than the default location, you must create a text initialization parameter file that points to the server parameter file

Upon startup, the instance first searches for the server parameter file namedspfile$ORACLE_SID.ora, and if not found, searches forspfile.ora. Using spfile.ora enables all Real Application Cluster (RAC) instances to use the same server parameter file.

If neither server parameter file is found, the instance searches for the text initialization parameter fileinit$ORACLE_SID.ora.

SCOPE Clause	Description
SCOPE = SPFILE	The change is applied in the server parameter file only. The effect is as follows: For dynamic parameters, the change is effective at the next startup and is persistent. For static parameters, the behavior is the same as for dynamic parameters. This is the onlySCOPE specification allowed for static parameters.
SCOPE = MEMORY	The change is applied in memory only. The effect is as follows: For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated. For static parameters, this specification is not allowed.
SCOPE = BOTH	The change is applied in both the server parameter file and memory. The effect is as follows: For dynamic parameters, the effect is immediate and persistent. For static parameters, this specification is not allowed.

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=50
                 COMMENT='temporary change on Nov 29'
                 SCOPE=MEMORY;

Method	Description
SHOW PARAMETERS	This SQL*Plus command displays the values of parameters currently in use.
CREATE PFILE	This SQL statement creates a text initialization parameter file from the binary server parameter file.
V$PARAMETER	This view displays the values of parameters currently in effect.
V$PARAMETER2	This view displays the values of parameters currently in effect. It is easier to distinguish list parameter values in this view because each list parameter value appears as a row.
V$SPPARAMETER	This view displays the current contents of the server parameter file. The view returnsFALSE values in theISSPECIFIED column if a server parameter file is not being used by the instance.

如何查看哪些参数是动态还是静态？

select   issys_modifiable from V$PARAMETER  

DEFERRED   ----->也是动态参数，对于当前session无效，下一个session生效
FALSE       ----->静态参数，需要重启db才能生效
IMMEDIATE  ----->动态参数，立即生效

游标参数 open_cursors   session_cached_cursors

设置建议脚本：

select
  'session_cached_cursors'  parameter,
  lpad(value, 5)  value,
  decode(value, 0, '  n/a', to_char(100 * used / value, '990') || '%')  usage
from
  ( select
      max(s.value)  used
    from
      v$statname  n,
      v$sesstat  s
    where
      n.name = 'session cursor cache count' and
      s.statistic# = n.statistic#
  ),
  ( select
      value
    from
      v$parameter
    where
      name = 'session_cached_cursors'
  )
union all
select
  'open_cursors'   parameter,
  lpad(value, 5)   value,
  to_char(100 * used / value,  '990') || '%' usage
from
  ( select
      max(sum(s.value))  used
    from
      v$statname  n,
      v$sesstat  s
    where
      n.name in ('opened cursors current', 'session cursor cache count') and
      s.statistic# = n.statistic#
    group by
      s.sid
  ),
  ( select
      value
    from
      v$parameter
    where
      name = 'open_cursors'
  );

详细内容请查看官方文档：Oracle® Database Administrator's Guide10g Release 2 (10.2) 第2章

链接：oracle官方链接