Oracle数据库期末复习

从实例与数据库之间的辩证关系来讲，实例用于管理和控制数据库；而数据库为实例提供数据。一个数据库可以被多个实例装载和打开；而一个实例在其生存期内只能装载和打开一个数据库。

重做日志文件组信息：V$log。

重做日志文件信息：V$logfile。

pfile位于<<基目录>>\admin\orcl\pfile下，spfile位于<<主目录>>\database下

表空间是Oracle数据库最大的逻辑容器 一个表空间包含一个或多个数据文件；一个数据文件只能属于一个表空间。一个表空间包含一个或多个模式对象。

数据库容量在物理上由数据文件大小与数量决定的，

在逻辑上由表空间大小与数量决定的。

初始化参数文件、控制文件、重做日志文件最好不要与数据文件存放在同一个磁盘上，以免数据库发生介质故障时，无法恢复数据库。

表空间的类型包括普通表空间、临时表空间和撤销表空间；

表空间中区的分配方式包括两种方式：自动扩展（AUTOALLOCATE）和定制（UNIFORM）；

段的管理包括两种方式：自动管理（AUTO）和手动管理（MANUAL）。

EXTENT MANAGEMENT：指定表空间的管理方式，取值为LOCAL（默认）或DICTIONARY。

AUTOALLOCATE（默认）或UNIFORM：设定区的分配方式。

SEGMENT SPACE MANAGEMENT：设定段的管理方式，其取值为MANUAL或AUTO （默认） 。

数据文件可以自动扩展：AUTOEXTEND ON NEXT 5M MAXSIZE 50M;

AUTOEXTEND ON设置数据文件可以自动扩展，NEXT指定每次扩展空间大小，MAXSIZE指定文件最大的存储空间，如果不受限制，可以设置为UNLIMITED。

数据文件分为永久性数据文件和临时数据文件两种

一个大文件表空间只包含一个数据文件，该数据文件可以包含4G（2**32）个数据块。

通过查询数据字典DATABASE_PROPERTIES中的PROPERTY_NAME字段值为'DEFAULT_TBS_TYPE'的PROPERTY_VALUE值，可以知道数据库默认创建的是小文件表空间还是大文件表空间。

例：创建一个大文件表空间，文件大小为1G，区的分配采用定制方式。

SQL>CREATE BIGFILE TABLESPACE big_tbs DATAFILE 'D:\APP\ORACLE\ORADATA\ORCL\big01.dbf' SIZE 1G UNIFORM SIZE 512K;

需要注意的是，大文件表空间中段的管理只能采用自动管理方式，而不能采用手动管理方式。

数据文件依附于表空间而存在，创建数据文件就是向表空间添加文件

在创建数据文件时应该根据文件数据量的大小确定文件的大小以及文件的增长方式。

语法

ALTER TABLESPACE…ADD DATAFILE

改变数据文件的扩展性

SQL>ALTER DATABASE DATAFILE 'D:\APP\ORACLE\ORADATA\ORCL\student01.dbf' AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

重新设置数据文件的大小

 SQL>ALTER DATABASE DATAFILE 'D:\APP\ORACLE\ORADATA\ORCL\index01.dbf‘ RESIZE 50M;

删除表空间

语法：

DROP TABLESPACE tablespace_name

如果表空间非空，应带有子句INCLUDING CONTENTS

若要删除操作系统下的数据文件，应带有子句AND DATAFILES

删除参照完整性约束，应带有子句CASCADE CONSTRAINTS

归档模式下数据文件可用性的改变

数据文件可用性的改变

ALTER DATABASE DATAFILE… ONLINE|OFFLINE

在归档模式下，将数据文件联机之前需要使用RECOVER DATAFILE对数据文件进行恢复

设置数据库的默认表空间

ALTER DATABASE DEFAULT TABLESPACE

非归档模式下数据文件可用性的改变

在非归档模式下，通常不能将数据文件脱机。

如果由于数据文件的损坏，需要将数据文件脱机，然后重新启动数据库，需要使用ALTER DATABASE DATAFILE…OFFLINE FOR DROP语句。例如：

