Oracle从入门到精通(笔记)

一、Oracle11g概述

1.6 启动与关闭数据库实例

1.6.1 启动数据库实例

Oracle数据库实例启动分3个步骤：启动实例，加载数据库，打开数据库；

命令格式：startup [nomount|mount|open|force] [resetrict] [pfile=filename]

nomount: 启动实例不加载数据库

mount: 启动实例、加载数据库并保持数据库的关闭状态

open: 启动实例、加载并打开数据库，默认选项

force: 终止实例并重启数据库

resetrict: 用于指定以受限制的会话方式启动数据库

pfile: 用于指定启动实例时所使用的文本参数文件，filename是参数文件名

1.6.2 关闭数据库实例

关闭数据库实例分为3个步骤：关闭数据库、卸载数据库、关闭数据库实例；

格式名：shutdown [normal|transactional|immediate|abort]

normal: 以正常方式关闭数据库

transactional: 在当前所有的活动事务被提交完毕之后，关闭数据库

immediate: 在尽可能短的时间内立即关闭数据库

abort: 以终止方式关闭数据库

********************************************************************************************************************

二、Oracle 11g 体系结构

2.1 Oracle 11g 体系结构概述

实例：指一组Oracle后台进程以及在服务器中分配的共享内存区域；

数据库：是基于磁盘的数据文件、控制文件、日志文件、参数文件和归档日志文件等组成的物理文件集合；

数据库服务器：指管理数据库的各种软件工具(例如，sqlplus)、实例及数据库3部分；

实例用于管理和控制数据库，而数据库为实例提供数据。一个数据库可以被多个实例装载和打开；而一个实例在其生存期内只能装载和打开一个数据库。

Oracle的存储结构分为逻辑存储结构和物理存储结构；

2.2 逻辑存储结构

Oracle数据库-->表空间-->数据段-->数据区-->s数据块

2.2.1 数据块

数据块是Oracle逻辑存储结构中最小的逻辑单位，也是执行数据库输入输出操作的最小存储单位；

Oracle数据块有一定的标准大小，其大小被写入到初始化参数DB_BLOCK_SIZE中；

查询Oracle数据块标准大小： select name,value from v$parameter where name='db_block_size';

数据快中可以存放表数据、索引数据和簇数据；

数据块由块头、表目录、行目录、空余空间和行数据5部分组成；

2.2.2 数据区

数据区是由一组连续的Oracle数据块所构成的Oracle存储结构；

数据区是Oracle存储分配的最小单位；

2.2.3 段

段是由一个或多个数据区构成的，它不是存储空间的分配单位，而是一个独立的逻辑存储结构，用于存储表、索引或簇等占用空间的数据对象；

使用段的目的是用来保存特定对象；

一个oracle数据库通常有4个类型的段：数据段、索引段、回滚段、临时段

（1）数据段

数据段中保存的是表中的数据记录，在创建数据表时，Oracle系统将为表创建数据段。当表中的数据量增大时，数据段的大小自然也随着增大，数据段的增大过程是通过向其添加

新的数据区来实现的。

当创建一个表时，系统会自动创建一个以该表的名字命名的数据段；

（2）回滚段

回滚段中保存了回滚条目，Oracle将修改前的旧值保存在回滚条目中。

（3）临时段

当执行创建索引、查询等操作时，Oracle可能会使用一些临时存储空间，用于暂时的保存解析过的查询语句以及在排序过程中产生的临时数据。

2.2.4 表空间

表空间是数据库的最大逻辑划分区域，通常用来存放数据表、索引、回滚段等数据对象，任何数据对象在创建时都必须被指定存储在某个表空间中。

一个表空间由一个或多个数据文件组成，一个数据文件只属于一个表空间

Oracle数据的存储空间在逻辑上表现为表空间，物理上表现为数据文件；

每个数据库至少有一个表空间(SYSTEM表空间)，表空间的大小等于所有从属于它的数据文件大小的总和。

在表空间中创建数据对象时存在2种情况：

1) 若表空间只包含一个数据文件，则该表空间中的所有对象都存储在这个数据文件中；

2) 若表空间包含多个数据文件，则Oracle即可将数据对象存储在该表空间的任意一个数据文件中，也可以将同一个数据对象中的数据分布在表空间的多个数据文件中；

（1） SYSTEM表空间

系统表空间，用于存放Oracle系统内部表和数据字典的数据，如表名，列名，用户名等；

查询数据库中数据字典的信息：select * from dict;

（2）SYSAUX表空间

是SYSTEM的辅助表空间，降低SYSTEM表空间的负载，主要存储数据字典以外的其他数据对象。SYSAUX一般不存储用户的数据；

（3）UNDO表空间

撤销表空间，用于存储撤销信息的表空间。对应的数据文件是UNDOTBS01.dbf

（4）USERS表空间

用户表空间，是Oracle建议使用的表空间，可以在这个表空间上创建各种数据对象。

2.3 物理存储结构

物理存储结构用来描述Oracle数据在磁盘上的物理组成情况；

物理存储结构主要有数据文件、控制文件、重做日志文件、归档日志文件、参数文件、口令文件和警告日志文件等；

2.3.1 数据文件

数据文件用于保存用户应用程序数据和Oracle系统内部数据的文件；

在创建表空间的同时，Oracle会创建该表空间的数据文件

一个数据对象的数据可以全部存储在一个数据文件中，也可以分布存储在同一个表空间的多个数据文件中；

通过查询dba_data_files或v$datafile数据字典来查看Oracle系统数据文件的信息：select file_name,tablespace_name from dba_data_files;

通过查询dba_temp_files或v$tempfile数据字典查看临时文件的信息：select file_name.tablespace_name from dba_temp_files;

2.3.2 控制文件

控制文件是一个二进制文件，记录了数据库的物理结构，主要包含数据库名，数据文件和日志文件的名字和位置、数据库建立日期等信息；

控制文件存放有数据文件、日志文件等信息，所以Oracle实例在启动时必须访问控制文件。只有控制文件正常，实例才能加载并打开数据库；

当Oracle实例在正常启动时，系统首先访问的是初始化参数文件pfile，然后Oracle为系统全局区分配内存。

每个数据库至少拥有一个控制文件，一个数据库可以同时拥有多个控制文件，但是一个控制文件只能属于一个数据库。

通过查询v$controlfile数据字典来查看Oracle系统控制文件信息：select name from v$controlfile;

2.3.3 日志文件

Oracle的日志文件包括重做日志文件和归档日志文件

（1）重做日志文件

用来记录数据库所有发生过的更改信息及由Oracle内部行为（创建数据表、索引等）而引起的数据库变化信息。在数据库恢复时，可以从该日志文件中读取原始记录。

通过v$logfile视图来查询Oracle系统的日志文件信息：select member from v$logfile;

（2）归档日志文件

通过v$database视图来查看当前Oracle系统是否采用归档模式: select dbid,name,log_mode from v$database;

查询归档日志的所在路径：show parameter log_archive_dest;

2.3.4 服务器参数文件

服务器参数文件SPFILE是二进制文件，用来记录Oracle数据库的基本参数信息。

数据库实例启动之前，Oracle系统首先会读取SPFILE参数文件中设置的参数，并根据这些参数的配置来启动实例，例如：设置标准数据块大小，设置日志缓冲区大小

（1）查看服务器参数

select name,value,ismodified from v$parameter;

show parameter;

（2）修改服务器参数

通过ALTER SYSTEM 命令修改标准数据块的大小为4096字节：alter system set db_block_size=4096

2.3.5 密码文件、警告文件和跟踪文件

（1）密码文件

密码文件是Oracle系统用于验证sysdba权限的二进制文件，当远程用户以sysdba或sysoper连接到数据库时，一般要用密码文件验证；

Oracle11g密码文件默认存放位置: %dbhome_1%\database

密码文件的命名格式PWD,sid表示数据库实例名。

创建密码文件命令格式：ORAPWD FILE= PASSWORD= ENTRIES=

filename:表示密码文件名称

password: 设置internal/sys账户口令；

max_users: 表示密码文件中可以存放的最大用户数，对应允许以sysdba/sysoper权限登录数据库的最大用户数；

创建密码文件文件后，需设置初始化参数remote_login_passwordfile来控制密码文件的使用状态，通常有3种状态值：

NONE表示只要通过操作系统验证，就不用通过Oracle密码文件验证；

SHARED 表示多个数据库实例都可以采用此密码文件验证；

EXCLUSIVE：表示只有一个数据库实例可以使用此密码文件验证；

例如: 创建一个密码文件，其SYS口令为012345

ORAPWD FILE=E:\app\Admin\product\11.2.0\dbhome_1\database\PWDorcl.ora password=012345 entries=40

（2）警告文件

警告文件是一个存储在Oracle系统目录下的文本文件，用来记录Oracle系统的运行信息和错误信息。

运行信息包括Oracle实例的启动与关闭，建立表空间，增加数据文件；

错误信息包括空间扩展失败，启动实例失败；

通过backgroud_dump_dest来查看警告文件的路径：

select name,value from v$parameter where name='backgroud_dump_dest'

（3）跟踪文件

跟踪文件包括后台进程跟踪文件和用户进程跟踪文件；

后台进程跟踪文件的磁盘位置由初始化参数BACKGROUD_DUMP_DEST来确定；

用户进程跟踪文件的位置由初始化参数user_dump_dest来确定；

查看当前实例的用户跟踪文件的路径：select value from v$parameter where name='user_dump_dest';

2.4 Oracle11g服务器结构

Oracle服务器主要由实例、数据库、程序全局区和前台进程组成。

（1）实例可划分为系统全局区（SGA）和后台进程；

系统全局区使用操作系统的内存资源，而后台进程需要使用CPU与内存资源。

（2）数据库中包含数据文件、控制文件、重做日志文件。

（3）程序全局区是一个非共享的内存区域，用于管理用户进程的私有资源。

（4）前台进程可以划分为用户进程和服务器进程；

2.4.1 系统全局区（SGA）

系统全局区是所有用户进程共享的一块内存区域，SGA中的数据资源可以被多个用户进程共同使用。

SGA主要由高速数据缓冲区、共享池、重做日志缓存区、Java池和大型池等内存结构组成。

（1）高速数据缓冲区

该区域存放着Oracle系统最近访问过的数据块。

高速数据缓冲区分为3个部分：脏数据区、空闲区、保留区

脏数据区：存放着已被修改过的数据，这些数据等待被写入到数据文件中。当一条更新或删除语句对某些数据块中的数据修改后，那么这些数据块就被标记为“脏”，然后等待提交命令

并通过后台进程DBWR将其写入到数据文件中；

空闲区：该区长的数据块不包含任何数据，这些数据块可以被写入数据，Oracle可以从数据文件中读取数据块，并将其存放到该区中；

保留区：包含那些正在被用户访问的数据块和明确保留以作为将来使用的数据快，这些数据块将被保留在缓冲区中；

（2）重做日志缓冲区

重做日志缓冲区用于存放对数据库进行修改操作时所产生的日志信息。

重做日志缓冲区的大小由LOG_BUFFER参数指定。

（3）共享池

共享池是SGA保留的内存区域，用于缓存SQL语句、PL/SQL语句、数据字典、资源锁、字符集及其他控制控制结构。

共享池包括库高速缓冲区和字典高速缓冲区。

库高速缓冲区，主要包括共享SQL区和私有SQL区2部分。存放最近用过的SQL语句、PL/SQL语句的文本和执行计划。

字典高速缓冲区用于存放Oracle系统内部管理所需要的数据字典信息，例如。用户名、数据对象和权限等。

共享池的内存空间大小可通过参数SHARED_POOL-SIZE来设置。例如，修改共享池的内存大小为30MB, alter system set shared_pool_size=30m;

（4）大型池

大型池，实例需要使用大型池来减轻共享池的访问压力。大型池的缓存区大小通过LARGE_POOL_SIZE来定义，例如修改Oracle大型池的缓存区大小为16MB：

alter system set large_pool_size=16m;

查看大型池大小：show parameter large_pool_size;

（5）Java池

用来提供内存空间给Java虚拟机使用，目的是支持在数据库中运行JAVA程序包，大小由JAVA_POOL_SIZE来定义；

（6）流池

Oracle流池用于在数据库与数据库之前进行信息共享；

2.4.2 程序全局区（PGA）

程序全局区也称作用户进程全局区，它的内存区在进程私有区而不是共享区中。

在程序全局区中，一个服务进程只能访问属于它自己的那部分PGA资源区，各个服务进程的PGA区的总和即为实例PGA的大小。通常PGA区由私有SQL区和会话区组成。

（1）私有SQL区

用于存储变量以及SQL语句运行时的内存结构信息，当每个用户连接到实例时，都会在实例中创建一个会话。

私有SQL区可分为静态区和动态区；

静态区的信息在会话过程中保持不变，只有当会话结束时，静态区才会被释放掉；

动态区的信息在整个会话过程中是不断变化的，一旦SQL语句指定完毕，即使会话还没有结束，动态区也被释放掉；

（2）会话区

会话区用于存放用户的会话信息。如果数据库处于共享服务器连接模式下，则会话区将位于SGA区域，而不是PGA中；

查看程序全局区的信息： show parameter pga;

2.4.3 前台进程

前台进程包括用户进程和服务器进程，不是实例的一部分。

（1）用户进程

指那些能够产生或执行SQL语句的应用程序。用户进程包括连接和会话。

（2）服务器进程

用于处理用户会话过程中向数据库实例发出的SQL语句或SQL*PLUS命令，分为专用服务器模式和共享服务器模式。

2.4.4 后台进程

Oracle后台进程是一组运行于Oracle服务器短的后台程序，是Oracle实例的重要组成部分。

其中SMON。PMON、DBWR、LGWR和CKPT这5个后台进程必须正常启动，否则将导致数据库实例崩溃。

（1）数据写入进程（DBWR）

该进程主要负责将内存中的脏数据块回写到数据文件中。脏数据块指高速数据缓冲区中的被修改过的数据块，这些数据块的内容与数据文件中的数据块内容不一致。

通常在以下几种情况发生时，DBWR进程会将脏数据块写入到数据文件。

1)当用户进程执行插入或修改操作时，需要将“新数据”写入高速数据缓冲区，如果在高速数据缓冲区中没有找到足够大的空闲数据块来存放这些“新数据”，这时，Oracle系统将

启动DBWR进程将脏数据块写入数据文件，以获得空闲数据块来存储这些新数据。

2）当检查点进程启动后，它会强制要求DBWR将某些脏数据块写入数据文件中；

3）当脏数据块在高速数据缓冲区中存放超过3秒钟，DBWR进程会自动启动并将某些在那个数据块写入到数据文件中；

可铜鼓修改服务器参数文件SPFILE的DB_WRITER_PROCESSESS参数，以允许使用多个DBWR进程，但进程总数不应超过系统处理器的数量。

（2）检查点进程（CKPT）

检查点可以看做是一个事件，当检查点事件发生时，CKPT会要求DBWR将某些脏数据回写到数据文件。

DBA可以通过修改初始化参数文件SPFILE中的CHECKPOINT_PROCESS参数为TRUE来启动检查点进程。

（3）日志写入进程（LGWR）

日志写入进程用于将重做日志缓冲区中的数据写入重做日志文件。Oracle系统首先将用户所做的修改日志信息写入日志文件，然后再将修改结果写入数据文件。

（4）归档进程（ARCH）

归档进程是一个可选择的进程，只有当Oracle数据库处于归档模式时，该进程才可能起到作用。

（5）系统监控进程（SMON）

系统监控进程是在数据库系统启动时执行恢复工作的强制性进程。

（6）进程监控进程（PMON）

进程监控进程用于监控其他进程的状态，当有进程启动失败时，PMON会清楚失败的用户进程，释放用户进程所占用的资源、

查询当前实例进程信息：select name,description from v$bgprocess;

2.5 数据字典

2.5.1 Oracle数据字典概述

Oracle数据字典的名称由前缀和后缀组成，使用_连接，代表的含义如下：

dba_: 包含数据库实例的所有对象信息；

v$_：当前实例的动态视图，包含系统管理和系统优化等所使用的视图；

user_: 记录用户的对象信息；

gv_ :分布式环境下所有实例的动态视图，包含系统管理和系统优化等所使用的视图；

all_: 记录用户的对象信息及授权访问的对象信息；

2.5.2 Oracle常用数据字典

（1）基本数据字典

基本数据字典主要包括描述逻辑存储结构和物理存储结构的数据表。

dba_tablespaces : 关于表空间的信息

dba_ts_quotas : 所有用户表空间限额

dba_free_space : 所有表空间中的自由分区

dba_segments : 描述数据库中所有段的存储空间

dba_extents: 数据库中所有分区的信息

dba_tables: 数据库中所有数据表的描述

dba_tab_columns：所有表、视图及簇的列；

dba_views: 数据库中所有视图的信息

dba_synonyms: 关于同义词的信息查询

dba_sequences :所有用户序列信息

dba_constraints : 所有用户表的约束信息

dba_indexs：关于数据库中所有索引的描述

dba_ind_columns：在所有表及簇上压缩索引的列；

dba_triggers：所有用户的触发器信息

dba__source：所有用户存储过程信息

dba_data_files：查询关于数据库文件的信息

dba_tab_grants/privs : 查询关于对象授权的信息

dba_objects：数据库中所有的对象

dba_users：关于数据库中所有用户的信息

（2）常用动态性能视图

动态性能视图以v$作为名称前缀，用户只能进行只读访问而不能修改它们。

v$database: 描述关于数据库的相关信息

v$datafile：数据库使用的数据文件信息

v$log：从控制文件中提取有关重做日志组的信息

v$logfile：有关实例重置日志组文件名及位置的信息

v$archived_log：记录归档日志文件的基本信息

v$archive_dest：记录归档日志文件的路径信息

v$controlfile：描述控制文件的相关信息

v$instance ：记录实例的基本信息

v$system_parameter : 显示实例当前有效的参数信息

v$sga：显示实例的SGA区的大小

v$sgastat : 统计SGA使用情况的信息

v$parameter : 记录初始化参数文件中所有项的值

v$lock ：通过访问数据库会话，设置对象锁的所有信息

v$session ：有关会话的信息

v$sql：记录SQL的详细信息

v$sqltext：记录SQL语句的语句信息

v$bgprocess:显示后台进程信息

v$process: 当前进程的信息

********************************************************************************************************************

三、SQL*PLUS命令

3.2 设置SQL*PLUS的运行环境

