ORACLE數(shù)據(jù)庫全技術(shù)

1、oracle數(shù)據(jù)庫全技術(shù)（v1.0）宏智科技股份有限公司2001年 11月日 期：2001年 11月密 級：公開資料 內(nèi)部資料 保密資料 機密資料狀 態(tài)：初稿 討論稿 發(fā)布項 目： 作 者：版權(quán)聲明宏智科技股份有限公司版權(quán)所有，保留一切權(quán)利。未經(jīng)本公司書面許可，任何單位和個人不得擅自摘抄、復(fù)制本文檔的部分或全部，并以任何形式傳播。文檔記錄頁版本日期責任人記錄說明備注v1.02001.12.14王強文檔初稿建立審批頁日期負責人職務(wù)簽字確認目 錄1數(shù)據(jù)庫71.1參數(shù)71.2啟動71.3關(guān)閉81.4日志文件91.5sql語句分類92系統(tǒng)功能92.1包92.1.1dbms_job包的用法102.2系統(tǒng)

2、數(shù)據(jù)字典132.2.1v$bgprocess(后臺進程)192.2.2表相關(guān)192.2.3各類文件相關(guān)202.2.4sga區(qū)212.3公用子句212.3.1存儲子句(storage)212.3.2約束子句（constraint）243表空間243.1數(shù)據(jù)文件243.2建立253.2.1在裸設(shè)備上建表空間253.3系統(tǒng)表空間253.4使用檢查253.4.1空間占用情況254安全管理264.1角色（role）264.1.1建立/修改/刪除264.1.2系統(tǒng)提供的角色264.2用戶（user）274.2.1建立/修改/刪除274.2.2系統(tǒng)提供的用戶274.3權(quán)限284.3.1賦權(quán)284.3.2回收

3、284.3.3說明284.4檢查284.4.1sysdba/sysoper權(quán)限285表285.1表操作285.1.1語法285.1.2說明295.2數(shù)據(jù)操作305.2.1語法305.2.2說明305.2.3性能估算315.3約束(constraint)315.3.1主鍵(primary key)315.4數(shù)據(jù)處理315.4.1刪除一個表中重復(fù)的記錄315.4.2兩個表中數(shù)據(jù)比較326索引327回滾段管理327.1回滾段概述327.1.1回滾段的作用327.1.2回滾段的種類337.1.3回滾段的使用337.1.4回滾段的擴張（extend）337.1.5回滾段的回收和optimal參數(shù)337.

4、2回滾段操作347.2.1創(chuàng)建回滾段347.2.2使回滾段在線347.2.3修改回滾段的存儲參數(shù)347.2.4回收回滾段的空間357.2.5使回滾段離線357.2.6刪除回滾段357.3查詢回滾段的信息367.3.1回滾段的統(tǒng)計信息367.3.2回滾段的當前活動事務(wù)367.3.3回滾段的數(shù)量規(guī)劃367.4回滾段的問題及解決方法368備份及恢復(fù)378.1exp與imp378.1.1幫助語法378.1.2參數(shù)說明398.1.3應(yīng)用舉例40oracle數(shù)據(jù)庫全技術(shù)1 數(shù)據(jù)庫oracle在初始安裝時建立一個缺省數(shù)據(jù)庫，實例名為orcl。每個數(shù)據(jù)庫最少有一個控制文件（建議3個，分別放在不同的磁盤上）。o

5、racle server可以同時啟動多個數(shù)據(jù)庫。一套操作系統(tǒng)上只能安裝一個版本的oracle數(shù)據(jù)庫系統(tǒng)。一套oracle數(shù)據(jù)庫系統(tǒng)中可以有多個oracle數(shù)據(jù)庫。每個oracle數(shù)據(jù)庫擁有一個數(shù)據(jù)庫實例（instance）。所以，一套操作系統(tǒng)上同時可以有多個oracle數(shù)據(jù)庫實例啟動。1.1 參數(shù)在oracle中，我們可以通過調(diào)整一些重要參數(shù)來提高數(shù)據(jù)庫性能。這些配置參數(shù)都可以在init<oracle_sid>.ora文件中找到，或者我們也可以用oracle enterprise manager來設(shè)置這些參數(shù)。我們可以結(jié)合運用oracle performance manager、查

