不设置sga_max_size参数

SQL> show sga;

Total System Global Area  420549952 bytes
Fixed Size                   451904 bytes
Variable Size             201326592 bytes
Database Buffers          218103808 bytes
Redo Buffers                 667648 bytes

SQL> select sum(bytes)from v$sgastat;

SUM(BYTES)
----------
 420538688

SQL> !ipcs -m       

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x9ba476a4 65536      oracle    640        440401920  65       

SQL> alter system set db_cache_size=300M;
alter system set db_cache_size=300M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

1)当sga_max_size不设置时，数据库启动时，会使用数据库默认分配sga大小为初始化值
2)当sga_max_size不设置时，不能在线扩展组件内存大小(使得sga大于当前大小)

设置sga_max_size参数

SQL>  alter system set sga_max_size=600M scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup 
ORACLE instance started.

Total System Global Area  638654020 bytes
Fixed Size                   452164 bytes
Variable Size             419430400 bytes
Database Buffers          218103808 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> !ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x9ba476a4 98304      oracle    640        658505728  65                      

SQL> select sum(bytes)from v$sgastat;

SUM(BYTES)
----------
 420538948

SQL> alter system set db_cache_size=405M;

System altered.


SQL> select 638654020/1024/1024 from dual;

638654020/1024/1024
-------------------
         609.067936

说明sga中剩余空闲内存0.01M
SQL> select 609.067936-sum(bytes)/1024/1024 from v$sgastat;

609.067936-SUM(BYTES)/1024/1024
-------------------------------
                     .010742244

继续增加组件大小
SQL> alter system set db_cache_size=416M;

System altered.

SQL> select sum(bytes)/1024/1024 from v$sgastat;

SUM(BYTES)/1024/1024
--------------------
          609.057194

SQL>  select 638654020/1024/1024 from dual;

638654020/1024/1024
-------------------
         609.067936

SQL> alter system set db_cache_size=417M;   
alter system set db_cache_size=417M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

这里显示，当db_cache_size增加到415M的时候，sga只有0.01M剩余
但是直到db_cache_size增加到417的时候才报错

1)当sga有剩余时，可以动态调整sga中的部分组件(java_pool_size不能在线设置)
2)当sga没有剩余时，如果继续增加某组件的内存，在一定的范围内，sga会自动调整其他组件大小，以实用该值增加

关于sga_max_size总结
1)如果你的系统内存比较紧张，对停机时间要求不是特别严格，那可以不设置sga_max_size参数，这样在重启数据库设置sga组件的时候，不会因忘记设置sga_max_size而导致不能正常启动
2)如果你的系统内存充足，对停机有严格限制，那建议设置一个较大的sga_max_size，后续可以根据需求动态在线调整sga部分组件

1.SMON_SCN_TIME表基础知识
0)作用
由smon收集scn和time映射关系，用于flashback/查询scn和time对应关系等操作

1)保留条数
官方文档给出说明instance number N * 12 times per hour * 24 hours * 5 days = 1440N rows，因为每次的时间间隔不是非常准确的5分钟，所以在具体的条数在实际生产环境中有一定的出入

2)采集和删除
smon进程没5分钟采集一次插入到SMON_SCN_TIME表中，同时将删除历史数据(超过5天前数据)，采用下面语句
delete from smon_scn_time where thread=0 and time_mp = (select min(time_mp) from smon_scn_time where thread=0)，如果有时候index出了问题，导致该语句执行很慢

3)当查询scn对应time，如果scn超过SMON_SCN_TIME表范围，将提示错误；或者查询time对应的scn，如果超过范围也同样报错。

2.当SMON_SCN_TIME表出现问题时，truncate操作语句

--找出CLUSTER
Select dbms_metadata.get_ddl('TABLE','SMON_SCN_TIME','SYS') FROM DUAL ;

9i
truncate CLUSTER "SYS"."SMON_SCN_TO_TIME";

10g
truncate  CLUSTER "SYS"."SMON_SCN_TO_TIME";

11g
truncate CLUSTER "SYS"."SMON_SCN_TO_TIME_AUX";

3.停止/开启smon进程收集scn信息

stop
alter system set events '12500 trace name context forever, level 10';

start
alter system set events '12500 trace name context off';

4.index异常处理

--找出index
Select index_name,index_type,owner from dba_indexes where table_name='SMON_SCN_TIME';
--对应index创建语句
Select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_TIM_IDX','SYS') FROM DUAL;

--重建index
drop index smon_scn_time_scn_idx;
drop index smon_scn_time_tim_idx;
create unique index smon_scn_time_scn_idx on smon_scn_time(scn);
create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp);
analyze table smon_scn_time validate structure cascade online;