3.2.2 使用SET命令设置运行环境

（1）PAGESIZE变量

该变量用来设置从顶部标题至页结束之前的行数，格式：SET PAGESIZE value，value默认值为14.

查看当前环境中一页有多少行：show pagesize;

select user_id,username,account_status from dba_users;

（2）NEWPAGE变量

该变量用来设置一页中空行的数量，格式: SET NEWPAGE value，value默认值为1

查询一页有多少空行: show newpage

select user_id,username from account_status from dba_users;

（3）LINESIZE变量

该变量用来设置一行所显示的最多字符总数，格式：SET LINESIZE value,value默认值为80

查询一行可以显示的最多字符数量：show linesize

（4）PAUSE变量

该变量用来设置输出结果是否滚动显示，格式：SET PAUSE value, value变量值有3各种情况：

OFF: 这是默认值，返回结果一次输出完毕，中间的每一页不会暂停；

ON: 表示输出结果的每一页都暂停，用户按ENTER键后继续显示；

TEXT：在设置PAUSE的值为ON之后，若再设置TEXT的值，则每次暂停都将显示该字符串。当PAUSE的值为OFF时，设置TEXT值没有任何意义。

使用set pause命令设置显示结果按页暂停，并在暂停后显示“按ENTER键继续”字符串。

（5）NUMFORMAT变量

该变量用来设置显示数值的默认格式，该格式是数值格式，格式：SET NUMFORMAT format ，其中format为数值的掩码，常用掩码如下：

9：查询结果中数字替换格式中的掩码，例如：999

0：格式中的掩码屏蔽掉查询结果中的数字，例如：999.00

$ ：在查询结果中的数字前添加美元前缀，例如：$999

S : 为数字显示符号类型，通常用于显示查询结果中的正负数字,例如：S999

，：在字符“，”位置上放置逗号，例如：999,99

3.3 常用SQL*PLUS 命令

3.3.1 HELP命令

HELP命令语法格式：HELP|?[topic]

?表示一个命令的部分字符，topic参数表示将要查询的命令的完整名称。

3.3.2 DESCRIBE命令

DESCRIBE用来查询指定数据对象的组成结构，语法格式：desc[ribe] object_name

describe可缩写为desc，object_name为将要查询的对象名称

可使用#desc object_name的命令格式来随时查看数据对象的结构

3.3.3 SPOOL命令

SPOOL可以把查询结果输出到指定文件中，SPOOL的语法格式：SPO[OL] [file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END] | OFF |OUT]

参数file_name用于指定脱机文件的名称，默认的文件扩展名为LST。

CRE[ATE] : 表示创建一个新的脱机文件，是SPOOL的默认状态

REP[LACE]: 表示替代已经存在的脱机文件

APP[END] : 表示把脱机内容附加到一个已经存在的脱机文件中；

OFF|OUT : 表示关闭SPOOL输出。

3.4.4 其他常用命令

（1）DEFINE命令

该命令用来定义一个用户变量并且可以分配给它一个CHAR值，格式：DEF[INE] [variable] | [variable=text]

variable：表示定义的变量名

text : 变量的CHAR值

使用define命令定义vjob变量，并给它分配一个CHAR值“SALESMAN”

（2）SHOW命令

该命令用来显示系统变量的值或环境变量的值，格式：SHO[W] option

（3）EDIT命令

EDIT命令用来编辑SQL缓冲区或指定磁盘文件中的SQL语句或PL/SQL块，语法格式：ED[IT] [file_name[.ext]]

file_name：表示要编辑的磁盘文件名。不指定file_name，则表示编辑SQL缓冲区中的最近一条SQL语句或PL/SQL块。

（4）SAVE命令

该命令实现将SQL缓冲区中的最近一条SQL语句或PL/SQL块保存到一个文件中。语法格式：SAVE file_name

file_name表示要保存的文件名，不指定路径，默认保存到Oracle系统安装的主目录中。

（5）GET命令

该命令实现把一个SQL脚本文件的内容放进SQL缓冲区，语法格式：GET [FILE] file_name[.ext] [LIST| NOLIST]

file_name：要检索的文件名，若省略文件扩展名，默认为.sql

LIST：指定文件的内容加载到缓冲区时显示文件的内容；

NOLIST: 指定文件的内容加载到缓冲区时不显示文件的内容；

（6）START和@命令

这2个命令都可以用来执行一个SQL脚本文件，语法：

STA[RT] {url|file_name[.ext]} [arg...]

@ {url|file_name[.ext]} [arg...]

url:表示要执行的SQL脚本文件的路径

file_name: 表示包含SQL脚本的文件名

arg: 其他参数

3.4 格式化查询结果

3.4.1 COLUMN命令

该命令可以实现格式化查询结果、设置列宽度、重新设置列标题等功能。语法格式：COL[UMN] [column_name| alias |option]

column_name：用于指定要设置的列的名称；

alias：用于指定列的别名，通过它可以把英文列标题设置为汉字；

option：用于指定某个列的显示格式，option选项的值如下表：

CLEAR : 清除指定列所设置的显示属性，从而回复列使用默认的显示属性

FORMAT：格式化指定的列

HEADING: 定义列标题

JUSTIFY : 调整列标题的对齐方式。默认，数值类型的列为右对齐，其他类型的列左对齐；

NULL：指定一个字符串，如果列的值为null，则由该字符串代替；

PRINT/NOPRINT: 显示列标题或隐藏列标题，默认为PRINT

ON|OFF：控制定义的显示属性的状态，OFF表示定义的所有显示属性都不起作用，默认为ON。

WRAPPED：当字符串的长度超过显示宽度时，将字符串的超出部分折叠到下一行显示

WORD_WRAPPED：表示从一个完整的字符处折叠

TRUNCATED ：表示截断字符串尾部

（1）FORMAT选项

该选项用于格式化指定的列，需要在FORMAT关键字后面跟一个掩码格式；

（2）WRAPPED、WORD_WRAPPED选项

这2个选项都用于实现折行的功能，WRAPPED选项按照指定长度折行，WORD_WRAPPED选项按照完整字符串折行。

3.4.2 TTITLE和BTITLE命令

这2个命令分别用来设置打印时每页的顶部和底部标题。TTITLE格式：TTI[TLE] [printspec [text|variable]...] | [OFF|ON]

printspec：用来作为头标题的修饰性选项，

text：用于设置输出结果的头标题

variable：用于在头标题中输出相应的变量值；

OFF：表示禁止打印头标题

ON：表示允许打印头标题

printspec选项的值：

COL：指定在当前行的第几列打印头部标题

SKIP：调到从下一行开始的第几行，默认为1

LEFT：在当前行中左对齐打印数据

CENTER：在当前行中间打印数据

RIGHT：在当前行中右对齐打印数据

BOLD：以黑体打印数据

********************************************************************************************************************

四、SQL语言基础

4.3.5 多表关联查询

（1）内连接

内连接使用关键字INNER JOIN来实现。其中INNER可以省略，当只使用JOIN关键字时，语句只表示内连接操作。

在内连接的检索结果中，所有记录行都是满足连接条件的。

内连接语法： SELECT column_list FROM table_name1 [INNER] JOIN table_name2 ON join_condition;

column_list：字段列表 table_name1和table_name2：两个要实现内连接的表 join_condition：实现内连接的条件表达式

（2）外连接

外连接分为左外连接（LEFT OUTER JOIN）、右外连接(RIGHT OUTER JOIN)、全连接（FULL OUTER JOIN）；

左外连接的查询结果中不仅包含了满足连接条件的数据行，而且还包含左表中不满足连接条件的数据行。

（3）自然连接

自然连接是指在检索多个表时，Oracle会将第一个表中的列与第二个表中具有相同名称的列进行自动连接。在自然连接中，用户不需要明确指定进行连接的列，由Oracle自动完成。

自然连接使用“NATURAL JOIN”关键字。实际很少应用。

（4）交叉连接

交叉连接是不需要任何连接条件的连接，使用CROSS JOIN关键字来实现。语法格式： select column_list from table_name1 cross join table_name2;

4.4 Oracle常用系统函数

4.4.1 字符类函数

（1）ASCII(c)函数和CHR(i)函数

ASCII(c)函数：用于返回一个字符的ASCII码，参数c表示一个字符；

CHR(i)函数：用于返回给出ASCII码值所对应的字符，i表示一个ASCII码值。这两个函数是互逆的关系；

dual表是Oracle系统内部提供的一个用于实现临时数据计算的特殊表，它只有一个列DUMMY，类型为VARCHAR2(1)

（2）CONCAT(s1,s2)函数

该函数将字符串s2连接到字符串s1的后面，如果s1为null, 则返回s2; 如果s2为null,则返回s1；如果s1和s2都为空，则返回null.

（3）INITCAP(s)函数

该函数将字符串s的每个单词的第一个字母大写，其他字母小写，单词之间用空格、控制字符、标点符号来区分。

（4）INSTR(s1,s2[,i][,j])函数

该函数用于返回字符s2在字符串s1中第j次出现时的位置，搜索从字符串s1的第i个字符开始。当没有发现要查找的字符时，该函数返回值为0；如果i为负数，那么搜索将从右到左进行，但

函数的返回位置还是按从左到右来计算。其中,s1和s2均为字符窜；i和j均为正整数，默认值为1.

（5）LENGTH(s)函数

该函数用于返回字符串s的长度，如果s为null,则返回值为null.

（6）LOWER(s)函数和UPPER(s)函数

LOWER(s)函数和UPPER(s)函数分别用于返回字符串S的小写形式和大小形式。

（7）LTRIM(s1,s2)函数、RTRIM(s1,s2)函数和TRIM(s1,s2)函数

这3个函数分别用来删除字符串s1左边的字符串s2, 删除字符串s1右边的字符串s2，删除字符串s1左右两端的字符串s2.如果在这3个函数中不指定字符串s2,则表示去除相应方位的空格。

（8）REPLACE(s1,s2[,s3])函数

该函数使用s3字符串替换出现在s1字符串中的所有s2字符串，并返回替换后的新字符串，其中，s3的默认值为空字符串。

（9）SUBSTR(s,i,[j])函数

该函数表示从字符串s的第i个位置开始截取长度为j的子字符串，如果省略参数j,则直接截取到尾部，其中i和j为正整数。

4.4.2 数字类函数

（1）CEIL(n)函数

该函数返回大于或等于数值n的最小整数。

（2）ROUND(n1,n2)函数

该函数返回舍入小数点右边n2位的n1的值，n2的默认值为0，这会返回小数点最接近的整数。如果n2为负数，就舍入到小数点左边相应的位上，n2必须是整数。

（3）POWER(n1,n2)函数

该函数返回n1的n2次方，其中n1和n2都为整数。

4.4.3 日期和时间类函数

（1）SYSDATE()函数

该函数返回系统当前的日期

（2）ADD_MONTHS(d,i)函数

该函数返回日期d加上i个月之后的结果。其中i为任意整数。

4.4.4 转换类函数

（1）TO_CHAR(X[,format])函数

该函数实现将表达式转换为字符串，format表示字符串格式。

（2）TO_NUMBER(s[,format[lan]])函数

该函数将返回字符窜s代表的数字，返回值按照format格式进行显示，format表示字符串格式，lan表示所使用的语言。

4.5 子查询的用法

4.5.2 多行子查询

多行子查询是指返回多行数据的子查询语句，当在WHERE子句中使用多行子查询时，必须使用多行运算符（IN、ANY、ALL）

（1）使用IN运算符

当在多行子查询中使用IN运算符时，外查询会尝试与子查询结果中的任何一个结果进行匹配，只要有一个匹配成功，则外查询返回当前检索的记录。

SQL> select empno,ename,job from emp where deptno in (select depno from dept where dname<>'SALES')

（2）使用ANY运算符

ANY运算符必须与单行比较运算符结合使用，并且返回行只要匹配子查询的任何一个结果即可。

SQL>select deptno,ename,sal from emp where sal> any (select sal from emp where deptno = 10 ) and deptno <> 10;

（3）使用ALL运算符

ALL运算符必须与单行运算符结合使用，并且返回行必须匹配所有子查询的结果。

SQL> select deptno,ename,sal from emp where sal> all (select sal from emp where deptno = 10);

4.6 操作数据库

4.6.3 删除数据

使用TRUNCATE语句删除表中的所有记录要比DELETE语句快的多，这是因为使用TRUNCATE语句删除数据时，它不会产生回滚记录。当然，执行了TRUNCATE语句的操作也无法使用ROLLBACK进行撤销。

在TRUNCATE语句中还可以使用REUSE STORAGE关键字或DROP STORAGE关键字，前者表示删除记录后仍然保存记录所占用的空间；后者表示删除记录后立即回收记录占用的空间。默认情况下

TRUNCATE语句使用DROP STORAGE关键字。

4.7 事务处理

4.7.2 操作事务

如果要回滚整个事务，Oracle系统内部将会执行如下操作过程：

（1）使用回滚段中的数据撤销对数据库所做的修改；

（2）Oracle后台服务进程释放掉事务所使用的系统资源；

（3）显示通知，告诉用户事务回滚成功；

Oracle不仅允许回顾整个未提交的事务，还允许回滚事务的一部分，这可以通过“保存点”来完成。在事务的执行过程中，用户可以通过建立保存点将一个较长的事务分隔成几部分、

这样用户就可以有选择的回滚到某个保存点，并且该保存点之后的操作都将被取消。

可以使用“savepoint 保存点名称”来设置保存点，例如：savepoint sp, 回滚到某个保存点：rollback to savepoint sp;

***************************************************************************************************************

五、PL/SQL编程

5.1 PL/SQL概述

5.1.1 PL/SQL块结构

整个PL/SQL块分为3个部分：声明部分（以DECLARE开头）、执行部分（以BEGIN开头）和异常处理部分（以EXCEPTION开头），语法格式如下：

[DECLARE]

--声明部分，可选

BEGIN

--执行部分，必须

[EXCEPTION]

--异常处理部分，可选

END

对于PL/SQL块中的语句：每一条语句都必须以分号结束，每条SQL语句可以写成多行的形式，同样必须使用分号来结束。另外，一行中也可以有多条SQL语句，但他们之前必须以分号分隔。

5.2 数据类型与定义变量和常量

5.2.1 基本数据类型

（1）数值类型

数值类型包括NUMBER、PLS_INTERGER、BINARY_INTERGER3种基本类型。

NUMBER：存储整数或浮点数； PLS_INTERGER、BINARY_INTERGER：这2个类型只能存储整数；

NUMBER类型可以通过NUMBER(P,S)的形式来格式化数字，其中，参数P表示精度，参数S表示刻度范围。精度指数值中所有有效数字的个数，而刻度范围是指小数点右边小数位的个数。精度和刻度范围都是可选的。

（2）字符类型

字符类型包括VARCHAR2、CHAR、LONG、NCHAR、NVARCHAR2。

VARCHAR2：用于存储可变长度的字符串，语法格式：VARCHAR2(maxlength) , 参数maxlength表示可以存储字符串的最大长度，在定义变量时必须给出。

CHAR：表示指定长度的字符串，语法格式：CHAR(maxlength), 参数maxlength表示可以存储字符串的最大长度，CHAR类型的默认最大长度是1字节。如果赋给CHAR类型变量的值不足maxlength，则在其后面用空格补全。

LONG: 表示一个可变的字符串，最大长度是32767字节，而数据库类型的LONG最大长度可达2GB。

NCAHR和NVARCHAR2：这2种数据类型的长度要根据各国字符集来确定。

（3）日期类型

日期类型只有一种：DATE类型。DATE类型的存储空间是7个字节，分别使用一个字节存储世纪、年、月、天、小时、分钟和秒。

（4）布尔类型

布尔类型也只有一种：BOOLLEAN类型。主要用于程序的流程控制和业务逻辑判断，其变量值可以是TRUE、FALSE和NULL中的一种。

5.2.2 特殊数据类型

（1）%TYPE类型

使用%TYPE关键字可以声明一个与指定列名称相同的数据类型，它通常紧跟在指定列名的后面。

例如：声明一个与emp表中job列的数据类型完全相同的变量var_job, 代码如下：

declare

var_job emp.job%type;

使用%TYPE定义变量有两个好处：第一，用户不必查看表中各个列的数据类型，就可以确保所定义的变量能够存储检索的数据；

第二，如果对表中已有列的数据类型进行修改，则用户不必考虑对已定义的变量所使用的数据类型进行修改，因为%TYPE类型的变量会根据列的实际类型自动调整自身的数据类型。

（2）RECORD类型

使用该类型的变量可以存储由多个列值组成的一行数据。在声明记录类型变量之前，首先需要定义记录类型，然后才可以声明记录类型的变量。记录类型是一种结构化的数据类型，它使用

type语句进行定义，在记录类型的定义结构中包含成员变量及其数据类型，语法格式如下：

type record_type is record

(

var_member1 data_type [not null] [:=default_value],

....

var_membern data_type [not null] [:=default_value])

record_type: 表示要定义的记录类型名称；

var_member1: 表示该记录类型的成员变量名称；

data_type：表示成员变量的数据类型；

（3）%ROWTYPE类型

%ROWTYPE类型的变量结合了%TYPE类型和RECORD类型变量的优点，它可以根据数据表中行的结构定义一种特殊的数据类型，用来存储从数据表中检索到的一行数据。语法：

rowVar_name table_name%rowtype;

rowVar_name：表示可以存储一行数据的变量名；

table_name：指定的表名；

5.2.3 定义变量和常量

（1）定义变量

定义变量必需的元素就是变量名和数据类型，还有可选择的初始值，语法：<变量名><数据类型> [(长度):=<初始值>];

（2）定义常量

定义常量必需的元素包括常量名、数据类型、常量值和constant关键字，语法： <常量名>constant <数据类型>:=<常量值>;

5.3 流程控制语句

5.3.1 选择语句

（1）if...then语句

if then

plsql_sentence

end if;

（2）if...then...else语句

if then

plsql_sentence1;

else

plsql_sentence2;

end if;

（3）if...then...elif语句

if then

plsql_sentence1;

else

plsql_sentence2;

...

else

plsql_sentence_n;

end if;

（4）case语句

case

when then plsql_sentence1;

when then plsql_sentence2;

...

when then plsql_sentencen;

[else plsql_sentence;]

end case;

selector：一个变量，用来存储要检测的值，通常称之为选择器。

5.3.2 循环语句

（1）loop语句

