Oracle基礎參考模板

1、Oracle基礎筆記，重新整理了一下，還是有點亂，主要是9i,部分會涉及10g的東西。一、結構一個Oracle服務由實例(instance)和數(shù)據(jù)庫(database)組成，數(shù)據(jù)庫和實例是一對多的關系，即一個實例只可以管理一個數(shù)據(jù)庫，而一個數(shù)據(jù)庫可以被多個實例同時管理著，如在RAC環(huán)境中。（1）實例、由一組進程和內(nèi)存結構組成、5個基本進程：dbwr（數(shù)據(jù)文件寫）, lgwr（日志文件寫）,smon（系統(tǒng)恢復和控制）,pmon（服務進程監(jiān)控）,chk（檢查點）,、其他進程：arch（歸檔日志寫），job作業(yè)進程等、內(nèi)存：SGA和PGA、SGA包括share poollib cache: 

2、; sql, pl/sql,dictionary cache:  數(shù)據(jù)字典緩存db  buffer：  nk_cache   block 是nk的表空間使用的bufferrecycle_cache：經(jīng)常不駐留內(nèi)存的對象緩沖區(qū)keep_cache：經(jīng)常駐留內(nèi)存的對象緩沖區(qū)relog buffer:  redo log 文件緩存large buffer： share模式下為uga所使用java buffer：  jvm使用streem buffer( 10g)最大比例的是db_cache_size，上面沒有列出db_c

3、ache_size，是因為其被nk_cache包含了，若db_block_size =8k，則db_cache_size就是8k_cache。1 / 17、參數(shù)SGA_MAX_SIZE  : SGA最大內(nèi)存SHARED_POOL_SIZE : share pool 的大小DB_CACHE_SIZE  : db cache 的大小DB_RECYCLE_CACHE_SIZE: 經(jīng)常不駐留內(nèi)存的對象緩沖區(qū)DB_KEEP_CACHE_SIZE: 經(jīng)常駐留內(nèi)存的對象緩沖區(qū)LOG_BUFFER     : log buffer 的大小LARGER

4、_POOL_SIZE  : large buffer的大小JAVA_POOL_SIZE     : java buffer的大小Db_2K_cache_size      : block size 為的表空間的數(shù)據(jù)緩沖Db_4K_cache_size      : block size 為4的表空間的數(shù)據(jù)緩沖Db_8K_cache_size      : block size 為8的表空間的數(shù)據(jù)緩

5、沖Db_16k_cache_size      : block size 為16的表空間的數(shù)據(jù)緩沖Db_32K_cache_size      : block size 為32的表空間的數(shù)據(jù)緩沖若db_block_size 為nk，則參數(shù)db_nk_cache_size無意義，nk的cache size由db_cache_size確定。 （2）數(shù)據(jù)庫、數(shù)據(jù)庫文件：數(shù)據(jù)文件，在線重做日志文件，控制文件、其他文件：參數(shù)文件，密碼文件，歸檔日志文件、物理結構：表空間（table space）

6、- 段(segment) - 擴展(extent)- 塊(block)、OMF : oracle manage file ，系統(tǒng)自動管理數(shù)據(jù)庫文件，即指定數(shù)據(jù)文件，日志文件的缺省位置，創(chuàng)建表空間的時候不需要指定數(shù)據(jù)文件，由系統(tǒng)在缺省位置自動生成數(shù)據(jù)文件、參數(shù)Db_block_size : 基本塊的大小Db_create_file_dest : OMF管理數(shù)據(jù)文件缺省位置Db_create_online_log_dest_n: OMF管理日志文件缺省位置Db_files  : 可打開數(shù)據(jù)文件的數(shù)量，注意數(shù)據(jù)文件數(shù)量增大時，要修改這個參數(shù)Control_files : 控制文件的全路徑D