6、詢命令以及autotrace功能，輔助優(yōu)化這些配置參數(shù)。autotrace功能能夠顯示出系統(tǒng)如何解析sql語句，并給出它的執(zhí)行情況統(tǒng)計報告。執(zhí)行下面這個命令將啟動autotrace功能：set autotrace on如果要查看執(zhí)行情況統(tǒng)計報告，但不想查看執(zhí)行計劃，我們執(zhí)行如下命令：set autotrace traceonly參數(shù)作用備注db_block_buffers設(shè)置系統(tǒng)全局區(qū)（sga，system global area）的大小。oracle使用這個緩沖區(qū)保存和處理數(shù)據(jù)。sga參數(shù)db_block_size設(shè)置內(nèi)存塊的大小。在oracle 8或者更高版本中，它可以設(shè)置為32k；在早期

7、版本中，最大限制是16k。警告：如果改變這個數(shù)值，你必須重新構(gòu)造數(shù)據(jù)庫。sga參數(shù)shared_pool_sizeoracle用這個內(nèi)存處理過程、包、觸發(fā)器。sga參數(shù)sort_area_size這是用于排序的內(nèi)存空間大小。1.2 啟動有以下幾種啟動方式：1、startup nomount 非安裝啟動，這種方式啟動下可執(zhí)行：重建控制文件、重建數(shù)據(jù)庫 讀取init.ora文件，啟動instance，即啟動sga和后臺進程，這種啟動只需要init.ora文件。2、startup mount dbname 安裝啟動，這種方式啟動下可執(zhí)行：數(shù)據(jù)庫日志歸檔、數(shù)據(jù)庫介質(zhì)恢復(fù)、使數(shù)據(jù)文件聯(lián)機或脫機、重新定位

8、數(shù)據(jù)文件、重做日志文件。 執(zhí)行“nomount”，然后打開控制文件，確認數(shù)據(jù)文件和聯(lián)機日志文件的位置，但此時不對數(shù)據(jù)文件和日志文件進行校驗檢查。3、startup open dbname 先執(zhí)行“nomount”，然后執(zhí)行“mount”，再打開包括redo log文件在內(nèi)的所有數(shù)據(jù)庫文件，這種方式下可訪問數(shù)據(jù)庫中的數(shù)據(jù)。4、startup，等于以下三個命令 startup nomount alter database mount alter database open5、startup restrict 約束方式啟動 這種方式能夠啟動數(shù)據(jù)庫，但只允許具有一定特權(quán)的用戶訪問 非特權(quán)用戶訪問時，會

9、出現(xiàn)以下提示： error： ora-01035: oracle 只允許具有 restricted session 權(quán)限的用戶使用6、startup force 強制啟動方式 當不能關(guān)閉數(shù)據(jù)庫時，可以用startup force來完成數(shù)據(jù)庫的關(guān)閉 先關(guān)閉數(shù)據(jù)庫，再執(zhí)行正常啟動數(shù)據(jù)庫命令7、startup pfile=參數(shù)文件名 帶初始化參數(shù)文件的啟動方式 先讀取參數(shù)文件，再按參數(shù)文件中的設(shè)置啟動數(shù)據(jù)庫 例：startup pfile=e:oracleadminoradbpfileinit.ora8、startup exclusive1.3 關(guān)閉有三種關(guān)閉方式：1、shutdown normal

10、 正常方式關(guān)閉數(shù)據(jù)庫。2、shutdown immediate 立即方式關(guān)閉數(shù)據(jù)庫。 在svrmgrl中執(zhí)行shutdown immediate，數(shù)據(jù)庫并不立即關(guān)閉，而是在oracle執(zhí)行某些清除工作后才關(guān)閉（終止會話、釋放會話資源），當使用shutdown不能關(guān)閉數(shù)據(jù)庫時，shutdown immediate可以完成數(shù)據(jù)庫關(guān)閉的操作。3、shutdown abort 直接關(guān)閉數(shù)據(jù)庫，正在訪問數(shù)據(jù)庫的會話會被突然終止， 如果數(shù)據(jù)庫中有大量操作正在執(zhí)行，這時執(zhí)行shutdown abort后，重新啟動數(shù)據(jù)庫需要很長時間。1.4 日志文件1.5 sql語句分類sql：structured quer

