Oracle基础参考模板

1、Oracle基础笔记，重新整理了一下，还是有点乱，主要是9i,部分会涉及10g的东西。一、结构一个Oracle服务由实例(instance)和数据库(database)组成，数据库和实例是一对多的关系，即一个实例只可以管理一个数据库，而一个数据库可以被多个实例同时管理着，如在RAC环境中。（1）实例、由一组进程和内存结构组成、5个基本进程：dbwr（数据文件写）, lgwr（日志文件写）,smon（系统恢复和控制）,pmon（服务进程监控）,chk（检查点）,、其他进程：arch（归档日志写），job作业进程等、内存：SGA和PGA、SGA包括share poollib cache: 

2、; sql, pl/sql,dictionary cache:  数据字典缓存db  buffer：  nk_cache   block 是nk的表空间使用的bufferrecycle_cache：经常不驻留内存的对象缓冲区keep_cache：经常驻留内存的对象缓冲区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、参数SGA_MAX_SIZE  : SGA最大内存SHARED_POOL_SIZE : share pool 的大小DB_CACHE_SIZE  : db cache 的大小DB_RECYCLE_CACHE_SIZE: 经常不驻留内存的对象缓冲区DB_KEEP_CACHE_SIZE: 经常驻留内存的对象缓冲区LOG_BUFFER     : log buffer 的大小LARGER

4、_POOL_SIZE  : large buffer的大小JAVA_POOL_SIZE     : java buffer的大小Db_2K_cache_size      : block size 为的表空间的数据缓冲Db_4K_cache_size      : block size 为4的表空间的数据缓冲Db_8K_cache_size      : block size 为8的表空间的数据缓

5、冲Db_16k_cache_size      : block size 为16的表空间的数据缓冲Db_32K_cache_size      : block size 为32的表空间的数据缓冲若db_block_size 为nk，则参数db_nk_cache_size无意义，nk的cache size由db_cache_size确定。 （2）数据库、数据库文件：数据文件，在线重做日志文件，控制文件、其他文件：参数文件，密码文件，归档日志文件、物理结构：表空间（table space）

6、- 段(segment) - 扩展(extent)- 块(block)、OMF : oracle manage file ，系统自动管理数据库文件，即指定数据文件，日志文件的缺省位置，创建表空间的时候不需要指定数据文件，由系统在缺省位置自动生成数据文件、参数Db_block_size : 基本块的大小Db_create_file_dest : OMF管理数据文件缺省位置Db_create_online_log_dest_n: OMF管理日志文件缺省位置Db_files  : 可打开数据文件的数量，注意数据文件数量增大时，要修改这个参数Control_files : 控制文件的全路径D

7、b_name : 数据库名称数据库名和实例名是两个概念，因为一个数据库可以由多个实例带动，不同实例可以有不同的名称，安装的时候，缺省是将数据库名和实例名设为一样的。 二、字符集、服务器字符集在建立数据库时候指定，之后一般不改动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   环境变量，操作系统用户配置文件.profile 中的NLS_LANGWindows  注册表 local machine- software oracle-HOME0-NLS_LANG常见如：SIMPLIFIED CHINESE_CHINA.ZHS16GBKAMERICAN_AMERICA.US7ASCII、如何获取字符集信息（1） 数据库服

9、务器字符集：select * from nls_database_parameters，其来源于props$，是表示数据库的字符集。（2）客户端字符集环境select * from nls_instance_parameters,其来源于v$parameter，表示客户端的字符集的设置，可能是参数文件，环境变量或者是注册表（3）会话字符集环境select * from nls_session_parameters，其来源于v$nls_parameters，表示会话自己的设置，可能是会话的环境变量或者是alter session完成，如果会话没有特殊的设置，将与nls_instance_para

10、meters一致。、 客户端的字符集要求与服务器一致，才能正确显示数据库的非Ascii字符。如果多个设置存在的时候，alter session>环境变量>注册表>参数文件、字符集要求一致，但是语言设置却可以不同，语言设置建议用英文。如字符集是zhs16gbk，则nls_lang可以是American_America.zhs16gbk。三、启动与停止服务（1）oracle服务、Startup 参数       Nomount   打开参数文件,分配SGA,启动系统进程  

11、;     Mount     打开控制文件，警告文件      Open      打开数据文件，日志文件 【缺省】       Restrict    以限制方式打开，只有拥有create session”,”restrict session”的用户才能连接数据库     &

12、#160; Force      不先用shutdown命令关闭数据库，而直接强行关闭并重启动，       Pfile.       以指定的pfile启动数据库       Exclusive   独占方式打开，用于多个实例带一个数据库的情况       Readonly &

13、#160;  以只读方式打开 、Shutdown参数不论哪种模式，都不允许新的连接       Normal  等待所有连接断开       Transaction 等待所有事务结束       Immediate 立即结束实例,checkpoint提交,回滚事务,重启动时不用恢复       Abort立即结束实例,不进行提交