7、b_name : 數(shù)據(jù)庫名稱數(shù)據(jù)庫名和實例名是兩個概念，因為一個數(shù)據(jù)庫可以由多個實例帶動，不同實例可以有不同的名稱，安裝的時候，缺省是將數(shù)據(jù)庫名和實例名設為一樣的。 二、字符集、服務器字符集在建立數(shù)據(jù)庫時候指定，之后一般不改動8i以上版本可以通過alter database來修改字符集，但也只限于子集到超集，不建議修改props$表，因可能導致嚴重錯誤,如下是修改字符集的例子>Startup nomount;>Alter database mount exclusive;>Alter system enable restricted session;>Alte

8、r system set job_queue_process=0;>Alter database open;>Alter database character set zhs16gbk;、客戶端字符集UNIX   環(huán)境變量，操作系統(tǒng)用戶配置文件.profile 中的NLS_LANGWindows  注冊表 local machine- software oracle-HOME0-NLS_LANG常見如：SIMPLIFIED CHINESE_CHINA.ZHS16GBKAMERICAN_AMERICA.US7ASCII、如何獲取字符集信息（1） 數(shù)據(jù)庫服

9、務器字符集：select * from nls_database_parameters，其來源于props$，是表示數(shù)據(jù)庫的字符集。（2）客戶端字符集環(huán)境select * from nls_instance_parameters,其來源于v$parameter，表示客戶端的字符集的設置，可能是參數(shù)文件，環(huán)境變量或者是注冊表（3）會話字符集環(huán)境select * from nls_session_parameters，其來源于v$nls_parameters，表示會話自己的設置，可能是會話的環(huán)境變量或者是alter session完成，如果會話沒有特殊的設置，將與nls_instance_para

10、meters一致。、 客戶端的字符集要求與服務器一致，才能正確顯示數(shù)據(jù)庫的非Ascii字符。如果多個設置存在的時候，alter session>環(huán)境變量>注冊表>參數(shù)文件、字符集要求一致，但是語言設置卻可以不同，語言設置建議用英文。如字符集是zhs16gbk，則nls_lang可以是American_America.zhs16gbk。三、啟動與停止服務（1）oracle服務、Startup 參數(shù)       Nomount   打開參數(shù)文件,分配SGA,啟動系統(tǒng)進程  

11、;     Mount     打開控制文件，警告文件      Open      打開數(shù)據(jù)文件，日志文件 【缺省】       Restrict    以限制方式打開，只有擁有create session”,”restrict session”的用戶才能連接數(shù)據(jù)庫     &

12、#160; Force      不先用shutdown命令關閉數(shù)據(jù)庫，而直接強行關閉并重啟動，       Pfile.       以指定的pfile啟動數(shù)據(jù)庫       Exclusive   獨占方式打開，用于多個實例帶一個數(shù)據(jù)庫的情況       Readonly &

13、#160;  以只讀方式打開 、Shutdown參數(shù)不論哪種模式，都不允許新的連接       Normal  等待所有連接斷開       Transaction 等待所有事務結束       Immediate 立即結束實例,checkpoint提交,回滾事務,重啟動時不用恢復       Abort立即結束實例,不進行提交

14、,回滾事務,重啟動時要恢復一般不使用shutdown abort，特殊情況下不得不用，則可在shutdown abort之前執(zhí)行以下命令Alter system checkpoint、改變數(shù)據(jù)庫狀態(tài)為靜默Alter system quiesce restricted;此時，不活動的session不能再活動,等待活動的session變?yōu)椴换顒?只有管理員的session可以繼續(xù)活動Alter system unquiesce; 、掛起數(shù)據(jù)庫Suspending and resuming a database掛起數(shù)據(jù)庫,將停止數(shù)據(jù)文件的所有I/OAlter system suspend;