11、y languageddl數(shù)據(jù)定義語言：create、alter、drop（創(chuàng)建、修改結(jié)構(gòu)、刪除）（其他：rename）dml數(shù)據(jù)操縱語言：insert、delete、select、update（增、刪、查、改）（其他：truncate）dcl數(shù)據(jù)控制語言：grant、revoke（授權(quán)、回收）、set role事務(wù)控制：commit、rollback、savepoint（其他：lock table、set constraint(s)、set transaction）審計控制：audit、noaudit系統(tǒng)控制：alter system會話控制：alter session其他語句：commen

12、t（添加注釋）、explain plan、analyze、validate、call2 系統(tǒng)功能2.1 包oracle提供了幾個包，它們可以用來完成很多任務(wù)，從內(nèi)部進程通信到文件i/o，到在pl/sql塊中動態(tài)創(chuàng)建和執(zhí)行sql語句。所有這些包由sys用戶所擁有當oracle最初安裝時兩個用戶中的一個，這些包中最重要的包括：dbms_alert不用輪詢就允許應(yīng)用命名并發(fā)出警告條件信號的過程與函數(shù)dbms_ddl允許獲取pl/sql程序內(nèi)部一定數(shù)量的ddl語句的過程dbms_describe為存儲過程與函數(shù)描述api的過程dbms_job管理blobs、clobs、nclobs與bfiles的過程

13、與函數(shù)dbms_output允許pl/sql程序生成終端輸出的過程與函數(shù)dbms_pipe允許數(shù)據(jù)庫會話使用管道通信(通信頻道)的過程與函數(shù)dbms_sql在pl/sql程序內(nèi)部執(zhí)行動態(tài)sql的過程與函數(shù)dbms_utilitydbms_utilityutl_file允許pl/sql程序讀寫服務(wù)器文件系統(tǒng)上的文本文件的過程與函數(shù)2.1.1 dbms_job包的用法2.1.1.1 包含以下子過程 broken()過程。change()過程。interval()過程。isubmit()過程。next_date()過程。remove()過程。run()過程。submit()過程。user_expor

14、t()過程。what()過程。2.1.1.1.1 broken()過程broken()過程更新一個已提交的工作的狀態(tài)，典型地是用來把一個已破工作標記為未破工作。這個過程有三個參數(shù)：job 、broken與next_date。procedure broken (job in binary_integer, broken in boolean, next_date in date :=sysdate)job參數(shù)是工作號，它在問題中唯一標識工作。broken參數(shù)指示此工作是否將標記為破true說明此工作將標記為破，而flase說明此工作將標記為未破。next_date參數(shù)指示在什么時候此工作將再次運

15、行。此參數(shù)缺省值為當前日期和時間。2.1.1.1.2 change()過程change()過程用來改變指定工作的設(shè)置。這個過程有四個參數(shù)：job、what 、next_date與interval。procedure change (job in binary_integer, what in varchar2, next_date in date, interval in varchar2)此job參數(shù)是一個整數(shù)值，它唯一標識此工作。what參數(shù)是由此工作運行的一塊pl/sql代碼塊。next_date參數(shù)指示何時此工作將被執(zhí)行。interval參數(shù)指示一個工作重執(zhí)行的頻度。2.1.1.1.3

