Oracle表空间简述和常用命令

Oracle表空间

Oracle表空间是一个或多个数据文件的逻辑集合，而数据文件实际上是对存储模式对象数据的一个容器/仓库。

Oracle引入逻辑结构的作用：
一、增加Oracle数据库的可移植性
二、便于管理，可以在Oracle数据库管理系统中完成从逻辑结构到物理结构操作的映射

Oracle逻辑结构如下图所示：（图片来自百度）

圆角型方框为实体，实现表示关系，单线表示一的关系，三条线（鹰抓状线）表示多的关系，于是可以获得以下信息：

• 每个数据库是由一个或多个表空间组成的（至少一个）
• 每个表空间基于一个或多个操作系统的数据文件（至少一个）
• 每个表空间中可以存放一个或多个段（segment）
• 每个段是由一个或多个区段（extent）组成
• 每个区段是由一个或多个连续的Oracle数据块所组成
• 每个Oracle数据块是由一个或多个连续的操作系统数据块（os块）所组成
• 每个操作系统数据文件是由一个或多个区段所组成
• 每个操作系统数据文件是由一个或多个操作系统数据块所组成

一、使用数据字典查询表空间

在数据字典中，一般可以使用v$tablespace动态性能视图，dba_tablespaces、user_tablespaces静态视图来查询表空间相关信息，

#v$tablespace记录信息，相对来说相对简略一些
SQL> select * from v$tablespace;
   TS# NAME 			  INC BIG FLA ENC
------ ------------------ --- --- --- ---
	 0 SYSTEM			  YES NO  YES
	 1 SYSAUX			  YES NO  YES
	 2 UNDOTBS1			  YES NO  YES
	 3 TEMP 			  NO  NO  YES
	 4 USERS			  YES NO  YES

SQL> 

#dba_tablespaces记录的信息就非常详细
SQL> desc dba_tablespaces;
 Name						   Null?    Type
 ----------------------------- -------- ----------------------------
 TABLESPACE_NAME			   NOT NULL VARCHAR2(30)
 BLOCK_SIZE				  	   NOT NULL NUMBER
 INITIAL_EXTENT 				    	NUMBER
 NEXT_EXTENT					   		NUMBER
 MIN_EXTENTS				   NOT NULL NUMBER
 MAX_EXTENTS					    	NUMBER
 MAX_SIZE					    		NUMBER
 PCT_INCREASE					    	NUMBER
 MIN_EXTLEN					    		NUMBER
 STATUS 					    		VARCHAR2(9)
 CONTENTS					    		VARCHAR2(9)
 LOGGING					   			VARCHAR2(9)
 FORCE_LOGGING					    	VARCHAR2(3)
 EXTENT_MANAGEMENT				    	VARCHAR2(10)
 ALLOCATION_TYPE				    	VARCHAR2(9)
 PLUGGED_IN					   			VARCHAR2(3)
 SEGMENT_SPACE_MANAGEMENT			    VARCHAR2(6)
 DEF_TAB_COMPRESSION				    VARCHAR2(8)
 RETENTION					    		VARCHAR2(11)
 BIGFILE					    		VARCHAR2(3)
 PREDICATE_EVALUATION				    VARCHAR2(7)
 ENCRYPTED					    		VARCHAR2(3)
 COMPRESS_FOR					    	VARCHAR2(12)

SQL> 

dba_tablespaces和user_tablespaces中有一些常用的列如：tablespace_name,block_size,status,contents,extent_management,segment_space_management等等。

SQL> select tablespace_name,block_size,status,contents ,extent_management,segment_space_management 
from dba_tablespaces;

TABLESPACE_NAME		   BLOCK_SIZE STATUS    CONTENTS  EXTENT_MAN SEGMEN
--------------------------------- --------- --------- --------- ---------- ------
SYSTEM				   		 8192 ONLINE    PERMANENT LOCAL	 MANUAL
SYSAUX				   	  	 8192 ONLINE    PERMANENT LOCAL	 AUTO
UNDOTBS1			      	 8192 ONLINE    UNDO      LOCAL	 MANUAL
TEMP				     	 8192 ONLINE    TEMPORARY LOCAL	 MANUAL
USERS				    	 8192 ONLINE    PERMANENT LOCAL	 AUTO

