说明：

• 开头带有“SQL>”标识的，表示是一个sql语句。
• 简单介绍SGA参数的调整、表空间的调整。

调整sga_max_size、processes、sessions、transactions大小。

• 下面显示oracle默认安装完成后的部分参数。

SQL> select * from v$parameter t where t.name in ('sga_max_size','sga_target','memory_max_target','memory_target','pga_aggregate_target', 'db_block_buffers','db_block_size','shared_pool_size','log_buffer','JAVA_pool_size','large_pool_size','processes','sessions','transactions','workarea_size_policy');

 

• 以上参数，value=0的为oracle自动调整，无需修改；实际需要调整的就是sga_max_size、memory_target、processes、sessions、transactions。

a) sga_max_size：调整成内存的40%-50%；memory_target值= sga_max_size值（命令行执行）

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

b) processes默认150，可以先调整成1000个，后续可以根据业务请求量调整。

SQL> alter system set processes=1000 scope=spfile;

c) sessions值根据sessions=(1.5*processes)+22调整。

SQL> alter system set sessions=1522 scope=spfile;

d) transactions 值根据transactions＝1.1*sessions调整。

SQL> alter system set transactions=1675 scope=spfile;

 

• 修改完后，重启数据库，检查参数。

 

打开归档模式。

• 检查，修改为归档模式，并设置参数。

SQL>archive log list

SQL>shutdown immediate

SQL>startup mount

SQL>alter database archivelog

SQL> alter system set log_archive_dest_1='location=/data/oradata/arch';

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

SQL> shutdown immediate;

SQL>startup;

 

 

• 测试

 

 

 

打开闪回。

• 确保数据库运行在归档模式下

 

• 参数设置

SQL>alter system set db_recovery_file_dest_size=2G;

SQL> alter system set db_recovery_file_dest='/opt/App/oracle/fast_recovery_area';

SQL>alter system set db_flashback_retention_target=10080;

SQL>alter system set undo_retention=10800;

SQL>shutdown immediate;

SQL>startup mount

SQL>alter database flashback on;

SQL>alter database open;

 

 

• 注意：归档日志目录，默认是放在闪回区的；建议换到另外的目录（log_archive_dest_1）。否则，有可能因为归档日志清理不及时，闪回区的大小（db_recovery_file_dest_size）用光，导致归档日志无法写入，从而数据库hang住。

新建业务用户表空间、业务用户，调整表空间。

• 新建业务表空间（建2个：分别用于存业务表和表索引；生成环境文件大小30G，本文仅为演示）

SQL> create tablespace BUSIDATA datafile '/data/oradata/orcl/BUSIDATA01.dbf' size 1G autoextend on;

SQL> create tablespace BUSIIDX datafile '/data/oradata/orcl/BUSIIDX01.dbf' size 200M autoextend on;

 

• 对于并发量大，SQL里排序、分组又多的情况，可以建临时表空间组(非必须）

SQL>create temporary tablespace tmp01 tempfile '/data/oradata/orcl/tmp01.dbf' size 50M autoextend on;

SQL>create temporary tablespace tmp02 tempfile '/data/oradata/orcl/tmp02.dbf' size 50M autoextend on;

SQL>create temporary tablespace tmp03 tempfile '/data/oradata/orcl/tmp03.dbf' size 50M autoextend on;

SQL>alter tablespace tmp01 tablespace group tempgrp;

SQL>alter tablespace tmp02 tablespace group tempgrp;

SQL>alter tablespace tmp03 tablespace group tempgrp;

SQL>alter database default temporary tablespace tempgrp;

 

• 新建业务用户BUSI，赋权限。

SQL>create user busi identified by "busi123" default tablespace BUSIDATA temporary tablespace tempgrp;

 

• 注意：建索引的时候，需要明确指定tablespace为BUSIIDX。
• 更换用户表的表空间：由于建表语句不规范，经常会存在这样的情况：用户所属表的存储表空间不一样，有的表存储在users甚至是system表空间下。这时需要调整表的存储表空间。需要注意的是，移动表空间时会产生锁，而且表上的索引有可能失效，因此操作时务必避开业务高峰。

以下sql中带“<>”根据实际情况改写：

SQL>alter table <tablename> move tablespace <newtbs>;

SQL>select * from user_indexes t where t.table_name=' <tablename> ' and t.status<>'VALID';

SQL>alter index <idx_name> rebuild tablespace <new_tab_space_name>;

• 调整表空间大小：

SQL>select dbf.tablespace_name, dbf.totalspace "总量(M)", dbf.totalblocks as 总块数,

dfs.freespace "剩余总量(M)", dfs.freeblocks "剩余块数", (dfs.freespace / dbf.totalspace) * 100 "空闲比例"

from (select t.tablespace_name, sum(t.bytes) / 1024 / 1024 totalspace, sum(t.blocks) totalblocks

from dba_data_files t group by t.tablespace_name) dbf,

(select tt.tablespace_name, sum(tt.bytes) / 1024 / 1024 freespace, sum(tt.blocks) freeblocks

from dba_free_space tt group by tt.tablespace_name) dfs

where trim(dbf.tablespace_name) = trim(dfs.tablespace_name);

a) 修改已存在数据文件的大小

SQL>alter database datafile '/data/oradata/orcl/SYSTEM01.DBF' resize 4096m;

b) 增加数据文件

SQL>ALTER TABLESPACE USERS ADD DATAFILE '/data/oradata/orcl/ USERS01.DBF' SIZE 20480M [AUTOEXTEND ON] [NEXT 100M MAXSIZE UNLIMITED];