16、 interval()過程interval()過程用來顯式地設(shè)置重執(zhí)行一個工作之間的時間間隔數(shù)。這個過程有兩個參數(shù)：job與interval。procedure interval (job in binary_integer, interval in varchar2)job參數(shù)標識一個特定的工作。interval參數(shù)指示一個工作重執(zhí)行的頻度。2.1.1.1.4 isubmit()過程isubmit()過程用來用特定的工作號提交一個工作。這個過程有五個參數(shù)：job、what、next_date、interval與no_parse。procedure isubmit (job in binary

17、_ineger, what in varchar2, next_date in date, interval in varchar2, no_parse in booean:=false)這個過程與submit()過程的唯一區(qū)別在于此job參數(shù)作為in型參數(shù)傳遞且包括一個由開發(fā)者提供的工作號。如果提供的工作號已被使用，將產(chǎn)生一個錯誤。2.1.1.1.5 next_date()過程next_date()過程用來顯式地設(shè)定一個工作的執(zhí)行時間。這個過程接收兩個參數(shù)：job與next_date。procedure next_date(job in binary_ineger, next_date in

18、 date)job標識一個已存在的工作。next_date參數(shù)指示了此工作應(yīng)被執(zhí)行的日期與時間。2.1.1.1.6 remove()過程remove()過程來刪除一個已計劃運行的工作。這個過程接收一個參數(shù)：procedure remove(job in binary_ineger);job參數(shù)唯一地標識一個工作。這個參數(shù)的值是由為此工作調(diào)用submit()過程返回的job參數(shù)的值。已正在運行的工作不能由調(diào)用過程序刪除。2.1.1.1.7 run()過程run()過程用來立即執(zhí)行一個指定的工作。這個過程只接收一個參數(shù)：procedure run(job in binary_ineger) job

19、參數(shù)標識將被立即執(zhí)行的工作。2.1.1.1.8 submit()過程使用submit()過程，工作被正常地計劃好。這個過程有五個參數(shù)：job、what、next_date、interval與no_parse。procedure submit ( job out binary_ineger, what in varchar2, next_date in date, interval in varchar2, no_parse in booean:=false)job參數(shù)是由submit()過程返回的binary_ineger。這個值用來唯一標識一個工作。what參數(shù)是將被執(zhí)行的pl/sql代碼塊

20、。next_date參數(shù)指識何時將運行這個工作。interval參數(shù)何時這個工作將被重執(zhí)行。no_parse參數(shù)指示此工作在提交時或執(zhí)行時是否應(yīng)進行語法分析true指示此pl/sql代碼在它第一次執(zhí)行時應(yīng)進行語法分析，而false指示本pl/sql代碼應(yīng)立即進行語法分析。2.1.1.1.9 user_export()過程user_export()過程返回一個命令，此命令用來安排一個存在的工作以便此工作能重新提交。此程序有兩個參數(shù)：job與my_call。procedure user_export(job in binary_ineger, my_call in out varchar2)job

21、參數(shù)標識一個安排了的工作。my_call參數(shù)包含在它的當前狀態(tài)重新提交此工作所需要的正文。2.1.1.1.10 what()過程what()過程應(yīng)許在工作執(zhí)行時重新設(shè)置此正在運行的命令。這個過程接收兩個參數(shù)：job與what。procedure what (job in binary_ineger, what in out varchar2)job參數(shù)標識一個存在的工作。what參數(shù)指示將被執(zhí)行的新的pl/sql代碼。2.1.1.2 一個簡單例子創(chuàng)建測試表sql> create table a(a date);表已創(chuàng)建。創(chuàng)建一個自定義過程sql> create or replace

22、 procedure test as 2 begin 3 insert into a values(sysdate); 4 end; 5 /過程已創(chuàng)建。創(chuàng)建jobsql> variable job1 number;sql> sql> begin 2 dbms_job.submit(:job1,'test;',sysdate,'sysdate+1/1440');-每天1440分鐘，即一分鐘運行test過程一次 3 end; 4 /pl/sql 過程已成功完成。運行jobsql> begin 2 dbms_job.run(:job1); 3

23、end; 4 /pl/sql 過程已成功完成。sql> select to_char(a,'yyyy/mm/dd hh24:mi:ss') 時間 from a;時間-2001/01/07 23:51:212001/01/07 23:52:222001/01/07 23:53:24刪除jobsql> begin 2 dbms_job.remove(:job1); 3 end; 4 /pl/sql 過程已成功完成2.2 系統(tǒng)數(shù)據(jù)字典以下表格中收集了oracle數(shù)據(jù)字典中幾乎所有的視圖或別名，至于每個視圖或別名的字段請用describe語句來查詢。命名說明：oracle數(shù)