SQL>ALTER DATABASE DATAFILE

'D:\APP\ORACLE\ORADATA\ORCL\USERS02.DBF'OFFLINE FOR DROP;

改变同一个表空间中的数据文件的名称或位置

ALTER TABLESPACLE…RENAME DATAFILE…TO

改变多个表空间中的数据文件的名称或位置

ALTER DATABASE RENAME FILE…TO

注意

改变数据文件的名称或位置时，Oracle只是改变记录在控制文件和数据字典中的数据文件信息，并没有改变操作系统中数据文件的名称和位置，因此需要DBA手动更改操作系统中数据文件的名称和位置。

临时表空间的区只能采用统一定制分配方式。

大文件表空间中段的管理只能采用自动管理方式，而不能采用手动管理方式。

系统默认表空间是给没有指定临时表空间的用户使用的。

SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_group;

撤销表空间创建不能使用DEFAULT STORAGE子句

UNDO_MANAGEMENT参数指定撤销表空间的管理方式

UNDO_TABLESPACE参数

该参数用于设置系统使用的撤销表空间。

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOtbs1;

如果不设置该参数,Oracle默认自动选择第一个可用的撤销表空间存放撤销数据。

UNDO_RETENTION参数

该参数用于指定保留撤销数据的时间，以便提供读一致性，默认值为900s。

多路复用控制文件是保护控制文件的最好方法。

数据字典视图

V$LOG：包含从控制文件中获取的所有重做日志文件组的基本信息。

V$LOGFILE：包含重做日志文件组及其成员文件的信息。

V$ARCHIVED_LOG：重做日志文件归档信息。

V$ARCHIVE_DEST：查看归档目标设置。

Archive log list：该命令可以显示归档模式信息

表空间的可用性是指表空间脱机或联机操作。

除了SYSTEM表空间、存放在线回退信息的撤销表空间和临时表空间不可以脱机外，其它的表空间都可以设置为脱机状态。

将某个表空间设置为脱机状态时，属于该表空间的所有数据文件都处于脱机状态。

可以使用
ALTER TABLESPACE…OFFLINE/ONLINE 将表空间脱机/联机。

改变同一个表空间中的数据文件的名称或位置

ALTER TABLESPACLE…RENAME DATAFILE…TO

改变多个表空间中的数据文件的名称或位置

ALTER DATABASE RENAME FILE…TO

V$TABLESPACE：从控制文件中获取的表空间名称和编号信息。

DBA_TABLESPACES：数据库中所有表空间的信息。

DBA_TABLESPACE_GROUPS：表空间组及其包含的表空间信息。

DBA_SEGMENTS：所有表空间中段的信息。

V$DATAFILE：所有数据文件信息，包括所属表空间的名称和编号。

V$TEMPFILE：所有临时文件信息，包括所属表空间的名称和编号。

DBA_DATA_FILES：数据文件及其所属表空间信息。

DBA_TEMP_FILES：临时文件及其所属表空间信息。

DBA_USERS：所有用户的默认表空间和临时表空间信息。

DBA_TS_QUOTAS：所有用户的表空间配额信息。

删除数据文件或临时数据文件时受到以下约束：

数据库运行在打开状态

数据文件或临时数据文件必须是空的

不能删除表空间的第一个或唯一的一个数据文件或临时数据文件

不能删除只读表空间中的数据文件

不能删除SYSTEM表空间的数据文件

不能删除采用本地管理的处于脱机状态的数据文件。

定义列级FOREIGN KEY约束

[CONSTRAINT constraint_name]

[FOREIGN KEY]

REFERENCES   ref_table_name (column_name,…)

定义表级FOREIGN KEY约束

[CONSTRAINT constraint_name]

FOREIGN KEY (column_name,…)

REFERENCES   ref_table_name (column_name,…)

[ON DELETE CASCADE|SET NULL];

ON DELETE CASCADE

删除子表中所有相关记录

ON DELETE SET NULL

