Oracle数据库基础及应用第10章管理表空间和文件课件

1、管理表空间和文件第1页，共80页。本章内容10.1 表空间概述10.2 管理表空间10.3 管理数据文件10.4 控制文件的管理10.5 日志文件的管理第2页，共80页。 10.1 表空间概述表空间是Oracle数据库中最大的逻辑结构。Oracle数据库是由一个或多个表空间组成。表空间和数据库物理结构联系密切。它在物理上与磁盘上的数据文件相对应。数据逻辑上存放在表空间中；物理上存放在数据文件中。第3页，共80页。Oracle数据库的存储结构第4页，共80页。表空间的作用控制数据库所占用的磁盘空间。控制用户所占用的表空间配额。通过表空间管理数据流向，提高I/O性能。将表空间设置为只读状态从而保持

2、静态数据。表空间提供了一个备份和恢复单位。第5页，共80页。 表空间的类型按是否存放的是系统数据分：按表空间的存放内容分：按表空间中文件的大小及最多个数分：第6页，共80页。表空间的类型 I按是否存放的是系统数据分：系统表空间： SYSTEM：存储Oracle的系统数据（如数据字典表）。创建数据库时，SYSTEM第一个被创建，具有较高的管理权限，不能被脱机。 SYSAUX：是Oracle 新引入的辅助系统表空间。存放其他可选组件的系统数据。 两者都不可以被删除和更改。非系统表空间： 分离不同类型数据信息，便于管理表空间。第7页，共80页。表空间的类型II按表空间内容分： 永久性表空间、临时表空

3、间、撤销表空间 select tablespace_name,contents from dba_tablespaces; 永久性表空间：存放永久性数据。 临时表空间：存储的是临时性数据。 （order by等SQL语句会产生大量的临时数据。在创建用户时，要给其指定一个临时表空间，存放其操作产生的临时数据，如果没有指定，就会增用系统表空间，影响服务器效率。） 撤销表空间：存放回滚段的数据信息。第8页，共80页。表空间的类型III大文件表空间、小文件表空间 select * from v$tablespace; select tablespace_name,bigfile from dba_ta

4、blespaces; 大文件表空间：是Oracle引入的表空间，为超大数据库设计的。大文件表空间只能放置一个数据文件，数据文件可以包括4G个数据块。最大可以达到32TB。 小文件表空间：可以放置1022个数据文件。 第9页，共80页。表空间的区、段管理方式区（Extent）的管理方式段 （Segment）的管理方式第10页，共80页。表空间的区管理方式 针对区的分配方式的不同，表空间的管理有两种管理方式：数据字典管理方式（DMT） 传统管理方式。10g已经不再使用该方式。 Oracle通过对数据字典中相关的基础表进行更新来分配新区、回收旧区。本地管理方式（LMT） 11g默认的管理方式。表空间

5、中所有数据文件头均有一个位图（bitmap），用于记录表空间所有区的分配情况。更新该位图来实现区的分配和回收。不对任何表作更新操作。第11页，共80页。在本地管理方式下，可以用关键字来指定表空间区的分配方式:UNIFORM ：统一分配，所有区大小都相同。AUTOALLOCATE：自动分配，指定由Oracle来自动管理区大小，Oracle会自动选择合适的大小。第12页，共80页。表空间的段管理方式在本地管理方式中，还可以指定段的管理方式：段的管理主要是Oracle管理段中已用数据块和空闲数据块的机制。MANUAL（手工）: 通过空闲列表管理。AUTO（自动）: 使用位图管理。根据位图中取值来判断

6、数据块是否可用。效率高。第13页，共80页。表空间的状态一、读写（read write）二、只读（Read only）三、脱机状态（Offline） 正常(Normal) ：执行检查点，不需要恢复。 临时（Temporary）：执行检查点，但不必保证所有数据文件都联机可用；如脱机时，有文件不可用，忽略错误。可能需要恢复。 立即（Immediate）：不执行检查点。需要恢复。 用于恢复（For Recover）：用于恢复的关闭方式。第14页，共80页。10.2 管理表空间10.2.1管理表空间原则1. 使用多重表空间。将用户数据与数据字典数据分离，并将不同表空间的数据文件分别存储在不同磁盘上可以

