Oracle培訓(BEST)文檔手冊

數(shù)據(jù)庫

ORACLE張志華（凌志）計算機專業(yè)技術培訓課程ORACLE10g/11gORACLE數(shù)據(jù)庫的基本知識

學習目標1

關鍵內(nèi)容2準備工作3結構設計41數(shù)據(jù)處理51理論知識6ORACLE10g/11g學習目標1ORACLE10g/11gORACLE主要學習目標掌握表空間及用戶的創(chuàng)建、修改和刪除等操作了解ORACLE的基本知識熟練掌握記錄的插入、刪除、修改和查詢操作了解PK/CK/UK/FK等知識掌握三種約束培訓目標掌握權限管理ORACLE10g/11g

關鍵內(nèi)容2ORACLE10g/11g關鍵內(nèi)容：oracle流程231創(chuàng)建用戶創(chuàng)建表空間分配表空間45數(shù)據(jù)處理授權ORACLE10g/11g關鍵內(nèi)容：創(chuàng)建表空間及表創(chuàng)建主鍵約束創(chuàng)建唯一性約束創(chuàng)建外鍵約束創(chuàng)建檢查約束

創(chuàng)建表創(chuàng)建表空間ORACLE10g/11g

理論知識3ORACLE10g/11gORACLE中相關名詞（一）一、PL/SQLPL/SQL也是一種程序語言，叫做過程化SQL語言（ProceduralLanguage/SQL）。PL/SQL是Oracle數(shù)據(jù)庫對SQL語句的擴展。在普通SQL語句的使用上增加了編程語言的特點，所以PL/SQL就是把數(shù)據(jù)操作和查詢語句組織在PL/SQL代碼的過程性單元中，通過邏輯判斷、循環(huán)等操作實現(xiàn)復雜的功能或者計算的程序語言。二、NLSRTL:全球化支持OracleNLSRuntimeLibrary(NLSRTL)：與oracle的字符集文件以及字符支持有關。Oracle的全球支持是通過OracleNLSRuntimeLibrary(NLSRTL)來實施的，NLS運行庫通過獨立的函數(shù)來完成運行時與語言相關的轉換及控制。ORACLE10g/11gORACLE中相關名詞（二）

