Oracle10G_install_RH4

RedHat4設定

Network Configuration
It is usually best to configure database servers with a static IP address. To do so, click on Edit .
A pop-up window appears. Uncheck the Configure using DHCP box, and enter the IP Address and Netmask for the server. Be sure that Activate on boot is checked, and click on OK .
In the Hostname box, select manually and enter the hostname.
In the Miscellaneous Settings box, enter the remaining network settings.

Firewall Configuration
For the purposes of this walk-through, no firewall is configured. Select No firewall
.

Time Zone Selection
Choose the time settings that are appropriate for your area. Setting the system clock to UTC is usually a good practice for servers. To do so, click on System clock uses UTC.


開設安裝群組
groupadd oinstall
groupadd dba

開設安裝user
useradd -g oinstall -G dba oracle
passwd oracle

開設安裝目錄
mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01/app/oracle

修改LINUX參數
#vi /etc/sysctl.conf
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.rmem_max=262144
net.core.wmem_max=262144


#vi /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

#vi /etc/pam.d/login
session required /lib/security/pam_limits.so

#vi /etc/profile
if [ \$USER = "oracle" ]; then
if [ \$SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi

#vi /etc/csh.login
if ( \$USER == "oracle" ) then
limit maxproc 16384
limit descriptors 65536
umask 022
endif

登出改用oracle 登入
#vi .bash_profile
umask 022
PATH=$PATH:$HOME/bin
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE
ORACLE_SID=orcl
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID
export PATH
unset USERNAME

開始安裝

oracle 登入
執行安裝程式
/mnt/cdrom/database/runInstaller

安裝重點
先裝ORACLE SEVER, 不要create database

裝完ORACLE SEVER後,
第二步再窩使用 dbca 去 create database

裝完後.
執行 . oraenv
指定oracle sid及oracle home 每一次重新開機後都要執行一次
$ . oraenv
ORACLE_SID = [oracle] ? demo1
Run SQL*Plus:

$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 27 15:40:29 2005Copyright (c) 1982, 2005, Oracle. All rights reserved.Enter user-name: / as sysdbaConnected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL>

$ emctl start dbconsole
http://192.168.42.4:1158/em <--從WEB連進管理端

http://192.168.42.4:5660/isqlplus <--從WEB連進管理端

http://192.168.42.4:5660/isqlplus/dba <--從WEB連進管理端

$ isqlplusctl start
$ isqlplusctl stop

$ lsnrctl start
$ lsnrctl stop

oracle 10G安裝參考網址
http://www.oracle.com/technology/pub/articles/smiley_10gdb_install.html

在ASP中使用SQL LOCK TABLE範例

Set XACT_ABORT ON
Begin tran
Select @SNO = strNum from test With (XLOCK) Where strYMD = @SToday
Update test set strNum = strNum + 1 where strYMD = @SToday
if @@Error <> 0
Rollback Tran
else
Commit Tran
Set XACT_ABORT OFF

oracle10G關於file group

就我的理解, dbms_file_group這個元件好像是用來
管理data dump file 用的. 它依層次分為group , version (版本), file 三種層次
一.建立file group
begin
dbms_file_group.create_file_group(
file_group_name => 'scott.test_fg',
min_versions =>6,max_versions => 14,
retention_days =>31,
default_directory=>'SH_FILES_DIR',
comments=>'For storing monthly sales data'


);
end;
相關view :select * from dba_file_groups

二.建立file group version
begin
dbms_file_group.create_version(
file_group_name =>'scott.test_fg',
version_name =>'SH_MAY2007',
comments =>'For test'
);
end;
相關view : select * from dba_file_group_versions

三. 將dump file 加入到file group version中
begin
dbms_file_group.add_file(
file_group_name=>'scott.test_fg',
file_name=>'PDUMP_DIR1.TESTEXP01.DMP',
file_type=>dbms_file_group.export_dump_file,
version_name =>'SH_MAY2007'

);
end;
相關view : select * from dba_file_group_files

四. 將dump file 從file group version中移除
begin
dbms_file_group.remove_file(
file_group_name=>'scott.test_fg',
file_name=>'PDUMP_DIR1.TESTEXP01.DMP',

version_name =>'SH_MAY2007'

);
end;

ORACLE10G.about MATERIALIZED VIEW

1.一般view 是一堆sql的別名, 但MATERIALIZED VIEW可以創造實體化的view
2.sample:
CREATE MATERIALIZED VIEW test_dept
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
三種refresh 方式
1.強制refresh
REFRESH FORCE ON DEMAND
2.完整refresh
REFRESH COMPLETE
3.快速refresh
REFRESH FAST ON COMMIT

WITH ROWID AS
SELECT * FROM dept;
限制:
不可以有sysdate,rownum,raw,long, group by 等語法
3.create materialized view log
一.sample:
create materialized view log on dept
with sequence,rowid
including new values
二.建立create materialized view log, 資料修改記錄會另存table
可用下列sql 查詢table名稱
select owner, table_name from dba_tables where table_name like 'MLOG%'
以這個例子為例.
資料修改記錄會另存table MLOG$_DEPT
select * from MLOG$_DEPT

ＡＳＰ＿ＲＥＷＲＩＴＥ ＳＡＭＰＬＥ

A網址轉到B網址 301導向
RewriteCond Host: ABC\.netfun\.com\.tw
RewriteRule / http\://DEF\.netfun\.com\.tw [I,RP,L]

RewriteCond Host: ABC\.netfun\.com\.tw
RewriteRule /board.asp http\://DEF\.netfun\.com\.tw/board.asp [I,RP,L]

RewriteCond Host: ([^w][^w][^w][A-Z0-9_().-]+)\.netfun\.com\.tw
RewriteRule / /$1/ [I]


#http://www.netfun.com.tw/username = http://www.netfun.com.tw/AA/BB/CC.asp?pageid=username
RewriteRule /([A-Z0-9_()]+) /AA/BB/CC.asp\?pageid=$1 [L,I,U]

oracle10G Using Sorted Hash Clusters sample

SQL> ;
1 create cluster call_cluster(
2 call_number number,
3 call_timestamp number sort,
4 call_duration number sort)
5* hashkeys 10000 single table hash is call_number size 50
SQL> /

1 create table calls (
2 call_number number, call_timestamp number, call_duration number,call_info varchar2(50)) cluster call_cluster
3* (call_number,call_timestamp,call_duration)
SQL> /

已建立表格.

oracle10G使用dbms_sqltune

三大功能
ACCESS PATH ANALYSIS
SQL Structure Analysis
SQL Tuning Advisor Recommendations
sample
------
declare
tname varchar2(30);
begin
tname :=
dbms_sqltune.create_tuning_task(
sql_text=>'select * from scott.dept',
task_name => 'test_tune2'
);
dbms_sqltune.execute_tuning_task('test_tune2');
end;
/
select task_name from user_advisor_log
/
select dbms_sqltune.report_tuning_task('TASK_NAME') from dual
select dbms_sqltune.report_tuning_task('test_tune2') from dual