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