三、VSS(VolumeShadowcopyService,卷映射拷貝服務)，它可以在多卷、或者單個卷上創(chuàng)建映射拷貝，同時不會影響到系統(tǒng)性能。A、快速數(shù)據(jù)備份和恢復；B、針對制定文件的恢復；C、磁帶備份、測試，以及數(shù)據(jù)挖掘方面；VSS整體框架包含了VSS核心模塊、請求者(Requestor)、寫入者(Writer)，以及提供者(Provider)。import---imp(導入)export---exp(導出)ORACLE10g/11gORACLE中相關名詞（三）四、RAC OracleRAC是OracleRealApplicationCluster的簡寫，官方中文文檔一般翻譯為“真正應用集群”，它一般有兩臺或者兩臺以上同構計算機及共享存儲設備構成，可提供強大的數(shù)據(jù)庫處理能力，現(xiàn)在是Oracle10g/11gGrid應用的重要組成部分。五、SGAPGA SGA:系統(tǒng)全局區(qū)，用于存儲數(shù)據(jù)庫信息的內(nèi)存區(qū)，該信息為數(shù)據(jù)庫所有進程所共享，包括oracle服務器的數(shù)據(jù)和控制信息。 PGA:程序全局區(qū)，包括單個服務器進程或單個后臺進程的數(shù)據(jù)和控制信息，與幾個進程共享的SGA剛好相反，PGA只能被一個進程所使用，在進程創(chuàng)建時分配，進程結束是收回。ORACLE10g/11gTNS透明網(wǎng)絡底層（一）Oracle中TNS的完整定義：TransparenceNetworkSubstrate，透明網(wǎng)絡底層。監(jiān)聽服務是它重要的一部分，不是全部，不要把TNS當作只是監(jiān)聽器。ORACLE當中，如果想訪問某個服務器，必須要設置TNS，它不像SQLSERVER那樣在客戶端自動列舉出在局域網(wǎng)內(nèi)所有的在線服務器，只需在客戶端選擇需要的服務器，然后使用帳號與密碼登錄即可。而ORCAL不能自動列舉出網(wǎng)內(nèi)的服務器，需要通過讀取TNS配置文件才能列出經(jīng)過配置的服務器名。配置文件名一般為：tnsnames.ora存取路徑：$ORACLE_HOME\network\admin\tnsnames.oraORACLE10g/11gTNS透明網(wǎng)絡底層（二）配置示例#TNSNAMES.ORANetworkConfigurationFile:C:\oracle\ora81\network\admin\tnsnames.ora#GeneratedbyOracleconfigurationtools.#sampledatabase_name=orcl(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=servicename)))斜體紅色處是需要修改的地方，但是一般我們只修改三處位置，連接別名database_name、連接地址、連接的數(shù)據(jù)庫名servicename。配置好了就可以使用客戶端軟件（如PL/SQLDeveloper、TOAD）來連接數(shù)據(jù)庫了。ORACLE10g/11gTNSPING命令OracleNet工具（命令）tnsping，是一個OSI會話層的工具，它用來：1）可以檢查tcp協(xié)議是否可用，即：我們平時說的網(wǎng)絡通不通；能驗證一臺服務器的名字解析（nameresolution，當然是oracle自己的網(wǎng)絡服務名）;2）tnsping命令監(jiān)聽服務器上的listener監(jiān)聽是否已開啟;3）tnsping命令不檢測服務器上的監(jiān)聽的服務名，所以tnsping通，不代表ORACLEDB就能連通。tnsping命令：如果能夠ping通，則說明客戶端能解析listener的機器名，而且lister也已經(jīng)啟動，但是并不能說明數(shù)據(jù)庫已經(jīng)打開，而且tnsping的過程與真正客戶端連接的過程也不一致。但是如果不能用tnsping通，則肯定連接不到數(shù)據(jù)庫。查看服務配置的ora文件路徑：tnsnames.ora格式：tnsping主機名或IP地址(如:tnspinglocalhost)(再如：tnsping00)ORACLE10g/11gLISTENER監(jiān)聽命令監(jiān)聽命令：LSNRCTLlsnrctlstart開啟監(jiān)聽lsnrctlstop關閉監(jiān)聽lsnrctlstatus監(jiān)聽狀態(tài)通過在lsnrctl中輸入setdisplayverbose，然后再通過命令service查看，服務狀態(tài)為READY表示PMON自動注冊的服務名，而UNKNOWN則表示該服務是手工在LISTENER.ORA（監(jiān)聽服務配置）中配置的數(shù)據(jù)庫服務！ORACLE10g/11gORACLE的主要管理工具TOAD小青蛙PL/SQLDEVELOPER增強版SQL開發(fā)工具OEM(OracleEnterpriseManager)甲骨文企業(yè)管理器ORACLE10g/11gTOADforOracle的使用TOAD英文含義是：青蛙蟾蜍。ToolsofOracleApplicationDevelopers(甲骨文應用開發(fā)工具)四個版本：標準版、專業(yè)版、專家版（開發(fā)套裝版）、管理套裝版。官方網(wǎng)站：TOAD比PL/SQLDEVELOPER貴100-200倍，價格一般在2-4萬人民幣。在Oracle應用程序的開發(fā)過程中，訪問數(shù)據(jù)庫對象和編寫SQL程序是一件乏味且耗費時間的工作，對數(shù)據(jù)庫進行日常管理也是需要很多SQL腳本才能完成的。QuestSoftware為此提供了高效的Oracle應用開發(fā)工具-Toad（ToolsofOracleApplicationDevelopers）。在Toad的新版本中，還加入了DBA模塊，可以幫助DBA完成許多日常管理工作。它最大的特點就是簡單易用，訪問速度快。使用Toad，我們可以通過一個圖形化的用戶界面快速訪問數(shù)據(jù)庫，完成復雜的SQL和PL/SQL代碼編輯和測試工作。Toad由Oracle開發(fā)專家專門為開發(fā)人員而設計，是一個功能強大、結構緊湊的專業(yè)化PL/SQL開發(fā)環(huán)境。ORACLE10g/11gTOAD監(jiān)聽程序配置（服務器端）通過開始---程序---OracleDB11g（服務器端）---配置和移植工具---NetConfigurationAssistant(NCA網(wǎng)絡配置助手)，建立監(jiān)聽程序配置。（如果存在監(jiān)聽程序，可以更新配置，沒有監(jiān)聽程序，就添加一個）注意：要不斷下一步，可以根據(jù)需要取一個個性化的名稱，默認監(jiān)聽程序為：LISTENER（可保持不變）;網(wǎng)絡協(xié)議選擇:TCP;端口配置時，使用甲骨文默認的標準端口：1521；ORACLE10g/11gTOAD監(jiān)聽程序配置（客戶端）通過開始---程序---OracleClient11g（客戶端）---配置和移植工具---NetConfigurationAssistant(NCA網(wǎng)絡配置助手)，建立與服務器端相同的監(jiān)聽程序（例如：均配置為LISTENER)。注意：如果存在監(jiān)聽程序，可以更新配置，如果沒有監(jiān)聽程序，就添加一個新配置;要不斷下一步，可以根據(jù)實際需要取一個個性化的名稱，默認監(jiān)聽程序為：LISTENER（可保持不變）;網(wǎng)絡協(xié)議選擇:TCP;端口配置時，使用甲骨文默認的標準端口：1521；view---ToadOpptions---Oracle---Transactios--勾選Commitaftereverystatement.就可以寫完sql語句后自動提交。在Toad中想同時選中多條Insert語句插入數(shù)據(jù)時會報無效字符,可以Excuteasscript.ORACLE10g/11g