SQL> 

表空间的类型一共有三种：

• UNDO 撤销表空间
• PERMANENT 永久表空间
• TEMPORARY 临时表空间

段的管理方式和区的管理方式是在建立表空间时确定的，共有有AUTO和MANUAL两种，区管理方式有LOCAL和字典管理（已淘汰）两种。

二、创建表空间

语法：

CREATE TABLESPACE 表空间名
[DATAFILE字句]
[MINIMUM EXTENT 正整数[K|M]]
[BLOCKSIZE 正整数[K]]
[LOGGING|NOLOGGING]
[DEFAULT 存储字句]
[ONLINE|OFFLINE]
[PERMANENT|TEMPORARY]
[区段管理字句]
[段管理字句]

参数说明：
[DATAFILE字句]：表空间创建的数据文件存储路径。
[MINIMUM EXTENT 正整数[K|M]]：
表空间中所使用的每个EXTENT都必须是该参数所指定的整数倍。
[BLOCKSIZE 正整数[K]]：
为该表空间说明非标准块的大小。在使用该字句前，必须先设置DB_CACHE_SIZE和DB_nk_CACHE_SIZE参数，而且该字句中所说明的正整数一定与DB_nk_CACHE_SIZE参数的设置相对应。
[LOGGING|NOLOGGING]：
说明在该表空间中方所有的数据变化都将写入重做日志文件中，这也是默认方式，NOLOGGING与之相反，只影响一些DML和DDL命令。
[DEFAULT 存储字句]：
所有在该表空间中所创建的对象的默认存储参数
[ONLINE|OFFLINE]：
在创建表空间之后立即被设置问联机或脱机状态（即不能使用）。
[PERMANENT|TEMPORARY]
[区段管理字句]
[段管理字句]

实例：

#创建本地管理表空间
CREATE TABLESPACE tbs 
DATAFILE '/u01/app/oracle/oradata/orcl/DISK1/tbs1.DBF' SIZE 50 M,
         '/u01/app/oracle/oradata/orcl/DISK2/tbs2.DBF' SIZE 50 M 
EXTENT MANAGEMENT LOCAL 
UNIFORM SIZE 1 M;
Tablespace created.

#查询验证：
SQL> select tablespace_name , block_size , extent_management , segment_space_management 
from dba_tablespaces 
where tablespace_name like 'tbs%';
TABLESPACE_NAME 	       BLOCK_SIZE EXTENT_MAN SEGMEN
------------------- ----------------- ---------- ----------
	tbs 			      		 8192  LOCAL      AUTO

SQL> 

#查询验证其他的磁盘存储参数：
SQL>SELECT TABLESPACE_NAME , INITIAL_EXTENT , NEXT_EXTENT , MAX_EXTENTS , PCT_INCREASE , MIN_EXTLEN TLEN 
FROM DBA_TABLESPACES 
WHERE TABLESPACE_NAME LIKE 'tbs';

TABLESPACE_NAME   INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS PCT_INCREASE MIN_EXTLEN
---------------- --------------- ----------- ----------- ------------ ----------- 
	tbs 			     1048576   1048576   2147483645	     0        1048576
SQL> 

#查询表空间名称及所对应数据文件路径：
SQL> SELECT FILE_ID , FILE_NAME , TABLESPACE_NAME , AUTOEXTENSIBLE 
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME LIKE 'tbs';
FILE_ID FILE_NAME	          	  	     TABLESPACE_NAME  AUTOEXTENSIBLE
---------- --------------------------------- ---------------- ----------
7 /u01/app/oracle/oradata/orcl/DISK1/tbs1.DBF   tbs			 	NO	 
8 /u01/app/oracle/oradata/orcl/DISK2/tbs2.DBF   tbs		 		NO
SQL>

在操作系统上查询物理文件/u01/app/oracle/oradata/orcl/DISK1/tbs1.DBF等已经生成。