15、Alter system resume;不能代替關閉數(shù)據(jù)庫,因為在掛起狀態(tài)下,可能有事務沒有提交的. （2）監(jiān)聽 $lsnrctlLsnrctl>startLsnrctl>stop可設置密碼Lsnrctl>set password  密碼 四、參數(shù)文件可在啟動時指定pfile，如沒有在啟動中指定pfile，則按以下順序找參數(shù)文件1、Spfile  init$ORACLE_SID.ora、缺省spfile 、Pfile 參數(shù)文件中顯式指定相關參數(shù)，沒有顯式指定的參數(shù)取系統(tǒng)的缺省值。 Show par

16、ameters 查看當前使用的參數(shù)值Create pfile  從spfile創(chuàng)建pfileV$parameter 當前有效的參數(shù)值V$parameter2 基本同上V$spparameter 參數(shù)文件里的參數(shù)值 使用pfile啟動，修改系統(tǒng)參數(shù)不能直接保存到pfile中，還需要手工去修改pfile使用spfile啟動，修改系統(tǒng)參數(shù)可以指定scopspfile，both，memory ,缺省是both 五、控制文件、創(chuàng)建數(shù)據(jù)庫時指定控制文件的位置,通常要有多個,放在不同的磁盤上,數(shù)據(jù)庫只讀第一個文件,但會寫入所有文件；、系統(tǒng)參數(shù)control_files 指定控制

17、文件的位置；、控制文件包括數(shù)據(jù)庫名稱,物理文件重做日志文件歸檔日志文件的信息,數(shù)據(jù)庫創(chuàng)建的時間,當前的日志序列號,檢查點信息等；、創(chuàng)建另外的控制文件(1)停止數(shù)據(jù)庫；(2)復制控制文件到其他位置；(3)更改參數(shù)文件中的control_files參數(shù),增加新的控制文件名稱；(4)啟動數(shù)據(jù)庫。創(chuàng)建新的控制文件原因：數(shù)據(jù)庫的控制文件被破壞,而控制文件又沒備份,或者要改變數(shù)據(jù)庫的某些固定參數(shù),如數(shù)據(jù)庫名稱。、備份控制文件Alter database backup controlfile to /oracle/backup/control.bkpAlter database backup control

18、file to trace;、控制文件信息V$database 從控制文件獲取數(shù)據(jù)庫的信息V$controlfile  控制文件名稱V$controlfile_record_section 控制文件記錄信息V$parameter 可從參數(shù)中獲取控制文件名稱 六、重做日志(redo log )、 一個數(shù)據(jù)庫必須用兩個或以上的online redo log 組，每個日志組包括1個或以上重做日志文件；、一個重做日志組的重做日志文件大小必須一致，不同日志組的日志文件大小可以不一致，但這樣并沒有任何好處；、LGWR進程循環(huán)使用重做日志組，一個組寫滿后，將進行日志切換；、LG

19、WR進程將重做記錄（records）寫入日志文件，一個重做記錄由一組change vectors 組成，一條change vectors描述數(shù)據(jù)庫某個block的改變，如表記錄的插入，更新，刪除等。重做日志是用來重新構建數(shù)據(jù)庫的數(shù)據(jù)更改，包括回滾段的修改。、Redo log buffer  SGA中作為重做日志的緩沖 Alter database add logfile group 10(/oracle/dbs/log1c.rdo,/oracle/dbs/log2c.rdo) size 500K 加一個日志組Select group#,archived,status fro

20、m v$log;查看日志組狀態(tài)Alter database drop logfile member oracle/dbs/log3c.rdo家刪除一個日志組成員Alter system switch logfile手工切換日志 七、歸檔日志(archive log )、數(shù)據(jù)庫兩種模式，歸檔或不歸檔。當物理磁盤損壞后，不歸檔模式只能恢復到上次物理備份時候的數(shù)據(jù)，歸檔模式則可以恢復到被歸檔的數(shù)據(jù)；、歸檔模式將滿的redolog文件復制一份到指定位置保存、查看是否歸檔模式archive log listorselect * from v$instance; 、改變歸檔模式