将子表中相关记录的外键约束列值设置为NULL

ON DELETE RESTRICTED

受限删除，即如果子表中有相关子记录存在，则不能删除主表中的父记录，默认引用方式。

DEFAULT

如果用户在插入新行时没有显示为列提供数据，系统将默认值赋给该列。

语法

[CONSTRAINT <约束名> ]  DEFAULT 表达式

禁用约束设置

在定义约束时，可以将约束设置为禁用状态，默认为激活状态。

也可以在约束创建后，修改约束状态为禁用。

创建表时禁用约束

CREATE TABLE S (SNO CHAR(11) PRIMARY KEY DISALBE,…);

以后利用ALTER TABLE…DISABLE禁用约束

ALTER TABLE STUDENT DISABLE CONSTRAINT S_CK1;

ALTER TABLE STUDENT DISABLE UNIQUE (sname);

禁用主键约束、惟一性约束时，会删除其对应的惟一性索引，而在重新激活时，Oracle为它们重建惟一性索引。若在禁用约束时，保留对应的惟一性索引，可使用                         ALTER TABLE…DISABLE…KEEP INDEX语句。

ALTER TABLE STUDENT DISABLE UNIQUE (sname) KEEP INDEX;

ALTER TABLE STUDENT DISABLE PRIMARY KEY KEEP INDEX;

若当前约束(主键约束、惟一性约束)列被引用，则需要使用ALTER TABLE…DISABLE…CASCADE语句同时禁用引用该约束的约束。

ALTER TABLE STUDENT DISABLE PRIMARY KEY KEEP INDEX CASCADE

激活约束设置

创建或添加约束时，默认为激活状态。

利用ALTER TABLE…ENABLE…语句激活约束

ALTER TABLE STUDENT ENABLE UNIQUE (sname);

禁用主键约束、惟一性约束时，会删除其对应的惟一性索引，而在重新激活时，Oracle为它们重建惟一性索引，可以为索引设置存储位置和存储参数(索引与表尽量分开存储)。

ALTER TABLE STUDENT ENABLE PRIMARY KEY

USING INDEX TABLESPACE indx STORAGE(INITIAL 32K   NEXT 16K);

默认情况下，在表中的约束都是不可延迟约束，Oracle在一条DML语句执行完毕之后立即进行约束检查（除非禁用）。

创建约束时可以显式使用DEFERRABLE关键字，创建可延迟的约束。

INITIALLY IMMEDIATE 或INITIALLY DEFERRED说明可延迟约束在初始状态下是立即检查还是延迟检查

如果在定义约束时设定为不可延迟，则约束创建后不能更改其可延迟性。只有创建时设定为可延迟的约束，创建后才能更改其可延迟性。

将new_emp表的外键约束检查延迟。

ALTER TABLE new_emp MODIFY CONSTRAINT NE_FK INITIALLY DEFERRED;

操作完后，应将new_emp外键约束检查恢复原来状态。

ALTER TABLE new_emp MODIFY CONSTRAINT NE_FK INITIALLY IMMEDIATE;

在修改约束的检查延迟性时，如果无法确定约束的名称或需要设置多个约束的延迟性，可以一次性将所有可延迟的约束延迟或恢复。（但是不能一次性设置延迟性）

SET CONSTRAINT ALL DEFERRED;

SET CONSTRAINT ALL IMMEDIATE;

查询表与约束信息

DBA_OBJECTS：查询对象（包括表）的编号等信息。

DBA_TABLES：查询表的基本信息。

DBA_TAB_COLUMNS：查询表中列的信息。

DBA_CONSTRAINTS：查询表中约束的基本信息。

DAB_CONS_COLUMNS：查询表中约束的作用列。

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

如果发现表的数据段具有不合理的区分配方式，但是又不能通过别的方法来进行调整（改变存储参数不会影响到已经分配的区），可以考虑将表移动到一个新的数据段中。

如果频繁地对表进行DML操作，会产生大量空间碎片和行迁移、行连接，可以考虑进行表结构重组。

