Oracle复习材料.docx

Oracle复习材料.docx

《Oracle复习材料.docx》由会员分享，可在线阅读，更多相关《Oracle复习材料.docx（19页珍藏版）》请在冰点文库上搜索。

Oracle复习材料

数据库名:

是一个数据库的名称标识,对应的参数DB_NAME

全局数据库名:

由数据库名（DB_NAME）与数据库服务器所在的域名（DB_DOMAIN）组成,格式”数据库名.网络名”,用来唯一标识一个网络数据库,主要用于分布式数据库.

实例名:

是一个Oracle实例的唯一名称标识.

检查数据库服务器的安装结果

1.检查系统服务2.检查文件体系结构（ora92—主要存放Oracle9i数据库管理系统相关的软件.admin—主要存放系统运行的跟踪软件.oradata—主要存放数据库的物理文件）3.查看Oracle9i数据库服务器网络配置4.利用企业管理器,查看数据库信息.

创建数据库的2种方式：

用数据库配置助手（DBCA）创建和手动创建数据库。

数据库两种操作模式专用服务器模式（一个服务器进程只对一个用户进程提供服务）和共享服务器模式.

数据库服务器初始化参数文件两种：

文本初始化参数文件和服务器初始化参数文件

区别以及如何互相转换:

执行数据库启动时系统查找初始化参数文件的顺序是怎样的？

修改服务器参数文件中的参数的语法是怎样的？

SQL>createspfilefrompfile=’D:

\oracle\admin\orcl\pfile\init.ora’;

SQL>createspfile=’D:

\oracle\ora92\database\spfilemynewdb.ora’frompfile=’D:

\oracle\admin\orcl\pfile\init.ora’;

Scope=spfile,scope=memory,scope=both有啥区别:

Scope=spfile:

对静态和动态都适用,修改后的参数在下一次数据库启动时生效.scope=memory:

只适合动态参数的修改,修改后立即生效,修改结果不保存,下一次用—重配.scope=both:

只适合动态的修改,修改后立即生效,保存修改的结果,下一次启动仍可用.

常用命令：

connect：

先断开当前连接，然后建立新的连接。

CONN[ECT][username]/[password][@hoststring]。

Disc：

断开与数据库的连接。

Password：

用于修改用户口令

文件操作命令：

save：

将SQL缓冲区的内容保存到一个SQL脚本文件中。

Get：

将SQL脚本文件装载到SQL*Plus的SQL缓冲区中进行编辑。

Start、@：

执行SQL脚本文件。

交互式命令：

替换变量：

如果替换变量为数值列提供数据，则可以直接引用；如果替换变量为字符类型或日期类型列提供数据，则需要在SQL语句中将替换变量用单引号引起来。

显示与设置环境变量：

showall：

显示所有环境变量。

Autocommit：

设置是否自动提交DML语句。

Linesize：

设置行长度。

Serveroutput：

用于设置是否执行DBMS_OUTPUT.PUT_LLINE命令的输出结果。

Time：

设置是否在SQL*Plus命令提示符之前显示时间。

Timing：

设置是否显示SQL语句的执行时间。

其他常用命令：

desc：

显示任何表的结构信息。

Spool：

将屏幕内容存放到文本文件中。

clearscreeen：

清屏。

Help：

查看SQL*Plus命令的帮助信息。

格式化查询结果：

column：

用于格式化列的输出。

Format：

制定列的显示格式。

数据库的存储结构分为：

物理存储结构和逻辑存储结构两种。

物理存储结构包括：

数据文件：

必须的文件--用于存储数据库中所有数据；控制文件：

必须的文件--用于记录和描述数据库的物理存储结构信息；重做日志文件：

必须的文件--用于记录外部程序（用户）对数据库的修改操作；初始化参数文件：

必须的文件--用于设置数据库启动时参数初始值；跟踪文件：

用于记录用户进程、数据库后台进程的运行情况；归档文件：

用于保存已经写满的重做日志文件；口令文件：

用于保存具有SYSDBA，SYSOPER权限的用户名和SYS用户口令。

数据文件与表空间的关系：

一个表空间可以包含几个数据文件，但一个数据文件只能对应一个表空间。

数据文件的管理：

创建数据文件：

语法ALTERTABLESPACE…ADDDATAFILE或者ALTERTABLESPACE…ADDTEMPFILE。

向ORCL数据库的USERS表空间中添加一个大小为10MB的数据文件。

ALTERTABLESPACEUSERSADDDATAFILE'D:

\Oracle\oradata\orcl\userdata02.dbf'SIZE10M;向ORCL数据库的TEMP表空间添加一个大小为5MB的临时数据文件。

ALTERTABLESPACETEMPADDTEMPFILE'D:

\Oracle\oradata\orcl\temp02.dbf‘SIZE5M;

修改数据文件的大小：

为ORCL数据库的USERS表空间添加一个自动增长的数据文件。

ALTERTABLESPACEUSERSADDDATAFILE'D:

\Oracle\oradata\orcl\userdata03.dbf'SIZE10MAUTOEXTENDONNEXT512KMAXSIZE250M;修改ORCL数据库USERS表空间的数据文件userdata02.dbf为自动增长。

ALTERDATABASEDATAFILE'D:

\Oracle\oradata\orcl\userdata02.dbf'AUTOEXTENDONNEXT512KMAXSIZEUNLIMITED;取消ORCL数据库USERS表空间的数据文件userdata02.dbf的自动增长。

ALTERDATABASEDATAFILE'D:

\Oracle\oradata\orcl\userdata02.dbf'AUTOEXTENDOFF;将ORCL数据库USERS表空间的数据文件userdata02.dbf大小设置为8MB。

ALTERDATABASEDATAFILE'D:

\Oracle\oradata\orcl\userdata02.dbf'RESIZE8M

每一个数据库必须至少有一个控制文件。

控制文件备份有两种方法：

1.将控制文件备份为二进制文件。

2.将控制文件备份为文本文件。

利用重做日志文件恢复数据库是通过事物的重做（REDO）或回退（UNDO）实现的。

每个数据库至少有两个重做日志文件。

逻辑存储结构包括：

表空间：

一个表空间对应一个或多个数据文件，数据文件大小决定了表空间的大小。

一个数据文件只能从属于一个表空间。

段：

是由一个或多个扩展区组成的逻辑存储单元。

区：

由一组连续的数据块构成，是存储分配的最小单位。

数据块：

最小的I/O单元。

关系：

数据块n-1区n-1段n-1表空间n-1数据库

表空间分为系统表空间和非系统表空间两类，其中非系统表空间分为：

撤销表空间、临时表空间和用户表空间。

用户数据不能放在撤销表空间和临时表空间。

表空间的管理方式：

字典管理方式和本地管理方式，9i默认的管理方式为本地管理方式。

创建表永久表空间，区定制分配：

SQL>createtablespaceorcltbs2datafile‘D:

\oracle\oradata\orcl\orcltbs2-1.dbf’size50Mextentmanagementlocaluniformsize512k;段采用自动管理方式:

SQL>createtablespaceorcltbs3datafile‘D:

\oracle\oradata\orcl\orcltbs3-1.dbf’size50Msegmentspacemanagementauto;

创建临时表空间:

SQL>createtemporarytablespaceorcltemp1datafile‘D:

\oracle\oradata\orcl\orcltemp1-1.dbf’size20Mextentmanagementlocaluniformsize16M;

如何查找所有日志文件信息：

如何查找所有数据文件信息：

如何查找所有控制文件信息：

创建撤销表空间：

可以使用CREATEUNDOTABLESPACE语句创建撤销表空间，但是在该语句中只能指定DATAFILE和EXTENTMANAGEMENTLOCAL两个子句，而不能指定其他子句。

为表空间添加数据文件：

可以通过ALTERTABLESPACE…ADDDATAFILE语句为永久表空间添加数据文件，通过ALTERTABLESPACE…ADDTEMPFILE语句为临时表空间添加数据文件。

将表空间设为只读/只写状态ALTERTABLESPACEtbs_nameREADONLY|READWRITE。

查询表空间数据文件信息：

SELECTFILE_NAME,BLOCKS,TABLESPACE_NAMEFROMDBA_DATA_FILES;

行链接：

当向表格中插入数据时，如果行的长度大于块的大小，行的信息无法存放在一个块中，需要使用多个块存放行信息，称为行链接。

行迁移：

当表格数据被更新时，如果更新后的数据长度大于块长度，Oracle将整行的数据从原数据块迁移到新的数据块中，只在原数据块中留下一个指针指向新数据块，称为行迁移。

Pctfree：

PCTFREE参数指定块中必须保留的最小空闲空间比例。

当数据块的自由空间百分率低于PCTFREE时，此数据块被标志为USED，此时在数据块中只可以进行更新操作，而不可以进行插入操作。

该参数默认为10。

Pctused：

