Oracle 表空间与数据文件

一、概念

表空间：是一个或多个数据文件的逻辑集合

表空间逻辑存储对象：永久段–>如表与索引

                                    临时段–>如临时表数据与排序段

   　　                         回滚段–>用于事物回滚或闪回内存的撤销数据

表空间分类：系统表空间(system、sysaux)，非系统表空间

一个表空间至少包含一个数据文件，一个数据文件只能属于一个表空间。

不可或缺的几个表空间：

    SYSTEM —>字典表空间，不能被损坏

    UNDO —>dml,dql把数据快照到此，数据提交即消失（用于恢复）

    SYSAUX —>10g 高并发系统繁忙时，会造成system争用，将工具放到SYSAUX,减轻system的压力，SYSAUX不影响系统（影响性能）

    TEMP —>临时数据相关的内容

    USERS —>10g  用户数据从system拨离出来

二、Oracle的存储结构

    1.Schema: 用户—>创建相关对象、表、视图、序列、函数、存储过程、包等

    2.逻辑结构：database数据库—>tablespace表空间—> segment段—>extent区间—-> block块

            逻辑结构是Oracle内部管理数据库中对象的方式

    3.物理结构：OS  block —>datafile 物理结构通常是一系列数据文件

    4.举例描述scott用户创建对象的组织方式 

–查看scott用户的默认表空间及数据文件

SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE    from dba_users  where username = ‘SCOTT‘;

–查看表空间及数据文件

SQL> select t1.name,t2.name  from v$tablespace t1,v$datafile t2  where t1.ts# = t2.ts#;

三、表空间

  —简要语法：

CREATE [BIGFILE | SMALLFILE] [TEMPORARY] TABLESPACE tablespace name

    DATAFILE datafile spec | TEMPFILE tempfile spec

    [MINIMUM EXTENT minimum extent size]

    [BLOCKSIZE blocksize]

    [[COMPRESS|NOCOMPRESS] DEFAULT STORAGE (default storage clause)]

    [LOGGING|NOLOGGING]

    [FORCE LOGGING]

    [ONLINE|OFFLINE]

    [EXTENT MANAGEMENT DICTIONARY |

    LOCAL [AUTOALLOCATE|UNIFORM SIZE size]]

    [SEGMENT SPACE MANAGEMENT MANUAL|AUTO]

    [FLASHBACK ON|OFF]

—创建表空间的条件

1.具有create tablespace的权限，DBA角色具有该权限，sysdba,sysoper

2.创建的是bigfiel ，还是smallifle  超过T 级别应考虑bigfile

3.新建的表空间的I/O，是否会导致磁盘I/O不够用

4.datafile后跟的路径应该具备写的权限  */

–查看缺省是BIGFILE 还是SMALLFILE,当缺省为SMALLFILE,创建表空间不指定表空间类型则为SMALLFILE

SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME like ‘%TBS%‘;

DEFAULT_TBS_TYPE SMALLFILE

–可修改默认值

SQL> alter database set default bigfile tablespace;

–改回默认值

SQL> alter database set default smallfile tablespace;

–创建表空间（单实例）

CREATE TABLESPACE YYHHQQ DATAFILE 

  ‘F:\MYDB\YYHHQQ.DBF‘ SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

–rac

CREATE TABLESPACE EXAMPLE DATAFILE
‘+DATA/bol/datafile/example01.dbf‘ SIZE 100M AUTOEXTEND ON NEXT 640K MAXSIZE 32767M
NOLOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

–修改db默认的表空间

SQL> ALTER DATABASE DEFAULT   TABLESPACE YYHHQQ

 —修改用户的默认表空间

SQL> alter user hr default tablespace IN_THE_CLEAR;

–移动表到另外的表空间

SQL> alter table tablename move tablespace tablespacename;

–创建临时表空间：

创建临时表空间，不能使用非标准数据块，另临时表空间不能存放永久对象。

SQL> DROP TABLESPACE YHQ_TEMP INCLUDING CONTENTS AND DATAFILES;

CREATE TEMPORARY TABLESPACE YHQ_TEMP TEMPFILE 

  ‘F:\MYDB\YHQ_TEMP.DBF‘ SIZE 20M AUTOEXTEND ON NEXT 10M MAXSIZE 100M

TABLESPACE GROUP ‘‘

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

临时表空间具有以下特征：

临时数据文件不能置为只读

临时数据文件不能重命名

监时数据文件的日志方式总是NOLOGGING

–重置临时文件大小

SQL> alter database tempfile   ‘F:\MYDB\YHQ_TEMP.DBF‘ resize 50m

–重置临时文件能自动扩展

SQL> alter database tempfile  ‘F:\MYDB\YHQ_TEMP.DBF‘ autoextend on next 10m maxsize 50m;

–增加临时文件

SQL> alter tablespace YHQ_TEMP add TEMPFILE ‘F:\MYDB\YHQ_TEMP2.DBF‘ size 10m;

–查看临时文件

SQL> select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 “SIZE(MB)” from dba_temp_files;

TEMP F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF 20

YHQ_TEMP F:\MYDB\YHQ_TEMP.DBF 50

YHQ_TEMP F:\MYDB\YHQ_TEMP2.DBF 10

–默认临时表空间

SQL> select * from database_properties;

–修改默认临时表空间：

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE YHQ_TEMP;