準備工作4ORACLE10g/11g啟動：startupopenstartupmountalterdatabasemountalterdatabaseopenORACLE10g/11gORACLE的啟動與關閉啟動：startupopen關閉：shutdownimmediate(請勿隨意關閉)ORACLE主要服務有：（1）OracleService<SID>：數(shù)據(jù)庫服務（*）（2）Oracle<HOME_NAME>TNSListener：監(jiān)聽器服務（*）（3）Oracle<Home_Name>Agent：代理服務（4）OracleDBConsole<SID>企業(yè)管理器（瀏覽器版）(*)（5）Oracle<Home_Name>HTTPServer（6）Oracle<Home_Name>ManagementServerOracle兩個主要的組成部分是：數(shù)據(jù)庫和實例Oracle數(shù)據(jù)庫用于存儲和檢索信息，是數(shù)據(jù)的集合。包括邏輯結構和物理結構。Oracle實例是指數(shù)據(jù)庫服務器的內(nèi)存及相關處理程序。甲骨文的使用者身份問題兩種身份SYSDBA管理員SystemDatabaseAdministratorSYSOPER操作員SystemOperatorORACLE10g/11gORACLE10g/11g正確使用SQL*PLUS開始---運行---CMD---SQLPLUS開始---運行---SQLPLUS退出：EXIT/QUIT清屏：CLEARSCREEN------相當于DOS中的CLS操作流程：實例---表空間---表---用戶---授權Instance---TableSpace---Table---User---Grant每個實例是龐大數(shù)據(jù)庫中的一個具體的分站點，DatabaseConfigurationAssistant（數(shù)據(jù)庫配置助手）可建立新實例。ORACLE10g/11g登錄請登錄數(shù)據(jù)庫再進行相關操作！管理員登錄：sysassysdba或systemassysdba操作員登錄：scottassysoper或systemassysoper查看登錄者身份：showuser;ORACLE10g/11g查看用戶是否鎖定查看數(shù)據(jù)庫管理員DBA表的結構情況：descdba_users;查看用戶(SYSMAN)的賬號是否鎖定：selectusername,account_statusfromdba_userswhereusername='SYSMAN';給用戶加鎖：alterusersysmanaccountlock;給用戶解鎖：alterusersysmanaccountunlock;[以HR/PM/BI/SCOTT/SYSMAN為例，如果用戶賬號狀態(tài)顯示加密過期EXPIRED，修改此賬號的密碼即可]ORACLE10g/11g甲骨文常用配置助手通過開始---程序找到ORACLE安裝菜單中的：配置和移植工具使用NCA配置網(wǎng)絡NCA---NetConfigurationAssitant(網(wǎng)絡配置助手)使用DCA配置新的數(shù)據(jù)庫實例DCA---DatabaseConfigurationAssistant(數(shù)據(jù)庫配置助手)ORACLE10g/11g常用排錯命令lsnrctlstatus查看服務器端監(jiān)聽狀態(tài)tnsping地址或服務器名查看客戶端網(wǎng)絡連接與服務器監(jiān)聽狀態(tài)sqlplus/nolog不登錄到服務器進行操作，不需要輸入用戶名和密碼ORACLE10g/11g

結構設計（形式）5ORACLE10g/11g如何使用SQLPLUS進行庫、表、列操作?(一)進入SQLPLUS,確定你現(xiàn)在是以管理員sysdba身份登錄的：1.首先，創(chuàng)建（新）用戶：（*）

createuserusernameidentifiedbypassword;