语法

ALTER TABLE tbname MOVE [TABLESPACE tbs_name]

注意 

直到表被完全移动到新的数据段中之后，Oracle才会删除原来的数据段；

表结构重组后，表中每个记录的ROWID会发生变化，因此该表的所有索引失效，需要重新建立索引；

如果表中包含LOB列，则默认情况下不移动LOB列数据和LOB索引段。

语法格式为：ALTER TABLE table_name

ADD /MODIFY/DORP COLUMN column_name [column_def];

该命令可以为表添加列、修改列、删除列。

默认情况下，索引与表存储在同一表空间中。索引与表存储在同一表空间中，有利于数据库维护操作，具有较高的可用性；

若索引与表存储在不同的表空间中，则可提高系统的存取性能，减少硬盘I/O冲突，但是表与索引可用状态可能出现不一致，如分别处于联机状态和脱机状态。

重建索引的实质是在指定的表空间中重新建立一个新的索引，然后再删除原来的索引，这样不仅能够消除存储碎片，还可以改变索引的存储参数设置，并且将索引移动到其他的表空间中。

反转键索引不可以与位图索引或索引组织表结合使用，因为不能对位图索引和索引组织表进行反转键处理。

分区概念

所谓的分区是指将一个巨型表或巨型索引分成若干独立的组成部分进行数据存储和管理，每一个相对小的、可以独立管理的部分，称为原来表或索引的分区（对应一个单独的段）。

每个分区都具有相同的逻辑属性，但物理属性可以不同。如具有相同列、数据类型、约束等，但可以具有不同的存储参数、位于不同的表空间等。

分区后，表中每个记录或索引条目根据分区条件分散存储到不同分区中 。

分区类型分为：

范围分区：按照字段值的范围分区。

范围分区是按照分区列值的范围来对表进行分区的。

通过PARTITION BY RANGE子句说明根据范围进行分区，其后括号中列出分区列，可以进行多列分区。

每个分区以PARTITION 关键字开头，其后是分区名。

VALUES LESS THAN子句用于设置分区中分区列值的范围。可以对每个分区的存储进行设置，也可以对所有分区采用默认的存储设置。

列表分区：按照字段值相等进行分区。

如果分区列的值并不能划分范围（非数值类型或日期类型），同时分区列的取值范围只是一个包含少数值的集合，则可以对表进行列表分区（LIST），如按地区、性别等分区。

与范围分区不同，列表分区不支持多列分区，只能根据一个单独的列来进行分区。

通过PARTITION BY LIST子句说明根据列表进行分区，其后括号中列出分区列。每个分区以PARTITION 关键字开头，其后是分区名。VALUES子句用于设置分区所对应的分区列取值。

散列分区：使用HASH函数自动进行分区。

在进行范围分区或列表分区时，由于无法对各个分区中可能具有的记录数量进行预测，可能导致数据在各个分区中分布不均衡，某个分区中数据很多，而某个分区中数据很少。

可以采用散列分区（HASH）方法，在指定数量的分区中均等地分配数据。

创建散列分区，需要指定分区列、分区数量或单独的分区描述。

通过PARTITION BY HASH指定分区方法，其后的括号指定分区列。使用PARTITION子句指定每个分区名称和其存储空间。或者使用PARTITIONS子句指定分区数量，用STORE IN子句指定分区存储空间。

复合分区：范围分区与列表或散列分区的组合分区。

复合分区包括：

范围-列表复合分区

范围-散列复合分区。

创建复合分区时需要指定

分区方法

PARTITION BY RANGE

子分区方法

SUBPARTITION BY HASH或者SUBPARTITION BY LIST

每个分区中子分区数量或子分区的描述。

本地分区索引

本地分区索引是指为分区表中的各个分区单独建立索引分区，各个索引分区之间是相互独立的。

全局分区索引

全局分区索引是指先对整个分区表建立索引，然后再对索引进行分区。

全局非分区索引

全局非分区索引是指对整个分区表创建标准的未分区的索引

