Oracle数据库表空间段区块详解

数据库逻辑结构

1. 表空间tablespace

1. 理论

表空间分类	名称	描述
EXAMPLE	示例表空间	示例表空间，存放示例数据库的方案对象信息及其培训资料
SYSAUX	辅助系统表空间	辅助系统表空间，用于减少system表空间的负荷，提高系统的工作效率。是oracle 10g新增加的表空间
SYSTEM	系统表空间	系统表空间，存放关于表空间名称、控制文件、数据文件等管理信息，存放着方案对象（如表，索引，同义词，序列）的定义信息，存放着所有pl/sql 程序（如过程，函数，包，触发器）的源代码，是oracle数据库中最重要的表空间。它属于SYS和SYSTEM方案，仅被SYS和SYSTEM或其他具有足够权限的用户使用。即使是SYS 和SYSTEM用户也不能删除或重命名该空间。他是用户的默认表空间，即当用户在创建一个对象时，如果没有指定特定的表空间，该对象的数据也会被保存在SYSTEM表空间中。
TEMP	临时表空间	存储数据库的中间执行过程，如：保存order by数据库排序，分组时产生的临时数据。操作完成后存储的内容会被自动释放。临时表空间是通用的，所的用户都使用TEMP作为临时表空间。一般只有tmp一个临时表空间，如果还需要别的临时表空间时，可以自己创建。
UNDO	UNDO表空间	保存数据修改前的副本。存储事务所修改的旧址，即被修改之前的数据。当我们对一张表中的数据进行修改的同时会对修改之前的信息进行保存，为了对数据执行回滚、恢复、撤销的操作。

# 查看system用户的默认表空间和临时表空间：
SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='SYSTEM';


# 默认表空间
用户在登陆后创建数据库对象时，如果没有指定表空间，那么这些数据就会存储到默认表空间。

# 查看默认的永久表空间
注意：如果创建用户时，不指定其永久表空间，则会使用默认的表空间。
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES;

# 设置默认表空间
针对某个用户的:
SQL> alter user user_name default tablespace tbs_name;

设置数据库的默认临时表空间:
SQL> alter database default tablespace temp_tbs_name;

# 查看默认的TEMP表空间
系统管理员：
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

# 普通用户
SQL> SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS

# 设置默认临时表空间
针对系统
SQL> alter database default temporary tablespace temp_grp;
针对用户
SQL> Alter user test temporary tablespace temp1;

# 查看UNDO表空间
SQL> show parameter undo;

2. 表空间/临时表空间操作

# 预先设置文件存储位置
SQL> alter system set DB_CREATE_FILE_DEST='/oracle/oradata' scope=both;

# 完整的创建表空间语句
create tablespace mytablespace datafile '/home/oracle/oradata/mytablespace.dbf' size 200m autoextend on next 512k maxsize unlimited

logging

online

extent management local autoallocate

blocksize 8k

segment space management manual

flashback on;

//创建一个小表空间，表空间名称为mytablespace，表空间的数据文件名为/home/oracle/oradata/mytablespace.dbf，表空间文件的初始大小是200m，文件自动扩展，文件块的大小是8k，表空间文件最大是32g。

// logging会创建重做日志，方便在数据丢失后找回数据

//online 表空间的状态设置成online，允许外部对表空间的对象进行访问；如果设置 成offline，则不允许外部对表空间的对象进行访问。

// extent是“区间”的意思,在oracle数据库中：extent management 有两种方式 extent management local（本地管理）; extent management dictionary（数据字典管理），默认的是local，本地管理表空间与字典管理表空间相比大大提高了管理效率和数据库性能。
// segment space management manual  手动管理，oracle使用自由列表（free list）管理段中的块，自由列表列出允许进行插入操作的数据块;自动管理，段的手工管理使用自由列表管理段中数据块的使用，自动管理使用位图来管理数据快，当块发生变化时，oracle会更新位图，以反映这个块是否允许insert操作，位图使oracle自动管理自由空间。 

// flashback on将以前被修改,删除了的数据,通过开启flashback 模式,回到我们需要回到的时间来查看数据,