username：新用戶名的用戶名password:新用戶的密碼也可以不創(chuàng)建新用戶，而仍然用以前的用戶，如：繼續(xù)利用scott用戶用戶默認是權限鎖定的，即accountlock,解鎖請用在上面的命令后面加上：accountunlock.只有解鎖后的用戶才可進行各種操作。以SYSDBA身份登陸時可以修改其他用戶的密碼，比如：SQL>alteruseruser01identifiedbyuser10;用戶已更改。這個是把USER01用戶密碼修改為USER10ORACLE10g/11g2.創(chuàng)建表空間：

（*）

createtablespacetablespacenamedatafile‘d:\space\data.dbf’

sizexxxm;tablespacename：表空間的名字‘d:\data.dbf’：表空間的存儲位置xxx表空間的大小，m單位為兆(M)autoExtendonnext10M--空間的自動增長的值是10Mpermanentonline;--永久使用EXTENTMANAGEMENTLOCALUNIFORMSIZE256K;--擴展本地管理表空間擴展區(qū)塊的統(tǒng)一大小為256K如何使用SQLPLUS進行庫、表、列操作?(二)ORACLE10g/11g如何使用SQLPLUS進行庫、表、列操作?(三)3.將表空間分配給用戶：（*）

alertuserusernamedefaulttablespacetablespacename;

將名字為tablespacename的表空間分配給username4.給用戶授權：

（*）connectresourcedbagrantcreatesession,createtable,unlimitedtablespacetousername;在TOAD中查看用戶SQL命令：descusername（用戶名）,然后執(zhí)行，即可彈出窗口;ORACLE10g/11g如何使用SQLPLUS進行庫、表、列操作?(四)5.然后再以自己創(chuàng)建的用戶登錄，登錄之后創(chuàng)建表即可。connusername/password@###;###手工指定表空間，將用戶與空間建立聯(lián)系6、創(chuàng)建角色與角色授權（*）

createrole角色名identifiedby密碼;--創(chuàng)建新角色grantcreatesessionto角色名；--給角色授予創(chuàng)建會話的權限grant角色名to用戶名；

--把角色授予用戶ORACLE10g/11g如何使用SQLPLUS進行庫、表、列操作?(五)7、刪除用戶（*）DROPUSERtest[CASCADE];8、刪除表空間（*）DROPTABLESPACEtest01INCLUDINGCONTENTSANDDATAFILES;1)刪除表空間內(nèi)容和數(shù)據(jù)文件；2)一般無效表空間占用磁盤空間,所以這個應該很常用。ORACLE10g/11g如何使用SQLPLUS進行庫、表、列操作?(六)9、查看用戶角色權限（*）select*fromuser_role_privs;PRIVILEGE-----privilege權限（allrights）10、查看用戶表權限select*fromuser_tab_privs;查看已有用戶狀態(tài)

※查看用戶與表空間的對應情況：selectusername,account_statusfromdba_users;通過dba_users，查看Oracle賬戶的鎖定狀態(tài)，狀態(tài)解釋如下：OPEN表示賬戶為解鎖狀態(tài)；EXPIRED表示賬戶為過期狀態(tài)(需要設置口令才能解除此狀態(tài))；LOCKED表示賬戶為鎖定狀態(tài)?！榭从脩襞c表空間的對應情況：selectusername,default_tablespacefromdba_users;ORACLE10g/11g對用戶進行鎖定和解鎖oracle數(shù)據(jù)庫安裝好之后，scott之類的用戶默認情況下是被鎖住的，無法使用scott用戶登錄數(shù)據(jù)庫。SQL>ALTERUSERusernameACCOUNTUNLOCK;如果要解鎖scott，就用scott代替上面的username部分。同樣，還可以將一個用戶鎖?。篠QL>ALTERUSERusernameACCOUNTLOCK;替換方式同上。最后補充下，修改用戶需要登錄sqlplus的這個用戶有alteruser的數(shù)據(jù)庫權限。使用dba執(zhí)行：SQL>grantalterusertousername;這樣，對應的需要登錄sqlplus的用戶就可以去解鎖其它用戶了。ORACLE10g/11gORACLE10g/11g如何使用SQLPLUS進行庫、表、列操作?(七)11、創(chuàng)建表（*）createtabledemo(id屬性,name屬性,pass屬性...);常用的數(shù)據(jù)類型有：numbervarchar2datecharlong等創(chuàng)建表