创建控制文件的基本步骤

1）制作数据库中所有的数据文件和重做日志文件列表

SQL>SELECT MEMBER FROM V$LOGFILE;

SQL>SELECT NAME FROM V$DATAFILE;

SQL>SELECT VALUE FROM V$PARAMETER WHERE NAME = 'CONTROL_FILES';

（2）如果数据库仍然处于运行状态，则关闭数据库

SQL>SHUTDOWN IMMEDIATE

（3）在操作系统级别备份所有的数据文件和联机重做日志文件(即手工复制粘贴)

（4）启动实例到NOMOUNT状态

SQL>STARTUP NOMOUNT

（5）利用前面得到的文件列表或备份出来的信息，执行CREATE CONTROLFILE创建一个新控制文件。

（6）在操作系统级别对新建的控制文件进行备份

（7）有必要就修改初始化参数CONTROL_FILE

（8）如果数据库需要恢复，则进行恢复数据库操作

（9）打开数据库

如果数据库不需要恢复或已经对数据库进行了完全恢复，则可以正常打开数据库。

SQL>ALTER DATABASE OPEN;

如果在创建控制文件时使用了RESETLOGS参数，则必须指定以RESETLOGS方式打开数据库。

SQL>ALTER DATABASE OPEN  RESETLOGS;

控制文件多路复用步骤

编辑初始化参数CONTROL_FILES。

SQL>ALTER SYSTEM SET CONTROL_FILES=     'D:\APP\ORACLE\ORADATA\ORCL\CONTROL01.CTL‘,   'D:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL‘,     'D:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\CONTROL03.CTL'

SCOPE=SPFILE;

关闭数据库

SQL>SHUTDOWN IMMEDIATE;

拷贝一个原有的控制文件到新的位置，并重新命名。

SQL>HOST COPY D:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL D:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\CONTROL03.CTL

重新启动数据库

SQL>STARTUP

将控制文件备份为文本文件

ALTER DATABASE BACKUP CONTROLFILE  TO TRACE

查询控制文件信息

• V$DATABASE
  • 从控制文件中获取的数据库信息；
• V$CONTROLFILE
  • 包含所有控制文件名称与状态信息；
• V$CONTROLFILE_RECORD_SECTION
  • 包含控制文件中各记录文档段信息；
• V$PARAMETER
  • 可以获取初始化参数CONTROL_FILES的值。

用户对数据库所做的修改都是在数据库的数据高速缓冲区中进行的，同时将产生的重做记录写入重做日志缓冲区。在一定条件下由DBWR进程将数据高速缓冲区中修改后的结果成批写回数据文件中，而重做日志缓冲区中的重做记录由LGWR进程周期性的写入重做日志文件。  

如果需要将当前处于CURRENT 状态的重做日志组立即切换到INACTIVE状态，必须进行手工日志切换。

冷备份发生在数据库已经正常关闭的情况下，冷备份不能按表或按用户恢复。

热备份需要数据库运行于归档模式下。可以非常精确地备份表空间级和用户级的数据。

归档方式下数据库的恢复要求从全备份到失败点所有的日志都要完好无缺。同时要求全部数据文件无损坏或有可用备份，一般建议先做一次数据库冷备份。

RMAN的运行环境

RMAN命令执行器：用于对目标数据库进行备份与恢复操作管理的客户端应用程序。

目标数据库：利用RMAN进行备份与恢复操作的数据库。

RMAN资料档案库：存储进行数据库备份、修复以及恢复操作时需要的管理信息等元数据。

RMAN恢复目录：建立在恢复目录数据库中的一种存储对象，存储RMAN的资料档案库信息。

RMAN恢复目录数据库：用于保存RMAN恢复目录的数据库，是一个独立于目标数据库的Oracle数据库

RMAN中与自动分配通道相关的预定义配置参数包括：

配置默认的设备类型：

CONFIGURE DEFAULT DEVICE TYPE TO disk|sbt

配置设备类型的并行度（通道数量）

CONFIGURE DEVICE TYPE disk|sbt PARALLELISM n