BIGFILE（大文件）表空间：
大文件表空间可以增加数据库的存储容量，并减轻管理许多数据文件和临时文件的负担，一个大文件表空间是一个单一的表空间，但可能非常大（可达4G块）的数据文件。相比之下，传统的smallfile表空间可以包含多个数据文件，但是文件不能那么大。

优点：

• 具有8K块的bigfile表空间可以包含32 TB的数据文件，具有32K块的bigfile表空间可以包含128 TB的数据文件，Oracle数据库中数据文件的最大数量受到限制（通常为64K文件），因此，大文件表空间可以显着增强Oracle数据库的存储容量。

• 大文件表空间可以减少数据库所需的数据文件数量。另一个好处是，可以调整and语句的DB_FILES初始化参数和MAXDATAFILES参数，以减少数据文件信息所需的SGA空间量和控制文件的大小。 CREATE DATABASECREATE CONTROLFILE

• Bigfile表空间通过提供数据文件透明性来简化数据库管理，该ALTER TABLESPACE语句的SQL语法使您可以对表空间执行操作，而不是对基础的单个数据文件执行操作。

Bigfile表空间仅支持具有自动段空间管理的本地管理的表空间，但以下三个例外：本地管理的撤消表空间，临时表空间和SYSTEM表空间。

创建一个大文件表空间
要创建一个大文件表空间，请指定语句的BIGFILE关键字CREATE TABLESPACE（CREATE BIGFILE TABLESPACE…）。

CREATE BIGDILE TABLESPACE BIG_TABLESPACE_NAME
DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

---

---

---

在生产环境中，如果是RAC，在共享存储中使用使用自动管理方式创建表空间

#创建永久表空间：
CREATE TABLESPACE TABLESPACE_NAME
DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
#添加永久表空间数据文件：
ALTER TABLESPACE TABLESPACE_NAME ADD 
DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

#创建索引表空间：
CREATE TABLESPACE IDX_TABLESPACE_NAME 
DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
#添加索引表空间数据文件：
ALTER TABLESPACE IDX_TABLESPACE_NAME ADD 
DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

#创建临时表空间：
CREATE TEMPORARY TABLESPACE TEMP_TABLESPACE_NAME 
TEMPFILE '+DATA' SIZE 10G;
#添加临时表空间数据文件：
ALTER TABLESPACE TEMP_TABLESPACE_NAME ADD 
TEMPFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

#创建undo表空间：
CREATE UNDO TABLESPACE UNDO_TABLESPACE_NAME
DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
#添加还原表空间数据文件：
ALTER TABLESPACE UNDO_TABLESPACE_NAME ADD 
DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

表空间一些常用的sql查询语句：

1、查看数据库表空间名字
#数据表空间：
SELECT DISTINCT(TABLESPACE_NAME) FROM DBA_DATA_FILES;

#临时表空间：
SELECT DISTINCT(TABLESPACE_NAME) FROM DBA_TEMP_FILES;

2、查看表空间及其对应的数据文件名
(1)数据表空间：
COL FILE_ID FOR 999;
COL FILE_NAME FOR A60;
COL TABLESPACE_NAME FOR A15;
SET LINES 200;
SELECT FILE_ID, FILE_NAME, TABLESPACE_NAME, STATUS FROM DBA_DATA_FILES;

(2)临时表空间：
COL FILE_ID FOR 999;
COL FILE_NAME FOR A60;
COL TABLESPACE_NAME FOR A15;
SET LINES 200;
SELECT FILE_ID, FILE_NAME, TABLESPACE_NAME, STATUS FROM DBA_TEMP_FILES;

3、查看数据库表空间和数据文件数量：
#查询某个表空间就加上条件：where tablespace_name='tablespace_name'
SELECT TABLESPACE_NAME,COUNT(1) FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME ORDER BY 1;

SELECT TABLESPACE_NAME,COUNT(1) FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME ORDER BY 1;

4、查看数据库语言字符集：
SELECT * FROM NLS_DATABASE_PARAMETERS;