createtable表名(idintnotnull,namevarchar2(20)notnull)tablespace表空間名--所屬的表空間storage(initial64K--表的初始值minextents1--最小擴展值maxextentsunlimited--最大擴展值);ORACLE10g/11g如何使用SQLPLUS進行庫、表、列操作?(八)12、描述表的結構desc表名；（*）13、刪除表：drop表名；（*）14、為usrs表添加主鍵和索引（*）altertableusersaddconstraintpkprimarykey(ID);15、為已經(jīng)創(chuàng)建users表添加外鍵（*）altertableusersaddconstraintfk_roleidforeignkey(roleid)referencesrole(role_id)ondeletecascad;--下邊寫主表的列ondeletecascad是創(chuàng)建級聯(lián)（cascad層疊、級聯(lián)、小瀑布）ORACLE10g/11g如何使用SQLPLUS進行庫、表、列操作?(九)查看ORACLE中已安裝的各種產(chǎn)品組件版本SQL>

select

*

from

v$version;從系統(tǒng)表中提取系統(tǒng)日期：select

sysdate

from

dual;顯示所有系統(tǒng)參數(shù)的狀態(tài)：showall;

顯示提示信息：prompt要顯示的信息（詞或句）;

ORACLE10g/11g如何使用SQLPLUS進行庫、表、列操作?(十)查詢所有的表空間：select*fromv$tablespace;select*fromdba_tablespaces；查詢所有用戶：（*）select*fromdba_users;查詢符合某些條件的用戶：（*）

select*fromdba_userswhereusernamelike'ABC%';查看所有表：selecttable_namefromall_tables;查看所有表對象：selectobject_namefromall_objectswhereobject_type='TABLE'；ORACLE10g/11g強調：表結構1、查看表結構DESC表名（*）2、更改表的結構：（*）ALTERTABLE表名ADD字段名字段屬性（添加）ALTERTABLE表名MODIFY字段名字段屬性（修改）ALTERTABLE表名DROPCOLUMN字段名（刪除）altertable表名renamecolumn舊的字段名to新的字段名;（實現(xiàn)字段名的修改）showlinesize（顯示行寬）setlinesize數(shù)值（設置行寬）ORACLE10g/11g強調：授權與撤消----------------------------授權權限----------------GRANT權限TO用戶；（*）一般授權權限有：dba、connect、resource語法結構：授權GRANT角色|權限TO用戶（角色）SQL>GRANTCONNECTTOlingzhi;授權成功。SQL>GRANTRESOURCETOlingzhi;授權成功。-------------------------回收權限-------------------------REVOKE角色|權限FROM用戶（角色）（*）//修改用戶的密碼ALTERUSER用戶名IDENTIFIEDBY新密碼//修改用戶處于鎖定（非鎖定）狀態(tài)ALTERUSER用戶名ACCOUNTLOCK|UNLOCKORACLE10g/11g查看數(shù)據(jù)庫的字符集，解決亂碼默認字符集為：ZHS16GBKSELECTPROPERTY_VALUEFROMDATABASE_PROPERTIESWHEREPROPERTY_NAME='NLS_CHARACTERSET';客戶端與數(shù)據(jù)庫服務器的字符集一致，方可避免亂碼。右鍵＂我的電腦＂，在＂屬性＂－－＂設置＂－－＂環(huán)境變量＂－－＂系統(tǒng)變量＂中增加一項，其中變量名為NLS_LANG，變量值為AMERICAN_AMERICA.ZHS16GBK重啟ＴＯＡＤ后，即可看到原為亂碼的內(nèi)容，顯示為正常中文。ORACLE10g/11gORACLE備份與還原（了解）備份數(shù)據(jù)就用導入導出imp/exp，導出dmp數(shù)據(jù)文件，也可導出為sql文件。在sqlplus中，導入用hostimp命令；在sqlplus中，導出用hostexp命令；然后根據(jù)每一步的提示自動完成相關導入和導出工作。

ORACLE10g/11g

數(shù)據(jù)處理（內(nèi)容）6ORACLE10g/11g強調：增刪改查insertinto表名values(值1,值2,值3,值4...);如：insertintomytbvalues(1,'zhang','123456','shanghai');deletefrom表名where條件；如：deletefrommytbwhereid=2;update表名set字段=‘新值’where條件；如：updatemytbsetname='liuhong'whereid=3;select*from表名；如：select*frommytb;ORACLE10g/11g補充：常見登錄方法直接登錄：sqlplus---用戶名---密碼以管理員身份登錄：sqlplus---用戶名assysdb