loop语句会先执行一个循环体，然后再判断“exit when”关键字后面的条件表达式的值是true还是false，如果是true，则程序会退出循环体，否则程序将再次执行循环体。语法：

loop

plsql_sentence;

exit when end_condition_exp

end loop;

end_condition_exp：循环结束条件表达式，当该表达式的值为true时，则程序会退出循环体，否则程序将再次执行循环体；

（2）while语句（先判断后执行)

语法格式：

while condition_expression loop

plsql_sentence;

end loop;

（3）for语句

for语句是一个可预置循环次数的循环控制语句，它有一个循环计数器，通常是一个整型变量。计数器值的合法性由上限值和下限值控制，若计数器值在上限值和下限值的范围内，

则程序执行循环；否则，终止循环；语法如下：

for variable_counter_name in [reverse] lower_limit..upper_limit loop

plsql_sentence;

end loop;

variable_counter_name：表示一个变量，通常为整数类型，用作计数器。默认情况下，计数器的值会循环递增，当在循环中使用reverse关键字时，计数器的值会循环递减。

lower_limit：计数器的下限值，当计数器的值小于下限值时，程序终止for循环；

upper_limit：计数器的上限值，当计数器的值大于上限值时，程序终止for循环；

例如：使用for语句求得前100个自然数中偶数之和，

declare

sum_i int=0;

begin

for i in reverse 1..100 loop

if mod(i,2)=0 then

sum_i:=sum_i+i;

end if;

end loop;

dbms_output.put_line('前100个自然数中偶数之和是：'||sum_i);

end;

/

5.4 PL/SQL 游标

游标主要用在服务器上；

游标的作用：相当于指针，通过游标PL/SQL程序可以一次处理查询结果集中的一行，并可以对该行数据执行特定操作，从而为用户在处理数据的过程中提供方便。

5.4.1 显示游标

显示游标是由用户声明和操作的一种游标，通常用于操作查询结果集，使用它处理数据的步骤包括：声明游标、打开游标、读取游标、关闭游标。

其中，读取游标可能是个反复操作的步骤。

（1）声明游标

声明游标主要包括游标名称和为游标提供结果集的SELECT语句。语法如下：

cursor cur_name[(input_parameter1[,input_parameter2]...)]

[return ret_type]

is select_sentence;

cur_name：表示所声明的游标名称；

ret_type：表示执行游标操作后的返回值类型，这是个可选项；

select_sentence：游标所使用的SELECT语句，它为游标的反复读取提供了结果集；

input_parameter1: 作为游标的“输入参数”，可以有多个，这是个可选项。它指定用户在打开游标后向游标中传递的值，该参数的定义和初始化格式如下：para_name [in] datatype [{:= | default} para_value]

para_name：表示参数名，关键字in表示输入方向，可以省略； datatype表示参数的数据类型，但数据类型不可以指定长度；para_value表示该参数的初始值或默认值，它也也可以是一个表达式；

para_name参数的初始值既可以以常规的方式赋值(:=),也可以使用关键字default初始化默认值；

（2）打开游标

打开游标的语法：open cur_name[(para_value1[,para_value2]...)];

（3）读取游标

读取游标就是将结果集中的数据保存到变量中，语法：fetch cur_name into {variable}；

variable：表示一个变量列表或“记录”变量，Oracle使用“记录”变量来存储游标中的数据；

（4）关闭游标

关闭游标的语法：close cur_name;

5.4.2 游标的属性

无论是显示游标还是隐式游标，都具有%found、%notfound、%isopen和%rowcount这4个属性。

%found: 布尔型属性，如果SQL语句至少影响到一行数据，则该属性为true，否则为false；

%notfound: 布尔型属性，与%found属性的功能相反；

%rowcount: 数字型属性，返回受SQL语句影响的行数；

%isopen：布尔型属性，当游标已经打开时返回true，游标关闭时则为false;

5.4.3 隐式游标

在执行一个SQL时，Oracle会自动创建一个隐式游标。这个游标是内存中处理该语句的工作区域。隐式游标主要是处理数据操作语句（UPDATE、DELETE）的执行结果。

由于隐式游标也有属性，当使用隐式游标的属性时，需要在属性前面加上隐式游标的默认名称--SQL。

5.4.4 通过For语法循环游标

在使用隐式游标或显示游标处理具有多行数据的结果集时，用户可以配合for语句来完成。在使用for语句遍历游标中的数据时，可以把它的计时器看做是一个自动的RECORD类型的变量。

（1）在for语句中遍历隐式游标中的数据时，通常在关键字in的后面提供由SELECT语句检索的结果集，在检索结果集的过程中，Oracle会自动提供一个隐式的游标sql。

（2）在for语句中遍历显示游标中的数据时，通常在关键字in的后面提供游标的名称，语法如下：

for var_auto_record in cur_name loop

plsqlsentence;

end loop;

var_auto_record: 自动的RECORD类型的变量，可以是任意合法的变量名称；

cur_name：指定的游标名称；

5.5 PL/SQL 异常处理

异常分为预定义异常和自定义异常；

********************************************************************************************************************

六、过程、函数、触发器和包

6.1 存储过程

存储过程是一种命名的PL/SQL程序块，它既可以没有参数，也可以有若干个输入、输出参数，甚至可以有多个既作输入又作输出的参数，但它通常没有返回值。存储过程被保存

在数据库中，它不可以被SQL语句直接执行或调用，只能通过EXECUT命令执行或在PL/SQL程序块内被调用。

创建存储过程需要使用PROCEDURE关键字，在关键字后面就是过程名和参数列表；创建存储过程不需要使用DECLARE关键字，转而是使用CREATE或REPLACE关键字，语法：

create [or replace] procedure pro_name [(parameter1[,parameter2]...)] is|as

begin

plsql_sentences;

[exception]

[dowith _sentences;]

end [pro_name];

pro_name: 存储过程的名称，如果数据库中已经存在了此名称，则可以指定“or replace"关键字，这样新的存储过程将覆盖掉原来的存储过程；

parameter1：存储过程的参数，若是输入参数，则需要在其后指定“in”关键字，若是输出参数，则需要在其后指定“out”关键字。在in或out关键字的后面是参数的数据类型，但不能指定该类型的长度；

plsql_sentences: PL/SQL语句，它是存储过程功能实现的主体；

dowith_sentences: 异常处理语句，也是PL/SQL语句，是一个可选项；

注意: 内部变量要在“is|as”关键字后面定义，并使用分号结束；

执行存储过程: execute 存储过程名

调用存储过程：

SQL>begin

存储过程名

end;

在创建存储过程的语法中，is关键字也可以使用as关键字来替代，结果是相同的；

6.1.2 存储过程的参数

存储过程可以接受多个参数，参数模式包括IN、OUT和IN OUT 3种。

（1）IN模式参数

这是一种输入类型的参数，参数值由调用方传入，并且只能被存储过程读取。这种参数模式是常用的，也是默认的参数模式，关键字in位于参数名称之后。

SQL>create or replace procedure insert_dept(

num_deptno in number,

var_ename in varchar2,

var_loc in varchar2) is

begin

insert into dept

values(num_deptno,var_ename,var_loc);

commit;

end insert_dept;

/

向存储过程传入参数可以有3种方式：

1）指定名称传递：指定名称传递是指向存储过程传递参数时需要指定参数名称，即参数名称在左侧，中间是赋值符号“>=”,右侧是参数值，语法如下：

pro_name(parameter1=>value1[,parameter2=>value2]...)

parameter1:参数名称，在传递参数值时，这个参数名称与存储过程中定义的参数顺序无关；

value1：参数值，在它的左侧不是常规的赋值符号“=”, 而是一种新的赋值符号“=>”,需要注意参数值的类型要与参数的定义类型兼容；

例如：向存储过程insert_dept传递参数：

SQL>begin

insert_dept(var_ename=>'采购部',var_loc=>'成都',num_deptno=>15);

end;

/

2）按位置传递

采用这种方式，用户提供的参数值顺序必须与存储过程中定义的参数顺序相同。

3）混合方式传递

就是将前两种方式结合在一起，这样就可以兼顾二者的优点。

（2）OUT模式参数

这是一种输出类型的参数，表示这个参数在存储过程中已经被赋值，并且这个参数值可以传递到当前存储过程以外的环境中，关键字out位于参数名称之后。

对out模式存储过程的调用和执行：

1）在PL/SQL块中调用OUT模式的存储过程：这种方式需要在PL/SQL块的DECLARE部分定义与存储过程中out参数兼容的若干变量；

SQL>declare

var_dname dept.dname%type;

var_loc dept.loc%type;

begin

select_dept(99,var_dname,var_loc);

dbms_output.put_line(var_dname||'位于：'||var_loc);

end;

/



把声明的两个变量传入到存储过程中，当存储过程执行时，其中的out参数会被赋值，当存储过程执行完毕，out参数的值会在调用处返回，这样定义的两个变量就可以得到out参数被赋予的值，最后这两个值就可以在存储过程外任意使用了。

2）使用EXEC命令执行OUT模式的存储过程：使用EXEC命令需要在SQL*PLUS环境中使用variable关键字声明两个变量，用以存储out参数的返回值。

SQL>variable var_dname varchar2(50);

SQL>variable var_loc varchar2(50);

SQL>exec select_dept(15,:var_dname,:var_loc);

通过exec执行存储过程时，用户看不到变量var_dname和var_loc的值，用户可以通过print命令或select命令来输出变量的值；

使用print打印， SQL>print var_dname var_loc;

使用select语句检索绑定的变量值，SQL> select :var_dname,:var_loc from dual;

（3）IN OUT 模式参数

在执行存储过程时，IN参数不能被修改，它只能根据被传入的指定值（或默认值）为存储过程提供数据，而OUT类型的参数只能等待被赋值，而不能像IN参数那样为存储过程本身提供数据。

但IN OUT参数可以兼顾其他两种参数的特点，在调用存储过程时，可以从外界向该类型的参数传入值；在执行完存储过程之后，可以将该参数的返回值传给外界。

6.1.3 IN 参数的默认值

Oracle支持在声明IN参数的同时给其初始化默认值，这样在存储过程调用时，如果没有向IN参数传入值，则存储过程可以使用默认值进行操作。

6.2 函数

6.2.1 创建函数

函数可以接受零或多个输入参数，并且函数必须有返回值，定义语法：

create [or replace] function fun_name[(parameter1[,parameter2]...) return data_type is

[inner_variable]

begin

plsql_sentence;

[exception]

[dowith _ sentences;]

end [fun_name];

fun_name:函数名称，如果数据库中已经存在了此名称，则可以指定“or replace”关键字这样新的函数将覆盖掉原来的函数；

parameter1:函数的参数，这是个可选项，因为函数可以没有参数；

data_type：函数的返回值类型，这个是可选项。在返回值类型的前面要使用return关键字来标明；

inner_variable：函数的内部变量，它有别于函数的参数，这是个可选项；

plsql_sentence：PL/SQL语句，它是函数主要功能的实现部分，也就是函数的主体；

dowith_sentences：异常处理代码，也是PL/SQL语句，这是一个可选项；

由于函数有返回值，所以在函数主体部分必须使用return语句返回函数值，并且要求返回值的类型要与函数声明时的返回值类型相同。

6.2.2 调用函数

由于函数有返回值，所以在调用函数时，必须使用一个变量来保存函数的返回值，这样函数和这个变量就组成了一个赋值表达式。

6.2.3 删除函数

使用drop function命令，其后面跟着要删除的函数名称，语法：drop function fun_name;

6.3 触发器

触发器可以看做一种特殊的存储过程，它定义了一些与数据库相关事件发生时应执行的“功能代码块”，通常用于管理复杂的完整性约束，或监控对表的修改，或通知其他程序，甚至可以实现对数据的审计功能。

触发器的语法格式：

create [or replace] trigger tri_name

[before | after |instead of ] tri_event

on table_name |view_name | user_name |db_name

[for each row ] [when tri_conditon]

begin

plsql_sentence;

end tri_name;

trigger：表示创建触发器的关键字；

before | after | instead of : 表示“触发时机”的关键字。before表示在执行DML等操作之后触发，这种方式能够防止某些错误操作发生而便于回滚或实现某些业务规则；

after表示在DML等操作发生之后，这种方式便于记录该操作或做某些事后处理信息；

instead of表示触发器为替代触发器；

on ：表示操作的数据表、视图、用户模式和数据库等，对它们执行某种数据操作，将引起触发器的运行。

for each row：指定触发器为行级触发器，当DML语句对每一行数据进行操作时都会引起该触发器的运行。如果未指定该条件，则表示创建语句级触发器，这是无论数据操作影响多少行，触发器都只会执行一次。

tri_event：触发事件，比如常用的有：INSERT、UPDATE、DELETE、CREATE、ALTER、DROP等。

table_name | view_name | user_name | db_name：分别表示操作的数据表、视图、用户模式和数据库，对它们的某些操作将引起触发器的运行。

tri_condition：表示触发条件表达式，只有当该表达式的值为true时，遇到触发事件才会自动执行触发器，使其执行触发操作；

6.4 程序包

程序包是由PL/SQL程序元素和匿名PL/SQL块、命名PL/SQL块组成。

程序包通常由规范和包主体组成。

6.4.1 程序包的规范

程序包一定要在“包主体”之前被创建，语法如下：

create [or replace] package pack_name is

[declare_variable];

[declare_type];

[declare_cursor];

[declare_function];

[declare_procedure];

end [pack_name];

pack_name：程序包名称。

define_variable：规范内声明的变量；

define_type：规范内声明的类型；

define_cursor：规范内定义的游标；

define_function：规范内声明的函数，但仅定义参数和返回值类型，不包括函数体；

define_procedure：规范内声明的存储过程，但仅定义参数，不包括存储过程主体；

6.4.2 程序包的主体

程序包主体的名称必须和规范的名称相同，创建程序包主体使用CREATE PACKAGE BODY语句，而不是CREATE PACKAGE。

create [or replace ] package body pack_name is

[inner_variable]

[cursor_body]

[function_title]

{begin

fun_plsql;

[exception]

[dowith _ sentences;]

end [fun_name]

{begin

pro_plsql;

[exception]

[dowith _ sentences;]

end [pro_name]}

...

end [pack_name];

pack_name：程序包的名称，要求与对应规范的程序包名称相同；

inner_variable：程序包主体的内部变量；

cursor_body：游标主体；

function_title：从规范中引入的函数头部声明；

fun_plsql：PL/SQL语句，这是函数主要功能的实现部分，从begin到end部分就是函数的body。

dowith _ sentences ：异常处理语句；

fun_name：函数的名称；

procedure_title：从规范中引入的存储过程头部声明；

pro_plsql：PL/SQL语句，这是存储过程主要功能的实现部分，从begin到end部分就是存储过程的body。

pro_name：存储过程的名称。

********************************************************************************************************************

七、管理控制文件和日志文件

7.1 管理控制文件

7.1.1 控制文件概述

控制文件是一个二进制文件，含有数据库的结构信息，包括数据文件和日志文件的信息。

Oracle的控制文件是在创建数据库时自动创建的；

控制文件记录了对数据库的结构信息和数据库当前的参数设置，包含的内容如下：

1）数据库名称和SID标识；

2）数据文件和日志文件列表（包括文件名称和对应路径信息）

3）数据库创建的时间戳

4）表空间信息

5）当前重做日志文件序列号

6）归档日志信息

7）检查点信息

8）回滚段的起始和结束；

9）备份数据文件信息

（1）及时备份控制文件

当Oracle数据库的实例启动时，控制文件用于在数据库和实例之间建立起关联，它在进行数据库操作时必须被打开。当数据库的物理组成（比如增加一个重做日志文件）发生变化时，Oracle将自动把这个变化信息记录到控制文件中。

（2）保护控制文件

目前采用的方法主要包括多路复用控制文件和备份控制文件

7.1.2 控制文件的多路复用

多路复用控制文件是指在系统不同的位置上同时存放多个控制文件的副本；

实现控制文件的多路复用主要包括更改CONTROL_FILES参数和复制控制文件两个步骤:

(1)更改CONTROL_FILES参数

在SPFILE文件中，CONTROL_FILES参数用于设置数据库的控制文件路径，Oracle通过该参数来定位并打开控制文件。

更改该参数设置可以使用ALTER SYSTEM语句。

SQL>alter system set control_files=

'E:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL'

scope=spfile;

（2）复制控制文件

7.1.3 创建控制文件

手动创建控制文件使用CREATE CONTROLFILE语句，语法如下：

create controlfile

reuse database db_name

logfile

group 1 redofiles_list1

group 2 redofiles_list2

group 3 redofiles_list3

...

datafile

datafile1

datafile2

datafile3

...

maxlogfiles max_value1

maxlogmembers max_value2

maxinstances max_value3

maxdatafiles max_value4

noresetlogs|resetlogs

archivelog|noarchivelog;

db_name：数据库名称，通常是orcl;

redofiles_list1：重做日志组中的重做日志文件列表1，列表中的重做日志文件可以有多个，其下面两个列表与此相同；

datafile1: 数据文件路径，其下面两个列表与此相同；

max_value1:最大的重做日志文件数，这是一个永久性的参数；

max_value2: 最大的重做日志组成员数，这是一个永久性的参数；

max_value3: 最大实例数，这是一个永久性的参数；

max_value4: 最大数据文件数，这是一个永久性的参数；

（1）查看数据文件和重做日志文件

查看日志文件: select member from v$logfile;

查看数据文件: select name from v$datafile;

查看控制文件: select name from v$controlfile;

（2）关闭数据库

shutdown immediate

（3）启动数据库实例

startup nomount

（4）打开数据库

正常方式打开数据库: alter database open;

以恢复方式打开数据库: alter database open resetlogs

7.1.4 备份和恢复控制文件

（1）备份控制文件

1）备份为二进制文件需要使用“alter database backup controlfile”语句；

SQL>alter database backup controlfile

to 'D:\OracleFiles\ControlFiles\control_file1.bkp';



2) 备份为脚本文件，实际上就是备份为可读的文本文件；

SQL>alter database backup controlfile to trace;

显示跟踪文件的存放位置： show parameter user_dump_dest;

（2）恢复控制文件

1）控制文件本身损坏

假设参数CONTROL_FILES所指定的某个控制文件被损坏，但该控制文件的目录仍然可以访问，并且有这个控制文件的一个多路复用文件，可以直接将其复制到对应的目录下，

无需修改初始化参数，步骤如下：

●关闭数据库: shutdown immediate;

●复制这个损坏文件对应的一个多路复用文件，覆盖掉原来目录下的损坏文件；

●重新启动数据库：startup;

2）磁盘介质永久性损坏

如果某个磁盘分区发生了物理性的永久损坏，而导致Oracle系统不能访问CONTROL_FILES参数指定的某个控制文件，并且在这个控制文件有一个多路复用文件，用户可以修改

初始化参数，将控制文件指定到新的可访问的位置上，步骤如下：

●关闭数据库实例，将当前控制文件的一个多路复用文件复制到一个新的可用位置；

●编辑初始化参数CONTROL_FILES，用新的控制文件的位置替换到原来损坏的位置，或者说删除原来损坏的位置，添加一个新的控制文件的位置；

●重新启动数据库；

7.1.5 删除控制文件

步骤如下：

●关闭数据库实例；

●编辑初始化参数CONTROL_FILES，清除掉打算要删除的控制文件的名称；

●重新启动数据库；

7.1.6 查询控制文件的信息

字典视图说明

V$CONTROLFILE 包含所有控制文件的名称和状态信息

V$CONTROLFILE_RECORD_SECTION 包含控制文件中各个记录文档段的信息

V$PARAMETER 包含了系统的所有初始化参数，从中可以查询参数CONTROL_FILES的值

使用v$controlfile_record_section视图查看控制文件中记录文档段的类型、文档段中每条记录的大小、记录文档中最多能够存储的条目数、已经创建的数目等信息，

SQL>select type,record_size,records_total,records_used from v$controlfile_record_section;

7.2 管理重做日志文件

7.2.1 重做日志文件概述

重做日志文件用于记载事务操作所引起的数据变化，当执行DDL或DML操作时，由LGWR进程将缓冲区中与该事务相关的重做记录全部写入重做日志文件。当丢失或损坏数据库汇总的数据时，Oracle会根据重做日志文件中的记录恢复丢失的数据。

在创建Oracle数据库的过程中，默认创建3个重做日志文件组，每个日志文件组中包含两个日志文件成员，并且每个日志文件组都有内部序号，Oracle按照序号从小到大的顺序向日志文件组中写入日志信息。当一个重做日志文件组写满后，后台进程

LGWR开始写入下一个重做日志文件组；当LGWR进程将所有的日志文件都写过一遍之后，它将再次转向第一个日志文进组重新覆盖。当前正在被LGWR进程写入日志记录的某组重做日志文件称为“联机重做日志文件”；

查看重做日志文件的状态: v$logfile

7.2.2 增加日志组及其成员

（1）添加新的重做日志文件组

增加日志文件组可以使用ALTER DATABASE ADD LOGFILE语句。

通常情况下，重做日志文件的大小最好在10MB到50MB之间，Oracle默认的日志文件大小是50MB。

如果需要为新创建的重做日志组指定编号，则需要在ALTER DATABASE ADD LOGFILE语句后添加GROUP关键字。

使用日志组编号必须是连续的，不能跳跃。

如果要创建的日志文件已经存在，则必须在ALTER DATABASE ADD LOGFILE语句后面使用REUSE关键字，这样可以覆盖已有的操作系统文件。在使用了REUSE的情况下，不能再使用

使用SIZE子句设置重做日志文件的大小，重做日志文件的大小将由已存在日志文件的大小决定。

（2）创建日志成员文件

为重做日志组添加新成员，需要使用ALTER DATABASE ADD LOG MEMBER语句。

7.2.3 删除重做日志

（1）删除日志成员

要删除一个日志成员文件，可以使用“ALTER DATABASE DROP LOGFILE MEMBER”语句。

（2）删除日志文件组

在删除日志组时，必须注意以下几点：

1）无论日志组中有多少个成员，一个数据库至少需要两个日志组，删除时不能超过这个限制；

2）只能删除处于INACTIVE状态的日志组。如果要删除处于CURRENT状态的重做日志组，必须执行一个手动切换日志，将它切换到INACTIVE状态；

3）如果数据库处于归档模式，在删除重做日志组之前必须确定它已经被归档；

若要删除一个重做日志组，需要使用ALTER DATABASE DROP LOGFILE语句。

删除数据库中编号为5的日志组，SQL>alter database drop logfile group 5;

与删除指定的日志文件相同，删除日志文件组也只是在数据字典和控制文件中将日志文件组的信息删除，而对应的物理文件并没有删除，若要删除，可以采取手动删除的方式。

（3）清空重做日志文件

清空日志文件，使用ALTER DATABASE CLEAR LOGFILE语句。

7.2.4 更改重做日志的位置或名称

将日志文件移动到其他地方的步骤：

（1）关闭数据库；

（2）手动复制源文件到目标位置，甚至可以对复制后的文件进行重命名；

（3）再次启动数据库实例，加载数据库，但不打开数据库：startup mount;

（4）使用ALTER DATABASE RENAME FILE语句重新设置重做日志文件的路径及名称。

（5）打开数据库：alter database open

7.2.5 查看重做日志信息

字典视图说明

V$LOG 显示控制文件中的日志文件信息

V$LOGFILE 日志组合日志成员信息

V$LOG_HISTORY 日志历史信息

日志组状态：CURRENT 、INACTIVE、ACTIVE。

FIRST_CHANGE：重做日志组上一次写入时的系统变更码（SCN），也称为检查点号。

7.3 管理归档日志文件

Oracle使用“归档日志文件”来提前一步保存一些即将被覆盖掉的重做日志记录。

7.3.1 日志模式分类

Oracle有2种日志模式：非归档模式（NOARCHIVELOG）和归档模式（ARCHIVELOG）

在非归档日志模式下，原日志文件的内容会被新的日志内容所覆盖；

在归档日志模式下，Oracle会首先对原日志文件进行归档存储，且在归档未完成之前不允许覆盖原有日志；

7.3.2 管理归档操作

要实现数据库在非归档模式与归档模式之间进行切换，可以使用ALTER DATABASE ARCHIVIELOG或NOARCHIVELOG语句。

（1）日志模式切换

由非归档模式切换成归档模式的具体步骤：

1）查看当前日志模式

SQL>select log_mode from v$database;

2) 关闭并重启数据库