7、降低I/O竞争；将一个应用的数据与其他应用分离，可以避免表空间脱机时多个应用受到影响；可根据需要将单个表空间脱机，从而获得较好的可用性；通过为不同类型的数据库预留表空间，以达到优化表空间的目的，如更新较高的或只读、或临时段存储等；备份单个表空间。第15页，共80页。2. 为用户指定表空间限额。要创建、管理与使用表空间，必须首先以Sys用户并以as sysdba身份登录数据库。启动SQL*Plus时的帐户和口令不需要加引号。命令格式：sqlplus sys/as sysdba第16页，共80页。10.2.2 创建表空间用户必须拥有Create tablespace权限才能创建表空间。所有的表空间

8、都应有sys用户来创建。在表空间的创建中，Oracle完成几个工作： 在数据字典、控制文件中记录该表空间。 在操作系统中指定相应的物理文件对应数据文件。 在预警文件中记录下创建表空间的信息。第17页，共80页。1.创建永久性的表空间1. 要指定表空间名；2. 指定表空间的类型;3. 指定和表空间物理对应的数据文件及属性；4. 指定区的管理参数；5. 指定段的管理参数；6. 指定其他属性 logging / nologging 等第18页，共80页。命令格式：SQLCREATEundoTABLESPACE tablespace_name datafile filespecautoextend_c

9、lause,filespecautoextend_clause minimum extent integerk|m|blocksize integerk| logging|nologging|default storage_clause|online|offline| permanent|temporary| extent_management_clause| segment_management_clause;Create undo tablespace:创建还原表空间，用于存入重做日志文件Create temporary tablespace:创建临时表空间，用于临时数据的存放；Creat

10、e tablespace:创建存放用户数据表空间Tablespace name: 表空间名datafile filespec: 指定数据文件的具体位置和大小如果有多个文件时，用逗号“，”隔开文件名必须为绝对地址，而不能是相对地址Logging|nologging: 是否创建重做日志Extent management local: 存储区管理方法本地管理(LOCAL):用二进制的方式管理磁盘,有很高的效率,同进能最大限度的使用磁盘.同时能够自动跟踪记录临近空闲空间的情况，避免进行空闲区的合并操作。UNIFORMSEGMENTSPACEMANAGEMENT:指定区大小，也可使用默认值(1MB)se

11、gment management local: 磁盘扩展管理方法使用该选项时区大小由系统自动确定。AUTO:只能使用在本地管理的表空间中.使用LOCAL管理表空间时,数据块中的空闲空间增加或减少后，其新状态都会在位图中反映出来。第19页，共80页。例：创建一个名为tbspace的表空间：SQLCREATE TABLESPACE tbspace nologging datafile D:appAdministratororadatatbspacetbspace01.ora size 50m blocksize 8192 extent management local uniform size 2

12、56k segment space management auto;第20页，共80页。2.使一个表空间脱机命令格式：SQLALTER TABLESPACE offline; 例：将表空间tbspace脱机：SQLALTER TABLESPACE tbspace offline; 注意:SYSTEM表空间不能脱机第21页，共80页。3.使一个表空间联机命令格式：SQLALTER TABLESPACE online; 例：将表空间tbspace联机：SQLALTER TABLESPACE tbspace online; 第22页，共80页。4.使表空间只读命令格式：SQLALTER TABLES

13、PACE read only; 例：将表空间tbspace只读：SQLALTER TABLESPACE tbspace read only; 第23页，共80页。5.使表空间可读可写命令格式：SQLALTER TABLESPACE read write; 例：将表空间tbspace脱机：SQLALTER TABLESPACE tbspace read write; 第24页，共80页。6.创建临时表空间命令格式：SQLCREATE TEMPORARY TABLESPACE temfile size m autoexten extent management local uniform size