--rebuild index
alter index sys.smon_scn_time_scn_idx rebuild online;
alter index sys.smon_scn_time_tim_idx rebuild online;
analyze table smon_scn_time validate structure cascade online;

补充知识点scn计算方法SCN=(SCN_WRP * 4294967296) + SCN_BAS

在很多时候，希望给用户最小的权限，让其访问系统数据字典，检查数据库的运行状态。这种事情在乙方的工作中非常常见。下面介绍三种方法处理这个问题
0.select any table权限
这里说明select any table不能直接访问数据字典

SQL> conn / as sysdba
Connected.
SQL> create user xff_any identified by xifenfei;

User created.

SQL> grant connect,select any table to xff_any;

Grant succeeded.

SQL> conn xff_any/xifenfei
Connected.
SQL>  select count(*) from dba_users;
 select count(*) from dba_users
                      *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select count(*) from chf.t_xff;

  COUNT(*)
----------
      2770

select any table默认情况下，只能访问业务的表，但是不能访问数据字典的数据。所以单纯的这个属性不能满足需求。

1.SELECT ANY DICTIONARY权限

SQL> conn / as sysdba 
Connected.
SQL> create user xff_DICTIONARY identified by xifenfei;

User created.

SQL> grant connect to xff_DICTIONARY;

Grant succeeded.

SQL> conn xff_DICTIONARY/xifenfei
Connected.
SQL> select count(*) from dba_users;
select count(*) from dba_users
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> conn / as sysdba
Connected.
SQL> grant SELECT ANY DICTIONARY to xff_DICTIONARY;

Grant succeeded.

SQL> conn xff_DICTIONARY/xifenfei
Connected.
SQL> select count(*) from dba_users;

  COUNT(*)
----------
        32

SQL> select count(*) from chf.t_xff;
select count(*) from chf.t_xff
                         *
ERROR at line 1:
ORA-00942: table or view does not exist

这里可以看出SELECT ANY DICTIONARY权限只能访问数据字典，不能访问业务的表，访问业务的表需要另外授权

2.SELECT_CATALOG_ROLE角色

SQL> conn / as sysdba
Connected.
SQL> create user xff_CATALOG identified by xifenfei;

User created.

SQL> grant connect,SELECT_CATALOG_ROLE to xff_CATALOG;

Grant succeeded.

SQL> conn xff_CATALOG/xifenfei
Connected.
SQL> select count(*) from dba_users;

  COUNT(*)
----------
        33

SQL> select count(*) from chf.t_xff;
select count(*) from chf.t_xff
                         *
ERROR at line 1:
ORA-00942: table or view does not exist

这里可以看出SELECT_CATALOG_ROLE权限只能访问数据字典，不能访问业务的表，访问业务的表需要另外授权

3.O7_DICTIONARY_ACCESSIBILITY参数

SQL> conn / as sysdba
Connected.
SQL> create user xff_O7 identified by xifenfei;

User created.

SQL> grant connect to xff_o7;

Grant succeeded.

SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true;
alter system set O7_DICTIONARY_ACCESSIBILITY=true
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup 
ORACLE instance started.

Total System Global Area  368263168 bytes
Fixed Size                  1345016 bytes
Variable Size             306186760 bytes
Database Buffers           54525952 bytes
Redo Buffers                6205440 bytes
Database mounted.
Database opened.
SQL> conn xff_o7/xifenfei
Connected.
SQL> select count(*) from dba_users;

  COUNT(*)
----------
        34

这里通过O7_DICTIONARY_ACCESSIBILITY和SELECT ANY TABLE权限，实现访问业务数据和数据字典

使用oradebug

session 1
--查询出需要跟踪会话v$process.pid

session 2
SQL> oradebug SETORAPID 15
Unix process pid: 14851, image: oracle@xifenfei (TNS V1-V3)

SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 4
Statement processed.

--执行session 1

session 1
SQL> oradebug EVENT 10046 trace name context off
Statement processed.

SQL> oradebug TRACEFILE_NAME
/u01/oracle/admin/xifenfei/udump/xff_ora_14851.trc

使用dbms_system.set_sql_trace_in_session

session 1
--查询出需要跟踪会话的sid，SERIAL#

session 2
SQL> exec dbms_system.set_sql_trace_in_session(12,130,true);

PL/SQL procedure successfully completed.

--session 1执行sql

session2关闭跟踪
SQL> exec dbms_system.set_sql_trace_in_session(12,130,false);

PL/SQL procedure successfully completed.

--通过session 1找出trace文件