Oracle基础知识学习笔记(自己整理的)

基本概念：数据：描述事物的符号；数据库：数据存放的地方，数据库是由数据和数据库对象组成的；数据库管理系统(DBMS)：用于管理数据的计算机软件，使用户能方便的定义和操纵数据，维护数据的安全性和完整性，以及进行多用户下的并发控制和数据库恢复。关系数据库(RDB)：基于关系模型的数据库。ORACLE数据库的体系结构是由一个实例和若干个数据库文件组成。SQL语句分为：数据定义语句(DDL):create、drop、alter、truncate（不能回滚）数据操作语句(DML):insert、select、update、delete、merge数据控制语句(DCL):grant、revoke事物控制语句:commit、rollback、savepointORACLE数据库的核心组件：数据字典（DD）动态性能表（DPT）触发器PL/SQL包用户与角色：角色是一些权限的集合，分为dba、connect、resource、exp_full_database、imp_full_database。用户分为sys、system。用户权限包括两种：是指执行某一SQL语句或访问另一用户对象的权限。它分为系统权限和对象权限。(Dba_roles/dba_roles_privs/dba_sys_privs/dba_tab_privs/dba_col_privs/role_role_privs/role_sys_privs/role_tab_privs)创建用户:Createuserusernameidentifiedbypassword;修改用户:Alteruserusernameidentifiedbynewpassword;创建角色:Createrolerolenameidentifiedbypassword;修改角色:Alterrolerolenameidentifiedbynewpassword;ORACLE数据库的物理结构：1.数据文件：用于存放所有的数据库数据，若干个数据文件组成表空间；它包括系统数据（数据字典）、用户数据（表、索引、簇等）、undo数据等。当建立数据库或表空间时，数据文件是通过DATAFILE选项制定的。一个或多个数据文件在物理上组成一个表空间。（v$datafile、v$datafile_header）2.控制文件：每个数据库必须至少得有两个控制文件，以组为单位。Oracle系统通过控制文件保持数据库的完整性以及决定恢复数据时使用哪些重做日志。Oracle通过控制文件将实例与数据库连接在一起。当装载数据库时（mount），Oracle根据初始化参数control_files定位控制文件；当打开数据库时，Oracle会根据控制文件记载的信息，打开所有数据文件和日志文件。（v$controlfile）3.重做日志文件：用于记录数据库的活动，记录数据库的所有修改操作；日志组：在单实例环境中，只有一个重做线程；在RAC（realapplicationcluster）环境中，包含多个重做线程。每个重做线程至少包含两个日志组。(v$log、v$logfile)4.归档日志文件：当数据库处于archivelog状态时，Oracle将自动归档重做日志文件。(v$archived_log)5.参数文件：定义启动例程的初始化参数，包含了实例和数据库配置参数列表，包括文本参数文件（PFILE）和服务器参数文件（SPFILE），PFILE的名称格式为initSID.ora，SPFILE的名称格式为spfileSID.ora，SID是例程名。6.口令文件：验证特权用户，特权用户是指具有sysdba或sysoper权限的特殊数据库用户。（v$pwfile_users）7.报警和跟踪日志文件；8.备份文件。ORACLE数据库的逻辑结构：1.表空间（tablespace）：是数据库的逻辑划分，每个数据库至少有一个表空间，一个表空间只能属于一个数据库。system表空间必须在MOUNT状态下恢复，而其它的数据表空间可以在OPEN状态下恢复，临时表空间不需要恢复。2.段（segment）：由一组范围组成，分为数据段、索引段、回滚段和临时段。数据段：对于每个非聚集的表有一数据段，表的所有数据存放在该段里。每一个聚集有一个数据段。索引段：每一个索引有一个索引段，存储索引数据。回滚段：由DBA建立，用于存储操作之前的数据影像(备份)。查看是否有回滚段竞争：select*fromv$waitstat；临时段：当一个SQL语句需要临时工作区时，由ORACLE建立。当语句执行完毕，临时段的范围退回给系统。3.盘区（extent）：由连续数据块组成；4.ORACLE数据块（block）：是ORACLE管理数据文件中存储空间的最小逻辑单位。数据块的物理结构：块头(header)：包含块的信息，即什么类型的段数据存储在块中，什么段在块中有数据，块地址以及指向存储在其中的实际行的指针;数据存储区(data)：是块实际存储数据的地方;自由空间区(freespace)：保留区域，用于存储有关在块中的行即将要更新的信息。PCTFREE和PCTUSED：PCTFREE规定了在块中保留的用于更新操作所需的自由空间的百分比。PCTUSED规定了最小的已使用空间的百分比。已使用空间百分比低于PCTUSED时，块被加入到自由列表中。自由列表中的块可以insert操作。PCTFREE和PCTUSED是段的两个参数。当ORACLE向数据库中写信息时，必须首先在一个段的分配区中找到一个或更多块来存储信息。ORACLE保留了块的一个列表，这些块对每个段来说都是自由的，称为自由列表。ORACLE使用PCTFREE和PCTUSED参数的组合确定块何时有、何时没有足够的空间接受新信息。pctused:如果数据块的使用率小于pctused的值，则该数据块重新加入到freelist中。Pctfree:如果数据块的使用率高于pctfree的值，则该数据块从freelist中移出。方案和方案对象（schema）方案是方案对象的集合；方案对象是直接引用数据库数据的逻辑结构，包括表、视图、索引、聚集、序列、同义词、数据库链、过程和包等。表（table）：数据库数据存储的基本单位。视图（view）：一个视图是由一个或者多个表中的数据的一种定制的表示，是一个查询定义，可以认为是一个存储的查询或是一个虚表。视图可以被查询，但修改、插入或者删除时具有一定的限制，受到基表的完整性约束和触发器的限制。索引（index）：索引是为了提高数据检索的性能而建立的，利用它可快速的确定指定的信息。索引分为：B-TreeIndex（最常用）、全局或局部索引、位图索引（BitmapIndex）、函数索引、反向键索引、键压缩索引、分区索引。索引通常能提高select/update/delete的性能,会降低insert的速度。B-TreeIndex：B树索引是一个典型的树结构。其包含的组件主要是：叶子节点（Leafnode）：包含条目直接指向表里的数据行；分支节点（Branchnode）：包含的条目指向索引里其他的分支节点或者是叶子节点；根节点（Rootnode）：一个B树索引只有一个根节点，它实际就是位于树的最顶端的分支节点。按照存储结构的不同可分为簇索引（clusteredindex）和非簇索引（nonclusteredindex）；簇索引：对表的物理数据页中的数据按列排序，再重新存储在磁盘上。簇索引查找数据很快，但占磁盘的空间也很大。非簇索引：将行定位器按关键字的值用一定的方式排序，与表的行在数据页的排序是不匹配的。一个表可以建立很多非簇索引，但只能建立一个簇索引。创建索引的语句：create[unique][clustered|nonclustered]indexindex_nameon{table|view}(column[asc|desc])[with[pad_index],[[fillfactor=fillfactor],[ignore_dup_key],[drop_existing],[statistics_norecompute],[sort_in_tempdb]][onfilegroup]其中Unique：创建唯一索引，及索引的键值不重复。Clustered：簇索引；Pad_index：指定填充索引的内部节点的行数，至少应大于等于两行；Fillfactor：填充因子，指定创建索引时，每个索引页的数据占索引页大小的百分比；Ignore_dup_key:控制了当向包含于一个唯一约束中的列中插入重复数据时数据库所做的反应；Drop_existing:指定要删除并重新创建簇索引。删除簇索引会导致所有的非簇索引被重建；Statistics_norecompute：指定分布统计不自动更新；Sort_in_tempdb:指定用于创建索引的分类排序结果将被存储到tempdb数据库中；Onfilegroup:指定存放索引的文件组。ORACLE数据字典（DD）数据字典的结构是基表和视图，记录了数据库的系统信息,其所有者为SYS。它由内部RDBMS（X$）表，数据字典表($)，动态性能视图(v$)和数据字典视图(all_,user_,dba_)组成。当执行DDL语句时，数据库修改数据字典。数据字典视图有三种：ALL_xxx，USER_xxx，DBA_xxx。（dba_tables、user_tables、all_tables、dba_users、dba_sys_privs（用户所具有的系统权限）、dba_tab_privs（用户所具有的对象权限）、dba_col_privs（用户所具有的列权限）、dba_role_privs（用户所具有的角色）、dba_jobs(sql执行计划)）。动态性能视图（DynamicPerformanceTables）是一组虚表（Virtualtables），记录数据库当前的活动情况，从内存和控制文件中获取信息，所有者为SYS，以v$开头，v$fixed_table(一览表)；（v$controlfile、v$database、v$datafile、v$instance、v$logfile、v$parameter、v$session、v$SGA、v$spparameter、v$tablespace、v$thread、v$version、v$db_cache_advice(buffercache命中率)）诊断IO、CPU性能状况的方法：查询并分析v$system_event/v$session_event(dbfilesequent)/v$session_wait(p1-p3的值.file_id/block_id/blocks)可查询Oracle系统的性能（包括CPU/IO/其他性能状况）；查询并分析v$sqlarea(disk_reads或buffer_gets/executions较大的SQL)。实例（instance）实例是用来访问数据库文件集的存储结构以及后台进程的集合，它是存取和控制数据库的软件机制。ORACLE实例分为单进程实例和多进程实例。实例是由内存结构和进程结构组成的；内存结构：基本的内存结构有系统全局区（SGA）和程序全局区（PGA）。系统全局区（SGA）：是系统为实例分配的一组共享缓冲存储区。它分为：数据高速缓存区（DatabaseBufferCache）：用来存储最近访问的数据块信息。由DB_CACHE_SIZE决定，它的大小由数据库实例init.ora中的DB_BLOCK_BUFFERS决定，ORACLE用LRU（最近最少算法）来管理可用空间。它分为脏列表、LRU（LeastRecentlyUsed）列表（空闲缓冲块、正在存取的缓冲块、已被修改但未被移到脏列表的缓冲块）SQL共享池（SharePool）：存放最近执行的SQL语句和数据字典信息。大小由SHARED_POOL_SIZE决定。分为字典缓存区（DatabaseDictionaryBufferCache）和库缓存区(共享S