Oracle培訓(xùn)手冊資料

Oracle培訓(xùn)手冊

書目

Oracle培訓(xùn)I手冊1

I.Oracle服務(wù)器安裝3

1.1.前提條件3

1.2.修改操作系統(tǒng)參數(shù)3

1.2.1.修改/etc/sysctl.conf文件3

1.2.2.修改/etc/security/limits.conf文件3

13安裝補丁包3

1.4.軟件安裝4

1.4.1.修改環(huán)境變量4

1.4.2.軟件安裝4

2.創(chuàng)建數(shù)據(jù)庫12

2.1.創(chuàng)建數(shù)據(jù)庫12

2.2.創(chuàng)建表空間18

2.3.創(chuàng)建用戶18

2.4.維護臨時表空間19

3.創(chuàng)建Schema20

3.1.創(chuàng)建表格20

3.2.創(chuàng)建視圖20

4,初始化數(shù)據(jù)導(dǎo)入21

4.1.公共數(shù)據(jù)導(dǎo)入21

4.2.用戶數(shù)據(jù)導(dǎo)入21

5.Oracle數(shù)據(jù)庫日常操作22

5.1.啟動和關(guān)閉監(jiān)聽程序22

5.2.啟動和關(guān)閉數(shù)據(jù)庫22

5.3.連接數(shù)據(jù)庫22

5.4.客戶端連接配置22

5.5.查看數(shù)據(jù)塊大小23

5.6.查看數(shù)據(jù)庫字符集23

5.7.導(dǎo)入導(dǎo)出數(shù)據(jù)23

1.Oracle服務(wù)器安裝

1.1.前提條件

I、在操作系統(tǒng)上創(chuàng)建oracle用戶和oinstall,dba組。

2^假如是linux操作系統(tǒng)，檢查是否安裝了開發(fā)工具包“DevelepmentTools"、"Xsoftware

Development^LegacysoftwareDevelopment假如沒有請安裝。

3、查看/etc/hosts文件，確認主機名和IP已經(jīng)設(shè)置。

4、將oracle安裝軟件和補丁包FTP到/homc/oracle/sctup書目下，并用下面吩咐解壓軟件：

zcatship_9204_linux_diskI.cpio.gz|cpio-idmv

zcatship_9204_linux_disk2.cpio.gz|cpio-idmv

zcatship_9204_linux_disk3.cpio.gz|cpio-idmv

注：以上操作可參照對應(yīng)的操作系統(tǒng)手冊。

1.2,修改操作系統(tǒng)參數(shù)

1.2.1.修改/etc/sysctl.conf文件

添加下面內(nèi)容：

kernel.shmmax=536870912（物理內(nèi)存一半）

kernel.shmmni=4096

kernel.shmall=2097152

kemel.sem=25032000100128

fs.file-max=65536

1.2.2.修改/etc/security/limits.conf文件

oraclesoftnofile65536

oraclehardnofile65536

oraclesoftnproc16384

oraclehardnproc16384

1.3.安裝補丁包

用root用戶依照下面依次安裝5個rpm包

openmotif21-

compat-li

14軟件安裝

L4.L修改環(huán)境變量

修改oracle用戶的.bash_profile文件，添加下面內(nèi)容：

exportORACLE_BASE=/usr/oraclc

exportORACLE_HOME=$ORACLE_BASE/product/9.2

exportORACLE_SID=safcdb

exportTHREADS_FLAG=native

exportLD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

exportLD_ASSUME_KERNEL=

exportPATH=$PATH:$ORACLE_HOME/bin

1.4.2.軟件安裝

1、以oracle用戶登錄OS,進入安裝文件所在書目，運行下面吩咐

$.cd/home/oracle/setup/Disk1

$./runinstaller

2、按Next鍵

0InventoryLocation

InventoryLocation

Youarestartingyourfirstinstallationonthiscomputer.Aspartof

thisfirstinstall,youwillneedtospecifyabasedirectoryfor

installationfiles.

Anotherdirectorywillbeautomaticallysetupforper-productinstallationfiles.

Theper-produc^installationfilescanvaryinsize,dependingon:hecomplexity

oftheproductsyouinstall.Youshouldplanonanaverageofabout15OKBper

product.

Thebasedirectory:

*Containsthepermanentandper-productfilesinsubdirectories.

*Mustbewritablebyanyoneinstallingordeinstallingproductsonthis

computer.

Whatwouldyoulikeasthebasedirectory?

|/u$r/oracle/oralnventoryBrowse...i

Inventoryfileswillbein:/usr/oracle/oralnventory

Per-productfileswillbein:/usr/oracle/oralnventory/Components

HelpIOKCancel)

3、按OK鍵

OracleUniversalInstaller:UNIXGroupName