14、;例：创建临时表空间tempSQLCREATE TEMPORARY TABLESPACE temp tempfile D:appAdministratororadatatemptemp01.ora size 500m autoextend off extent management local uniform size 512k;第25页，共80页。7.添加临时表空间的数据文件命令格式：SQLALTER TABLESPACE add tempfile size m; 例：为临时表空间temp_ren添加数据文件：SQLALTER TABLESPACE temp_ren add tempfile

15、 D:appAdministratororadatatemptemp.dbf size 100m; 第26页，共80页。8.调整临时表空间的数据文件命令格式：SQLALTER DATABASE tempfile resize m; 例：调整临时表空间的数据文件大小SQLALTER DATABASE tempfile D:appAdministratororadatatemptemp.ora resize 20m; 第27页，共80页。9.将表空间的数据文件或临时文件脱机命令格式：SQLALTER DATABASE datafile offline; 或SQLALTER DATABASE tem

16、pfile offline;示例：SQLALTER DATABASE datafile D:appAdministratororadatatemptemp.ora offline; 第28页，共80页。10.将临时表空间联机命令格式：SQLALTER DATABASE tempfile online; 例：SQLALTER DATABASE tempfile D:appAdministratororadatatemptemp.ora online; 第29页，共80页。11.删除表空间，但不删除其文件命令格式：SQLDROP TABLESPACE ; 例：删除表空间tbspace，但不删除其文

17、件SQLDROP TABLESPACE tbspace ; 第30页，共80页。12.删除包含目录内容的表空间命令格式：SQLDROP TABLESPACE including contents; 例：删除表空间tbspace及其包含的内容：SQLDROP TABLESPACE tbspace including contents; 第31页，共80页。13.删除包含目录内容和数据文件在内的表空间命令格式：SQLDROP TABLESPACE including contents and datafiles; 例：将表空间tbspace及其包含的内容、数据文件及相关约束一同删除：SQLDROP

18、 TABLESPACE tbspace including contents and datafiles; 第32页，共80页。14.当含有参照笥约束时，删除包含目录内容和数据文件在内的表空间命令格式：SQLDROP TABLESPACE including contents and datafiles cascade constraints; 例：将表空间tbspace及其包含的内容、数据文件及相关约束一同删除：SQLDROP TABLESPACE tbspace including contents and datafiles cascade constraints; 第33页，共80页。

19、15.表空间更名说明：在Oracle 11g中可直接更名永久表空间和临时表空间。但是，SYSTME和SYSAUX表空间不能更名。命令格式：SQLALTER TABLESPACE rename to ; 例：将表空间tbspace更改为newtbspace：SQLALTER TABLESPACE tbspace rename to newtbspace; 第34页，共80页。10.2.3 查询表空间V$tablespace DBA_TablespacesALL_TablespacesUSER_TablespacesDBA_segmentsALL_segmentsUSER_segmentsDBA_

20、extentsALL_extentsUSER_extentsV$datafileV$tempfileDBA_datafilesDBA_tempfilesDBA_ts_quotasDBA_free_spaceUSER_free_space第35页，共80页。系统数据字典dba_tablespaces中记录了关于表空间的信息：select * from sys.dba_tablespaces;字段含义字段含义tablespace_nameTablespace_namemin_extlenminimum extent size for the tablespaceblock_sizetablespa

21、ce block sizestatusonline,offline readinitial_extentdefault initial extent sizecontentspermanent, temporary, undonext_extentdefault incremental extent sizeloggingdefault logging attributemin_extentsdefault minimum number of extentsforce_loggingtablespace force logging modemax_extentsdefault maximum

22、number of extentsextent_managementdictionary, localpct_increasedefault percent increase for extent sizeallocation_typetype of extent allocation in effect for this tablespaceplugged_insegment_space_managementauto, manual第36页，共80页。列出表空间、表空间文件、分配的空间、空闲空间以及下一个空闲分区第37页，共80页。列出数据文件、表空间名以及大小：第38页，共80页。列出表空