PCTUSED参数指定可以向块中插入数据时块已使用的最大空间比列。

当数据块使用空间低于PCTUSED时，此块标志为FREE，可以对数据块中数据进行插入操作；反之，如果使用空间高于PCTUSED，则不可以进行插入操作。

该参数默认为10。

区空间的分配方式：

定制分配和自动分配

段分为：

数据段、索引段、临时段和回退段4类。

数据段又分为：

表数据段、索引表数据段、分区表数据段和簇数据段四种。

实例由内存结构和后台进程组成。

内存结构分为：

系统全局区（SGA）和程序全局区（PGA）.

SGA由数据高速缓存区（必须）、共享池（必须）、重做日志缓冲区（必须）、大型池（可选）、java池（可选）和其他结构组成。

（理解各组成部分的功能）

数据高速缓存区功能：

1.数据缓冲区存储的是从数据文件中检索出来的数据拷贝。

应用程序要访问的数据必须从磁盘的数据文件读到数据缓冲区中处理。

2.在数据缓冲区中被修改后的数据由数据写入进程写到硬盘的数据文件中永久保存。

3.提高获取和更新数据的性能

重做日志缓冲区功能：

用于缓存在用户对数据进行修改的操作过程中生成的重做记录。

当重做日志缓冲区被添满时，由日志写入进程把重做日志缓冲区的内容写到磁盘的重做日志文件中做永久的保存。

共享池功能：

用于缓存与SQL或PL/SQL语句、数据字典、资源锁以及其他控制结构相关的数据

大型池功能：

大型池是一个可选的内存配置项，主要为Oracle多线程服务器、服务器I/O进程、数据库备份与恢复操作、执行具有大量排序操作的SQL语句、执行并行化的数据库操作等需要大量缓存的操作提供内存空间。

如果没有在SGA区中创建大型池，上述操作所需要的缓存空间将在共享池或PGA中分配，因而影响共享池或PGA的使用效率。

JAVa池功能：

Java提供对Java程序设计的支持，用于存储Java代码、Java语句的语法分析表、Java语句的执行方案和进行Java程序开发。

数据高速缓冲区的工作过程：

重做日志缓冲区的工作过程：

共享池的组成部分：

库缓存和数据字典缓存区

后台进程：

主要后台进程包括：

DBWR、LGWR、CKPT、SMON、PMON、ARCH、RECO、LCKn和Dnnn等，前5个进程是必需的。

各后台进程的功能：

DBWR（数据库写入进程）功能：

数据库写入进程负责把数据高速缓冲区中已经被修改过的数据（“脏”缓存块）成批写入数据文件中永久保存，同时使数据高速缓冲区有更多的空闲缓存块，保证服务器进程将所需要的数据从数据文件读取到数据高速缓冲区中，提高缓存命中率。

LGWR（日志写入进程）功能：

1.日志写入进程负责把重做日志缓冲区的数据写入重做日志文件中永久保存。

2.数据写入进程在工作之前，需要了解日志写入进程是否已经把相关的日志缓冲区中记载的数据写入硬盘中，如果相关的日志缓冲区中的记录还没有被写入，DBWR会通知LGWR完成相应的工作，然后DBWR才开始写入。

CKPT（检查点进程）功能：

执行检查点，更新控制文件与数据文件的头部，使其同步触发DBWR进程，将脏缓存块写入数据文件。

SMON（系统监控进程）功能：

1.在实例启动时负责对数据库进行恢复；2.回收不再使用的临时空间。

3.将各个表空间的空闲碎片合并（表空间的存储参数PCTINCREASE不为0时）。

PMON（进程监控进程）功能：

1.负责恢复失败的用户进程或服务器进程，并且释放进程所占用的资源；2.清除非正常中断的用户进程留下的孤儿会话，回退未提交的事务，释放会话所占用的锁、SGA、PGA等资源；3.监控调度进程和服务器进程的状态，如果它们失败，则尝试重新启动它们，并释放它们所占用的各种资源。

ARCH（归档进程）功能：

归档进程负责在日志切换后将已经写满的重做日志文件复制到归档目标中，防止写满的重做日志文件被覆盖。

RECO（恢复进程）功能：

RECO进程负责在分布式数据库环境中自动解决分布式事务的故障。

一个节点的RECO自动解决所有的悬而未决的事务。

当一个数据库服务器的RECO后台进程试图建立同一远程服务器的通信，如果该远程服务器不可用或者网络连接不能建立时，RECO自动地在一个时间间隔之后再次连接。