UNIXGroupName

YoucanspecifyaUNIXgroupnamewhichwillhavepermissiontoupdateOraclesoftwareon

thissystem.

Ifyouwantonlyroottohavepermission,leavethefieldblankandpress-Next-tocontinue

theinstall

UNIXGroupName:|Oin$tall|

4^輸入oinstalL按Next鍵

5、在另外一個窗口用root用戶執(zhí)行所提示腳本，按Continue鍵

6、按Next鍵

AvailableProducts

Selectaproducttoinstall.

?Oracle%Database.0

Installsanoptionalpre-configuredstarterdatabase,productoptions,managementtools,

networkingservices,utilitiesandbasicclientsoftwareforanOracledatabaseserver.

cOracle%Client.0

Installsenterprisemanagementtools,networkingservices,utilities,developmenttoolsand

precompilersandbasicclientsoftware

cOracle9iManagementandIntegration.0

Installsthemanagementserver,managementtools,OracleInternetDirectory,OracleIntegration

Server,networkingservices,utilitiesandbasicclientsoftware

cOracleClusterManager.0

OracleClusterManager,Release.0forLinuxIntel

7、按產(chǎn)品語言鍵

LanguageSelection

一I

Oracle9iDatabase.0

Pleaseselectthelanguagesyourproduct

Oracle9iDatabase.0willrunin.

AvailableLanguages:SelectedLanguages:

NorwegianEnglish

PolishSimplifiedChinese

Portuguese

Romanian

Russian

Slovak

Slovenian

Spanishb

Swedish

Thai《《I

TraditionalChines

HelpJOKCancel

8、選入簡體中文，按OK鍵，進入下一步

9、選擇企業(yè)版，按Next鍵

OracleUniversalInstaller:DatabaseConfiguration一

DatabaseConfiguration

Selectadatabasesuitedtoyourneeds.

rGeneralPurpose

Installsapre-configureddatabaseoptimizedforgeneralpurposeusage

cTransactionProcessing

Installsapre-configureddatabaseoptimizedfortransactionprocessing

cDataWarehouse

Installsapre-configureddatabaseoptimizedfordatawarehousing

CCustomized

Allowsyoutocreateacustomizeddatabase.Thisoptiontakeslongerthanthepre-configured

<6SoftwareOnly

“…TristalTssbYtwareonlyanddoesnotcreateadatabaseatthistime

10、選擇只安裝軟件，按Next鍵

Cracle.U川vergalJnaallefsSummi1Kl

Summary

Oracle9iDatabase.0

/GlobalSettings

-Source:/ho^;e/oracle/$etup/Disk1/stage/productsJar

-OracleHome:/usr/oracle/product/9.2(OUIHome)

1-InstallationType:EnterpriseEdition

mProductLanguages

English

tSimplifiedChinese

6SpaceRequirements

L/Required2.63GB(includes91MBtemporary):Available46.O1GB

&NewInstallations(200products)

-AdvancedQueueing(AQ)API9.2.0.40

—AdvancedReplication.0

-AgentRequiredSupportFiles.0

-ApacheConfigurationforOracleJavaServerPages.0

-ApacheConfigurationforOracleXMLDeveloper'sKit.0

-Anxfh。IServ11000a

ExitUHelpnstalledProducts..Previous劇Install

ORACLG

H、按Install鍵

12、安裝過程中，將彈出下面窗口，依據(jù)提示執(zhí)行root.sh文件后，按OK鍵

13、按Exi1鍵，完成軟件安裝

2.創(chuàng)建數(shù)據(jù)庫

2.1,創(chuàng)建數(shù)據(jù)庫

1、運行（ibca

$cd$ORACLE_HOME/bin

$./dbca

DatabaseConfigurationAssistant:Welcome

WelcometoDatabaseConfigurationAssistantforOracledatabase.

TheDatabaseConfigurationAssistantenablesyoutocreateadatabase,configure

databaseoptionsinanexistingdatabase,deleteadatabase,andmanagedatabase

templates.

Cancel

2、按Next鍵

3、選擇"createadatabase”項，按Next鍵

4、選擇ageneralpurpose項，按Next鍵

5、全局數(shù)據(jù)庫名和SID都輸入safedb,按Next鍵

DatabaseConfigurationAssistant,Step4of7:DatabaseConnectionOptions

Selectthemodeinwhichyouwantyourdatabasetooperatebydefault:

rDedicatedServerMode

Foreachclientconnectionthedatabasewillallocatearesourcededicatedtoserving

onlythatclient.Usethismodewhenthenumberoftotalclientconnectionsis

expectedtobesmallorwhenclientswillbemakingpersistentlong-running

requeststothedatabase.

?SharedSer/erMode

