Oracle 表空间相关操作

Oracle 表空间相关操作

日常工作中经常要对Oracle 数据库进行扩容，做个记录方便查看

环境信息

系统环境：
CentOS7.9
软件版本：
Oracle 11G

一、表空间剩余情况检查

-- 查看表空间剩余大小
SELECT 
	A.TABLESPACE_NAME "表空间名",
	TOTAL / (1024 * 1024 * 1024) "表空间大小(G)",
	FREE / (1024 * 1024 * 1024) "表空间剩余大小(G)",
	(TOTAL - FREE) / (1024 * 1024 * 1024) "表空间使用大小(G)",
	ROUND((TOTAL - FREE) / TOTAL, 4) * 100 "使用率 %"
FROM (
	SELECT TABLESPACE_NAME, SUM(BYTES) FREE
	FROM DBA_FREE_SPACE
	GROUP BY TABLESPACE_NAME
) A,
(
	SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL
	FROM DBA_DATA_FILES
	GROUP BY TABLESPACE_NAME
) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME

二、查看表空间文件位置

SELECT 
	FILE_NAME,
	ROUND(BYTES/(1024*1024*1024),2)||'G' BYTES,
	AUTOEXTENSIBLE,
	ROUND(MAXBYTES/(1024*1024*1024),2)||'G' MAXBYTES 
FROM DBA_DATA_FILES T
WHERE T.TABLESPACE_NAME = 'TABLESPACENAME';

三、创建表空间

/**
 * 表空间的创建
 * 注意文件路径是存在的
 * 单个表空间文件大小最大是32G
 */

-- 1、创建一个大小为50M的表空间
CREATE TABLESPACE TEST1 DATAFILE '/data/app/oracle/oradata/scoot/TEST1/TEST01.dbf' SIZE 50M;

-- 2、创建一个大小为10M的表空间 并自动扩展10M 直到最大32G
CREATE TABLESPACE TEST2 DATAFILE '/data/app/oracle/oradata/scoot/TEST2/TEST01.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M;

-- 3、创建一个大小为10M的表空间 自动扩展10M 最大不超过1G
CREATE TABLESPACE TEST2 DATAFILE '/data/app/oracle/oradata/scoot/TEST3/TEST01.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 1024M;

四、表空间的修改

/**
 * 表空间的修改
 * 可以修改已有数据文件大小
 * 可以新增数据文件
 * 可以修改用户表空间位置
 */

-- 1、修改表空间,创建一个1G的数据文件，每次递增500M 最大不超过 32G
ALTER TABLESPACE TEST1 ADD DATAFILE '/data/app/oracle/oradata/scoot/TEST1/TEST02.dbf' SIZE 1024M AUTOEXTEND ON NEXT 500M MAXSIZE 32760M;

-- 2、修改表空间，创建一个 10G 的表空间数据文件
ALTER TABLESPACE TEST1 ADD DATAFILE  '/data/app/oracle/oradata/scoot/TEST1/TEST03.dbf' SIZE 10G;

-- 3、修改表空间，调整原有文件大小
ALTER DATABASE DATAFILE '/data/app/oracle/oradata/scoot/TEST1/TEST03.dbf' RESIZE 15G 

-- 4、修改当前用户默认表空间位置
ALTER DATABASE DEFAULT TABLESPACE TEST1;

-- 5、修改表空间名称
ALTER TABLESPACE TEST1 RENAME TO TEST1_1;

-- 6、删除表空间 不能删除默认表空间
DROP TABLESPACE TES1  INCLUDING CONTENTS AND DATAFILES;

五、临时表空间的操作

/**
 * 临时表空间相关操作
 * 临时表空间的用量查询
 * 创建文件、调整大小等
 */
-- 查询表空间占用情况
SELECT A.TABLESPACE_NAME "临时表空间名",
	ROUND(A.BYTES / 1024 / 1024 / 1024, 2) "临时表空间大小(G)",
	ROUND((A.BYTES - B.BYTES_USED) / 1024 / 1024 / 1024, 2) "临时表空间剩余大小(G)",
	ROUND(B.BYTES_USED / 1024 / 1024 / 1024, 2) "临时表空间使用大小(G)",
	ROUND(B.BYTES_USED * 100 / A.BYTES, 4) || '%' "使用率 %"
FROM (
	SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
		FROM DBA_TEMP_FILES
	GROUP BY TABLESPACE_NAME
) A,
(
	SELECT TABLESPACE_NAME, SUM(BYTES_CACHED) BYTES_USED
	FROM V$TEMP_EXTENT_POOL
	GROUP BY TABLESPACE_NAME
) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;

-- 1、查看临时表空间文件
SELECT FILE_NAME,
	ROUND(BYTES/(1024*1024*1024),2)||'G' BYTES,
	AUTOEXTENSIBLE,
	ROUND(MAXBYTES/(1024*1024*1024),2)||'G' MAXBYTES 
FROM DBA_TEMP_FILES T
WHERE T.TABLESPACE_NAME = 'TEMP';


-- 创建一个 1G 临时表空间 自动增长 1G 最大32G
ALTER TABLESPACE TEMP ADD tempfile '/data/app/oracle/oradata/scoot/tempdata/temp02.dbf' SIZE 1024M AUTOEXTEND ON NEXT 1024M MAXSIZE 32765M;

-- 注意 下面两句不能合并执行

-- 修改原有空间大小 
ALTER DATABASE TEMPFILE '/data/app/oracle/oradata/scoot/tempdata/temp02.dbf' RESIZE 2048M 

-- 修改自增的最大大小 
ALTER DATABASE TEMPFILE '/data/oradata/hdappzs/TEMPHDAPP01.dbf' AUTOEXTEND ON NEXT 1024M MAXSIZE 32765M