表空间相关操作大全

表空间相关操作大全

表空间是Oracle将相关逻辑结构分组在一起的数据库存储单元。数据库数据文件存储在表空间中。表空间（tablespaces）实际上是一个逻辑的概念，在物理上是并不存在的。DBA在日常管理中，表空间的管理尤为重要，关乎数据安全。本篇博客整理了一些表空间的相关操作。

系统重要表空间介绍

1.1 SYSTEM，系统表空间，存放关于表空间名称、控制文件、数据文件等管理信息，存放着方案对象（如表，索引，同义词，序列）的定义信息，存放着所有pl/sql 程序（如过程，函数，包，触发器）的源代码，是oracle数据库中最重要的表空间。它属于SYS和SYSTEM方案，仅被SYS和SYSTEM或其他具有足够权限的用户使用。即使是SYS 和SYSTEM用户也不能删除或重命名该空间。
自动管理，如发现空间占据70%以上，可考虑添加数据文件
1.2 SYSAUX，系统辅助表空间，用于减少system表空间的负荷，提高系统的工作效率
1.3 TEMP ，临时表空间，存放临时表和临时数据，用于排序。每个数据库都应该有一个（或创建一个）临时表空间，以便在创建用户时将其分配给用户，否则就会将TEMP表空间作为临时表空间。
1.4 UNDOTBS，重做表空间，存放数据库的有关重做的相关信息和数据，日志切换频繁时需添加数据文件
1.5 REDO，Oracle通过Redo来保证数据库的事务可以被重演，从而使得在故障之后，数据可以被恢复。Redo对于Oracle数据库来说至关重要。在数据库中，Redo的功能主要通过3个组件来实现：Redo Log Buffer、LGWR后台进程和Redo Log File（在归档模式下，Redo Log File最终会经由ARCn进程写出为归档日志文件）。
1.6 USERS，用户表空间，存放永久性的用户对象的数据和私有信息，因此也被称为数据表空间。每个数据库都应该有一个（或创建一个）用户表空间，以便在创建用户时将其分配给用户，否则将会使用SYSTEM表空间来保存数据。

1.创建表空间操作

--标准格式
CREATE TABLESPACE lmtbsb 
	LOGGING DATAFILE  
	'/u02/oracle/data/lmtbsb01.dbf' 
	SIZE 32M AUTOEXTEND ON    
	NEXT 32M MAXSIZE 2048M 
	EXTENT MANAGEMENT LOCAL; 

--创建非标准128K扩展区的表空间
CREATE TABLESPACE lmtbsb 
	LOGGING DATAFILE 
	'/u02/oracle/data/lmtbsb01.dbf' 
	SIZE 50M 
	EXTENT MANAGEMENT LOCAL 
	UNIFORM SIZE 128K;

--SET LONG 10000
SELECT DBMS_METADATA.GET_DDL('TABLESPACE',TABLESPACE_NAME) 
	FROM DBA_TABLESPACES 
	WHERE TABLESPACE_NAME='USERS';

2. 表空间查询操作

2.1 查询表空间使用情况

--包含自动扩展和非自动扩展表空间使用情况，比例等，DBA常用查询
--set linesize 4000
--set pagesize 2000

select a.tablespace_name,a.autoextensible,
               round(a.bytes_alloc / 1024 / 1024/1024) alloc_GB,
               round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024/1024) used_GB,
               round((a.bytes_alloc - nvl(b.bytes_free, 0)) * 100 /
                     a.maxbytes) used_of_max,
               round((a.maxbytes - a.bytes_alloc + nvl(b.bytes_free, 0)) /
                     1048576/1024) free_of_max_GB,
               round(a.maxbytes / 1048576/1024) max_GB,
                           round(((a.maxbytes - a.bytes_alloc + nvl(b.bytes_free, 0))/a.maxbytes*100),2) ratio
          from (select f.tablespace_name,f.autoextensible,
                       sum(f.bytes) bytes_alloc,
                       sum(decode(f.autoextensible,
                                  'YES',
                                  f.maxbytes,
                                  'NO',
                                  f.bytes)) maxbytes
                  from dba_data_files f
                 group by tablespace_name,autoextensible) a,
               (select f.tablespace_name, sum(f.bytes) bytes_free
                  from dba_free_space f
                 group by tablespace_name) b, dba_tablespaces
         where a.tablespace_name = b.tablespace_name and a.tablespace_name=dba_tablespaces.tablespace_name;

2.2 查询数据文件

-- 查询某个表空间的数据文件
SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'USER';

-- 查询临时表空间数据文件
SELECT * FROM DBA_TEMP_FILES D;

2.3 查询表空间下用户信息

-- 查询表空间下面所有的用户
SELECT DISTINCT S.OWNER 
	FROM DBA_SEGMENTS S 
	WHERE S.TABLESPACE_NAME ='TABLESPACENAME';

--查看用户默认表空间、临时表空间：
SELECT USERNAME,TEMPORARY_TABLESPACE,
	DEFAULT_TABLESPACE 
	FROM DBA_USERS;

2.4 查询表空间下所有对象

--删除表空间前查询表空间下所有对象
SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,
	SEGMENT_NAME AS SEGMENT_NAME , 
	SUM(BYTES)/1024/1024  AS SEGMENT_SIZE  
	FROM DBA_SEGMENTS
	WHERE TABLESPACE_NAME='TABLESPACENAME' 
	GROUP BY TABLESPACE_NAME,SEGMENT_NAME 
	ORDER BY 3
--ORDER BY 3 按照第三列（SEGMENT_SIZE）排序

2.5 查询表空间的日志模式

--使用OGG软件数据同步时需要开启logging
SELECT TABLESPACE_NAME,LOGGING 
	FROM DBA_TABLESPACES 
	WHERE TABLESPACE_NAME='TABLESPACENAME';

3. 表空间扩展操作

-- 直接修改原有数据文件大小，修改成32G，单个数据文件最大只能32G
ALTER DATABASE DATAFILE '/ORADATA/XXX.DBF' RESIZE 32G;

-- 给表空间增加数据文件
ALTER TABLESPACE DATA_ODS 
ADD DATAFILE '/oradata/xxx.dbf' 
SIZE 1G AUTOEXTEND ON
NEXT 8M MAXSIZE 30G;

4. 表空间删除操作

--删除users表空间，包含目录
DROP TABLESPACE users INCLUDING CONTENTS;

--删除users表空间，包含目录和数据文件
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;

5. 表空间给用户授权和配额

表空间存储限制是用户在某一个表空间中可以使用的存储空间总数。 在创建或修改用户时，可以由参数QUOTA指出。
若用户在向表空间存储数据时，超出了此限额，则会产生错误。 错误信息：ORA-01536:SPACE QUOTA EXCEEDED FOR TABLESPACE TABLESPACENAME…’。 可以通过查询字典DBA_TS_QUOTAS查看表空间限额信息。

--查看所有用户表空间的配额情况 
SELECT * FROM DBA_TS_QUOTAS;

--查看当前用户表空间的配额情况   
SELECT * FROM USER_TS_QUOTAS;
--更改用户的表空间限额：
--全局：
GRANT UNLIMITED TABLESPACE TO ABC;
--针对某个表空间：
ALTER USER ABC QUOTA UNLIMITED ON TEST;
--回收：
REVOKE UNLIMITEDTABLESPACE FROM ABC;
ALTER USER ABCQUOTA 0 ON TEST;

如果没得到你想要的答案，请移步至官方文档《Managing Tablespaces》。