24、據(jù)字典中，對象名稱多數(shù)以"user."，"all."，"dba."為前綴"user."視圖中記錄通常記錄執(zhí)行查詢的帳戶所擁有的對象的信息，"all."視圖中記錄包括"user"記錄和授權(quán)至public或用戶的對象的信息，"dba."視圖包含所有數(shù)據(jù)庫對象，而不管其所有者。使用舉例：select * from dba_data_files查詢表空間的信息(當前用戶必須擁有dba角色)。select owner , object_name ,object_ty

25、pe from all_objects查詢某一用戶下的所有表、過程、函數(shù)等信息。視圖名 版本描述 all_catalogall tables, views, synonyms, sequences accessible to the user all_col_commentscomments on columns of accessible tables and views all_col_grants_madegrants on columns for which the user is owner or grantorall_col_grants_recdgrants on columns

26、 for which the user or public is the grantee all_col_privsgrants on columns for which the user is the grantor, grantee, owner, or an enabled role or public is the grantee all_col_privs_madegrants on columns for which the user is owner or grantorall_col_privs_recdgrants on columns for which the user,

27、 public or enabled role is the grantee all_constraintsconstraint definitions on accessible tables all_cons_columnsinformation about accessible columns in constraint definitions all_db_linksdatabase links accessible to the user all_def_audit_optsauditing options for newly created objects all_dependen

28、ciesdependencies to and from objects accessible to the user all_errorscurrent errors on stored objects that user is allowed to create all_indexesdescriptions of indexes on tables accessible to the user all_ind_columnscolumns comprising indexes on accessible tables all_objectsobjects accessible to th

29、e user all_refreshall the refresh groups that the user can touch all_refresh_childrenall the objects in refresh groups, where the user can touch the group all_sequencesdescription of sequences accessible to the user all_snapshotssnapshots the user can look at all_sourcecurrent source on stored objec

30、ts that user is allowed to create all_synonymsall synonyms accessible to the user all_tablesdescription of tables accessible to the user all_tab_columnscolumns of all tables, views and clusters all_tab_commentscomments on tables and views accessible to the user all_tab_grants_madeuser's grants a

31、nd grants on user's objects all_tab_grants_recdgrants on objects for which the user or public is the grantee all_tab_privsgrants on objects for which the user is the grantor, grantee, owner, or an enabled role or public is the grantee all_tab_privs_madeuser's grants and grants on user's

32、objects all_tab_privs_recdgrants on objects for which the user, public or enabled role is the grantee all_triggerstriggers accessible to the current user all_trigger_colscolumn usage in user's triggers or in triggers on user's tables all_usersinformation about all users of the database all_v

33、iewstext of views accessible to the user audit_actionsdescription table for audit trail action type codes. maps action type numbers to action type names column_privilegesgrants on columns for which the user is the grantor, grantee, owner, or an enabled role or public is the grantee dictionarydescrip

34、tion of data dictionary tables and views dict_columnsdescription of columns in data dictionary tables and views dual全部global_nameglobal database name index_histogramstatistics on keys with repeat count index_statsstatistics on the b-tree resource_costcost for each resource role_role_privsroles which

35、 are granted to roles role_sys_privssystem privileges granted to roles role_tab_privstable privileges granted to roles session_privsprivileges which the user currently has set session_rolesroles which the user currently has enabled. system_privilege_mapprivileges which can be granted to user.table_p

36、rivilegesgrants on objects for which the user is the grantor, grantee, owner, or an enabled role or public is the grantee user_audit_connectaudit trail entries for user logons/logoffs user_audit_objectaudit trail records for statements concerning objects, specifically: table, cluster, view, index, s

37、equence, public database link, public synonym, procedure, trigger, rollback segment, tablespace, role, user user_audit_sessionuser_audit_statementaudit trail records concerning grant, revoke, audit, noaudit and alter system user_audit_trailaudit trail entries relevant to the user user_catalogtables,

38、 views, synonyms and sequences owned by the user user_clustersdescriptions of user's own clusters user_clu_columnsmapping of table columns to cluster columns user_col_commentscomments on columns of user's tables and views user_col_grantsgrants on columns for which the user is the owner, gran