14、,回滚事务,重启动时要恢复一般不使用shutdown abort，特殊情况下不得不用，则可在shutdown abort之前执行以下命令Alter system checkpoint、改变数据库状态为静默Alter system quiesce restricted;此时，不活动的session不能再活动,等待活动的session变为不活动,只有管理员的session可以继续活动Alter system unquiesce; 、挂起数据库Suspending and resuming a database挂起数据库,将停止数据文件的所有I/OAlter system suspend;

15、Alter system resume;不能代替关闭数据库,因为在挂起状态下,可能有事务没有提交的. （2）监听 $lsnrctlLsnrctl>startLsnrctl>stop可设置密码Lsnrctl>set password  密码 四、参数文件可在启动时指定pfile，如没有在启动中指定pfile，则按以下顺序找参数文件1、Spfile  init$ORACLE_SID.ora、缺省spfile 、Pfile 参数文件中显式指定相关参数，没有显式指定的参数取系统的缺省值。 Show par

16、ameters 查看当前使用的参数值Create pfile  从spfile创建pfileV$parameter 当前有效的参数值V$parameter2 基本同上V$spparameter 参数文件里的参数值 使用pfile启动，修改系统参数不能直接保存到pfile中，还需要手工去修改pfile使用spfile启动，修改系统参数可以指定scopspfile，both，memory ,缺省是both 五、控制文件、创建数据库时指定控制文件的位置,通常要有多个,放在不同的磁盘上,数据库只读第一个文件,但会写入所有文件；、系统参数control_files 指定控制

17、文件的位置；、控制文件包括数据库名称,物理文件重做日志文件归档日志文件的信息,数据库创建的时间,当前的日志序列号,检查点信息等；、创建另外的控制文件(1)停止数据库；(2)复制控制文件到其他位置；(3)更改参数文件中的control_files参数,增加新的控制文件名称；(4)启动数据库。创建新的控制文件原因：数据库的控制文件被破坏,而控制文件又没备份,或者要改变数据库的某些固定参数,如数据库名称。、备份控制文件Alter database backup controlfile to /oracle/backup/control.bkpAlter database backup control

18、file to trace;、控制文件信息V$database 从控制文件获取数据库的信息V$controlfile  控制文件名称V$controlfile_record_section 控制文件记录信息V$parameter 可从参数中获取控制文件名称 六、重做日志(redo log )、 一个数据库必须用两个或以上的online redo log 组，每个日志组包括1个或以上重做日志文件；、一个重做日志组的重做日志文件大小必须一致，不同日志组的日志文件大小可以不一致，但这样并没有任何好处；、LGWR进程循环使用重做日志组，一个组写满后，将进行日志切换；、LG

19、WR进程将重做记录（records）写入日志文件，一个重做记录由一组change vectors 组成，一条change vectors描述数据库某个block的改变，如表记录的插入，更新，删除等。重做日志是用来重新构建数据库的数据更改，包括回滚段的修改。、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;查看日志组状态Alter database drop logfile member oracle/dbs/log3c.rdo家删除一个日志组成员Alter system switch logfile手工切换日志 七、归档日志(archive log )、数据库两种模式，归档或不归档。当物理磁盘损坏后，不归档模式只能恢复到上次物理备份时候的数据，归档模式则可以恢复到被归档的数据；、归档模式将满的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 归档日志文件 八、内存结构l、SGA share pool (一般几百M就可以了)：共享sql,pl/sql，数据字典；db cache 数据缓冲区，通常越大越好。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 的不同平台和不同版本下可能不一样，但对于确定环境是一个固定的值，里面存储了sga

27、各部分组件的信息，可以看作引导建立sga的区域。 variable size        包含了shared_pool_size、java_pool_size、large_pool_size 等内存设置 database buffers        指数据缓冲区，在9i 中包含db_cache_size、db_keep_cache_size、db_recycle_cache_size、 db_nk_cache_size。 

28、redo buffers       指日志缓冲区，log_buffer。对于v$parameter、v$sgastat、v$sga查询值可能不一样。v$ parameter 里面的值，是指用户在初始化参数文件里面设置的值，v$sgastat是oracle 实际分配的日志缓冲区大小（因为缓冲区的分配值实际上是离散的，也不是以block 为最小单位进行分配的），v$sga 里面查询的值，是在oracle 分配了日志缓冲区后，为了保护日志缓冲区，设置了一些保护页，通常我们会发现保护页大小是8k(不同环境可能不一样)。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参数，每个进程使用的hash,sort区都由系统自动调节，我们把pga_aggre

33、gate_target设置到合适大小就可以了。 九、数据结构 （l）表空间 段    - 表，索引等数据对象 区    - 最小的分配单元 Block - 固定大小，一般为2K倍数，安装后不能更改、由数据文件组成，数据文件大小一般为2G的倍数、区（extents）管理方式：本地管理或字典管理两种方式的差别是表空间中extends的使用情况存放在表空间本身还是存在在数据字典中。本地管理比字典管理的性能要好，因为减少了访问数据字典的冲突。Extent mana