配置通道默认的备份文件的备份路径和备份格式

CONFIGURE CHANNEL DEVICE TYPE  DISK FORMAT ‘C:/%d_DB_%u_%s_%p';

配置n号通道的备份文件的备份路径和备份格式

CONFIGURE CHANNEL n DEVICE TYPE DISK FORMAT ‘d:/back1/%U';

数据库必须处于加载或打开状态，并且与目标数据库建立连接。而且数据库处于归档模式。

RMAN备份对象包括：整个数据库、数据文件、表空间、归档重做日志文件、控制文件、服务器初始化参数文件

使用BACKUP DATABASE命令备份整个数据库。

        RMAN> run {

        2> allocate channel t1 type disk;

      3> backup database  tag full_db_backup

      4> format ‘d:/oracle/backups/db_t%t_s%s_p%p';

      5> release channel t1;

      6>}

使用BACKUP TABLESPACE命令备份一个或多个表空间。

RMAN> run {

       2> allocate channel t1 type disk;

       3> backup tablespace users tag tbs_users_read_only

       4> format ‘d:/oracle/backups/tbs_users_t%t_s%s‘;

       6> release channel t1

       7> }

复制数据文件

RMAN>BACKUP AS COPY DATAFILE

'D:\APP\ORADATA\ORCL\USERS01.DBF ' FORMAT 'D:\BACKUP\%U';

除了使用BACKUP进行备份之外。也可以直接使用COPY命令。

RMAN>run{

2> allocate channel c1 type disk;

3> copy datafile 'c:\oracle\oradata\rman\users01.dbf' to 'e:\dbbackup\u.dbf' tag=u1215;

4> release channel c1;

5> }

备份控制文件

RMAN> run {       

       2> allocate channel dev1 disk ;

       3> backup  current controlfile format 'd:\back\cf_t%t_s%s_p%p';

       4> release channel dev1;

       5> }

备份归档日志文件

RMAN> run {

    2> allocate channel dev1 type disk;

    3> sql ”alter system archive log current”

    3> backup archivelog all delete input

    4> format ‘d:/oracle/backups/log_t%t_s%s_p%p';

    5> release channel dev1;

    6> }

也可以在对数据文件、表空间或控制文件进行备份时使用BACKUP…PLUS ARCHIVELOG命令，同时对归档重做日志文件进行备份。

可以使用BACKUP SPFILE命令备份当前数据库使用的服务器初始化参数文件。

RMAN>BACKUP SPFILE FORMAT 'D:\BACKUP\%U';

0级备份

RMAN> run {

        2> allocate channel dev1 type disk;

        3> backup incremental level 0

        4> filesperset 4

        5> format 'd:/oracle/backups/sunday_level0_%t'

        6> (database include current controlfile);

        7> release channel dev1;

        8> }

闪回查询（Flashback Query）：查询过去某个时间点或某个SCN值时表中的数据信息；

闪回版本查询（Flashback Version Query）：查询过去某个时间段或某个SCN段内表中数据的变化情况；

闪回事务查询（Flashback Transaction Query）：查看某个事务或所有事务在过去一段时间对数据进行的修改；

闪回表（Flashback Table）：将表恢复到过去的某个时间点或某个SCN值时的状态；

闪回删除（Flashback Drop）：将已经删除的表及其关联对象恢复到删除前的状态；

闪回数据库（Flashback Database）：将数据库恢复到过去某个时间点或某个SCN值时的状态。

闪回数据库操作的限制：

数据文件损坏或丢失等介质故障不能使用闪回数据库进行恢复。闪回数据库只能基于当前正常运行的数据文件；

闪回数据库功能启动后，如果发生数据库控制文件重建或利用备份恢复控制文件，则不能使用闪回数据库；

不能使用闪回数据库进行数据文件收缩操作；

不能使用闪回数据库将数据库恢复到在闪回日志中可获得最早的SCN之前的SCN，因为闪回日志文件在一定条件下被删除，而不是始终保存在闪回恢复区中。