39、tor or grantee user_col_grants_madeall grants on columns of objects owned by the user user_col_grants_recdgrants on columns for which the user is the grantee user_col_privsgrants on columns for which the user is the owner, grantor or grantee user_col_privs_madeall grants on columns of objects owned

40、by the user user_col_privs_recdgrants on columns for which the user is the grantee user_constraintsconstraint definitions on user's own tables user_cons_columnsinformation about accessible columns in constraint definitions user_cross_refscross references for user's views and synonyms user_db

41、_linksdatabase links owned by the user user_dependenciesdependencies to and from a users objects user_errorscurrent errors on stored objects owned by the user user_extentsextents comprising segments owned by the user user_free_spacefree extents in tablespaces accessible to the user user_indexesdescr

42、iption of the user's own indexes user_ind_columnscolumns comprising user's indexes or on user's tablesuser_jobsall jobs owned by this user user_objectsobjects owned by the user user_object_sizesizes, in bytes, of various pl/sql objects user_obj_audit_optsauditing options for user's o

43、wn tables and views user_refreshall the refresh groups user_refresh_childrenall the objects in refresh groups, where the user owns the refresh group user_resource_limitsdisplay resource limit of the user user_role_privsroles granted to current user user_segmentsstorage allocated for all database seg

44、ments user_sequencesdescription of the user's own sequences user_snapshotssnapshots the user can look at user_snapshot_logsall snapshot logs owned by the user user_sourcesource of stored objects accessible to the user user_synonymsthe user's private synonyms user_sys_privssystem privileges g

45、ranted to current user user_tablesdescription of the user's own tables user_tablespacesdescription of accessible tablespaces user_tab_audit_optsauditing options for user's own tables and views user_tab_columnscolumns of user's tables, views and clusters user_tab_commentscomments on the t

46、ables and views owned by the user user_tab_grantsgrants on objects for which the user is the owner, grantor or grantee user_tab_grants_madeall grants on objects owned by the user user_tab_grants_recdgrants on objects for which the user is the grantee user_tab_privsgrants on objects for which the use

47、r is the owner, grantor or grantee user_tab_privs_madeall grants on objects owned by the user user_tab_privs_recdgrants on objects for which the user is the grantee user_triggerstriggers owned by the user user_trigger_colscolumn usage in user's triggers user_ts_quotastablespace quotas for the us

48、er user_usersinformation about the current user user_viewstext of views owned by the user accessible_columnssynonym for all_tab_columns all_col_grantssynonym for column_privileges all_jobssynonym for user_jobs all_tab_grantssynonym for table_privileges catsynonym for user_catalog clu synonym for use

49、r_clusters colssynonym for user_tab_columns dba_audit_connect synonym for user_audit_connect dba_audit_resourcesynonym for user_audit_resource dba_refresh_childrensynonym for user_refresh_children dictsynonym for dictionary indsynonym for user_indexes objsynonym for user_objects seqsynonym for user_

50、sequences sm$versionsynonym for sm_$version synsynonym for user_synonyms tabssynonym for user_tables v$accesssynonym for v_$access v$archivesynonym for v_$archive v$backupsynonym for v_$backup v$bgprocesssynonym for v_$bgprocess v$circuitsynonym for v_$circuit v$compatibilitysynonym for v_$compatibi

51、lity v$compatsegsynonym for v_$compatseg v$controlfilesynonym for v_$controlfile v$databasesynonym for v_$database v$datafilesynonym for v_$datafile v$dbfilesynonym for v_$dbfile v$dblinksynonym for v_$dblink v$db_object_cachesynonym for v_$db_object_cache v$dispatchersynonym for v_$dispatcher v$ena

52、bledprivssynonym for v_$enabledprivs v$filestatsynonym for v_$filestat v$fixed_tablesynonym for v_$fixed_table v$latchsynonym for v_$latch v$latchholdersynonym for v_$latchholder v$latchnamesynonym for v_$latchname v$librarycachesynonym for v_$librarycache v$licensesynonym for v_$license v$loadcstatsynonym for v_$loadcstat v$loadtstatsynonym for v_$loadtstat v$locksynonym for