shutdown immediate

startup mount

3)改变日志模式

SQL>alter database archivelog;

4)打开数据库

SQL>alter database open;

（2）配置归档进程

通过修改系统初始化参数"LOG_ARCHIVE_MAX_PROCESSES"可以调整启动ARCN进程数量，

SQL>alter system set log_archive_max_processes=3;

7.3.3 设置归档文件位置

归档日志文件保存的位置称为归档目标，归档目标在初始化参数LOG_ARCHIVE_DEST_n中进行设置，其中n为1~10的整数，即可以为数据库指定1~10个归档目标。

在设置归档目标时，可以指定本地机器作为归档目标，也可以选择远程服务器作为归档目标，

（1）本地归档目标(LOCATION)

SQL>alter system set log_archive_dest_1='location=D:\OracleFiles\archive1';

使用初始化参数LOG_ARCHIVE_DEST_n设置归档位置时，可以指定OPTIONAL、MANDATORY或REOPEN选项；

OPTIONAL：默认选项，使用该选项时，无论归档操作是否执行成功，都可以覆盖重做日志文件；

MANDATORY：该选项用于强制进行归档操作，使用该选项时，只有在归档成功后，重做日志文件才能被覆盖；

REOPEN：这是一个属性选项，它用于设定重新归档的时间间隔，默认值为300秒。REOPEN属性必须跟在MANDATORY选项后。

SQL>alter system set log_archive_dest1='location=D:\OracleFiles\archive1 optional';

SQL>alter system set log_archive_dest2='location=D:\OracleFiles\archive2 mandatory reopen=400';

可以使用初始化参数LOG_ARCHIVE_DEST_STATE_n设置归档位置是否可用。如果设置该参数为ENABLE，表示激活相应的归档位置；如果设置该参数为DEFER，表示禁用相应的归档位置；

SQL>alter system set log_archive_dest_state_1=defer;

在设置禁用归档位置时，要求参数LOG_ARCHIVE_DEST_STATE_n的序号n的值要大于LOG_ARCHIVE_MIN_SUCCEED_DEST的参数值。

（2）远程归档目标（SERVER）

SQL>alter system set log_archive_dest1='server=MRKJ';

MRKJ是一个远程的服务器名。

7.3.4 查看归档日志信息

查看归档日志信息有2种方法：使用数据字典和动态性能视图，使用ARCHIVE LOG LIST。

（1）使用数据字典和动态性能视图

字典视图说明

V$DATABASE 用于查询数据库是否处于归档模式

V$ARCHIVE_LOG 包含控制文件中所有已经归档的日志信息

V$ARCHIVE_DEST 包含所有归档目标信息

V$ARCHIVE_PROCESSESS 包含已启动的ARCN进程状态信息

V$BACKUP_REDOLOG 包含所有已经备份的归档日志信息

（2）使用ARCHIVE LOG LIST命令

SQL>archive log list;

********************************************************************************************************************

八、管理表空间和数据文件

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

创建表空间时必须创建数据文件，增加数据文件时也必须指定表空间。

查看表空间与对应的数据文件的相关信息，可从DBA_DATA_FILES数据字典获得。

SQL> select tablespace_name,file_name,bytes from dba_data_files order by tablespace_name;

8.2 Oracle11g的默认表空间

默认表空间是指在创建Oracle数据库时，系统自动创建的表空间，这些表空间用于存放Oracle系统内部数据和提供样例所需要的逻辑空间。

表空间说明

EXAMPLE 若安装时选择“实例方案”，则此表空间存放各样例的数据

SYSAUX SYSTEM表空间的辅助空间。主要用于存储除数据字典外的其他数据对象，这样可以减少SYSTEM表空间的负担。

SYSTEM 存放数据字典、包括表、视图、存储过程的定义等

TEMP 存放SQL语句处理的表和索引的信息

UNDOTBS1 存储撤销数据的表空间

USERS 通常用于存放“应用系统”所使用的数据库对象

8.2.1 SYSTEM表空间

SYSTEM表空间主要存放SYS用户的各个对象和其他用户的少量对象。

用户可以从DBA_SEGMENTS数据字典中查询到某个表空间所存放的数据对象及其类型和拥有者。

SQL> select segment_type,segment_name,owner from dba_segments where tablespace_name='USERS';

8.2.3 SYSAUX表空间

查询SYSAUX表空间的相关信息：

SQL> select owner,count(segment_name) from dba_segments where tablespace_name='SYSAUX' group by owner;

8.3 创建表空间

8.3.1 创建表空间的语法

CREATE [SMALLFILE/BIGFILE] TABLESPACE tablespace_name

DATAFILE '/path/filename' SIZE num[k/m] REUSE

[,'/path/filename' SIZE num[k/m] REUSE]

[,...]