21、0;shutdown immediate startup mount alter database archivelog noarchivelog alter system set log_archive_dest_1='location=d:oracleproduct10.2.0oradataoasis' alter database open 5、手工歸檔 alter system archive log current ; 將當前日志文件歸檔，并切換 alter system switch logfil

22、e 切換日志文件，則歸檔 v$log 歸檔日志組 v$logfile 歸檔日志文件 八、內(nèi)存結構l、SGA share pool (一般幾百M就可以了)：共享sql,pl/sql，數(shù)據(jù)字典；db cache 數(shù)據(jù)緩沖區(qū)，通常越大越好。Larger pool  (大I/O操作，如備份等，此外共享服務器時用于服務進程，Java pool、sga的各個組成部分sql> select * from v$sga;name           

23、;           value-              -fixed size                   104936variable size  &#

24、160;           823164928database buffers          1073741824redo buffers                 172032 或者sql> show sgatotal

25、system global area   1897183720 bytesfixed size                   104936 bytesvariable size              823164928 bytesdatabase buffer

26、s          1073741824 bytesredo buffers                 172032 bytes fixed size        oracle 的不同平臺和不同版本下可能不一樣，但對于確定環(huán)境是一個固定的值，里面存儲了sga

27、各部分組件的信息，可以看作引導建立sga的區(qū)域。 variable size        包含了shared_pool_size、java_pool_size、large_pool_size 等內(nèi)存設置 database buffers        指數(shù)據(jù)緩沖區(qū)，在9i 中包含db_cache_size、db_keep_cache_size、db_recycle_cache_size、 db_nk_cache_size。 

28、redo buffers       指日志緩沖區(qū)，log_buffer。對于v$parameter、v$sgastat、v$sga查詢值可能不一樣。v$ parameter 里面的值，是指用戶在初始化參數(shù)文件里面設置的值，v$sgastat是oracle 實際分配的日志緩沖區(qū)大小（因為緩沖區(qū)的分配值實際上是離散的，也不是以block 為最小單位進行分配的），v$sga 里面查詢的值，是在oracle 分配了日志緩沖區(qū)后，為了保護日志緩沖區(qū)，設置了一些保護頁，通常我們會發(fā)現(xiàn)保護頁大小是8k(不同環(huán)境可能不一樣)。3、PGAsql>

29、; show parameters area_size name                                 type    value-       

30、;        -     -bitmap_merge_area_size                   integer    1048576create_bitmap_area_size       

31、            integer    8388608hash_area_size                           integer   

32、0; 131072sort_area_size                            integer     65536 實際上，i里面設置了pga_aggregate_target參數(shù)，每個進程使用的hash,sort區(qū)都由系統(tǒng)自動調節(jié)，我們把pga_aggre

33、gate_target設置到合適大小就可以了。 九、數(shù)據(jù)結構 （l）表空間 段    - 表，索引等數(shù)據(jù)對象 區(qū)    - 最小的分配單元 Block - 固定大小，一般為2K倍數(shù)，安裝后不能更改、由數(shù)據(jù)文件組成，數(shù)據(jù)文件大小一般為2G的倍數(shù)、區(qū)（extents）管理方式：本地管理或字典管理兩種方式的差別是表空間中extends的使用情況存放在表空間本身還是存在在數(shù)據(jù)字典中。本地管理比字典管理的性能要好，因為減少了訪問數(shù)據(jù)字典的沖突。Extent mana

34、gement local | dictionaryExtent的分配，可以是autoallocate （自動分配）或 uniform（統(tǒng)一大小） 、段空間管理：自動或手動自動管理使用位圖管理段的可用空間，手動管理使用可用鏈表管理，需要設置pctused,freelists,freelists groups 等參數(shù)。自動管理比手動性能要好，現(xiàn)在一般都使用自動管理，省事。 、9.2版本以上，若system表空間是local manage，則不能創(chuàng)建dictionary manage的表空間。、區(qū)管理是指在創(chuàng)建一個新段時候，如何去給其分配extents，而段管理是指出在一個段上插

35、入數(shù)據(jù)時如何去分配空間。、區(qū)管理和段管理方式在創(chuàng)建表空間時指定，之后不能再改，只有本地管理表空間可以使用自動段空間管理方式。Create tablespace lmtbsb datafile /oracle/datra/lmtbsb01.dbf size 50MExtent management local uniform size 128KSegment space management autoBlocksize 16k;  可指定與db_block_size不同的block size,但必須設置相應的db_nk_cache_size參數(shù)值才可以使用、存儲參數(shù)Create tab

36、lespace tbsbDatafile /oracle/data/tbsa01.dbf size 50MExtent management dictionaryDefault storage(Initial 50K     每個段第一個extent的大小Next 50K      每個段第二個extent的大小Minextents 2    每個段創(chuàng)建時候的extents數(shù)量 Maxextents 50   每個段最多可以有extents的數(shù)量

37、，可以是unlimitedPctincrease 0);   第三個extent開始增加的空間大小比例、臨時表空間Create temporary tablespace lmtemp tempfile /oracle/data/lmtemp01.dbfSize 20M reuseExtent management local uniform size 16M;臨時表空間只能使extent統(tǒng)一大小的表空間，不可以使用autoallocate子句 、Alter tablespace coalesce 來接合可用extents 10、Loging 和nolog

38、ing創(chuàng)建表空空間可以指定是否對其記錄redo log ，創(chuàng)建對象，如表，也可以這樣指定，如果想表空間下所有對象都統(tǒng)一屬性，可以使用 force logging，則即使在表空間下用nologging創(chuàng)建表，也會是logging屬性。 11、Offline 和online除開system表空間外，所有表空間都可以offlineAlter tablespace users offlinet normal|temporary|immediate|for recoverNormal : checkpoint for all datafiles of the tablespaceTempora

39、ry  如果數(shù)據(jù)文件有錯，可以使用temporary來offline 表空間Immediate  不checkpoint,但需要先 recovery 才能online ，如果是非歸檔模式，則不可以使用recoverSelect block_id,bytes,blocks from dba_free-space where tablespace_name=tabsp_4Order by block_id 12、刪除表空間Drop tablespace users including contents and datafiles 可同時刪除表空間的內(nèi)容和數(shù)據(jù)文件。&#

40、160;V$TABLESPACE  從控制文件中獲得的表空間的信息DBA_TABLESPACES, USER_TABLESPACES   所有或當前用戶的表空間信息DBA_SWGMENTS, USER_SEGMENTS        所有或當前用戶的段信息DBA_EXTENTS,USER_EXTENTS            所有或當前用戶的extents信息DBA_FREE_SPACE

41、,USER_FREE_SPACE      所有或當前用戶的可用extents信息V$DATAFILE                              所有數(shù)據(jù)文件的信息，則重動態(tài)信息     

42、60;                         V$TEMPFILE                       

43、0;      所有臨時文件的信息，則重動態(tài)信息DBA_DATA_FILES                         所有數(shù)據(jù)文件的信息，主要是靜態(tài)信息            

44、60;               DBA_TEMP_FILES                        所有臨時文件的信息，主要是靜態(tài)信息V$TEMP_EXTENT_MAP   &#

45、160;               本地管理臨時表空間的extents信息V$TEMP_SPACE_POOL                    本地管理臨時表空間的擦cacheV$TEMP_SPACE_HEADER    &

46、#160;            臨時文件的空間使用情況DBA_USERS                              用戶DBA_TS_QUOTAS   

47、                      用戶在表空間的使用限額V￥SORT_SEGMENT                       臨時表空間的排序段 

48、                      V$SORT_USER                           

49、 用戶使用的臨時表空間排序區(qū)  十、臨時表空間與臨時表一個實例必須有一個缺省的臨時表空間，每個用戶，又可指定其自己的臨時表空間。臨時表使用 create global temporary table來建立，表保存在臨時表空間中，在企業(yè)管理器中看臨時表的屬性，它所屬的表空間是不正確的（只是顯示名稱字母排序最小的表空間名罷了）。臨時表對所有session可見，但數(shù)據(jù)是各自獨立的。有兩種session，一種是表的數(shù)據(jù)在事務結束后自動刪除，一種是在session結束后再刪除，在創(chuàng)建臨時表時通過ON COMMIT DELETE|PRESERVE ROWS字句來指定。 十一、

50、undo space 、Undo record 的作用：（1）rollback語句執(zhí)行時，undo records 用來undo change 未提提交的事務；（2）數(shù)據(jù)庫recovery時用來undo未從redo寫到數(shù)據(jù)文件的未提交數(shù)據(jù)。（3）提供一致性讀，即在某用戶修改某個表的同時，其他用戶仍然可以讀取表原來的數(shù)據(jù)。 、兩種回滾的管理方式（1）auto（2）manual通過設置參數(shù)UNDO_MANAGEMENTUndo_management =autoUndo_tablespace  undo表空間Undo_RETENTION  undo記錄保存時間

51、，以秒為單位Undo_SUPPRESS_ERRORS 如果為真，真在自動管理模式下使用undo管理sql語句產(chǎn)生的錯誤將被抑制(suppress) 、undo表空間可以在數(shù)據(jù)庫創(chuàng)建的時候使用undo tablespace 子句來創(chuàng)建，如果不指定，則系統(tǒng)會自動建立一個undo表空間。也可以在數(shù)據(jù)庫可用時候通過create undo tablespace來創(chuàng)建 、切換undo表空間Alter system set undo_TABLESPACE = undotbs_02; 、Flashback 查詢（10g)Undo space使Oracle可以使用flashsbac