23、间、大小、空闲空间以及空闲空间的百分比第39页，共80页。计算表空间每个数据文件实际的最小空间以及对应的文件名，其大小与磁盘操作系统中显示的不同第40页，共80页。10.3 管理数据文件10.3.1数据文件数据文件是用于存储数据库中数据的操作系统文件。创建数据库时会创建几个Oracle系统使用的数据文件；数据文件与表空间密不可分的，创建表空间同时必须为该表空间创建对应的数据文件；数据文件依赖于表空间，不能独立存在，在创建数据文件时必须指定隶属的表空间。否则，不会被存取。 DBA可以单独创建数据文件，并必须指定其隶属的表空间，也可以单独对其进行管理。第41页，共80页。10.3.2 创建数据文件

24、创建数据文件的过程实质上就是向表空间中添加文件的过程。在创建表空间时，通常会预先估计表空间所需要的存储空间大小，然后为它建立若干适当大小的数据文件。如果在使用过程中发现表空间存储空间不足，可以再为它添加新的数据文件。第42页，共80页。10.3.2 创建数据文件可以在创建表空间、修改表空间、创建数据库时创建数据文件。Create tablespace . datafile语句Alter tablespace . Add datafile 语句Create temporary tablespace . tempfile语句Alter tablespace add tempfile 语句Creat

25、e database语句例：下面的语句为表空间tbspace添加一个大小为20MB的数据文件SQLALTER TABLESPACE USER01 2 add datafile D:ORADATATESTtbspace01.dbf size 20m;第43页，共80页。10.3.3 修改数据文件大小除了创建新的数据文件外，另一种增加表空间的存储空间的方法是改变已经存在的数据文件的大小。改变数据文件大小的方式有两种：设置数据文件为自动增长 手动改变数据文件的大小第44页，共80页。1. 设定数据文件自动增长方式 1）创建表空间时设定Create tablespace Datafile|tempfi

26、le size . Autoextend on next . Maxsize .2）表空间中增加一个新的自增文件Alter database add datafile size Autoextend on next maxsize 3）取消已有数据文件的自增方式Alter database Datafile Autoextend off;4）如果数据文件已创建，现要修改为自增 Alter database datafileAutoextend on next maxsize;第45页，共80页。2. 手动改变数据文件大小 手动方式改变数据文件大小时，需要使用resize语句。例如，下面的语句将

27、数据文件的大小增长为100MB。SQL ALTER DATABASE 2 datafile D:appAdministratorORADATATESTmy02_1.dbf 3 resize 100m;第46页，共80页。10.3.4 移动数据文件因不同原因，在创建数据文件后，需要考虑移动数据文件到新的位置。例如：将一个迅速增长的文件移至磁盘空间较大的盘区；调整I/O性能；当某个磁盘损毁时，为了使数据库系统继续运行，也可能要将一个或几个数据文件移动到其他磁盘上。移动分为两种情况：同一表空间下的数据文件移动、多个表空间下的数据文件移动。注意：移动数据文件改变数据库物理结构，在操作之前要对数据库进行

28、备份。以便操作失败恢复。在完成后要及时备份控制文件，记录下修改后的数据库物理结构。第47页，共80页。1.同一表空间的数据文件移动如：将demo表空间下的数据文件由默认位置移至D:下。步骤 ： （在open状态下操作） 1. 使表空间脱机； 2. 移动数据文件； 3. 执行 alter tablespace 表空间名 rename datafile 文件名, 文件名 to 文件名, 文件名; 4. 使表空间连机； 5. 备份控制文件第48页，共80页。2.多个表空间的数据文件移动如:将teachertbs表空间的teachertbs02.dbf、studenttbs表空间的studenttbs

29、02.dbf由默认位置移至D:下。步骤: 1. 使表空间脱机； 2. 移动数据文件； 3. 使用 alter database 数据库名 rename datafile 文件名, 文件名 to 文件名, 文件名; 4. 使表空间联机； 5. 备份控制文件。第49页，共80页。删除数据文件当表空间的内容不在需要时，就可以将其删除。除系统表空间及temp、undotbs外。删除表空间的内容及数据文件。SQL alter tablespace second offline;SQL drop tablespace second including contents and datafiles;SQL

