Oracle表空间操作详解(备份)

建立表空间
CREATE TABLESPACE data01
DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M
UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k

删除表空间
DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

修改表空间大小
alter database datafile '/path/NADDate05.dbf' resize 100M

移动表至另一表空间
alter table move tablespace room1;

一、建立表空间
CREATE TABLESPACE data01
DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M
UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k

二、建立UNDO表空间
CREATE UNDO TABLESPACE UNDOTBS02
DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M

#注意：在OPEN状态下某些时刻只能用一个UNDO表空间，如果要用新建的表空间，必须切换到该表空间:

ALTER SYSTEM SET undo_tablespace=UNDOTBS02;

三、建立临时表空间
CREATE TEMPORARY TABLESPACE temp_data
TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M

四、改变表空间状态

1.使表空间脱机
ALTER TABLESPACE game OFFLINE;
如果是意外删除了数据文件，则必须带有RECOVER选项
ALTER TABLESPACE game OFFLINE FOR RECOVER;

2.使表空间联机
ALTER TABLESPACE game ONLINE;

3.使数据文件脱机
ALTER DATABASE DATAFILE 3 OFFLINE;

4.使数据文件联机
ALTER DATABASE DATAFILE 3 ONLINE;

5.使表空间只读
ALTER TABLESPACE game READ ONLY;

6.使表空间可读写
ALTER TABLESPACE game READ WRITE;

五、删除表空间
DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

六、扩展表空间

首先查看表空间的名字和所属文件
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;

1.增加数据文件
ALTER TABLESPACE game
ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;

2.手动增加数据文件尺寸
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf'
RESIZE 4000M;

3.设定数据文件自动扩展
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf'
AUTOEXTEND ON NEXT 100M
MAXSIZE 10000M;

4.设定后查看表空间信息
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

 

2）修改表空间

          需要注意的是：不能将本地管理的永久性表空间转换为本地管理的临时表空间，也不能修改本地管理表空间中段的管理方式。

           1）  扩展表空间

                   a) 为表空间添加数据文件

                     通过ALTERTABLESPACE…ADD DATAFILE语句为永久表空间添加数据文件，通过ALTER TABLESPACE… ADD TEMPFILE语句为临时表空间添加临时数据文件。

                     举例：

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

                     SQL>ALTER TABLESPACE ORCLTBS1ADD DATAFILE

                                'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ORCLTBS1_2.DBF'SIZE 10M;

                     为ORCL数据库的ORCLTEMP1表空间添加一个大小为10 MB的临时数据文件。

                     SQL>ALTER TABLESPACE ORCLTEMP1ADD TEMPFILE

                               'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ORCLTEMP1_2.DBF'SIZE 20M;

                  b)  改变数据文件的大小

                        可以通过改变表空间已有数据文件的大小，达到扩展表空间的目的。

                        举例：

                        将ORCL数据库的ORCLTBS1表空间的数据文件ORCLTBS1_2.DBF大小增加到20 MB。

                       SQL>ALTER DATABASE DATAFILE

                                  'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ORCLTBS1_2.DBF'RESIZE 20M;

                 c) 改变数据文件的扩展方式

                     如果在创建表空间或为表空间增加数据文件时没有指定AUTOEXTENDON选项，则该文件的大小是固定的。如果为数据文件指定了AUTOEXTENDON选项，当数据文件被填满时，数据文件会自动扩展，即表空间被扩展了。

                 举例：

                 将ORCL数据库的ORCLTBS1表空间的数据文件ORCLTBS1_2.DBF设置为自动扩展，每次扩展5 MB空间，文件最大为100 MB。

                 SQL>ALTER DATABASE DATAFILE

                           'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ORCLTBS1_2.DBF'

                           AUTOEXTEND ON NEXT 5M MAXSIZE100M;

           2）  修改表空间可用性

                   离线状态的表空间是不能进行数据访问的,所对应的所有数据文件也都处于脱机状态。

                  一些表空间必须是在线状态:SYSTEM表空间，存放在线回退信息的撤销表空间，临时表空间。

                  语法：

                  ALTER TABLESPACE tablespace_nameONLINE|OFFLINE

                 举例：

                 在归档模式下，将USERS表空间中所有的数据文件脱机，但USERS表空间不脱机。然后再将USERS表空间中的所有数据文件联机。

                 SQL>ALTER TABLESPACE USERS DATAFILEOFFLINE;

                 SQL>RECOVER TABLESPACE USERS;

                 SQL>ALTER TABLESPACE USERS DATAFILEONLINE;

           3）  修改表空间的读写性

                   语法：

                   ALTER TABLESPACE tablespace_nameREAD ONLY|READ WRITE

                   表空间只有满足下列要求才可以转换为只读状态：

                   a)      表空间处于联机状态；

                   b)      表空间中不能包含任何活动的回退段；

                   c)      如果表空间正在进行联机数据库备份，不能将它设置为只读状态。因为联机备份结束时，Oracle更新表空间数据文件的头部信息。

                  示例：

                  SQL>ALTER TABLESPACE ORCLTBS1 READ ONLY;

                  SQL>ALTER TABLESPACE ORCLTBS1 READ WRITE

           4）  设置默认表空间

                   在Oracle10g数据库中，默认表空间为USERS表空间，默认临时表空间为TEMP表空间。

                   设置数据库的默认表空间：

                     ALTER DATABASE DEFAULT TABLESPACE

                   设置数据库的默认临时表空间：

                     ALTER DATABASE DEFAULT TEMPORARY TABLESPACE 

                   示例：

                    SQL>ALTERDATABASE DEFAULT TABLESPACE ORCLTBS1;

                    SQL>ALTERDATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

            5）  表空间重命名

                    语法：

                     ALTER TABLESPACE…RENAME TO

                   当重命名一个表空间时数据库会自动更新数据字典、控制文件以及数据文件头部中对该表空间的引用。在重命名表空间时，该表空间ID号并没有修改，如果该表空间是数据库默认表空间，那么重命名后仍然是数据库的默认表空间。

                   注意：

                    不能重命名SYSTEM表空间和SYSAUX表空间

                    不能重命名处于脱机状态或部分数据文件处于脱机状态的表空间。

 