[AUTOEXTEND [ON|OFF] NEXT num[k/m]

[MAXSIZE num[k/m] | UNLIMITED]

[MININUM EXTENT num[k/m]]

[DEFAULT STORAGE storage]

[ONLINE | OFFLINE]

[LOGGING | NOLOGGING]

[PRERMANENT | TEMPORARY]

[EXTENT MANAGEMENT DICTIONARY| LOCAL [AUTOALLOCATE| UNIFORM SIZE num[k/m]]]

SMALLFILE|BIGFILE：表示创建的是小文件表空间还是大文件表空间；

REUSE：表示若该文件存在，则清除该文件再重新建立该文件，若该文件不存在，则创建该文件。

AUTOEXEND [ON|OFF] NEXT : 表示数据文件为自动扩展（ON）或非自动扩展（OFF），如果是自动扩展，则需要设置NEXT的值；

MAXSIZE : 表示当数据文件自动扩展时，允许数据文件扩展的最大长度字节数，如果指定UNLIMITED关键字，则不需要指定字节长度；

MINIMUN EXTENT : 指定最小的长度，由操作系统和数据库的块决定；

ONLINE| OFFLINE：创建表空间时可以指定为在线或离线；

PERMANENT|TEMPORARY: 指定创建的表空间是永久表空间或临时表空间，默认为永久性表空间；

LOGGING|NOLOGGING:指定该表空间内的表在加载数据时是否产生日志，默认为产生日志（LOGGING）。即使设置NOLOGGING，但在进行INSERT、UPDATE和DELETE操作时，Oracle仍会操作信息记录到Redo Log Buffer中；

EXTENT MANAGEMENT DICTIONARY | LOCAL :指定表空间的扩展方式是使用数据字典管理还是本地化管理，默认为本地化管理。Oracle不推荐使用数据字典管理表空间；

AUTOALLOCATE|UNIFORM SIZE: 如果采用本地化管理表空间，在表空间扩展时，指定每次盘区扩展的大小是由系统自动指定还是按照等同大小进行。若是按照等同大小进行，则默认每次扩展的大小为1MB；

DEFAULT STORAGE:指定以后要创建的表、索引及簇的存储参数值，这些参数将影响以后表等的存储参数值；

'/path/filename' ：该参数表示数据文件的路径与名字；

8.3.2 通过本地化管理方式创建表空间

本地管理表空间具有以下优点:

（1）使用本地化的扩展管理功能（包括自动大小和等同大小两种），可以避免发生重复的空间管理操作；

（2）本地化管理的自动扩展（AUTOALLOCATE）能够跟踪临近的自由空间，这样可以消除结合自由空间的麻烦。本地化的扩展大小可以由系统自动确定，也可以选择所有扩展有同样的大小（UNIFORM）。通常使用EXTENT MANAGEMENT LOCAL子句创建本地化的可变表空间；

创建本地化管理方式的表空间，都是用extent management local子句。

当创建扩展大小等同的表空间时，使用uniform关键字，并指定每次扩展时的大小；

当创建扩展大小为自动管理时，使用autoallocate关键字，并且不需要指定扩展时的大小；

8.3.3 通过段空间管理方式创建表空间

段空间管理方式是建立在本地化空间管理方式基础之上的，使用“SEGMENT SPACE MANAGEMENT MANUAL/LOCAL”语句；

段空间管理分为手动段和自动段两种空间管理方式；

（1）手动段空间管理方式

手动段空间管理方式，使用自由块列表和PCT_FREE与PCT_USED参数来标识可供插入操作使用的数据块；

（2）自动段空间管理方式

如果采用自动段空间管理方式，那么数据库会使用位图而不是自由列表来标识哪些数据块可以用于插入操作，哪些数据块需要从自由块列表上将其取下。此时，表空间段

PCT_FREE和PCT_USED参数会被自动忽略。

自由段空间管理方式比手动段空间管理方式性能更好，所以是创建表空间时的首选方式。

使用自由段空间管理方式，需要注意的地方：

1）自由段空间管理方式不能用于创建临时表空间和系统表空间；

2）Oracle本身推荐使用自动段空间管理方式管理永久表空间，但其默认情况下却是MANUAL管理方式，所以在创建表空间时需要明确指定为AUTO。

8.3.4 创建非标准块表空间

在Oracle数据库中，通常的块大小为8192字节，即8KB。但Oracle11g允许创建块大小与基本块不同的表空间，块大小可由创建表空间时的blocksize参数指定，这样有利于存储不同大小的对象，但需要注意：

（1）表空间的非标准块的大小为基本块的倍数，例如，大小为16KB,64KB,128KB;

（2）Oracle11g通常使用SGA自动共享内存管理，因此需要设置初始化参数db_16k_cache_size=16K

（3）这种块较大的表空间通常用来存放大对象（LOB）类型；

8.3.5 建立大文件表空间

Oracle11g版本引进了一个新的表空间类型----大文件(BIGFILE).

大文件表空间存放在一个单一的数据文件中，并且它需要更大的磁盘容量来存放数据。

创建大文件表空间，只需要在CREATE 语句中使用BIGFILE关键字即可；

SQL>create bigfile tablespace tbs_test_big datafile '/u01/oracle/app/oradata/ORCL/datafile5.dbf' size 2g;

当需要重新设置大文件表空间的大小时，不需要标识数据文件的具体路径和名称，只需要使用ALTERTABLESPACE命令指定大文件表空间的名称，即可修改大小；

在创建表空间的语法中使用size来标识数据文件的大小，在修改表空间时，要使用resize关键字来重置数据文件的大小。

SQL>alter tablespace tbs_test_big resize 1g; #修改表空间

SQL>alter database datafile '/u01/oracle/app/oradata/ORCL/datafile5.dbf' resize 100m; #修改数据文件

8.4 维护表空间与数据文件

8.4.1 设置默认表空间

Oracle如果不指定表空间时，则默认的临时表空间是TEMP，默认的永久表空间是SYSTEM、

Oracle建议将非SYSTEM表空间设置为应用系统的默认永久表空间，并且将非TEMP临时表空间设置为应用系统的临时表空间。

更改默认临时表空间需要使用ALTER DATABASE DEFAULT TEMPRORY TABLESPACE语句，更改默认永久表空间需要使用ALTER DATABASE DEFAULT TABLESPACE语句。

SQL>alter database default temprory tablespace temp1;

SQL>alter database default tablespace temp1;

8.4.2 更改表空间的状态

表空间只有只读和可读写2种状态；

只读状态：用户不可进行INSERT、UPDATE、DELETE等操作，但可进行索引和目录的删除操作；

设置表空间为只读状态，需满足如下条件：

（1）该表空间必须为ONLINE状态；

（2）该表空间不能包含任何回滚段；

（3）该表空间不能在归档模式下；

更改表空间的读写状态需要使用ALTER TABLESPACE...READ|ONLY READ WRITE语句。例如：

SQL>alter tablespace tbs_test1 read only; #修改表空间tbs_test1为只读状态；

SQL>alter tablespace tbs_test1 read write; #修改表空间tbs_test1为可读写状态；

8.4.3 重命名表空间

在Oracle11g之前，表空间无法重命名；

数据库管理员只能对普通的表空间进行更名，不能对SYSTEM和SYSAUX表空间进行重命名，也不能对处于OFFLINE状态的表空间进行重命名；

重命名表空间需要使用ALTER TABLESPACE...RENAME TO语句；

SQL>alter tablesapce tbs_test1 rename to tbs_test1_new ;

8.4.4 删除表空间

如要删除表空间，需要用户具有DROP TABLESPACE权限；

默认情况下，Oracle系统不采用Oracle Managed Files 方式管理文件，这样删除表空间实际上仅是从数据字典和控制文件中将该表空间的有关信息清除掉，并没有真正删除该

表空间包含的所有物理文件。因此，在执行删除删除表空间之后，还需要手动删除该空间包含的所有物理文件；

当Oracle系统采用Oracle Managed Files方式管理文件时，删除某个表空间后，Oracle系统将自动删除该表空间包含的所有物理文件。

删除表空间使用DROP TABLESPACE命令，语法如下：DROP TABLESPACE tbs_name[INCLUDING CONTENTS] [CASCADE CONSTRAINTS]

tbs_name：表示要删除的表空间名称；

INCLUDING CONTENTS：表示删除表空间的同时删除表空间中的数据。如果不指定INCLUDING CONTENTS参数，而该表空间又存在数据时，则Oracle会提示错误；

CASCADE CONSTRAINTS：表示当删除当前表空间时也删除相关的完整性限制。完整性限制包括主键及唯一索引等。如果完整性存在，而没有CASCADE CONSTRAINTS参数，则Oracle会提示错误，并不会删除表空间；

SQL> drop tablespace tbs_test1_1 including contents cascade constraints; #删除表空间tbs_test_1及其包含的所有内容；

8.4.5 维护表空间中的数据文件

维护表空间中的数据文件主要包括：向表空间中添加数据文件，从表空间中删除数据文件和对表空间中的数据文件进行自动扩展设置；

（1）向表空间中添加数据文件

#向users表空间添加一个数据文件datafile5.dbf，该文件支持自动扩展，扩展能力为每次扩展5MB, 并且该文件的最大空间不受限制；

SQL>alter tablespace users add datafile '/u01/oracle/app/oradata/ORCL/datafile5.dbf' 10m autoextend on next 5m maxsize unlimited;

（2）从表空间中删除数据文件

要删除数据文件，需要使用ALTER TABLESPACE...DROP DATAFILE语句；

从表空间users中删除数据文件datafile5.dbf

SQL>alter tablespace users drop datafile '/u01/oracle/app/oradata/ORCL/datafile5.dbf'

（3）对数据文件的自动扩展设置

可以使用AUTOEXTEND ON命令使数据文件在使用中能根据需要自动扩展，用户可通过如下4种方式设置数据文件的自动扩展功能：

1）在CREATE DATABASE语句中设置；

2）在ALTER DATABASE语句中设置；

3）在CREATE TABLESPACE语句中设置；

4）在ALTER TABLESPACE语句中设置；

一般使用后3种方式；

首先查询TBS_TEST_2表空间中的数据文件是否为自动扩展，若不是，修改为自动扩展，扩展量为10MB，并且最大扩展空间不受限制。

SQL>select file_name,autoextensible from dba_data_files where tablespace_name='TBS_TEST_2';

#修改为自动扩展

SQL>alter database datafile '/u01/oracle/app/oradata/ORCL/datafile5.dbf' autoextend on next 10m maxsize unlimited;

8.5 管理撤销表空间

8.5.1 撤销表空间的作用

撤销表空间也称UNDO表空间；

撤销段的作用：使读写一致、可以回滚事务、事务恢复、闪回操作

8.5.2 撤销表空间的初始化参数

和UNDO表空间有关的参数如下：

（1）UNDO_TABLESPACE

该初始化参数用于指定例程所要使用的UNDO表空间，使用自动UNDO管理模式时，通过配置该参数可以指定例程所要使用的UNDO表空间；

（2）UNDO_MANAGEMENT

该初始化参数用于指定UNDO数据的管理模式，如果为AUTO，则为自动撤销管理模式；如果为MANUAL，则为回滚段管理模式；

（3）UNDO_RETENTION

该初始化参数用于控制UNDO数据的最大保留时间，默认为900秒；

用户想要查询当前实例所设置的UNDO表空间的参数，可以通过SHOW PARAMETER 命令来完成。

8.5.3 撤销表空间的基本操作

（1）创建UNDO表空间

创建UNDO表空间需要使用CREATE UNDO TABLESPACE语句。

SQL>create undo tablespace undo_tbs_1 datafile '/u01/oracle/app/oradata/ORCL/datafile5.dbf' size 100m; #创建一个UNDO表空间，指定数据文件大小为100M;

在创建表空间时，需要注意：

1）UNDO表空间对应的数据文件大小通常由DML操作可能产生的最大数据量来确定，通常该数据文件的大小至少为1GB；

2）由于UNDO表空间只用于存放撤销数据，所以不要在UNDO表空间内建立任何数据对象；

（2）修改UNDO表空间

当事务用尽了UNDO表空间后，可以使用ALTER TABLESPACE...ADD DATAFILE语句添加新的数据文件；

当UNDO表空间所在的磁盘填满时，使用ALTER TABLESPACE...RENAME DATAFILE语句将数据文件移动到其他磁盘上；

当数据库处于ARCHIVELOG模式时，可以使用ALTER TABLESPACE...BEGIN BACKUP/END BACKUP语句备份UNDO表空间；

（3）切换UNDO表空间

启动例程并打开数据库后，同一时刻指定例程只能使用一个UNDO表空间，切换UNDO表空间是指停止例程当前使用的UNDO表空间，启动其他UNDO表空间；

SQL>alter system set undo_tablespace=undo_tbs_1; #将当前系统默认的表空间切换到表空间undo_tbs_1;

（4）删除UNDO表空间

删除UNDO表空间使用DROP TABLESPACE语句；

当例程正在使用UNDO表空间时，是不能删除的；所以，删除前需先切换到其他表空间后，再做删除；

SQL>drop tablespace undo_tbs_1;

（5）查询UNDO表空间的信息

UNDO表空间的信息，包括如下几种：

●当前例程正在使用的UNDO表空间

SQL>show parameter undo_tablespace;

●实例的所有UNDO表空间

SQL>select tablespace_name from dba_tablespaces where contents='UNDO';

●UNDO表空间的统计信息

可通过查询动态性能视图V$UNDOSTAT收集UNDO统计信息；

SQL>select to_char(begin_time,'hh24:mi:ss') as 开始时间,to_char(end_time,'hh24:mi:ss') as 结束时间,undoblks as 回退块数 from v$undostat order by begin_time;

●显示UNDO段统计信息

使用自动UNDO管理模式时，Oracle会在UNDO表空间上建立10个UNDO段；

V$ROLLNAME: 显示所有联机UNDO段的名称；

V$ROLLLISTAT: 显示UNDO段的统计信息；

●显示活动事务信息

V$SESSION：显示会话详细信息；

V$TRANSACTION：显示事务详细信息；

●显示UNDO区信息

DBA_UNDO_EXTENTS：查询UNDO表空间中所有区的详细信息；

8.6 管理临时表空间

8.6.1 临时表空间概述

临时表空间是一个磁盘空间，主要用于内存排序区不够而必须将数据写入到磁盘的那个逻辑区域；该空间在排序操作完成后可以由Oracle系统自动释放；

需要用到临时表空间的几种操作：

●SELECT DISTINCT 不重复检索

●UNION联合查询

●MINUS计算

●ANALYZE分析

●连接两个没有索引的表

8.6.2 创建临时表空间

使用CREATE TEMPORARY TABLESPACE语句创建临时表空间；

SQL>create temporary tablespace temp_01 tempfile '/u01/oracle/app/oradata/ORCL/datafile5.dbf' size 300m; #创建一个空间大小为300M的临时表空间；

SQL>alter database default temporary temp_01; #设置temp_01为默认临时表空间；

8.6.3 查询临时表空间的信息

Oracle11g将临时表空间和相应的临时文件信息都存放在DBA_TEMP_FILES数据字典中；

V$TEMPFILES : 查询临时表空间的使用情况；

8.6.4 关于临时表空间组

用户使用临时表空间组来管理临时数据的作用：

●避免因大量的排序数据而导致单一临时表空间不足；

●当一个用户同时有多个会话时，可以使用组中的不同的临时表空间；

●使并行的服务器在单一节点上可以使用多个临时表空间；

（1）创建临时表空间组

主要使用group子句；

SQL>create temporary tablespace tp1 tempfile '/u01/oracle/app/oradata/ORCL/datafile5.dbf' size 10m tablespace group group1;

（2）转移临时表空间到另一个组

主要使用alter tablespace子句；

SQL >alter tablespace tp1 tablespace group group3; #将临时表空间tp1转移到组group3中；

SQL>select * from dba_tablespace_groups where group_name='GROUP3'; #查询组group3中所包含的临时表空间

（3）把临时表空间组分配给指定的用户使用

主要使用alter user子句；

SQL>alter user hr temporary tablespace group3;

（4）设置默认的临时表空间组

主要使用alter database子句

SQL>alter database orcl default temporary tablespace group3;

（5）删除临时表空间组

删除临时表空间组主要通过删除组成临时表空间组的所有临时表空间来实现；

SQL>drop tablespace tp1 including contents and datafiles; #删除临时表空间tp1;

********************************************************************************************************************

九、数据表对象

9.2 创建数据表

Oracle提供了多种内置的列的数据类型，包括数值类型、字符类型、日期时间类型、LOB类型与ROWID类型；

（1）日期时间类型

常用获取日期的方法：SYSDATE函数

SQL> select sysdate from dual;

可以使用TO_CHAR函数将数值或字符串转换成DATE类型；

Oracle默认的日期和时间格式由初始化参数NLS_DATE_FORMAT指定，一般为DD-MM-YY.

（2）LOB类型

LOB数据类型用于大型的、未被结构化的数据，例如二进制文件、图片文件和其他类型的外部文件。

LOB类型的数据可以直接存储在数据库内部，也可以将数据存储在外部文件中，而将指向数据的指针存储在数据库中。

LOB数据类型分为BLOB、CLOB和BFILE数据类型。

●BLOB类型：用于存储二进制对象。能够存储最大128MB的二进制对象；例如，图像、音频文件、视频文件；

●CLOB类型：用于存储字符格式的大型对象。能够存储最大128MB的对象。Oracle首先将数据转换成Unicode格式的编码，然后再将它存储在数据库中；

●BFILE类型：用于存储二进制格式的文件。可以将最大128MB的二进制文件作为操作文件存储在数据库外部，文件的大小不能超过操作系统的限制；BFILE类型的字段中仅保存二进制文件的指针，

并且BFILE字段是只读的，不能通过数据库对其中的数据进行修改；

（3）ROWID数据类型

ROWID数据类型被称为“伪列类型”，用于在Oracle内部保存表中的每条记录的物理地址。在Oracle内部通过ROWID来定位所需记录的。

Oracle自动为每一个表建立一个名称为ROWID的字段，可以对这个字段进行查询，更新和删除等操作，设置利用ROWID来访问表中的记录以获得最快的操作速度。

9.2.3 数据表的特性

（1）存储参数

在创建表时，可以通过使用STORAGE子句来设置存储参数，这样可以控制表中盘区的分配管理方式。

对于本地化管理的表空间而言，如果指定盘区的管理方式为AUTOALLOCATE，则可以在STORAGE子句中指定INITAL、NEXT和MINEXTENTS这3个存储参数，Oracle将根据

这3个存储参数的值为表分配的数据段初始化盘区大小，以后盘区的分配由Oracle自动管理。

如果指定的盘区管理方式为UNIFORM，这时不能为表指定任何STORAGE子句，盘区的大小将是统一大小。

参数NEXT用于指定为存储表中的数据分配的第二个盘区大小。该参数在字典管理的表空间中起作用，而在本地化管理的表空间中不起作用；因为随后分配的盘区由Oracle自动决定其大小；

参数MINEXTENTS用于指定允许为表中的数据所分配的最小盘区数目，同样在本地化管理表空间时不起作用；

INITAL参数，用于为表指定分配的第一个盘区大小，以KB或MB为单位。

SQL> select initial_extent from user_tables where table_name='students'; #查询表students的存储参数情况；

（2）数据块管理参数

用户可以设置的数据块参数分为如下几类：

●PCTFREE和PCTUSED

这2个参数用于控制数据块中空闲空间的使用方法。

PCTFREE用于指定数据库中必须保留的最小空闲空间比例，当数据块达到PCTFREE参数的限制后，该数据块将被标记为不可用，默认值为10.

PCTUSED用于设置数据块是否可用的界限。已经占用的存储空间必须低于PCTUSED设置的比例。

●INITRANS参数

该参数用于指定一个数据块所允许的并发事务数目。

●缓存参数

SQL> select table_name ,cache from user_tables where table_name='STUDENTS'; #查询某个表是否启用缓存参数

9.3 维护数据表

普通用户只能对自己模式中的表进行修改，如果要对任何模式中的表进行修改操作，用户必须具有ALTER ANY TABLE系统权限；

9.3.1 增加和删除字段

可以使用ALTER TABLE...ADD语句向表中添加新的字段。

SQL>alter table student_1 add(province varchar2(10)); #给student_1表新增一个字段province;

可以使用ALTER TABLE...DROP语句删除表中的指定字段。但不能删除表中所有的字段，也不能删除SYS模式中任何表的字段。

如果仅需要删除一个字段，则必须在字段名前指定COLUMN关键字。

SQL>alter table student_1 drop column province; #删除表student_1的province字段；

如果要在一条语句中删除多个列，则需要将删除的字段名称放在括号中，各个字段之间用逗号隔开，这时不能使用COLUMN关键字。

SQL>alter table students_1 drop(sex,age);

9.3.2 修改字段

可以使用ALTER TABLE...MODIFY语句修改字段，语法如下：alter table table_name modify column_name column_property

table_name：表示要修改的列所在的表名称

column_name：要修改的列名称

column_peoperty：要修改列的属性，包括数据类型的长度、数字列的精度、列的数据类型和列的默认值；

SQL>alter table students_1 modify departno varchar2(4); #将departno的字段长度由2变成4；

9.3.3 重命名表

使用ALTER TABLE...RENAME语句，语法：alter table table_old_name rename to table_new_name;

table_old_name：表示原表名称

table_new_name：表示新表名称

SQL>alter table students_1 rename to student_2; #将students_1重命名为students_2;

9.3.4 改变表空间和存储参数

（1）修改表空间

若要将一个“非分区”表移动到一个新的表空间，使用ALTER TABLE...MOVE TABLESPACE语句。

SQL>alter table student1 move tablespace tbsp2; #将表student1移动到表空间tbsp2;

（2）修改存储参数

修改存储参数，主要是指修改数据块参数PCTFREE和PCTUSED，若改变了这2个参数值，则表中所有的数据块都将受到影响；一般使用ALTER TABLE语句；

SQL>alter table students1 pctfree 25 pctused 45;

9.3.5 删除表

用户只能删除自己模式中的表，如果要删除其他模式中的表，则必须具有DROP ANY TABLE系统权限。删除表用DROP TABLE语句，语法如下：

drop table table_name [cadcade constraints];

如果该表存在约束、关联的视图和触发器，则必须使用"cascade constraints"这个可选的子句才能将其删除；

在删除一个表的结构时，通常Oracle会执行如下操作：

●删除表中所有的数据；

●删除与该表相关的所有索引和触发器；

●如果有视图或PL/SQL过程依赖于该表，这些视图或PL/SQL过程将被置于不可用状态；

●从数据字典中删除该表的定义；

●回收为该表分配的存储空间；

SQL>drop table student_5 cascade constraints; #删除表student_5以及所有引用这个表的视图、约束或触发器；

当某个表被删除之后，实际上它并没有被彻底删除，而是把该表放到了回收站中，这样当用户需要还原该表时，可以使用FLASHBACK TABLE语句进行还原。

使用闪回技术还原被删除表的步骤：

●首先确认表是否删除；

SQL>select * from student5;

●查询数据字典视图RECYCLEBIN来了解该表是否在回收站中；

SQL>select object_name,original_name from recyclebin where original_name='STUDENT5';

●使用FLASHBACK TABLE语句恢复被删除的student5表；

SQL>flashback table student5 to before drop;

●查看表是否已恢复；

SQL>select * from student5;

9.3.6 修改表的状态

Oracle11g中，用户可以将表置于READ ONLY状态。处于该状态的表不能执行DML和某些DDL操作；

SQL>alter table student1 read only; #将student1置于只读read only状态；

9.4 数据完整性和约束性

在Oracle11g系统中，约束的类型包括非空约束、主键约束、唯一约束、外键约束、检查约束和默认约束。

9.4.1非空约束

非空约束就是限制必须为某个列提供值。空值是不存在的值，它既不是数字0，也不是空字符串；

在创建完表之后，可以使用ALTER TABLE MODIFY 语句为已经创建的表删除或重新定义NOT NULL约束。

SQL>alter table Books modify bookname not null; #设置非空约束

SQL>alter table Books modify bookname null; #删除非空约束

9.4.2主键约束

主键约束用于唯一标识表中每一行记录。

在一个表中，最多只能有一个主键约束，主键约束既可以由一个列组成，也可以由两个或两个以上的列组成。

对于表中的每一行数据，主键约束列都是不同的，主键约束同时也具有非空约束的特性。

如果主键约束由一列组成时，该主键约束被称为行级约束；如果主键约束由两个或两个以上的列组成时，则该主键约束称为表级约束。

若要设置某个或某些列为主键约束，通常使用CONSTAINT...PRIMARY KEY语句定义。

SQL>create table Books_1

(

BookNo number(4) not null,

BookName varchar2(20),

Author varchar2(10),

SalePrice number(9,2),

PublisherNo varchar2(4) not null,

PublishDate date,

ISBN varchar2(20) not null,

constraint BOOK_PK primary key (BookNo)

);

如果表在创建时未定义主键约束，用户可以使用ALTER TABLE...ADD CONSTRAINT...PRIMARY KEY语句为该表添加主键约束。

SQL>alter table Books add constraint Books_PK primary key(BookNo);

SQL>create table Books_2

(

BookNo number(4) primary key,

BookName varchar2(20),

Author varchar2(10),

SalePrice number(9,2),

PublisherNo varchar2(4) not null,

PublishDate date,

ISBN varchar2(20) not null

);



可以使用ALTER TABLE...ADD语句添加由系统自动分配名称的主键约束。

SQL>alter table Books add primary key(BookNo);

可以使用ALTER TABLE...DROP语句来删除主键约束；

SQL>alter table Books_1 drop constraint BOOK_PK;

9.4.3唯一性约束

唯一性约束的列允许空值。

若要设置某个列为UNIQUE约束，使用CONSTRAINT...UNIQUE标记该列。

如果UNIQUE约束的列有值，则不允许重复，但是可以插入多个NULL值，即该列的空值可以重复。

可以使用ALTER TABLE,,,ADD CONSTRAINT...UNIQUE语句为现有的表添加UNIQUE约束。

SQL>alter table members add constraint Email_UK unique (email);

可以使用ALTER TABLE...DROP CONSTRAINT语句来删除UNIQUE约束。

SQL>alter table members drop constraint Email_UK;

9.4.4外键约束

外键是指“当前表”（外键表）引用“另外一个表”（被引用表）的某个列或某几个列，而“另外一个表”中被引用的列必须具有主键约束或者唯一性约束。在“另外一个表”中，被引用列中不存在的数据不能出现在

“当前表”对应的列中。一般情况下，当删除被引用表中的数据时，该数据也不能出现在外键表的外键列中。如果外键列存储了被引用表中将要被删除的数据，那么对被引用表的删除操作将失败；

如果外键表的外键列与被引用表的被引用列列名相同，则为外键表定义外键列时可以省略“reference”关键字后面的列名称。

在定义外键约束时，可以通过关键字ON指定引用行为的类型。当尝试删除被引用表中的一条记录时，通过引用行为可以确定如何处理外键表中的外键列，引用行为的类型包括：

●在定义外键约束时，如果使用了关键字NO ACTION，那么当删除被应用表中被引用类的数据时将违反外键约束，该操作将被禁止执行，这也是外键的默认引用类型；

●在定义外键约束时，如果使用了关键字SET NULL,那么当被引用表中被引用列的数据被删除时，外键表中外键列被设置为NULL，要使这个关键字起作用，外键列必须支持NULL值。

●在定义外键约束时，如果使用了CASCADE关键字，那么当被引用表中被引用列的数据被删除时，外键表中对应的数据也将被删除，这个删除方式称做“级联删除”。

9.4.5 禁用和激活约束

禁用约束的原因：因为约束的存在会降低插入和更改数据的效率，系统必须确认这些数据是否满足定义的约束条件。

（1）在定义约束时禁用

在使用CREATE TABLE或ALTER TABLE语句定义约束时，可以使用DISABLE来禁用约束。

（2）禁用已经存在的约束

对于已存在的约束，可以使用ALTER TABLE...DISABLE CONSTRAINT 语句禁用该约束。

SQL>alter table employees_temp disable constraint temp_departid_fk; #禁用表employees_temp中的约束temp_departid_fk

（3）激活约束

可以在ALTER TABLE语句中使用ENABLE CONSTRAINT子句，语法如下：alter table table_name enable [novalidate | validate] constraint con_name;

table_name：表示要激活约束的表的名称；

novalidate：表示在激活约束时不验证表中已经存在的数据是否满足约束，如果没有使用该关键字，或者使用VALIDATE关键字，则在激活约束时系统将验证表中的数据是否满足约束的定义；

9.4.6 删除约束

可以使用带DROP CONSTRAINT子句的ALTER TABLE语句删除约束，删除约束语法: alter table table_name drop constraint con_name;

table_name：要删除约束的表名称

con_name：要删除的约束名称

********************************************************************************************************************

十、其他数据对象

10.1 索引对象

10.1.1 索引概述

【索引分类】

按索引数据的存储方式： B树索引、位图索引、反向索引、基于函数索引；

按索引列的唯一性：唯一索引、非唯一索引；

按索引列的个数：单列索引、复合索引

建立索引必须注意几点：

●索引应该建立在WHERE子句频繁引用表列上，如果在大表上频繁使用某列或某几个列作为条件执行索引操作，并且检索行数低于总行数15%，那么应该考虑在这些列上建立索引；

●如果经常需要基于某列或某几个列执行排序操作，那么在这些列上建立索引可以加速排序速度；

●限制表的索引个数。索引主要用于加快查询速度，但会降低DML操作的速度。所以，规划索引时，必须仔细权衡查询和DML的需求；

●指定索引块空间的使用参数；

●将表和索引部署到相同的表空间，可以简化表空间的管理；将表和索引部署到不同的表空间，可以提供访问性能；

●当在大表上建立索引时，使用NOLOGGING选项可以最小化重做记录。使用NOLOGGING选项可以节省重做日志空间，降低索引创建时间、提高索引并行建立的性能；

●不要在小表上建立索引；

●为了提高多表连接的性能，应该在连接列上建立索引；

10.1.2 创建索引

创建索引使用CREATE INDEX语句。

若要以其他用户建立索引，则要求用户必须具有CREATE ANY INDEX系统权限或相应表的INDEX对象权限。

（1）建立B树索引

默认情况下，B树索引中的数据是以升序方式排列的；

SQL>create index emp_deptno_index on emp(deptno) pctfree 25 tablespace users; #为emp表的deptno列创建索引；

（2）建立位图索引

SQL>create bitmap index emp_salary_bmp on employees(salary) tablespace users; #在employees表的salary列创建位图索引

初始化参数CREATE_BITMAP_AREA_SIZE用于指定建立位图索引时分配的位图区大小，默认值为8MB。

（3）建立反向键索引

反向键索引是一种特殊类型的B树索引。

创建反向键索引时只需要在CREATE INDEX语句中指定关键字REVERSE即可。

SQL>create index emp_job_reverse on emp(job) reverse tablespace users; #为表emp的job列创建反向键索引

（4）基于函数的索引

SQL>select empno,ename,sal from emp where job=upper('manager'); #使用函数将mangager转换成大写；

SQL>create index emp_job_fun on emp(lower(job)); #为表emp的job列创建函数索引

10.1.3 修改索引

修改索引使用ALTER INDEX语句。

若要用其他身份修改索引，则要求该用户必须具有ALTER ANY INDEX系统权限或在相应的表上的INDEX对象权限。

SQL>alter index emp_deptno_index coalesce deallocate unused; #对索引emp_deptno_index执行合并操作

消除索引碎片的另一个方式就是重建索引，可以使用ALTER INDEX...REBUILD语句。

SQL>alter index emp_deptno_index rebuild; #对索引emp_deptno_index进行重建

10.1.4 删除索引

删除索引使用DROP INDEX语句。若要其他用户删除索引，则用户必须具有DROP ANY INDEX系统权限或在相应表上的INDEX对象权限。

需要删除某个索引的情况主要有：

●如果移动了表中的数据，导致索引中包含过多的存储碎片，此时需要删除并重建索引；

●通过一段时间的监视，发现很少有查询会使用该索引；

●该索引不再需要时应该删除该索引，以释放其所占用的空间；

10.1.5 显示索引信息

（1）显示表的所有索引

DBA_INDEXES：显示数据库的所有索引；

ALL_INDEXES：显示当前用户可访问的所有索引；

USER_INDEXES：显示当前用户的索引信息；

（2）显示索引列

DBA_IND_COLUMNS: 显示所有索引的表列信息；

ALL_IND_COLUMNS:显示当前用户可访问的所有索引的表列信息；

USER_IND_COLUMNS：显示当前用户索引的表列信息；

（3）显示函数索引

DBA_IND_EXPRESSIONS：显示数据库所有的函数索引所对应的函数或表达式；

USER_IND_EXPRESSIONS: 显示当前用户函数索引所对应的函数或表达式

10.2 视图对象

10.2.1 创建视图

创建视图使用CREATE VIEW语句；

如果要在其他用户模式下创建视图，则用户必须具有CREATE ANY VIEW系统权限，创建视图如下：

create [or replace] view [alias[,alias]...)]