当且仅当数据库配置为分布式事务处理，且初始化参数。

LCKn（锁进程）功能：

LCKn进程用于Oracle并行服务器环境中。

在数据库中最多可以启动10个LCKn进程，主要用于实例间的封锁。

Dnnn（调度进程）功能：

Dnnn进程是多线程服务器（MultithreadedServer，MTS）的组成部分，以后台进程的形式运行。

调度进程接受用户进程请求，将它们放入请求队列中，然后为请求队列中的用户进程分配一个服务器进程。

最后，从响应队列返回数据给用户进程。

数据库的启动步骤：

1.创建并启动实例（初始化参数文件）状态：

NOMOUNT2.装载数据库（控制文件）状态：

MOUNT3.打开数据库（数据文件与重做日志文件）状态：

OPEN

数据库的关闭步骤:

1.关闭数据库（关闭数据文件与重做日志文件）2.卸载数据库（关闭控制文件）3.关闭实例（释放内存、关闭后台进程）。

Startup[nomount][mount][open][force][restrict][pfile=filename]各参数的含义和作用。

STARTUPNOMOUNT：

创建一个新的数据库，重建数据库的控制文件。

STARTUPMOUNT：

重命名数据文件，添加、删除或重命名重做日志文件，改变数据库的归档模式，执行数据库完全恢复操作。

STARTUPRESTRICT：

执行数据库数据的导出或导入操作，执行数据装载操作，暂时阻止普通用户连接数据库，进行数据库移植或升级操作。

数据库各状态间的切换：

启动模式间转换1.STARTUPNOMOUNT;2.ALTERDATABASEMOUNT;3.ALTERDATABASEOPEN;读写状态转换1.ALTERDATABASEOPENREADWRITE;2.ALTERDATABASEOPENREADONLY;受限/非受限状态转换1.ALTERSYSTEMENABLERESTRICTEDSESSION;2.ALTERSYSTEMDISABLERESTRICTEDSESSIOIN;

一、表约束分为：

主键约束、唯一性约束、检查约束、外键约束和空/非空约束。

1、PRIMARYKEY定义主键，起惟一标识作用，其值不能为NULL，也不能重复；一个表中只能定义一个主键约束；建立主键约束的同时，在该列上建立一个惟一性索引，可以为它指定存储位置和存储参数；主键约束可以是列级约束，也可以是表级约束。

2、UNIQUE

（1）定义为惟一性约束的某一列或多个列的组合的取值必须惟一；

（2）如果某一列或多个列仅定义惟一性约束，而没有定义非空约束，则该约束列可以包含多个空值；（3）Oracle自动在惟一性约束列上建立一个惟一性索引，可以为它指定存储位置和存储参数；惟一性约束可以是列级约束，也可以是表级约束。

3、PRIMARYKEY与UNIQUE比较

（1）在一个基本表中只能定义一个PRIMARYKEY约束，但可定义多个UNIQUE约束；

（2）对于指定为PRIMARYKEY的一个列或多个列的组合，其中任何一个列都不能出现空值，而对于UNIQUE所约束的唯一键，则允许为空。

（3）不能为同一个列或一组列既定义UNIQUE约束，又定义PRIMARYKEY约束。

4、CHECK：

特点：

1.检查约束用来限制列值所允许的取值范围，其表达式中必须引用相应列，并且表达式的计算结果必须是一个布尔值；2.约束表达式中不能包含子查询，也不能包含SYSDATE、USER等SQL函数，和ROWID、ROWNUM等伪列；3.一个列可以定义多个检查约束；4.检查约束可以是列级约束，也可以是表级约束。

5、FOREIGNKEY概念：

FOREIGNKEY约束指定某一个列或一组列作为外部键，其中，包含外部键的表称为从表，包含外部键所引用的主键或唯一键的表称主表。

系统保证从表在外部键上的取值要么是主表中某一个主键值或唯一键值，要么取空值。

以此保证两个表之间的连接，确保了实体的参照完整性。

特点：

1.定义外键约束的列的取值要么是主表参照列的值，要么为空；2.外键列只能参照于主表中的主键约束列或惟一性约束列；3.可以在一列或多列组合上定义外键约束；4.外键约束可以是列级约束，也可以是表级约束。

6、NULL/NOTNULL特点：

1.在同一个表中可以定义多个NOTNULL约束；2.只能是列级约束。

定义约束的方法有两种：

列级约束和表级约束：

列约束是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔,不必指定列名；表约束与列定义相互独立，不包括在列定义中。

