Oracle 10g參數(shù)調(diào)整(processes和sga)

– 1.oracle連接數(shù)調(diào)整 Oracle連接數(shù)默認(rèn)為150，通過(guò)一下設(shè)置把oracle的連接數(shù)調(diào)整到500 用sys,以sysdba權(quán)限登錄數(shù)據(jù)庫(kù)（推薦使用PL/SQL） 以下操作請(qǐng)?jiān)贑ommand Window下執(zhí)行 SQL show parameter processes; NAME TYPE VALUE ——————————–

–

1.Oracle連接數(shù)調(diào)整
Oracle連接數(shù)默認(rèn)為150，通過(guò)一下設(shè)置把oracle的連接數(shù)調(diào)整到500

用sys,以sysdba權(quán)限登錄數(shù)據(jù)庫(kù)（推薦使用PL/SQL）
以下操作請(qǐng)?jiān)贑ommand Window下執(zhí)行
SQL> show parameter processes;

NAME???????????????????????????????? TYPE??????? VALUE
———————————— ———– ——————————
aq_tm_processes????????????????????? integer???? 0
db_writer_processes????????????????? integer???? 1
gcs_server_processes???????????????? integer???? 0
job_queue_processes????????????????? integer???? 10
log_archive_max_processes??????????? integer???? 2
processes??????????????????????????? integer???? 150

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

System altered

SQL> show parameter processes;

NAME???????????????????????????????? TYPE??????? VALUE
———————————— ———– ——————————
aq_tm_processes????????????????????? integer???? 0
db_writer_processes????????????????? integer???? 1
gcs_server_processes???????????????? integer???? 0
job_queue_processes????????????????? integer???? 10
log_archive_max_processes??????????? integer???? 2
processes??????????????????????????? integer???? 150

SQL> create pfile from spfile;

Done

重新啟動(dòng)下Oracle數(shù)據(jù)庫(kù)（即oracle服務(wù)）

重啟好后，用sys,以sysdba權(quán)限登錄數(shù)據(jù)庫(kù)
在Command Window下執(zhí)行
SQL> show parameter processes;

NAME???????????????????????????????? TYPE??????? VALUE
———————————— ———– ——————————
aq_tm_processes????????????????????? integer???? 0
db_writer_processes????????????????? integer???? 1
gcs_server_processes???????????????? integer???? 0
job_queue_processes????????????????? integer???? 10
log_archive_max_processes??????????? integer???? 2
processes??????????????????????????? integer???? 500

查看你這一行數(shù)據(jù)
processes??????????????????????????? integer???? 500
即表明數(shù)據(jù)庫(kù)的連接數(shù)被成功修改為500
2.調(diào)整Oracle數(shù)據(jù)庫(kù)的sga（相關(guān)參數(shù)的設(shè)置與服務(wù)器的物理內(nèi)存有關(guān)，下面參數(shù)適用內(nèi)存為2G的服務(wù)器，如果內(nèi)存為其他值的請(qǐng)與開(kāi)發(fā)這邊聯(lián)系，確定具體的參數(shù)值）
用sys,以sysdba權(quán)限登錄數(shù)據(jù)庫(kù)（推薦使用PL/SQL）
以下操作請(qǐng)?jiān)贑ommand Window下執(zhí)行

–要?jiǎng)討B(tài)修改一定要用spfile啟動(dòng)。如果現(xiàn)在是用pfile啟動(dòng)，可以這樣切換成spfile啟動(dòng)：
–

–create spfile from pfile;
–shutdown immediate;
–startup;
–用show parameter spfile 查看如果values對(duì)應(yīng)有值，表示是spfile啟動(dòng)的。否則就是pfile啟動(dòng)的。

SQL> show parameter sga;

NAME???????????????????????????????? TYPE??????? VALUE
———————————— ———– ——————————
lock_sga???????????????????????????? boolean???? FALSE
pre_page_sga???????????????????????? boolean???? FALSE
sga_max_size???????????????????????? big integer 164M
sga_target?????????????????????????? big integer 0

SQL> show parameter statistics_level;

NAME???????????????????????????????? TYPE??????? VALUE
———————————— ———– ——————————
statistics_level???????????????????? string????? TYPICAL

保證STATISTICS_LEVEL 參數(shù)設(shè)置為 TYPICAL (the default) 或者 ALL

如果不是TYPICAL 或者 ALL，那么請(qǐng)執(zhí)行
SQL> alter system set statistics_level = TYPICAL;

System altered

SQL> commit;

Commit complete

SQL> alter system set sga_max_size = 800m scope=spfile;

System altered

SQL> commit;

Commit complete

至此請(qǐng)重新啟動(dòng)下Oracle數(shù)據(jù)庫(kù)（即oracle服務(wù)）

重啟好后，用sys,以sysdba權(quán)限登錄數(shù)據(jù)庫(kù)
在Command Window下執(zhí)行
SQL> show parameter sga;

NAME???????????????????????????????? TYPE??????? VALUE
———————————— ———– ——————————
lock_sga???????????????????????????? boolean???? FALSE
pre_page_sga???????????????????????? boolean???? FALSE
sga_max_size???????????????????????? big integer 800M
sga_target?????????????????????????? big integer 0

SQL> alter system set sga_target = 700m;

SQL> commit;

Commit complete

完成所有操作后，完成對(duì)Oracle數(shù)據(jù)庫(kù)sga的調(diào)整