30、alter tablespace tmp tempfile offline;SQL drop tablespace tmp including contents and datafiles;第50页，共80页。查询数据文件Dba_tablespacesDba_data_filesDba_temp_filesV$datafileV$tempfile第51页，共80页。10.4 控制文件的管理10.4.1 控制文件概述 每个Oracle数据库都必须具有至少一个控制文件。控制文件时一个二进制格式的操作系统文件，其中记录了关于数据库物理结构的基本信息，包括数据库的名称、相关数据文件的名称和位置、当前的

31、日志序列号等内容用于描述和维护数据库的物理结构。数据库的启动和正常运行都离不开控制文件。 启动数据库时，Oracle从初始化参数文件中获得控制文件的名字及位置，打开控制文件，然后从控制文件中读取数据文件和联机日志文件的信息，最后打开数据库。数据库运行时，Oracle会修改控制文件，所以，一旦控制文件损坏，数据库将不能正常运行。第52页，共80页。10.4.2 创建控制文件通常创建控制文件的时候有两种：创建数据库时创建初始控制文件。在创建数据库之后再创建控制文件。 所有数据库控制文件遭到永久破坏，没有任何备份。希望改变数据库名称或其他一些数据库参数的永久性设置。第53页，共80页。创建控制文件使

32、用create controlfile语句：create controlfilereuse database “test”logfile group 1 c:oradatatestredo01.log size 50M, group 2 c:oradatatestredo02.log size 50M, group 3 c:oradatatestredo03.log size 50Mnoresetlogsnoarchivelog datafile c:oradatatestsystem01.dbf, c:oradatatestsysaux01.dbf, c:oradatatestundotbs

33、01.dbf, c:oradatatestusers01.dbfmaxlogfiles 16maxlogmembers 3maxloghistory 292maxdatafiles 100maxinstances 8character set zhs16gbk;第54页，共80页。创建控制文件的步骤如下：1）获取数据库的数据文件和重做日志文件列表；2）关闭数据库。Shutdown immediate3）备份所有的数据文件、重做日志文件和spfile参数文件；4）使用startup nomount启动数据库实例；5）使用create controlfile语句创建控制文件。如果需要重命名数据库，

34、则使用resetlogs子句，否则使用noresetlogs子句；6）将新的控制文件备份到其他不在线的存储介质中，如U盘、移动硬盘或磁带等；7）根据实际情况修改control_file参数；如果修改了数据库名称，则还需要修改db_name参数；8）如果需要的话，则恢复数据库；9）如果在第8步进行了恢复数据库的操作，则需要执行alter database open语句打开数据库。如果在创建控制文件时使用了resetlogs子句，则需要使用alter database open resetlgos语句。第55页，共80页。10.4.3 恢复控制文件如果控制文件被破坏，但存储控制文件的目录仍然是可以

35、访问的，可以使用下面的方法恢复：1）关闭数据库实例；2）使用操作系统将控制文件副本复制到控制文件目录下；3）使用startup命令打开数据库实例。如果存储介质被破坏，导致存储控制文件的目录无法访问，则可以使用下面的方法恢复：1）关闭数据库实例；2）使用操作系统将控制文件副本复制到一个新的可以访问的目录下；3）修改control_files参数，将无效的控制文件目录修改为新的目录；4）使用startup命令打开数据库实例。第56页，共80页。删除控制文件修改初始化参数文件SPFILE中的初始化参数control_files，删除不再需要的控制文件的名称。1）关闭数据库。2）物理移出不需要的控制文