as

[with check option] [constraint constraint_name]

[with read only]

alias：用户指定视图列的别名；

subquery：用于指定视图对应的子查询语句；

with check option：该字句用于指定在视图上定义的CHECK约束；

with read only：该字句用于定义只读视图；

在创建视图时，如果不提供视图列别名，Oracle会自动使用子查询的列名或列别名；

如果视图子查询包含函数或表达式，则必须定义列别名；

系统在执行CREATE VIEW语句创建视图时，只是将视图的定义信息存入数据字典，并不会执行其中的SELECT语句。

10.2.2 管理视图

用户可以对视图进行管理，主要有：查看视图定义、修改视图定义、重新编译视图和删除视图；

（1）查看视图定义

可以通过USER_VIEWS获取视图的定义信息；

（2）修改视图定义

可以使用CREATE OR REPLACE VIEW语句；

（3）重新编译视图

可以使用ALTER VIEW 语句手动编译视图；

SQL>alter view emp_view_union compile; #手动方式重新编译视图emp_view_union

（4）删除视图

使用DROP VIEW语句删除视图。

10.3 同义词对象

同义词是表、索引、视图等模式对象的一个别名。通过模式对象创建同义词，可以隐藏对象的实际名称和所有者信息，或者隐藏分布式数据库中远程对象的设置信息，由此为对象提供

一定的安全性保证。

同义词只在Oracle数据库的数据字典中保存其定义描述，因此同义词也不占用任何实际的存储空间。

Oracle的同义词有2种类型：公有同义词和私有同义词；

公有同义词被一个特殊的用户组PUBLIC所拥有，数据库中的所有用户都可以使用公有同义词；

私有同义词只被创建它的用户所拥有，只能由该用户以及被授权的其他用户使用。

（1）创建同义词

建立公有同义词使用CREATE PUBLIC SYNONYM语句；

建立私有同义词使用CREATE SYNONYM语句；

如果要在其他模式中创建私有同义词，则数据库用户必须具有CREATE ANY SYNONYM系统权限；

（2）删除同义词

要删除其他模式中的私有同义词，用户必须具有DROP ANY SYNONYM系统权限。

要删除公有同义词，用户必须具有DROP PUBLIC SYNONYM系统权限。

删除私有同义词使用DROP SYNONYM语句；

删除公有同义词使用DROP PUBLIC SYNONYM语句；

10.4 序列对象

序列是Oracle提供的用于生成一系列唯一数字的数据库对象。

序列会自动生成顺序递增的序列号，以实现自动提供唯一的主键值。

10.4.1 创建序列

序列不占用实际的存储空间，只是在数据字典中保存它的定义信息。

使用CREATE SEQUENCE语句创建序列的语法：

create sequence

[start with n]

[increment by n]

[minvalue n| nomainvalue]

[maxvalue n| nomaxvalue]

[cache n | nocycle]

[cycle | no cycle]

[order | noorder];

seq_name：创建的序列名；

increment：该子句可选，表示序列的增量。默认值为1

minvalue：可选子句，决定序列生成的最小值；

maxvalue：可选子句，决定序列生成的最大值；

start：可选子句，指定序列的开始位置。默认情况下，递增序列的起始值为minvalue，递减序列的起始值为maxvalue;

cache：可选子句，决定是否产生序列号预分配，并存储在内存中；

cycle：可选子句，当序列达到最大值或最小值时，可以复位并继续下去。如果达到极限，生成的下一个数据将分别是最大值或最小值。如果使用nocyle，那么在序列达到最大或最小值之后，再获取下一个值就会报错；

order：可选子句，可以保证生成的序列值是按照顺序产生的。

建立序列时，必须为序列提供相应的名称。

使用序列时，需要用到序列的两个伪列NEXTVAL和CURRVAL。其中NEXTVAL将返回序列生成的下一个序列号，而伪序列CURRVAL则会返回序列的当前序列号。

首次引用序列时，必须使用伪列NEXTVAL。

SQL>select empno_seq.currval from dual; #使用伪列CURRVAL查询当前的序列号；

10.4.2 管理序列

（1）修改序列

使用ALTER SEQUENCE语句可以对序列进行修改。

除序列的起始值START WITH不能修改外，其他可以设置序列的任何子句和参数都可以被修改。

如果要修改序列的起始值，则必须先删除序列，然后重建该序列。

对序列进行修改后，缓存中的序列值将全部丢失。

（2）查询序列

可以通过数据字典USER_SEQUENCES查询序列的信息；

SQL>desc user_sequences;

（3）删除序列

使用DROP SEQUENCE语句删除序列；

SQL>drop sequence empno_seq;

********************************************************************************************************************

十一、表分区与索引分区

分区技术主要包括表分区和索引分区。

11.2 创建表分区

11.2.1 范围分区

创建范围分区的关键字是"RANGE",创建该分区后，其中的数据可以根据分区键值指定的范围进行分布，当数据在范围内均匀分布时，性能最好。

当表结构采用范围分区时：

①首先要考虑分区的列应该符合范围分区的方法；

②其次要考虑列的数据值的取值范围；

③最后考虑边界问题；

#创建一个商品零售表，然后为该表按照销售日期所在的季度创建4个分区：

create table ware_retail_part

(

id integer primary key,

retail_date date,

ware_name varchar2(50)

)

partition by range(retail_date)

(

partition par_01 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace TBSP_1,

partition par_02 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace TBSP_1,

partition par_03 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace TBSP_2,

partition par_04 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace TBSP_2

);

#插入记录

insert into ware_retail_part values(1,to_date('2011-01-20','yyyy-mm-dd'),'平板电脑');

insert into ware_retail_part values(2,to_date('2011-04-15','yyyy-mm-dd'),'s 智能手机');

insert into ware_retail_part values(3,to_date('2011-07-25','yyyy-mm-dd'),'sMP5');

#查询记录

SQL>select * from ware_retail_part partition(par_02); #查询数据表ware_retail_part中par_02分区的全部记录；

Range分区的字段可以是两个或者多个。

SQL>create table ware_retail_part2

(

id integer primary key,

retail_date date,

ware_name varchar2(50)

)

partition by range(id,retail_date)

(

partition par_01 values less than(10000,to_date('2011-12-01','yyyy-mm-dd')) tablespace TBSP_1,

partition par_02 values less than(20000,to_date('2012-12-01','yyyy-mm-dd')) tablespace TBSP_1,

partition par_03 values less than(maxvalue,maxvalue) tablespace TBSP_2 15

);

11.2.2 散列分区

HASH分区通过指定分区编号将数据均匀分布在磁盘设备上，使得这些分区大小一致，充分降低了I/O磁盘争用的情况。

一般，以下情况可以采用HASH分区：

（1）HASH分区可以由HASH键来分布；

（2）DBA无法获知具体的数据值；

（3）数据的分布由Oracle处理；

（4）每个分区有自己的表空间；

#创建分区表

create table ware_retail_part3

(

id integer primary key,

retail_date date,

ware_name varchar2(50)

)

partition by hash(id)

(

partition par_01 tablespace TBSP_1,

partition par_02 tablespace TBSP_2

);

#插入数据

insert into ware_retail_part3 values(99,to_date('2011-11-11','yyyy-mm-dd'),'computer');

#查询数据

select * from ware_retail_part3 partition(par_02);

在Oracle系统中，可以实现由系统自动分配分区名；

create table person

(

id number primary key,

name varchar2(20),

sex varchar2(2)

)

partition by hash(id)

partitions 2

store in (tbsp_1,tbsp_2);

#查询表分区名称

select partition_name from user_segments where segment_type='TABLE PARTITION' and segment_name='person';

在创建HASH分区表时，用户还可以指定所有分区的初始分配空间。

create table goods

(

id number,

goodname varchar2(50)

)

storage(initial 2048k)

partition by hash(id)

(

partition par1 tablespace tbsp_1,

partition par2 tablespace tbsp_2

);

11.2.3 列表分区

关键字是LIST，如果表的某个列的值可以枚举，则可以考虑对表进行列表分区。

create table clients

(

id integer primary key,

name varchar2(50),

province varchar2(20)

)

partition by list(province)

(

partition shandong values('shandong'),

partition guangdong values('guangdong'),

partition yunnan values('yunnan')

);

11.2.4 组合分区

create table person2

(

id number primary key,

name varchar2(20),

sex varchar2(2)

)

partition by range(id)

subpartition by hash(name)

subpartitions 2 store in(tbsp_1,tbsp_2)

(

partition par1 values less than(5000),

partition par2 values less than(10000),

partition par3 values less than(maxvalue)

);

11.2.5 Interval分区

关键字是Interval,是范围分区的一种增强功能，可实现equi_sized范围分区的自动化。

创建的分区作为元数据，只有最开始的分区是永久分区。随着数据的增加会分配更多的部分，并自动创建新的分区和本地索引。

create table saleRecord

(

id number primary key,

goodsname varchar2(50),

saledate date,

quantity number

)

partition by range(saledate)

interval (numtoyminterval(1,'year'))

(

partition par_fist values less than (to_date('2012-01-01','yyyy-mm-dd'))

);

函数numtoyminterval的功能是将数字转换成INTERVAL YEAR TO MONTH文字。

11.3 表分区策略

采用ANALYZE TABLE语句分析表数据量。

11.4 管理表分区

11.4.1 添加表分区

对已经存在表分区的某个表，如果要添加一个新的表分区，使用ALTER TABLE...ADD PARTITION语句。

SQL>alter table clients

add partition hebei values('hebei')

storage(initial 10K next 20k) tablespace tbsp_1

nologging;

11.4.2 合并分区

在合并分区之后，Oracle系统将做以下处理:

●在合并分区时，HASH列函数将分区内容分布到一个或多个保留分区中；

●原来内容所在的分区完全被清除；

●与分区对应的索引也被清除；

●将一个或多个索引的本地索引分区标识为不可用（UNSABLE）；

●需要对不可用的索引进行重建；

（1）合并散列分区

使用ALTER TABLE...COALESCE PARTITION 语句可以完成HASH列分区的合并。

SQL>alter table person coalesce partition;

（2）合并复合分区

使用ALTER TABLE...MODIFY语句实现将某个子分区的内容重新分配到一个或多个保留的子分区中。

SQL>alter table person2 modify partition par3 coalesce subpartition;

11.4.3 删除分区

可以从范围分区或复合分区中删除分区。但是散列分区和复合分区的散列子分区，只能通过合并来达到删除的目的。

（1）删除一个表分区

使用ALTER TABLE...DROP PARTITION语句删除范围分区和复合分区。

删除分区时，该分区的数据也被删除。如果不希望删除数据，必须采用合并分区的方法。

SQL>alter table ware_retail_part drop partition par_04;

（2）删除有数据和全局索引的表分区

删除表分区语句同（1），但索引会被标识为不可用，需要重建索引。

SQL>alter index ware_retail_index rebuild; #重建索引

如果 ware_retail_index是范围分区的全局索引，就需要重建所有索引的分区。

alter index ware_retail_index rebuild index_01;

alter index ware_retail_index rebuild index_02;

alter index ware_retail_index rebuild index_03;

（3）使用DELETE和ALTER TBALE ...DROP PARTITION语句

首先执行DELETE语句删除分区的所有数据行，然后执行ALTER TBALE ...DROP PARTITION语句。

（4）删除具有完整性约束的分区

如果分区的表具有完整性约束，可以采用如下2种方法：