52、k查詢來查詢之前的數(shù)據(jù)內(nèi)容。Dbms_flashback 、手工管理undo Undo_management = manualRollback_segments 實例啟動時獲得的的回滾段Transactions   最大的并發(fā)事務數(shù)量Transactions_per_rollback_segment 每個回滾段可以同時處理的事務數(shù)量Max_roolback_segments  一個實例能同時在線的最大的回滾段數(shù)量  【10g】在AUM（自動undo管理）模式下，UNDO_RETENTION參數(shù)用以控制事務提交以后undo信息

53、保留的時間。該參數(shù)以秒為單位，9iR1初始值為900秒，在Oracle9iR2增加為10800秒。但是這是一個NO Guaranteed的限制。也就是說，如果有其他事務需要回滾空間，而空間出現(xiàn)不足時，這些信息仍然會被覆蓋。很多時候這是不希望看到的。從Oracle10g開始，如果你設置UNDO_RETENTION為0，那么Oracle啟用自動調整以滿足最長運行查詢的需要。當然如果空間不足，那么Oracle滿足最大允許的長時間查詢。而不再需要用戶手工調整。同時Oracle增加了Guarantee控制，也就是說，你可以指定UNDO表空間必須滿足UNDO_RETENTION的限制。 SQL&

54、gt; alter tablespace undotbs1 retention guarantee; Tablespace altered SQL> alter tablespace undotbs1 retention noguarantee; Tablespace altered 在DBA_TABLESPACES視圖中增加了RETENTION字段用以描述該選項: SQL> select tablespace_name,contents,retention from dba_tablespaces; TABLESPACE

55、_NAME                CONTENTS  RETENTION- - -SYSTEM                         PERMANENT NOT APPLYUNDOT

56、BS1                       UNDO      NOGUARANTEESYSAUX                  

57、0;      PERMANENT NOT APPLYTEMP                           TEMPORARY NOT APPLYUSERS                          PERMANENT NOT APPLYEYGLE                        &