oracle install

資料備份[@more@]

安裝oracle及資料的說明：
1.新增主機IP，編輯檔案/etc/hosts
vi /etc/hosts
#新增內容，192.168.100.16為對應的主機IP，請根據實際情況作調整
127.0.0.1 localhost localhost.localdomain
192.168.100.16 testapp1
2.修改主機名為testapp1
點選選單上的“系統”→“管理”→“網路”→“DNS”，修改主機名為testapp1，儲存。
3.建立Oracle使用者
groupadd -g 500 oinstall
groupadd -g 501 dba
groupadd -g 502 oper
useradd -u 500 -d /home/oracle -g oinstall -G dba,oper -m -s /bin/bash oracle
id oracle
##設定oracle使用者密碼為oracle
passwd oracle

4.切換到oracle使用者，編輯系統屬性
# su - oracle
$ vi ~/.bash_profile
新增以下內容
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=moepoc
export ORA_CRS_HOME=$ORACLE_BASE/product/10.2.0/crs_1
export LD_LIBRARY_PATH=$ODBC_HOME/lib:$ORACLE_HOME/lib:$ORA_CRS_HOME/bin:/usr/lib64:/usr/lib:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:/usr/bin:$PATH
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
stty erase ^h
export ODBC_HOME=/etc
export ODBCINI=$ODBC_HOME/odbc.ini
umask 022

5.把檔案複製到根目錄，並解壓檔案到根目錄,（由於解壓檔案為/u01,請確保/u01目錄不衝突，可先執行"rm -rf /u01"
cd /
tar zxcf oracle.tar.gz

6.修改許可權
chown -R oracle:oinstall /u01
chmod -R 755 /u01

7.重啟系統後，啟動資料庫
reboot
su - oracle
lsnrctl start
sqlplus / as sysdba
startup

create smallfile tablespace xuesheng datafile '/u01/app/oracle/oradata/moepoc/moepoc01.dbf'
size 1600m autoextend on next 10m maxsize unlimited logging extent management local segment space management auto;

alter tablespace xuesheng add datafile '/u01/app/oracle/oradata/moepoc/moepoc02.dbf'
size 1600m autoextend on next 10m maxsize unlimited;

create user xuesheng
identified by moepoc123
default tablespace xuesheng
temporary tablespace temp
profile default;

grant connect to xuesheng;
grant dba to xuesheng;
grant unlimited tablespace to xuesheng;

drop tablespace xuesheng including contents and datafiles;

CREATE TABLESPACE "XUESHENG_YYZW"
LOGGING
DATAFILE '/u01/app/oracle/oradata/moepoc/XUESHENG_YYZW.dbf' SIZE 5000M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
alter tablespace XUESHENG_YYZW add datafile '/u01/app/oracle/oradata/moepoc/XUESHENG_YYZW2.dbf'
size 5000m autoextend on next 10m maxsize unlimited;

CREATE TABLESPACE "WWJHR"
LOGGING
DATAFILE '/u01/app/oracle/oradata/moepoc/WWJHR01.dbf' SIZE 5000M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

alter tablespace WWJHR add datafile '/u01/app/oracle/oradata/moepoc/WWJHR03.dbf'
size 5000m autoextend on next 10m maxsize unlimited;

CREATE TEMPORARY TABLESPACE "XUESHENG_YYZW_TMP"
TEMPFILE '/u01/app/oracle/oradata/moepoc/XUESHENG_YYZW_tmp.dbf' SIZE 2000M EXTENT
MANAGEMENT LOCAL UNIFORM SIZE 1M;



/**
建立使用者
*/

CREATE USER "XUESHENG_YYZW" PROFILE "DEFAULT"
IDENTIFIED BY "moepoc123" DEFAULT TABLESPACE "XUESHENG_YYZW"
TEMPORARY TABLESPACE "XUESHENG_YYZW_TMP"
ACCOUNT UNLOCK;
/**
使用者賦予許可權
*/

GRANT CREATE PROCEDURE TO "XUESHENG_YYZW" WITH ADMIN OPTION;
GRANT CREATE SEQUENCE TO "XUESHENG_YYZW" WITH ADMIN OPTION;
GRANT CREATE TABLE TO "XUESHENG_YYZW" WITH ADMIN OPTION;
GRANT CREATE TRIGGER TO "XUESHENG_YYZW" WITH ADMIN OPTION;
GRANT CREATE VIEW TO "XUESHENG_YYZW" WITH ADMIN OPTION;
GRANT "CONNECT" TO "XUESHENG_YYZW" WITH ADMIN OPTION;
GRANT "DBA" TO "XUESHENG_YYZW" WITH ADMIN OPTION;
GRANT "RESOURCE" TO "XUESHENG_YYZW" WITH ADMIN OPTION;

drop user XUESHENG_YYZW cascade;

declare
maxrecords constant int:=10000;
i int :=1;
begin
for i in 1..maxrecords loop
insert into xs01
(ZA0100,XJH,XM)
values
(SYS_GUID(), to_char('9999'+i),'測試' || to_char('9999'+i)) ;
end loop;
commit;
end;
/

select count(ZA0100) from xs01;

declare
maxrecords constant int:=2;
i int :=1;
begin
for i in 1..maxrecords loop

insert into xs01
(ZA0100,XJH,XM) select SYS_GUID(), to_char(XJH + i), '測試' || to_char(XJH + i) from xs01 ;

end loop;
commit;
end;
/
select count(ZA0100) from xs01;