36、件，也可不作。3）重启数据库。第57页，共80页。移动控制文件修改初始化参数文件SPFILE中的初始化参数control_files，修改需移动的控制文件的名称。1）关闭数据库。2）移动需要移动的控制文件到新位置。3）重启数据库。第58页，共80页。10.5 日志文件的管理日志文件又被称为重做日志文件（redo log file），用来记录Oracle数据库中的每一个更改操作。oracle的日志文件是记录数据库变化的一个凭证, 就是oracle对于一切数据库的操作的记录 方便以后查找分析错误，有可以恢复数据等作用。重做日志文件是由一条条重做记录组成的，重做记录是由一个个修改向量(change

37、vector)组成的。每个修改向量对应数据块。重做日志文件是保存在磁盘上的一个实际的文件，空间有限，所以，每个Oracle数据库至少要包含两个或两个以上的重做日志文件组，LGWR后台进程以循环的方式将重做记录写入其中。第59页，共80页。10.5.2 增加日志文件一般情况下，我们先对数据库的重做日志文件组及成员进行数量、大小的规划，并且在创建数据库时创建相应的重做日志文件组及成员。在特定情况下，如果发现LGWR经常处于等待状态，则需要考虑添加日志组及其成员，一个数据库最多可以拥有MAXLOGFILES个日志组。增加重做日志是使用alter database语句完成的，执行该语句时要求用户具有a

38、lter database系统权限。第60页，共80页。1.添加重做日志文件组当警告文件出现了checkpoint not complete时，应该增加日志组。增加日志组的目的是为了确保DBWR进程和ARCH进程不会妨碍LGWR进程工作，进而提高系统性能。语法：Alter database add logfile group n(logfile1 reuse,logfile2 reuse,logfile3 reuse,logfilem reuse) size integer M；第61页，共80页。日志组1日志组4ALTER DATABASE ADD LOGFILE GROUP 4 (D:re

39、do04_1.log,D:redo04_2.log)SIZE 10M;redo04_2.logRedo04_1.logredo01.logredo01_1.log日志组2redo02.logredo02_1.log日志组3redo03.logredo03_1.log第62页，共80页。建议：不指定组号。新组号是自动分配的下一个组号，不建议使用跳跃式组号，会耗费控制文件的空间。Select * from v$log;新组的状态为UNUSED，当首次使用日志成员时，该成员变为活动的。第63页，共80页。添加一个新的重做日志文件组，组号5，该组有两个成员，分别 redo05_1.log和redo05

40、_2.log。大小为10M添加一个新的重做日志文件组。第64页，共80页。ALTER DATABASE ADD LOGFILE (redo05_1.log,redo05_2.log) size 10m;ALTER DATABASE ADD LOGFILE；第65页，共80页。2.添加重做日志文件组的成员增加日志成员即多元化重做日志，避免某个日志组的某个成员损坏后系统不能正常运转。语法：Alter database add logfile memberLogfile reuseTo group n;第66页，共80页。ALTER DATABASE ADD LOGFILE MEMBERD:redo

41、01_3.log TO GROUP 1,D:redo02_3.log TO GROUP 2,D:redo04_3.log TO GROUP 3;日志组1日志组2日志组3Redo01.logRedo01_2.logRedo02.logRedo02_2.logRedo01_3.logRedo02_3.logredo03.logRedo03_2.logRedo03_3.log第67页，共80页。建议：不指定大小。新成员的大小是由组中现有成员的大小决定的。Select * from v$logfile;新成员的状态为INVALID，当首次使用日志成员时，该成员变为活动的。第68页，共80页。在MOUN

42、T状态下移动重做日志文件：关闭数据库，启动到mount移动重做日志文件Alter database rename file语句将数据库启动到OPEN状态备份控制文件或数据库。10.5.3 移动重做日志文件第69页，共80页。10.5.4 删除日志组及成员当日志成员损坏或者丢失时，应该删除该日志成员。当日志组大小不合适时，需要重新建立日志组，并删除原来的日志组。删除重做日志是使用alter database语句完成的，执行该语句时要求用户具有alter database系统权限。第70页，共80页。1.删除日志成员ALTER DATABASE DROP LOGFILE MEMBER D:redo02_2.l