5、查看数据库块大小：
SHOW PARAMETER BLOCK

6、查看数据库总数据文件数：
SELECT COUNT(*) FROM DBA_DATA_FILES;

7、查看实例中设置的最大数据文件数：
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE 'DB_FILES';

8、修改最大数据文件数限制(重启实例生效)：
ALTER SYSTEM SET DB_FILES = 10240 SCOPE = SPFILE;

9、查看表空间大小（MB）：
select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

10、查看表空间空闲大小（MB）：
select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

11、根据用户查找其默认表空间：
select username, default_tablespace from dba_users where username='USERNAME';

12、表空间重命名
alter tablespace old_tablespace_name rename to new_tablespace_name;

三、删除表空间：

删除表空间及其数据文件

#删除空的表空间，但是不包含物理文件：
drop tablespace tablespace_name;

#删除非空表空间，但是不包含物理文件：
drop tablespace tablespace_name including contents;

#删除空表空间，包含物理文件：
drop tablespace tablespace_name including datafiles;

#删除非空表空间，包含物理文件：
drop tablespace tablespace_name including contents and datafiles;

#如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

#删除临时表空间数据文件：
ALTER DATABASE TEMPFILE '临时文件路径' DROP INCLUDING DATAFILES;

#数据库OPEN下不能删除的表空间是:
(1)system
(2)active undo tablespace
(3)default temporary tablespace
(4)default tablespace

#数据库OPEN下不能offine的表空间是:
(1)system
(2)active undo tablespace
(3)default temporary tablespace

四、表空间状态

1、将表空间设置为脱节状态：

一个表空间的正常状态是（online）状态，此时数据库用户可以访问该表空间中的数据，当表空间处于脱机模式（offline）状态，可以进行以下维护操作：

• 在数据库打开状态下移动数据文件
• 在数据库处于打开状态下恢复一个表空间或一个数据文件
• 执行对表空间的脱机备份（表空间也可以进行联机备份）
• 使数据库的一部分不能被访问，其他部分可以正常访问
• 表空间设置为脱机状态会被记录到数据字典和控制文件中

并不是所有的表空间都可以被设置为脱机状态，以下表空间不能设置为脱机状态:
1、SYSTEM表空间
2、默认临时表空间
3、上面有活动的还原/回滚段的表空间

1、将表空间设置为脱机状态：
ALTER TABLESPACE tablespace_name OFFLINE;

2、将表空间设置为联机状态：
ALTER TABLESPACE tablespace_name ONLINE;

2、只读表空间

当一个表空间处于只读（read-only）状态时，在该表空间中的数据只能进行只读操作，也就是说在上面的数据是不会变化的，因此不需要做重做日志来进行维护

当使用命令将一个表空间的状态改为只读时，一开始该表空间处于一种中间的状态，在这种状态，除了哪些没有完成的事务之外在该表空间中不能进行任何的写操作，在那些没有完成的事务结束（提交或回滚），该表空间被设置为只读状态。

1、将表空间设置为只读状态：
ALTER TABLESPACE tablespace_name READ ONLY;

2、将表空间设置为可读可写(正常状态)时，该表空间必须处于联机状态：
ALTER TABLESPACE tablespace_name READ WRITE;

#表空间状态变化及相应的时间都已经被记录在报警文件alert_orcl.log中

五、重置数据文件大小

通过ALTER DATABASE来增加或减少数据文件的大小，增大数据文件的空间就不用添加更多的数据文件，也可以重置某个数据文件的大小来重新收回数据文件中没用到的空间。

在回收数据文件的空间时，如果该数据文件的大小已经小于数据文件中所存的全部对象的大小总和，那么数据文件的大小将只被减少到数据文件中最后一个对象的最后一个数据块。

#重置数据文件大小：
ALTER DATABASE DATAFILE '数据文件路径' RESIZE K|M|G;

关于表空间的简述和常用命令现在介绍这么多，后续在陆续补充，谢谢。

表空间概述和创建表空间命令详情请参阅
Oracle官方文档：Database Administrator’s Guide & SQL Language Reference