1）首先禁止完整性约束，然后执行ALTER TABLE...DROP PARTITION，最后激活约束；

SQL>alter table books_1 disable constraints BOOK_PK;

SQL>alter table books_1 drop partition part_01;

SQL>alter table books_1 enable constraints BOOK_PK;

2）首先执行DELETE语句删除分区中的行，然后用ALTER TABLE...DROP PARTITION语句删除分区

SQL>delete from books_1 where bookno<1000;

SQL>alter table books_1 drop partition part_01;

11.4.4 并入分区

用户可以使用MERGE PARTITION语句将相邻的范围分区合并在一起变成一个新的分区，该分区继承原来两个分区的边界；

原来的两个分区与相应的索引一起被删除；如果被合并的分区非空，则该分区被标识为UNSABLE；不能对HASH分区表执行MERGE PARTITION语句；

并入范围分区是将两个以上的分区合并到一个存在的分区中，合并后一般索引要重建；

SQL>alter table sales merge partitions part_sea3,part_sea4 into partition part_sea4; #把第3个分区并入到第4个分区中

SQL>alter table sales modify partition part_sea4 rebuild unusable local indexes; #重建索引

11.5 创建索引分区

Oracle索引分区分为本地索引分区和全局索引分区。

全局索引不反映基础表的结构，因此若要分区就只能进行范围分区；

局部索引反映基础表的结构，因此，对表的分区或子分区进行维护时，系统会自动对本地索引的分区进行维护；

11.5.2 本地索引分区

本地索引分区就是使用和分区表同样的分区键进行分区的索引，索引分区采用的列与该表的分区所采用的列是相同的；

本地索引分区的优点：

●如果只有一个分区需要维护，则只有一个本地索引受影响；

●支持分区独立性

●只有本地索引能够支持单一分区的装入和卸载

●表分区和各自的本地索引可以同时恢复

●本地索引可以单独重建

●位图索引仅由本地索引支持

创建本地索引，使用CREATE INDEX...带LOCAL子句；

DBA_IND_PARTITIONS ：查询索引分区信息；

#1.创建3个表空间

create tablespace ts_1 datafile '/u01/oracle/app/oradata/ORCL/ts1.dbf' size 10m extent management local autoallocate;

create tablespace ts_2 datafile '/u01/oracle/app/oradata/ORCL/ts2.dbf' size 10m extent management local autoallocate;

create tablespace ts_3 datafile '/u01/oracle/app/oradata/ORCL/ts3.dbf' size 10m extent management local autoallocate;

#2.创建一个存储学生成绩的分区表studentgrade，该表共有3个分区；

create table studentgrade

(

id number primary key,

name varchar2(10),

subject varchar2(10),

grade number

)

partition by range(grade)

(

partition par_nopass values less than(60) tablespace ts_1,

partition par_pass values less than(70) tablespace ts_2,

partition par_good values less than(maxvalue) tablespace ts_3

);

#3.根据表分区创建本地索引分区，索引分区也有3个

create index grade_index on studentgrade(grade)

local

(

partition p1 tablespace ts_1,

partition p2 tablespace ts_2,

partition p3 tablespace ts_3

);

#4. 查询索引分区信息

select partition_name,tablespace_name from dba_ind_partitions where index_name='GRADE_INDEX';



11.5.3 全局索引分区

全局索引分区就是没有与分区表相同分区键的分区索引。

当分区中出现许多事务并且要保证所有分区中的数据记录唯一时，采用全局索引分区。

11.6 管理索引分区

11.6.2 索引分区管理的实际操作

（1）删除索引分区

使用ALTER INDEX...DROP PARTITION语句；

SQL>alter index index_saleprice drop partition p2; #删除索引分区；

使用ALTER INDEX...REBUILD PARTITION语句重建索引分区；

（2）重命名索引分区

重命名索引分区的语法：alter index index_name rename partition partition_old_name to partition_new_name;

********************************************************************************************************************

十二、用户管理与权限分配

12.1 用户与模式的关系

SYSDBA或SYSOPER用户用于执行数据库的维护操作，如启动数据库、关闭数据库、建立数据库以及执行备份和恢复操作；

SYSDBA和SYSOPER的区别在于：SYSDBA不仅具备SYSOPER的所有权限，而且还可以建立数据库，执行不完全恢复。

在Oracle11g中，默认的特权用户SYS，当以特权用户身份登录数据库时，必须带有AS SYSDBA或AS SYSOPER选项，例如：SQL>connect system/1qaz2wsx as sysdba;

模式或方案是用户所拥有的数据库对象的集合。

12.2 创建与管理用户

12.2.1 身份验证

Oracle为用户帐户提供了3种验证方式：密码验证、外部验证、全局验证。

12.2.2 创建用户

创建用户采用CREATE USER命令，语法格式：

create user user_name identified by pass_word

[or identified exeternally]

[or identified globally as 'CN=user']

[default tablespace tablespace_default]

[temporary tablespace tablespace_temp]

[quota [integer k[m]] [unlimited] ] on tablespace_specify1

[,quota [integer k[m]] [unlimited] ] on tablespace_specify2

[,...]...on tablespace_specifyn

[profiles profile_name]

[account lock or account unlock]

user_name：用户名

pass_word：密码

identified exeternally：表示用户名在操作系统下验证，这种情况下要求该用户必须与操作系统中所定义的用户名相同；

identified globally as 'CN=user' : 表示用户名由Oracle安全域中心服务器验证，CN名字表示用户的外部名

[default tablespace tablespace_default]：表示该用户在创建数据对象时使用的默认表空间

[temporary tablespace tablespace_temp]：表示该用户所使用的临时表空间

[quota [integer k[m]] [unlimited] ] on tablespace_specify1：表示该用户在指定表空间中允许占用的最大空间；

[profiles profile_name]：资源文件的名称；

[account lock or account unlock]：用户是否被加锁，默认是不加锁的；

（1）创建用户，并指定默认表空间和临时表空间

create user mr identified by mrsoft

default tablespace users

temporary tablespace temp;

（2）创建用户，并配置其在指定表空间上的磁盘限额。在创建用户时，可通过QUOTA xxxM ON tablespace_specify子句配置指定表空间的最大可用限额。

create user east identified by mrsoft

default tablespace users

temporary tablespace temp

quota 10m on tbsp_1;

（3）创建用户，并配置其在指定表空间上不受限制，如果要设置用户在指定表空间上不受限制，使用QUOTA UNLIMITED ON tablespace_specify子句。

create user df identified by mrsoft

default tablespace users

temporary tablespace temp

quota unlimited on tbsp_1;

在创建完用户之后，需要注意的几点：

●如果建立用户时不指定DEFALUT TABLESPACE子句，Oracle会将SYSTEM表空间作为用户默认表空间；

●如果建立用户时不指定TEMPORARY TABLESPACE子句，Oracle会将默认临时表空间作为用户临时表空间；

●初始建立的用户没有任何权限，为了使用户能连接到数据库，必须授予其CREATE SESSION权限；

●如果建立用户时没有为表空间指定QUOTA子句，用户在特定表空间上的配额为0，用户将不能在相应的表空间上建立数据对象；

●初始建立的用户没有任何权限，不能执行任何数据库操作；

12.2.3 修改用户

（1）修改用户的磁盘配额

SQL>alter user east quota 20m on tbsp_1; #修改用户east的磁盘配额为20m;

（2）修改用户的口令

SQL>alter user east identified by 123456;

（3）解锁被锁住的用户

SQL>alter user SH account unlock;

SQL> select USERNAME,ACCOUNT_STATUS from dba_users; #查询当前数据库中用户的锁定状态

12.2.4 删除用户

删除用户使用DROP USER语句。

删除用户后，Oracle会从数据字典中删除用户、方案及其所有对象方案；语法： drop user user_name[cascade]

cascade：级联删除选项，如果用户包含数据库对象，则必须加CASCADE选项，此时连同该用户所拥有的对象一起删除。

SQL>drop user df cascade;

12.3 用户权限管理

12.3.2 授权操作

在Oracle11g中，所有系统特权都被列举在SYSTEM_PRIVILEGE_MAP数据目录视图中。授权操作使用GRANT命令，语法如下：

grant sys_privi | role to user | role | public [with admin option]

sys_privi：表示Oracle系统权限，系统权限是一组约定的保留字，例如创建表”CREATE TABLE“；

role：角色；

user：具体的用户名，或者是一些列的用户名；

public：保留字，代表Oracle系统的所有用户；

with admin option：表示被授权者可以再将权限授予另外的用户；

SQL>grant connect,resource to east;

如果想要east将这两个权限可以传递给其他的用户，则需要在grant语句中使用with admin option关键字；

#1.创建用户dongfang

create user dongfang identified by mrsoft

default tablespace users

temporary tablespace temp

quota 10m on users;

#2.创建用户xifang

create user xifang identified by mrsoft

default tablespace users

temporary tablespace temp

quota 10m on users;

#3.使用管理员权限给dongfang授权

grant create session,create table to dongfang with admin option;

#4.连接dongfang用户，并给xifang用户授权

SQL>connect dongfang/mrsoft;

SQL>grant create session,create table to xifang;

#4.连接xifang用户，并创建表

SQL>connect xifang/mrsoft;

SQL>create table tb_xifang

(id number,

name varchar2(20)

);

12.3.3 回收系统权限

REVOKE命令的语法格式如下： revoke sys_privi | role from user | role | public

sys_privi：系统权限或角色

role：角色

user：具体的用户名

public：保留字，代表Oracle系统所有的用户

SQL>revoke resource from east; #撤销用户east的resource权限；

如果数据库管理员用GRANT命令给用户A授予系统权限是带有WITH ADMIN OPTION选项，则该用户A有权限将系统权限再次授予另外的用户B。

在这种情况下，如果数据库管理员使用REVOKE命令撤销A用户的系统权限，则用户B的系统权限仍然有效。

12.3.4 对象授权

授予对象权限给用户或角色也使用GRANT命令，语法如下：grant obj_privi | all column on schema.object to user | role | public [with grant option] | [with hierarchy option]

obj_privi：表示对象的权限，可以是ALTER、EXECUTE、SELECT、UPDATE和INSERT等；

role：角色名；

user：被授权的用户名；

with admin option：表示被授权者可再将系统权限授予其他的用户；

with hierarchy option：在对象的子对象（在视图上再建立视图）上授权给用户；

SQL>grant select,insert,delete,update on scott.emp to xifang;

12.3.5 回收对象权限

使用REVOKE命令，语法： revoke obj_privi | all on schema.object from user | role | public cascade constraints

obj_privi：表示对象的权限;

public：保留字，代表Oracle系统的所有权限；

cascade ascade constraints：表示有关联关系的权限也被回收；

SQL>revoke delete,update on scott.emp from xifang;

12.3.6 查询用户与权限

数据字典名称说明

DBA_USERS 数据库用户基本信息表

DBA_SYS_PRIVS 已授予用户或角色的系统权限

DBA_TAB_PRIVS 数据库对象上的所有权限

USER_SYS_PRIVS 登录用户可以查看自己的系统权限

ROLE_SYS_PRIVS 登录用户查看自己的角色

ALL_TABLES 用户自己可以查询的基表信息

USER_TAB_PRIVS 用户自己将哪些基表权限授予哪些用户

ALL_TAB_PRIVS 哪些用户给自己权限

12.4 角色管理

角色是简化权限管理的一种数据库对象；

12.4.1 角色概述

角色是一个独立的数据库实体，它包括一组权限。

角色是包括一个或多个权限的集合，它并不被哪个用户所拥有。角色可以被授予任何用户，也可以从用户中将角色收回。

12.4.2 预定义角色

系统预定义角色是指在数据库安装完成后由系统自动创建的一些常用角色，这些角色已经由系统授予了相应的系统权限，数据库管理员可以直接使用。

常用预定义角色：CONNECT、RESOUCE、DBA、EXP_FULL_DATABASE、IMP_FULL_DATABASE.

CONNECT、RESOURCE、DBA：主要用于数据库管理，这2个角色之间相互没有包含关系；

IMP_FULL_DATABASE、EXP_FULL_DATABASE：分别用于操作数据库的导入或导出工具；

角色名包含权限

CONNECT ALTER SESSION、CREATE CLUSTER、CREATE DATABASE LINK、CREATE SEQUENCE、CREATE SYNONYM、CREATE TABLE、CREATE VIEW

RESOURCE CREATE CLUSTER、CREATE INDEXTYPE、CREATE OPERATOR 、CREATE PROCEDURE、CREATE SEQUENCE、CREATE TABLE、CREATE TRIGGER、CREATE TYPE

DBA 所有权限，不受限制

EXP_FULL_DATABASE SELECT ANY TABLE、BACKUP ANY TABLE、EXECUTE ANY PROCEDURE、EXECUTE ANY TYPE、ADMINSTER RESOURCE MANAGER

IMP_FULL_DATABASE EXECUTE_CATALOG_ROLE、SELECT_CATALOG_ROLE

select * from DBA_ROLES：查询数据库中的角色信息；

12.4.3 创建角色与授权

创建自定义角色使用CREATE ROLE语句，语法如下： create role role_name [ not identified | identified by [password] | [exeternally] | [ globally]]

role_name：角色名

identified by password：角色口令；

identified by externally：表示角色名在操作系统下验证；

identified globally：表示用户是Oracle安全域中心服务器来验证，此角色由全局用户来使用。

#1.创建角色designer

SQL>create role designer identified by 123456;

给角色授权使用GRANT...TO语句，如果系统管理员具有GRANT_ANY_PRIVILEGE权限，就可以给角色授权；

#2.给角色授权

SQL>grant create view,create table to designer;

#3.将角色授权给用户

SQL>grant designer to dongfang;

12.4.4 管理角色

（1）查看角色所包含的权限

查看角色权限通常使用ROLE_SYS_PRIVS数据字典；

SQL>select * from role_sys_privs where role='DESIGNER';

（2）修改角色密码

修改角色密码包括取消角色密码和修改角色密码2种情况，可以使用ALTER ROLE来实现。

SQL>alter role designer not identified; #取消角色密码

SQL>alter role designer identified by mrsoft; #修改角色密码；

（3）设置当前用户要生效的角色

最大可生效角色由参数MAX_ENABLED_ROLES设定；

设置角色生效使用SET ROLE语句；

SQL>create role queryer; #创建一个不使用密码验证的角色

SQL>set role queryer; #设置角色生效；

SQL>set role designer identified by mrsoft; #设置带有密码的角色也生效；

（4）删除角色

使用DROP ROLE语句实现；

SQL>drop role queryer;

12.4.5 角色与权限的查询

数据字典名称说明

DBA_COL_PRIVS 数据库列上的所有权限

DBA_ROLE_PRIVS 显示已经授予用户或其他角色的角色

DBA_TAB_PRIVS 数据库对象上的所有权限

DBA_SYS_PRIVS 已授予用户或角色的系统权限

SQL>select granted_role,default_role from dba_role_privs where grantee='HR'; #查询HR用户被授予的系统角色；

SQL>select * from session_roles; #在SCOTT模式下，查询当前用户的所有有效角色；

12.5 资源配置PROFILE

PROFILE作为用户配置文件，它是密码限制、资源限制的命名集合；

在安装数据库时，Oracle会自动建立名为DEFAULT的默认配置文件；

使用PROFILE文件的注意事项：

●建立用户时，如果不指定PROFILE选项，Oracle会自动将DEFAULT分配给相应的数据库用户；

●建立PROFILE文件时，如果只设置了部分密码和资源限制选项，其他选项会自动使用默认值，即使DEFAULT文件中有相应选项的值；

●使用PROFILE管理密码时，密码管理选项总是处于被激活状态，但是如果使用PROFILE管理资源，必须激活资源限制；

●一个用户只能分配一个PROFILE文件。如果要同时管理用户的密码和资源，那么在建立PROFILE时应该同时指定密码和资源选项；

12.5.2 使用PROFILE管理密码

为了实现密码限制，必须首先建立PROFILE文件。建立PROFILE文件使用CREATE PROFILE语句，一般由DBA执行，如果要以其他用户身份建立PROFILE文件，则要求该用户

必须具有CREATE PROFILE系统权限。

使用PROFILE文件可以实现如下4种密码管理：账户锁定、密码的过期时间、密码历史和密码的复杂度；

（1）账户锁定

Oracle为锁定账户提供如下2个参数：

●FAILED_LOGIN_ATEMPTS：该参数限制用户在登录到Oracle数据库时允许失败的次数。一旦某个用户尝试登录数据库的次数达到该值，则系统将该用户账户锁定；

●PASSWORD_LOCK_TIME：该参数用于指定账户被锁定的天数；

SQL>create profile lock_account limit failed_login_attempts 5 password_lock_time 7; #输错密码超过5次后，账户被锁定7天；

SQL>alter user dongfang profile lock_account; #将建立后的profile文件分配给dongfang用户；

（2）密码的过期时间

为了将强制用户定期修改密码，Oracle提供了如下参数：

PASSWORD_LIFE_TIME：该参数用于设置用户密码的有效时间，单位为天数。超过这一段时间，用户必须重新设置口令；

PASSWORD_GRACE_TIME：该参数用于设置口令失效的“宽限时间”，如果口令达到PASSWORD_LIFE_TIME设置的失效时间，设置宽限时间后，用户仍然可以继续使用一段时间。

SQL>create profile password_lift_time limit password_life_time 30 password_grace_time 3; #设置密码有效期为30天，宽限时间为3天；

SQL>alter user dongfang profile password_lift_time;

（3）密码历史

密码历史用于控制帐户密码的可重复使用次数或可重用时间。

使用密码历史参数后，Oracle会将修改信息存放到数据字典中。密码历史有如下两个参数：

PASSWORD_REUSE_TIME：该参数指定密码可重用的时间，单位为天；

PASSWORD_REUSE_MAX：该参数设置口令在能够被重用之前，必须改变的天数；

（4）密码的复杂度

在Oracle11g中，在$ORACLE_HOME\rdbms\admin目录下创建了一个新的密码验证文件UTLPWDMG.SQL，其中包括之前的验证函数VERIFY_FUNCTION，还提供了一个新建的VERIFY_FUNCTION_11G函数；

12.5.3 使用PROFILE管理资源

如果使用PROFILE管理资源，必须将RESOURCE_LIMIT参数设置为TRUE，以激活资源限制。该参数为动态参数，可使用ALTER SYSTEM语句进行修改；

SQL>show parameter resource_limit; #查询resource_limit参数的值；