# 创建表空间(多个数据文件)
CREATE TABLESPACE test_db  DATAFILE '/oracle/oradata/orcl/test_db1.dbf' SIZE 10m  AUTOEXTEND ON NEXT 10M   MAXSIZE UNLIMITED, '/oracle/oradata/orcl/test_db2.dbf' size 10M autoextend on next 100M maxsize 100m;


# 创建表空间
SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test.dbf' size 100m autoextend ON NEXT 10M  maxsize unlimited  extent management local uniform size 2m segment space management manual;
段空间的手工管理（Manual Segment Space Management）

# 创建表空间（指定数据块大小），DB_nK_CACHE_SIZE (其中n = 2,4，8,16，32) 指定nK缓冲区的缓存大小。仅当DB_BLOCK_SIZE具有nK以外的值时，才可以设置此参数。例如，如果DB_BLOCK_SIZE = 4096，则指定参数DB_4K_CACHE_SIZE是非法的 (因为DB_CACHE_SIZE已经指定了4 KB块缓存的大小)。

SQL> show parameter  block  # 首先查看默认数据块大小

SQL> show parameter db_16k_cache_size # 查看16k块大小

SQL> alter system set db_16k_cache_size=100m; # 设置16k块

SQL> create tablespace ts16k datafile '/oracle/data/ts16k.dbf' size 5m blocksize 16k;

# 


# 创建表空间超大文件
CREATE bigfile TABLESPACE test_db  DATAFILE '/oracle/oradata/orcl/test_db1.dbf' SIZE 10m  AUTOEXTEND ON NEXT 10M   MAXSIZE 4T;

# 修改数据文件属性
SQL> alter database datafile '/oracle/oradata/orcl/test_db1.dbf' resize 20M;

SQL> alter database datafile '/oracle/oradata/orcl/tbs1.dbf' autoextend on;

SQL> alter database datafile '/oracle/oradata/orcl/tbs1.dbf' autoextend on next 20M;

SQL> alter database datafile '/oracle/oradata/orcl/tbs1.dbf' autoextend on next 20M 
maxsize 20G;

# 查看数据文件及所属表空间
SQL> select file_name,tablespace_name from dba_data_files;

# 给表空间添加数据文件
SQL> alter tablespace test_db add datafile '/oracle/oradata/orcl/test_db-2.dbf' size 10M autoextend on next 20M maxsize 20G;

# 删除表空间数据文件
alter tablespace test_db drop datafile '/oracle/oradata/orcl/test_db2.dbf';

# 创建临时表空间
CREATE TEMPORARY TABLESPACE test_temp TEMPFILE '/oracle/oradata/orcl/test_temp.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

# 创建临时表空间，临时空间属于某个组
create temporary tablespace temp1 tempfile '$ORACLE_BASE/oradata/PROD1/temp02.dbf' size 50m tablespace group temp_grp;

SQL>alter database default temporary tablespace tempgrp;

# 临时表空间添加数据文件
ALTER TABLESPACE TEMP ADD TEMPFILE '/u04/gsp/oradata/temp02.dbf' SIZE 4G AUTOEXTEND ON NEXT 128M MAXSIZE 6G;

# 查看表空间信息
SELECT * FROM v$tablespace;

# 删除表空间/临时表空间
drop tablespace test_db including contents and datafiles;

# 查看表空间及数据文件：
SQL> SELECT FILE_NAME,TABLESPACE_NAME from DBA_DATA_FILES;

3. UNDO表空间

命令

（1）创建undo表空间
使用create undo tablespace来创建undo tablespace。undo tablespace用来保存事务的回退信息，用户不能在其中创建数据库对象。

# 查看UNDO表空间
show parameter undo;

# 创建UNDO表空间
create undo tablespace test_undo datafile '/oracle/oradata/orcl/test_undo.dbf' size 2m;
 

(2)修改undo表空间

可以使用alter tablespace修改undo表空间，允许对undo表空间进行如下操作：

  --添加undo表空间的数据文件；

  --重命名undo表空间的数据文件；

  --将undo表空间的数据文件联机或脱机；

  --启用或禁用保护回退信息在回退段中的保留时间；

# 为undo tablespace添加新的数据文件
alter tablespace undotbs1 add datafile '/home/app/oracle/oradata/orcl/untbs02.dbf' size 50M;