–修改用户的默认临时表空间

 SQL> alter user hr  temporary tablespace TEMP;

默认临时表空间最好要指定一下，如果没有指定默认的临时表空间，那么将使用system表空间作为排序区

默认临时表空间有一定的限制：

默认临时表空间不能删除

默认临时表空间不能脱机

获取创建表空间的语句

SQL> SELECT dbms_metadata.get_ddl(‘TABLESPACE‘,‘SYSTEM‘) FROM dual; –返回一个clob

–创建UNDO表空间（还原表空间）

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE 

  ‘F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF‘ SIZE 35M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

ONLINE

RETENTION NOGUARANTEE

BLOCKSIZE 8K

FLASHBACK ON;

–修改当前系统的UNDO表空间:

SQL>  ALTER SYSTEM SET UNDO_TABLESPACE=yhq_undo; —-scope=memory|spfile|both

—default 的是memory，实例重启后失效

—spfile保存在spfile中，重启后才能有效

—both保存在实例和spfile 中

—system只能指定一个空间

 

UNDO表空间扩容：

–重置DATAFILE大小

–让DATAFILE能AUTOEXTEND

–添加数据文件

注：当前的UNDO表空间不能删除

系统只能使用一个UNDO表空间 

 

CREATE UNDO TABLESPACE yhq_undo DATAFILE 

 ‘F:\MYDB\YHQ_undo.DBF‘  SIZE 35M AUTOEXTEND ON NEXT 5M MAXSIZE 100m

ONLINE

RETENTION NOGUARANTEE

BLOCKSIZE 8K

FLASHBACK ON;

 

–查看表空间使用的管理方式

SQL> select TABLESPACE_NAME,EXTENT_MANAGEMENT,BLOCK_SIZE,STATUS,CONTENTS,FORCE_LOGGING,BIGFILE from dba_tablespaces;

–表空间改名，必须在open状态下，system，sysaux不能改名

SQL> alter tablespace xxx renmae to yyy;

–重置数据文件大小

SQL> alter database datafile  ‘F:\MYDB\YYHHQQ.DBF‘  resize 100m;

–设置数据文件能自动增长

SQL> alter database  DATAFILE ‘F:\MYDB\YYHHQQ.DBF‘ AUTOEXTEND OFF;

SQL> alter database  DATAFILE ‘F:\MYDB\YYHHQQ.DBF‘ AUTOEXTEND ON NEXT 4M  MAXSIZE 50M;

–查看

SQL> select name,file#,bytes/1024/1024 MB,status from v$datafile;

–为表空间添加数据文件

SQL> alter tablespace YYHHQQ add datafile ‘F:\MYDB\YYHHQQ2.DBF‘ size 100m;

—表空间的四种状态：

online

offline

read only

read write

–alter tablespace test1 offline;

–alter tablespace test1 online;

–alter tablespace test1 read only;

–alter tablespace test1 read write;

tablespace_name	online	read only
system	必须online	ready write
sysaux	可以offline	不能read only
undo	不能offline	不能read only

–查看表空间的状态

SQL> select tablespace_name,file#,v.status,v.enabled from dba_data_files d,v$datafile v where d.file_id = v.file#;

删除表空间：

 

意味着表空间和数据文件的信息从数据库中删除，同时也应该将相应的操作系统文件删除

不能删除的表空间

系统表空间

有活动回滚段的表空间

默认临时/永久表空间

DROP TABLESPACE tablespace_name [INCLUDING CONTENTS [AND DATAFILES]]

    INCLUDING CONTENTS                — 删除表空间及所有段

INCLUDING CONTENTS [AND DATAFILES] –删除表空间、所有段、数据文件、同时删除系统中的物理文件

SQL>  alter tablespace starhub including contents and datafiles cascade constraints;

–限制某个用户对表空间的使用

–alter user username quota n on tablespacename;

SQL> alter user scott QUOTA 10m on UU;

–取消限制

–alter user username QUOTA UNLIMITED on tablespacename;

–grant unlimited tablespace to username;

 —获取表空间和数据文件信息

–表空间信息,系统视图

dba_tablespaces,v$tablespace

–数据文件信息

dba_data_files,v$datafile

–临时数据文件信息

dba_temp_files,v$tempfile

四、数据文件

数据文件是数据库中最重要的一个要求，是所有的数据库内容存放的地方

datafile是按照表空间为组织单位，表空间的构成是按照段区块为层次－－－数据文件的逻辑结构

datafile就是表空间的物理文件。

datafile中的内容是通过dbwr把data buffer cache中的dirty buffer data 写入的

—数据文件的增加与删除

SQL> alter tablespace tb1 add datafile ‘/u01/app/oradata/orcl/tbs02.dbf‘ size 10m autoextend on;

SQL> alter tablespace tb1 drop datafile 9;–v$datafile中的file#，有数据的数据文件不能删，

数据文件的日志记录

只有temp表空间是nologing  别的都是logging的

日志记录的几种模式

nologing          并不是不记录日志而是记录的很少

logging          正常记录日志信息

force logging    记录的日志比logging还详细 记录信息非常多  用于DG data guard 容灾的情况

SQL> alter tablespace tb1 nologging  | logging | force logging;

SQL> select tablespace_name ,logging,force_logging from dba_tablespaces;

SQL> alter tablespace EXAMPLE logging;