通常用于对多个列一起进行约束，与列定义用’,’分隔。

定义表约束时必须指出要约束的那些列的名称。

二、掌握建表的sql语句

1、创建一个表：

CREATETABLEplayer（IDNUMBER（6）,snoNUMBER（6）,snameVARCHAR2（10）,sageNUMBER（6,2）,resumeVARCHAR2（1000））;

表参数设置：

TABLESPACE：

TABLESPACE子句用于指定表存储的表空间。

STORAGE：

STORAGE子句用于设置表的存储参数。

若不指定，则继承表空间的存储参数设置。

NITIAL；NEXT；PCTINCREASE；MINEXTENTS；MAXEXTENTS；BUFFER_POOL（KEEP、RECYCLE、DEFAULT）

数据块管理参数：

1.PCTFREE：

用于指定数据块中必须保留的最小空闲空间。

2.PCTUSED：

用于指定当数据块空闲空间达到PCTFREE参数的限制后，数据块能够被再次使用前，已占用的存储空间必须低于的比例。

3.INITRANS：

用于指定能够并发访问同一个数据块的事务的数量。

4.MAXTRANS：

用于指定能够并发访问同一个数据块的事务的最大数量。

基本语法：

ALTERTABLE<表名>

ADD<新列名><数据类型>[<完整性约束定义]

MODIFY<列名><数据类型>

RENAMECOLUMNoldnameTOnewname

SETUNUSEDCOLUMNcolumn//singlecolumn

SETUNUSEDCOLUMNS（column1，column2…）

DROPCOLUMN//singlecolumn

DROP//multicolumn

DROPUNUSEDCOLUMNS

列的添加、删除、修改

ALTERTABLEemployee

ADD（phoneVARCHAR2（10）,hiredateDATEDEFAULTSYSDATENOTNULL）;

添加主键约束：

ALTERTABLEplayerADDCONSTRAINTP_PKPRIMARYKEY（ID）;

添加惟一性约束：

ALTERTABLEplayerADDCONSTRAINTP_UKUNIQUE（sname）;

添加检查约束：

ALTERTABLEplayerADDCONSTRAINTP_CKCHECK（sageBETWEEN20AND30）;

添加外键约束：

ALTERTABLEplayerADDCONSTRAINTP_FKFOREIGNKEY（sno）REFERENCESstudent（sno）ONDELETECASCADE;

添加空/非空约束：

注意:

为表列添加空/非空约束时必须使用MODIFY子句代替ADD子句。

ALTERTABLEplayerMODIFYresumeNOTNULL;ALTERTABLEplayerMODIFYresumeNULL;

表参数修改

可以对表的参数进行修改，包括存储参数、存储位置、数据块设置等。

ALTERTABLEemployee

PCTFREE30PCTUSED60STORAGE（NEXT512KPCTINCREASE10）;

注意：

表创建后不能对INITIAL、MINEXTENTS两个参数进行修改

表结构重组：

将一个非分区的表移动到一个新的数据段中，或者移动到其他的表空间中，通过这种操作可以重建表的存储结构，称为表结构重组。

语法：

ALTERTABLEtbnameMOVE[TABLESPACEtbs_name]注意：

直到表被完全移动到新的数据段中之后，Oracle才会删除原来的数据段；表结构重组后，表中每个记录的ROWID会发生变化，因此该表的所有索引失效，需要重新建立索引；如果表中包含LOB列，则默认情况下不移动LOB列数据和LOB索引段

表重命名等：

语法：

ALTERTABLEtable_old_nameRENAMETOtable_new_name;RENAMEtable_old_nameTOtable_new_name;说明：

Oracle自动更新相应的约束、索引和与此表相关的权限；同时以此表为参考的视图、同义词、存储过程和函数为非法。

为表和列添加注释：

为表添加注释：

语法：

COMMENTONTALBEtable_nameIS…；为列添加注释：

语法：

COMMENTONCOLUMNtable_name.column_nameIS…注释可以通过以下数据字典来查看.ALL_COL_COMMENTS；USER_COL_COMMENTS；ALL_TAB_COMMENTS；USER_TAB_COMMENTS；

索引的作用：

索引是为了加速对表中元组的检索而创建的一种分散存储结构；是对表而建立的，由除存放表的数据页面以外的索引页面组成，独立于被索引的表；通过使用索引加速行的检索，但减慢更新的速度；快速定位数据，减少磁盘I/O；Oracle自动使用、维护索引。

索引的分类：

1