Oracle表空间管理

Oracle表空间管理

1. 查看表空间信息

SQL> SELECT tablespace_name, status, contents, logging, extent_management, allocation_type,

 segment_space_management from dba_tablespaces;

TABLESPACE_NAME STATUS     CONTENTS   LOGGING            EXTENT_MANAGEMENT    ALLOCATION_TYPE    SEGMENT_SPAC
--------------- ---------- ---------- ------------------ -------------------- ------------------ ------------
SYSTEM          ONLINE     PERMANENT  LOGGING            LOCAL                SYSTEM             MANUAL
SYSAUX          ONLINE     PERMANENT  LOGGING            LOCAL                SYSTEM             AUTO
UNDOTBS1        ONLINE     UNDO       LOGGING            LOCAL                SYSTEM             MANUAL
TEMP            ONLINE     TEMPORARY  NOLOGGING          LOCAL                UNIFORM            MANUAL
USERS           ONLINE     PERMANENT  LOGGING            LOCAL                SYSTEM             AUTO

SQL> SELECT file_name, file_id, tablespace_name from dba_data_files;

FILE_NAME                                             FILE_ID TABLESPACE_NAME
-------------------------------------------------- ---------- ---------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system          1 SYSTEM
_kq1w7f1j_.dbf

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux          3 SYSAUX
_kq1w8j7q_.dbf

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_          7 USERS
kq1w90kc_.dbf

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotb          4 UNDOTBS1
s1_kq1w8zf8_.dbf

FILE_NAME                                             FILE_ID TABLESPACE_NAME
-------------------------------------------------- ---------- ---------------

2.使用OMF关闭表空间

OMF(Oracle Managed Files): ORACLE数据文件托管服务

2.1 关闭OMF

因为已经打开了OMF，所以先关闭OMF, 要关闭只需将DB_CREATE_FILE_DEST参数置空即可

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='';

System altered.

SQL> CREATE TABLESPACE tbs_1;
CREATE TABLESPACE tbs_1
                      *
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause

可以看到创建表空间失败了（注：开启OMF创建表空间只需指定名称）

2.2 打开OMF

只需将DB_CREATE_FILE_DEST参数置空即可

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/app/oracle/oradata/';

System altered.

SQL>

2.2.1使用OMF创建表空间

创建表空间

SQL> CREATE TABLESPACE "USER1";

Tablespace created.

SQL>

查看表空间

SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES;

FILE_NAME                                                         TABLESPACE_NAME
----------------------------------------------------------------- ---------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_kq1w7f1j_.dbf  SYSTEM
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_kq1w8j7q_.dbf  SYSAUX
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_kq1w8zf8_.db UNDOTBS1
f

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_kq1w90kc_.dbf   USERS
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_user1_kqn9dkxd_.dbf   USER1

SQL>

3. 管理表空间

3.1 创建表空间

SQL> CREATE SMALLFILE  # 指定是小文件还是大文件
  2  TABLESPACE "USER1"   #表空间的名字
  3  DATAFILE '/u01/app/oracle/oradata/orcl/user1.dbf' # 指明数据文件的存放位置
  4  SIZE 100M # 数据文件的大小
  5  AUTOEXTEND ON NEXT 100M # 每次自动增长多大的表空间
  6  LOGGING # 是否开启日志记录
  7  DEFAULT NOCOMPRESS # 默认没有压缩
  8  ONLINE # 是否联机
  9  EXTENT MANAGEMENT LOCAL AUTOALLOCATE #自动区分配
  10 SEGMENT SPACE MANAGEMENT AUTO; # 是否自动段管理

Tablespace created.

SQL>

• 数据文件： smallfile，小文件；bigfile 大文件

• 表空间类型：默认持久（permanent）,还有临时表空间（Temporary）， 撤销表空间 undo ；

• 是否联机：Online和Offlie

• 是否开启日志记录：LOGGING和NOLOGGING, 如果开启，对数据对象的更改会保存到redo日志，否则不保存；

  如果没有开启而使用了SQL * Loader进行数据的Insert插入，该操作不会记录到redo日志，所以一旦丢失数据，是不可恢复的。

  创建未启用日志对象时，希望它们是可恢复的，则必须备份这些对象。

• 是否自动区分配：分为自动和统一。

3.2 修改表空间

3.2.1 表空间脱机

脱机使用时，可以使用以下选项

正常：如果表空间的任何数据文件都没有错误条件，则该表空间可以正常脱机。

• Oracle数据库通过在表空间的所有数据文件脱机时为其设置检查点来确保将所有数据写入磁盘。

临时(temporary)：即使某个表空间的一个或多个文件存在错误，也可以使该表空间暂时脱机。

• Oracle Database使尚未脱机的数据文件脱机，从而对它们执行检查点。

• 如果您使用Temporary子句，但是没有文件变为脱机状态。则不需要介质恢复就可以使表空间恢复联机状态。

• 如果表空间的一个或多个文件由于写入错误而被您临时把表空间脱机，则该表空间需要恢复，然后才能使其重新联机。

立即：表空间可以立即脱机，而Oracle数据库无需在任何数据文件上设置检查点。

• 当您指定立即时，必须先恢复表空间的介质，然后才能使表空间联机。

• 如果数据库以NOARCHIVELOG模式运行，则不能使用立即脱机。

3.2.2 向表空间增加数据文件

SQL> ALTER TABLESPACE "USER1" 
ADD DATAFILE '/u01/app/oracle/oradata/orcl/user_2.dbf' size 100M;

Tablespace altered.

SQL>

查看数据文件

SQL> SELECT NAME FROM V$DATAFILE;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_kq1w7f1j_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_kq1w8j7q_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_kq1w8zf8_.dbf
/u01/app/oracle/oradata/orcl/user1.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_kq1w90kc_.dbf
/u01/app/oracle/oradata/orcl/user_2.dbf

6 rows selected.

SQL>

3.2.3 修改表空间数据文件

修改数据文件大小

SQL> ALTER DATABASE DATAFILE
  '/u01/app/oracle/oradata/orcl/user_2.dbf' RESIZE 120M;

Database altered.

SQL>

修改数据文件自动增长

SQL> ALTER DATABASE DATAFILE 
'/u01/app/oracle/oradata/orcl/user_2.dbf'
 AUTOEXTEND ON NEXT 50M MAXSIZE 200M;

Database altered.

SQL>

3.3 删除表空间

危险操作，可以先删除表空间，数据文件保存一段时间，确认无误后，再删除。

语法：SQL> DROP TABLESPACE dataspace_name [ INCLUDING CONTENTS [ AND DATAFILES ] [ CASCADE CONSTRAINTS ] ];

SQL> DROP TABLESPACE "USER1" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Tablespace dropped.

SQL>

SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;

TABLESPACE_NAME
---------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

SQL>