（3）表空间的备份

          语法：

            ALTER TABLESPACE tablespace_name BEGIN|ENDBACKUP

          在数据库进行热备份（联机备份）时，需要分别对表空间进行备份。

          基本步骤为：

           1）使用ALTERTABLESPACE…BEGIN BACKUP语句将表空间设置为备份模式。

           2）在操作系统中备份表空间所对应的数据文件。

           3）使用ALTERTABLESPACE…END BACKUP语句结束表空间的备份模式。

          示例：备份ORCL数据库的ORCLTBS1表空间。

           SQL>ALTER TABLESPACE ORCLTBS1 BEGINBACKUP;

           复制ORCLTBS1表空间的数据文件ORCLTBS1_1.DBF和ORCLTBS1_2.DBF到目标位置。

           SQL>ALTER TABLESPACE ORCLTBS1 END BACKUP;

（4）删除表空间

          语法：

           DROP TABLESPACE tablespace_name

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

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

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

           示例：

           1）删除ORCL数据库的ORCLTBS1表空间及其所有内容。

                 SQL>DROP TABLESPACE ORCLTBS1 INCLUDINGCONTENTS;

           2）删除ORCL数据库的ORCLUNDO1表空间及其所有内容，同时删除其所对应的数据文件。

                 SQL>DROP TABLESPACE ORCLUNDO1 INCLUDINGCONTENTS AND DATAFILES;

           3）删除ORCL数据库的ORCLUNDO1表空间及其所有内容，同时删除其所对应的数据文件，以及其他表空间中与ORCLUNDO1表空间相关的参照完整性约束。

                 SQL>DROP TABLESPACE ORCLUNDO1 INCLUDINGCONTENTS AND DATAFILES CASCADE CONSTRAINTS;

（5）大文件表空间的管理

          大文件表空间只包含一个数据文件，减少数据库中数据文件的数量，减少SGA中用于存放数据文件信息的内存需求，同时减小控制文件。

          通过对大文件表空间的操作可以实现对数据文件的透明操作，简化了对数据文件的管理。

          大文件表空间只能采用本地管理方式，其段采用自动管理方式。

          如果在数据库创建时设置系统默认的表空间类型为BIGFILE，则使用CREATETABLESPACE语句默认创建的就是大文件表空间。如果要创建传统的小文件表空间，则需要使用CREATE SMALLFILE TABLESPACE语句。

          1）  创建大文件表空间

                  语句：CREATE BIGFILE TABLESPACE

                  示例：创建一个大文件表空间ORCLTBS5。

                   SQL>CREATE BIGFILE TABLESPACEORCLTBS5 DATAFILE

                             'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ORCLTBS5_1.DBF'SIZE 20M;

         2）大文件表空间的操作

               将大文件表空间ORCLTBS5的数据文件D:\ORACLE\PRODUCT\10.2.0\ORADATA\ ORCL\ORCLTBS5_1.DBF 大小修改为30 MB。

                  SQL>ALTER TABLESPACE ORCLTBS5RESIZE 30M;

              将大文件表空间ORCLTBS5的数据文件D:\ORACLE\PRODUCT\10.2.0\ORADATA\ ORCL\ORCLTBS5_1.DBF修改为可以自动扩展。

                 SQL>ALTER TABLESPACE ORCLTBS5AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

（6）表空间信息查询

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

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

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

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

           DBA_EXTENTS：所有表空间中区的信息。

           DBA_FREE_SPACE：所有表空间中空闲区的信息。

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

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

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

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

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

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

           V$SORT_SEGMENT：数据库实例的每个排序段信息。

           V$SORT_USER：用户使用临时排序段信息。

          示例：

           查询表空间基本信息

           SQL>SELECT TABLESPACE_NAME,EXTENT_MANAGEMENT,ALLOCATION_TYPE,

                     SEGMENT_SPACE_MANAGEMENT, CONTENTS,NEXT_EXTENT FROM DBA_TABLESPACES;

          查询表空间数据文件信息

           SQL>SELECTFILE_NAME,BLOCKS,TABLESPACE_NAME FROM DBA_DATA_FILES;

          查询表空间空闲空间大小

          SQL>SELECT TABLESPACE_NAME,SUM(BYTES),FREE_SPACESFROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

         统计表空间空闲空间信息

          SQL>SELECT TABLESPACE_NAME "TABLESPACE",FILE_ID,COUNT(*) "PIECES", MAX(blocks) "MAXIMUM",MIN(blocks) "MINIMUM",

                     AVG(blocks) "AVERAGE",SUM(blocks) "TOTAL"

                     FROM DBA_FREE_SPACE

                     GROUP BY TABLESPACE_NAME, FILE_ID;