# 将undo tablespace里面的untbs02.dbf文件扩充为100M
alter database datafile '/home/app/oracle/oradata/orcl/untbs02.dbf' resize 100M;
 

（3）删除undo表空间

drop tablespace test_db including contents and datafiles;


（4）切换undo表空间，直接修改即可，无需重启实例
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_name

（5）参数 UNDO_RETENTION 设置撤销保留时间的大小
前提：
# 使用ALTER DATABASE命令保证数据库中的撤销保留。

SQL> alter tablespace UNDOTBS1 retention guarantee;

关闭撤销信息的保证保留

SQL> alter tablespace UNDOTBS1 retention noguarantee;

SQL> alter system set UNDO_RETENTION=1800       设置保留时间为30分钟（1800秒）。

UNDO_RETENTION参数默认设置为900秒。

2. 段segment

1. 理论

段类型	名称	描述
表	table	段名 = 表名
表分区	table partition	段名 = 表名 and 分区名不为空
表子分区	table subpartition	段名 = 表名 and 分区名不为空且不同
索引	index	段名 = 索引名
索引分区	index partition	段名 = 索引名 and 分区名不为空
索引子分区	index subpartition	段名 = 索引名 and 分区名不为空且不同
聚簇	cluster
lob 段	lobsegment	每个 lob 字段都会对应一个 lob 段，可分区
lob 分区	lob partition
lob 子分区	lob subpartition
lob 索引	lobindex
回滚段	rollback	8i及以前，名称 system
撤销段	type2 undo	9i及以后
嵌套表	nested table

2. 实操

# 查看表空间、段、区信息
SQL> select * from dba_tablespaces;    
SQL> select segment_name,segment_type,tablespace_name,bytes,blocks,extents from user_segments;

# 查询段，权限由大至小
select t.segment_name, t.* from dba_segments t;
select t.segment_name, t.* from user_segments t;

# 查找表
select t.table_name, t.* from dba_tab_comments t;
select t.table_name, t.* from all_tab_comments t;
select t.table_name, t.* from user_tab_comments t;

# 查找 lob 字段
select * from dba_lobs;
select * from dba_lob_partitions;
select * from dba_lob_subpartitions;

# 创建表空间（所有参数，包括段、区参数）
SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test.dbf' size 100m autoextend ON NEXT 10M  MAXSIZE UNLIMITED  extent management local uniform size 2m segment space management manual;

3. 区extend

1. 理论

#  查询 区 extend
-- 段空间使用完后，自动为该段分配一个新的区（扩展）
select * from dba_extents;
select * from user_extents;

# 查看表空间、段、区信息
SQL> select * from dba_tablespaces;    
SQL> select segment_name,segment_type,tablespace_name,bytes,blocks,extents from user_segments;

# 创建表空间（所有参数，包括段、区参数）
SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test.dbf' size 100m autoextend ON NEXT 10M  MAXSIZE UNLIMITED  extent management local uniform size 2m segment space management manual;

4. 数据块block

1. 理论

1. 数据块 和 操作系统块
   (1) 数据块   : '最小的逻辑单元'
   (2) 操作系统块: 读取或写入磁盘的最小单元（'I/O'）
   (3) 数据快 和 操作系统快分离的意义：'屏蔽了不同操作系统的差异'
       -- 也就是说，数据库在 Windows 或 Linux 等一样使用
   
2. 数据块的大小
   (1) 数据库创建时设置，之后无法更改，除非重新创建数据库。
   (2) 一般默认大小 '8kb'
       show parameters db_block_size -- (8192/1024 = 8 kb)

3. 行数据格式（Row Data）
   (1) 可通过伪列 rowid 查看，共计 18 位
	   select rowid, -- 18 位
		      -- 数据对象id（前 6 位）
			  dbms_rowid.rowid_object(rowid) object_id, 
		      -- 相对文件id（3 位）
		      dbms_rowid.rowid_relative_fno(rowid) relative_fno, 
		      -- 数据块id（6 位）
		      dbms_rowid.rowid_block_number(rowid) block_number, 
		      -- 行id（后 3 位）
		      dbms_rowid.rowid_row_number(rowid) row_number
	     from scott.emp;