SQL>alter system set resource_limit=true; #设置参数值为true;

如下参数是PROFILE文件中对各种资源限制的参数：

SESSION_PER_USER：用户可以同时连接的会话数量。

CPU_PER_SESSION：限制用户再一次进行数据库会话期间可以使用的CPU时间，单位为百分之一秒。当达到该时间值后，系统就会终止该会话。如果用户还需要执行操作，则必须重新建立连接；

CPU_PER_CALL：该参数用户限制每条SQL语句所能使用的CPU时间，参数值是一个整数，单位为百分之一秒；

LOGICAL_READS_PER_SESSION：限制每个会话所能读取的数据块数量，包括从内存中读取的数据块和从磁盘中读取的数据块；

CONNECT_TIME：限制每个用户连接到数据库的最长时间，单位为分钟，当连接时间超出该设置时，该连接终止；

IDLE_TIME：该参数限制每个用户会话连接到数据库的空闲时间，超过该空闲时间的会话，系统会终止该会话。

12.5.4 维护PROFILE文件

DBA可以修改配置文件、删除配置文件、激活或禁用配置文件；

（1）修改PROFILE文件

使用ALTER PROFILE语句修改PROFILE文件的资源参数和密码参数；

SQL>create profile password_lift_time1 limit

cpu_per_session 20000

sessions_per_user 10

cpu_per_call 500

password_life_time 180

failed_login_attempts 10;

（2）删除PROFILE文件

使用DROP PROFILE语句删除PROFILE文件。如果要删除的配置文件已经被指定给了用户，则必须在DROP PROFILE语句中使用CASCADE关键字；

SQL>drop profile password_lift_time1 cascade;

12.5.5 显示PROFILE信息

（1）显示用户的资源配置信息

可通过数据字典视图DBA_USERS查询用户使用的PROFILE文件；

SQL>select profile from dba_users where username='DONGFANG';

（2）显示指定PROFILE文件的资源配置信息

可通过查询DBA_PROFILES来显示PROFILE的密码限制、资源限制信息；

SQL>select resource_name,resource_type,limit from dba_profiles where profile='LOCK_ACCOUNT';

********************************************************************************************************************

十三、Oracle系统调优

13.1 调整初始化参数

13.1.1 Oracle初始化参数分类

Oracle11g初始化参数分为基本参数和高级参数：基本参数是一组可调整的参数，高级参数是一组精细I调整的参数；

按与环境的关系，参数分为如下：

起源参数：由另外的参数计算得到，这些参数的值不需要在参数文件中改变或指定；

带GC前缀的全局高速缓存参数：即全局高速缓存，这些参数通常在多个实例并行的环境下使用；

与操作系统有关的参数：如参数DB_FILE_MULTIBLOCK_READ_COUNT与主机的操作系统对磁盘的I/O有关；

可变参数：与系统的性能有关；

异类服务参数：可用于设置网关的参数；

13.1.2 主要系统调优参数介绍

Oracle11g的初始化参数存放在初始化参数文件SPFILE文件中。SPFILE是一个二进制文件，只能由ORACLE系统进行读写；

参数说明

buffer_pool_keep 保留池大小（从DB_BLOCK_BUFFERS分配）。目的是将对象保存在内存中，以减少I/O；

buffer_pool_recycle 循环池大小（从DB_BLOCK_BUFFERS分配）。目的是使用对象后将其清除，以便重复使用内存；

control_file_record_keep_time 控制文件中可重新使用部分中的记录必须保留的最短时间（天数）

cursor_space_for_time 在一个游标引用共享SQL区时，确定将SQL区保留在共享池中还是从中按过期作废处理

db_block_buffers 缓冲区高速缓存中ORACLE块的数量，该参数会显著影响一个例程的SGA总大小

db_keep_cache_size 指定KEEP缓冲池中缓冲区数，KEEP缓冲池中的缓冲区大小是主要块大小

db_recycle_cache_size 指定RECYCLE缓冲池的大小，RECYCLE池中的缓冲区大小是主要块大小

java_max_sessionspace_size 以字节为单位，指定可供在服务器中运行的JAVA程序所使用的最大内存量。用于存储每次数据库调用的Java状态。

java_pool_size 以字节为单位，指定Java存储池的大小，它用于存储Java的方法和类定义在共享内存中的表示法，以及在调用结束时移植到Java会话空间的Java对象

large_pool_size 指定大型池的分配堆大小，它可被共享服务器用作会话内存，用作并行执行的消息缓冲区以及用作RMAN备份和恢复的磁盘I/O缓冲区

log_buffer 以字节为单位，指定在LGWR将重做日志条目写入重做日志文件之前，用于缓存这些条目的内存量。重做条目保留对数据库块所作更改的一份记录；

log_checkpoint_interval 指定在出现检查点之前，必须写入重做日志文件中的OS块的数量。

log_checkpoint_timeout 指定距下一个检查点出现的最大时间间隔。将该时间值指定为0,将禁用以时间为基础的检查点。

max_dump_file_size 指定每个跟踪文件的最大大小；

object_cache_max_size_percent 指定会话对象的高速缓存增长可超过最佳高速缓存大小的百分比，最大大小等于最佳大小加上该百分比与最佳大小的乘积。

optimizer_index_caching 调整基于成本的优化程序的假定值，即在缓冲区高速缓存中期望用于嵌套循环连接的索引块的百分比。

opimize_index_cost_adj 在考虑太多或太少索引访问路径的情况下，可用来优化程序的性能。该值越低，优化程序越容易选择一个索引。

query_rewrite_enabled 启用或禁用对实体化视图的查询重写。

read_only_open_delayed 用于加速某些操作。

shared_pool_reservered_size 指定要为较大连续共享池内存请求而保留的空间，以避免由碎片引起的性能下降。

shared_pool_size 以字节为单位，指定共享池的大小。

sort_area_size 以字节为单位，指定排序所使用的最大内存量。排序完成后，各行将返回，并且内存将释放。增大该值可以提高大型排序的效率，如果超过了该内存量，将使用临时磁盘段

在进行系统优化时，可以使用ALTER SYSTEM或ALTER SESSION命令来修改这些系统优化参数；

用ALTER SYSTEM所修改的参数会影响到整个数据库的实例；

用ALTER SESSION命令修改的参数只影响该会话；

使用SHOW PARAMETERS命令查询系统优化参数的值；

13.2 系统全局区优化

日志记录的2种方式：逻辑记录方式、物理记录方式

逻辑记录方式：恢复速度慢、但节省磁盘空间

物理记录方式：恢复速度快、但占用较大磁盘空间

使用初始化参数log_buffer设置日志缓冲区的大小，单位为字节；

SQL>select distinct lebsz from x$kccle; #查询当前Oracle实例的日志缓冲区大小

由LGWR进程将日志缓冲区中的日志信息写入到日志文件中；

触发LGWR进程并将日志缓冲区中的日志信息写入联机日志文件，通常在以下几种情况发生：

●前台进程触发，包括2种情况。一种是用户发出COMMIT或ROLLBACK语句进行提交时；一种是在日志缓冲区中找不到足够的内存放置日志信息时，会自动触发LGWR进程将一些日志信息写入联机日志文件，从而释放空间；

●每隔3秒钟，LGWR启动一次；

●在DBWR启动时，如果发现脏数据块所对应的重做条目还没有写入联机日志文件，则DBWR触发LGWR进行并等待LGWR写完后才会继续；

●日志信息的数量达到整个日志缓冲区的1/3时，自动触发LGWR；

●日志信息的数量达到1MB时，自动触发LGWR；

●发生日志切换时，自动触发LGWR；

13.2.3 调整共享池

共享池中的主要组件有以下3个：

●库缓存：主要缓存共享SQL和PL/SQL语句的相关信息；

●数据字典缓存：缓存数据字典表的信息，用于解释权限、表结构；

●UGA（user global area）：在共享服务器模式下，当没有配置大池时，UGA会占用共享池的空间；

使用v$LIBRARYCACHE查询实例启动后所有库缓存的活动；

SQL>select sum(pins) as 请求存取数 ,sum(reloads) as 不命中数 from v$librarycache;

库高速缓存总不命中数与总存取数之比应当接近零。当该比率如果接近或大于1%时，可采用如下方法减少不命中数：

●增加初始化参数shared_pool_size的值，提高库高速缓存中可用的内存数量，为取得好的效果，还要增加初始化参数open_cursors的值，以提高对话允许的光标数。但为库缓存分配太多的内存可能引起调页或交换；

●写等价的SQL语句，尽可能让SQL语句和PL/SQL块共享一个SQL区，以减少库缓存的不命中。注意大小写和空格也要等价；

使用视图V$ROWCACHE查询数据字典高速缓存的活动情况；

SQL>select sum(gets) as 请求存取数, sum(getmisses) as 不命中数 from v$rowcache;

使用视图V$SESSTAT和V$STATNAME查询Orace收集对话信息使用的总内存的统计；

#显示当前分配给所有会话的内存数

SQL>select sum(value)|| '字节' as 当前分配给所有会话的内存数 from v$sesstat,v$statname where name='session uga memory' and v$sestat.statistic#=v$statnam.statistic#;

#显示曾经分配给所有会话的最大内存数

SQL>select sum(value)|| '字节' as 曾经分配给所有会话的最大内存数 from v$sesstat,v$statname where name='session uga memory max' and v$sestat.statistic#=v$statnam.statistic#;

13.2.4 调整数据库缓冲区

Orace启动后不断收集和统计数据存取情况，并将其存放在V$SYSSTAT视图中；有以下几种统计：

●dbblock gets：该统计值为数据请求的总数；

●consistent gets：该统计值为通过对内存缓冲区存取既能满足的请求数；

●physical reads：该统计值为磁盘文件存取的总数；

#查询一段时间内V$SYSSTAT表中的统计信息：

SQL>select name,value from v$sysstat where name in ('db block gets','physical reads');

计算缓冲区高速缓存的命中率公式: 1-(physical reads/(db block gets+consistent gets))

13.3 排序区优化

在Oracle数据库中，用户数据的排序可使用的两个区域：内存排序区，磁盘临时段；

可使用sort_area_size设置排序区大小；

13.3.1 排序区与其他内存区的关系

大池(large_pool)用于数据库备份和恢复工具，large_pool的大小由large_pool_size确定；

SQL>show parameter large_pool_size #查看参数large_pool_size的值；

SQL>alter system set large_pool_size=32m; #设置large_pool_size的大小；

13.3.2 排序活动

在以下情况下，需要进行排序:

●在创建索引时；

●使用Order by的SQL语句

●使用Group by的SQL语句

●进行table join时，由于现有索引的不足而导致SQL优化器调用MERGE SORT;

13.3.3 专用模式下排序区的调整

（1）sort_area_size

推荐使用pga_aggregate_target进行PGA内存自动管理；

（2）sort_area_retained_size

该参数指定了在排序操作完成后，继续保留用户全局区UGA内存的最大大小，以维护内存中的排序，直到所有数据行被返回后才释放回UGA；

设置了pga_aggregate_target参数后，该参数无效；

参数workarea_size_policy决定采用手动管理还是自动管理；

SQL>show parameter pga_aggregate_target; #查看所有服务器进程使用的内存的总量；

SQL>show parameter workarea_size_policy; #查看PGA采用的管理方式

SQL>show parameter sort_area_size; #查看会话信息及堆栈空间的大小

13.3.4 共享模式下排序区的调整

对于大事务、长事务、超短事务、小事务：使用专用连接；

对于网站：使用共享连接；

********************************************************************************************************************

十四、优化SQL语句

14.1 常规SQL语句优化

14.1.1 建议不用*来代替所有列名

建议用户在写SELECT语句时，采用与访问表有关的实际列名；

14.1.2 用TRUNCATE代替DELETE

当使用DELETE删除表中的数据行时，Oracle会使用撤销表空间来存放恢复的信息；

当使用TRUNCATE语句对表的数据进行删除时，系统不会将被删除的数据写到回滚段中。TRUNCATE的语法如下：

truncate [table | cluster] schema.[table_name] [cluster_name] [drop | reuse storage]

table_name：要清空的表名；

cluster_name：要清空的簇名；

drop|reuse storage：表示保留被删除的空间以供该表的新数据使用，默认为drop storage;

#创建一个存储过程，实现使用TRUNCATE命令动态删除数据表

create or replace procedure tun_table(table_deleted in varchar2) as #实现清空指定的表

cur_name integer; #定义内部变量，存储打开的游标

begin

cur_name:=dbms_sql.open_cursor; #打开游标

dbms_sql.parse(cur_name,'truncate table'||table_deleted||'drop storage',dbms_sql.navite); #指定truncate语句清空指定的表

dbms_sql.close_cursor(cur_name);

exception

when others then dbms_sql.close_cursor(cur_name); #出现异常，关闭游标

raise;

end trun_table;

14.1.3 在确保完整性的情况下多用COMMIT语句

COMMIT所释放的资源如下：

●回滚段上用于恢复数据的信息，撤销表空间也只做短暂的保留；

●被程序语句获得的锁；

●redo log buffer中的空间；

●Oracle为管理上述资源的内部花费；



14.1.4 尽量减少表的查询次数

14.1.5 用[NOT] EXISTS 代替[NOT ] IN

14.3 合理使用索引

14.3.5 监视索引是否被使用

使用ALTER INDEX...MONITORING USAGE语句，语法如下：alter index schema.index_name monitoring usage;

SQL>alter index grade_index monitoring usage; #设置监视索引grade_index

SQL>select * from v$object_usage; #检查索引使用情况

SQL>drop index grade_index; #删除索引

14.4 优化器的使用

14.4.2 运行EXPLAIN PLAN

建议在用户的帐户下执行UTLXPLAN脚本来完成PLAN_TABLE表的创建，该表存放执行计划信息；

运行该脚本后，Oracle在用户的帐户下创建TABLE_NAME表。

让系统生成某个SQL语句的执行计划，使用EXPLAIN PLAN命令，语法如下：explain plan [set statement_id [=] ] [into

] for

********************************************************************************************************************

十五、Oracle数据备份与恢复

15.1 备份与恢复概述

Oracle能实现2种备份方式：物理数据备份和逻辑数据备份

物理数据备份适合于：数据文件、控制文件、归档重做日志；

备份一个Oracle数据库有3种标准方式：导出、脱机备份和联机备份。

********************************************************************************************************************

十六、数据导出和导入

16.1 EXPDP和IMPDP概述

数据泵导出使用工具EXPDP将数据库对象的元数据或数据导出到转储文件中；

数据泵导入使用工具IMPDP将转储元件中的元数据及数据导入到Oracle数据库中；

16.2 EXPDP导出数据

EXPDP可以导出表、用户模式、表空间和全数据库4种数据；

EXPDP是服务器端工具，只能在服务器端使用，不能在Oracle客户端使用；

EXPDP工具只能将导出的转储文件放在DIRETORY对象对应的OS目录中，而不能直接指定转储文件所在的OS目录。因此，使用EXPDP工具，必须首先建立DIRETORY对象，

并且需要为数据库用户授予DIRETOEY对象的权限。

16.2.2 EXPDP命令参数

（1）CONTENT

该参数用于指定要导出的内容，默认值为ALL，语法如下：content={all | data_only| metadata_only}

all：将导出对象定义及其所有数据；

data_only：只导出对象数据；

metadata_only：只导出对象定义；

（2）QUERY

该参数用于指定过滤导出数据的WHERE条件，语法如下：query=[schema.][table_name:] query_clause

schema：用于指定模式名；

table_name：用户指定表名；

query_clause：用户指定条件限制子句；

query参数不能与content=metadata_only, extimate_only, transport_tablespace等参数同时使用；

（3）DIRETORY

该参数指定存储文件和日志文件所在的目录，语法如下 : diretory=diretory_object

diretory_object：指定目录对象的名称；

（4）DUMPFILE

该参数用于指定转储文件的名称，默认名称为expdat.dmp，语法如下：dumpfile=[diretory_object:]file_name[,...]

diretory_object：用于指定目录对象名；

file_name：用户指定转储文件名；

（5）FULL

该参数用于指定数据库模式导出，默认为n, 语法如下: full={y|n}

当设置该选项为y时，表示执行数据库导出；

（6）LOGFILE

该参数用于指定导出日志文件的名称，默认名称为export.log，语法如下：logfile=[diretory_object:]file_name

（7）STATUS

该参数用于指定显示导出作业进程的详细信息，默认值为0,语法如下: status=integer

integer：用于指定显示导出作业状态的时间间隔，单位为秒。

（8）TABLES

该参数用于指定表模式导出，语法：Tables=[Schema_name.]table_name[:partition_name][,...]

schema_name：用于指定模式名；

table_name：用于指定要导出的表名；

partition_name：用于指定要导出的分区名；

（9）TABLESPACES

该参数用于指定要导出的表空间列表；

16.3 IMPDP导入数据

16.3.2 IMPDP命令参数

（1）REMAP_SCHEMA

该参数用于将源模式中的所有对象转载到目标模式中，语法如下: remap_schema=source_schema:target_shema

source_schema：源模式

target_schema：目标模式

（2）REMAP_TABLESPACE

该参数用于指定导入时更改表空间名称。语法如下：remap_tablespace=source_tablespace:target_tablespace

source_tablespace：源表空间

tartget_tablespace：目标表空间

（3）SQLFILE

使用该参数，可以从DMP文件中提取对象的DDL语句，语法如下：sqlfile=[diretory_object:]file_name

file_name：表示包含DDL语句的文件；

（4）TABLE_EXISTS_ACTION

该参数用于指定当表已经存在时导入作业要执行的操作，语法如下：tables_exists_action={skip | append | truncate | replace}

skip：导入作业会跳过已存在表处理下一个对象；

append：会追加数据；

truncate：导入作业会截断表，然后为其追加新数据；

replace：导入作业会删除已存在表，然后重建表追加数据；

（5）TRANSPORT_DATAFILES

该参数用于指定移动空间时要被导入到目标数据库的数据文件，语法如下：transport_datafiles=datafile_name

datafile_name：用于指定被复制到目标数据库的数据文件。

16.4 SQL*Loader工具

使用SQL*Loader导入数据时，必须编辑一个控制文件(.CTL)和一个数据文件(.DAT).

控制文件用于描述要加载的数据信息，包括数据文件名、数据文件中数据的存储格式、文件中的数据要存储到哪一个字段、哪些表和列要加载数据、数据的加载方式等；

巴特西

Oracle从入门到精通(笔记)

最新文章

热门文章