34、gement local | dictionaryExtent的分配，可以是autoallocate （自动分配）或 uniform（统一大小） 、段空间管理：自动或手动自动管理使用位图管理段的可用空间，手动管理使用可用链表管理，需要设置pctused,freelists,freelists groups 等参数。自动管理比手动性能要好，现在一般都使用自动管理，省事。 、9.2版本以上，若system表空间是local manage，则不能创建dictionary manage的表空间。、区管理是指在创建一个新段时候，如何去给其分配extents，而段管理是指出在一个段上插

35、入数据时如何去分配空间。、区管理和段管理方式在创建表空间时指定，之后不能再改，只有本地管理表空间可以使用自动段空间管理方式。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参数值才可以使用、存储参数Create tab

36、lespace tbsbDatafile /oracle/data/tbsa01.dbf size 50MExtent management dictionaryDefault storage(Initial 50K     每个段第一个extent的大小Next 50K      每个段第二个extent的大小Minextents 2    每个段创建时候的extents数量 Maxextents 50   每个段最多可以有extents的数量

37、，可以是unlimitedPctincrease 0);   第三个extent开始增加的空间大小比例、临时表空间Create temporary tablespace lmtemp tempfile /oracle/data/lmtemp01.dbfSize 20M reuseExtent management local uniform size 16M;临时表空间只能使extent统一大小的表空间，不可以使用autoallocate子句 、Alter tablespace coalesce 来接合可用extents 10、Loging 和nolog

38、ing创建表空空间可以指定是否对其记录redo log ，创建对象，如表，也可以这样指定，如果想表空间下所有对象都统一属性，可以使用 force logging，则即使在表空间下用nologging创建表，也会是logging属性。 11、Offline 和online除开system表空间外，所有表空间都可以offlineAlter tablespace users offlinet normal|temporary|immediate|for recoverNormal : checkpoint for all datafiles of the tablespaceTempora

39、ry  如果数据文件有错，可以使用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 可同时删除表空间的内容和数据文件。&#

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                              所有数据文件的信息，则重动态信息     

42、60;                         V$TEMPFILE                       

43、0;      所有临时文件的信息，则重动态信息DBA_DATA_FILES                         所有数据文件的信息，主要是静态信息            

44、60;               DBA_TEMP_FILES                        所有临时文件的信息，主要是静态信息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、 用户使用的临时表空间排序区  十、临时表空间与临时表一个实例必须有一个缺省的临时表空间，每个用户，又可指定其自己的临时表空间。临时表使用 create global temporary table来建立，表保存在临时表空间中，在企业管理器中看临时表的属性，它所属的表空间是不正确的（只是显示名称字母排序最小的表空间名罢了）。临时表对所有session可见，但数据是各自独立的。有两种session，一种是表的数据在事务结束后自动删除，一种是在session结束后再删除，在创建临时表时通过ON COMMIT DELETE|PRESERVE ROWS字句来指定。 十一、

50、undo space 、Undo record 的作用：（1）rollback语句执行时，undo records 用来undo change 未提提交的事务；（2）数据库recovery时用来undo未从redo写到数据文件的未提交数据。（3）提供一致性读，即在某用户修改某个表的同时，其他用户仍然可以读取表原来的数据。 、两种回滚的管理方式（1）auto（2）manual通过设置参数UNDO_MANAGEMENTUndo_management =autoUndo_tablespace  undo表空间Undo_RETENTION  undo记录保存时间

51、，以秒为单位Undo_SUPPRESS_ERRORS 如果为真，真在自动管理模式下使用undo管理sql语句产生的错误将被抑制(suppress) 、undo表空间可以在数据库创建的时候使用undo tablespace 子句来创建，如果不指定，则系统会自动建立一个undo表空间。也可以在数据库可用时候通过create undo tablespace来创建 、切换undo表空间Alter system set undo_TABLESPACE = undotbs_02; 、Flashback 查询（10g)Undo space使Oracle可以使用flashsbac

52、k查询来查询之前的数据内容。Dbms_flashback 、手工管理undo Undo_management = manualRollback_segments 实例启动时获得的的回滚段Transactions   最大的并发事务数量Transactions_per_rollback_segment 每个回滚段可以同时处理的事务数量Max_roolback_segments  一个实例能同时在线的最大的回滚段数量  【10g】在AUM（自动undo管理）模式下，UNDO_RETENTION参数用以控制事务提交以后undo信息

53、保留的时间。该参数以秒为单位，9iR1初始值为900秒，在Oracle9iR2增加为10800秒。但是这是一个NO Guaranteed的限制。也就是说，如果有其他事务需要回滚空间，而空间出现不足时，这些信息仍然会被覆盖。很多时候这是不希望看到的。从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                        &