Severalclentconnectionsshareadatabase-allocatedpoolofresources.Usethis

modewhenmorethanasmallnumberofusersneedtoconnecttothedatabase

simultaneouslywhileefficientlyutilizingsystemresources.TheOracleshared

serverfeaturewillbeenabled.

EditSharedConnectionsParameters..,j

CanceliHelp&BackNext?)Finishf

6、選擇aSharedServerModew項，按Next鍵

7、輸入合適的數(shù)據(jù)庫內(nèi)存參數(shù)，這些值總合不要超出實際系統(tǒng)的共享內(nèi)存，按“Character

Sets”頁。（內(nèi)存參數(shù)可以參照上圖的值，以后依據(jù)實際系統(tǒng)配置可以調(diào)整）

注：數(shù)據(jù)庫字符集選ZHS16GBK

8、選“choosefromthelistofcharactersets”項,選擇“ZHS32GBi8030”字符集,按“Archive”

頁

9、選中"ArchiveLogMode”和aAutomaticarchivalw項，按Next鍵

10、按Next鍵

11、按Finish鍵

Summary

Foll^wi

ngoperation(s)willbeperformed:

Creationofdatabasewithdbname"safedb".

Usethisdatabasetemplatetocreateapre-configureddatabaseoptimizedforgeneral

purposeusage.

CommonOptions

OptionNameSelected

ExampleSchemastrue

OracleDataMiningtrue

OracleIntermediatrue

OracleJVMtrue

OracleLabelSecurityfalse

OracleOLAPtrue

OracleSpatialtrue

OracleTexttrue

OracleUltraSearchtrue

OracleXMLDBtrue

▼

SaveasanHTMLfile.

ICanceljHelp

12、按OK鍵

DatabaseConfigurationAssistant,Step7of7:CreationOptions

Selectthefollowingdatabasecreationoptions:

CancelHelp《BackHext:finish

13、數(shù)據(jù)庫創(chuàng)建完成后，有對話框彈出，你可以修改SYS和SYSTEM用戶缺省密碼。

2.2.創(chuàng)建表空間

$sqlplussystem/manager@safedb

SQL>createtablespacesafedatadatafile7usr/oracle/oradata/safedb/safedata1.dbfsize500M

segmentspacemanagementauto;

建議：數(shù)據(jù)表空間大小數(shù)據(jù)硬盤容量的80%,假如操作系統(tǒng)對文件大小有限制，可以在表

空間中放置多個數(shù)據(jù)文件即可。

23創(chuàng)建用戶

新增用戶

$sqlplussystem/manager@safedb

SQL>createusershenbaoidentifiedby"shenbao"defaulttablespacesafedata;

授權(quán)

SQL>grantconnecttoshenbao;

SQL>grantcreatetabletoshenbao;

SQL>grantcreateviewtoshenbao;

SQL>grantunlimitedtablespacetoshenbao;

2.4.維護臨時表空間

$sqlplussystem/manager@safedb

SQL>altertablespacetempaddtempfile7usr/oracle/oradata/safedb/temp02.dbfsize2000M;

建議：臨時表空間大?。?G

3.創(chuàng)建Schema

3.1,創(chuàng)建表格

$sqlplusshenbao/shenbao@safedb

SQL>spool/home/oracle/createTable.Iog;

SQL>@/home/oracle/crcatcTablc.sql;

SQL>spooloff';

注：

1）createTable.sql是建表腳本，createTable.log是腳本執(zhí)行中產(chǎn)生的日志文件。

2）通過日志文件查看腳本是否執(zhí)行正常。

3）假如在unix或者linux執(zhí)行該腳本，須要將該腳本用UtralEdit工具進行unix文件格

式轉(zhuǎn)換。（用UtralEdil打開該腳本，用菜單File—）Conversions—）DOStoUNIX轉(zhuǎn)

換，保存文件即可）

32創(chuàng)建視圖

$sqlplusshenbao/shenbao@safedb

SQL>spooi/home/oracle/createViev/.log;

SQL>@/home/oracle/createView.sql;

SQL>spooloff;

注：

1）crcatcVicw.sql是建視圖腳本，crcatcVicw.log足腳本執(zhí)行中產(chǎn)生的日志文件。

2）通過日志文件查看腳本是否執(zhí)行正常。

3）假如在unix或者linux執(zhí)行該腳本，須要將該腳本用UtralEdit工具進行unix文件格

式轉(zhuǎn)換。（用UtralEdit打開該腳本，用菜單File—）Conversions—）DOStoUNIX轉(zhuǎn)

換，保存文件即可）

4.初始化數(shù)據(jù)導(dǎo)入

4.1.公共數(shù)據(jù)導(dǎo)入

$sqlplussh