oracle表空间查询维护命令大全之一(数据表空间)史上最全

表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。所有的数据库对象都存放在建立指定的表空间中。但主要存放的是表， 所以称作表空间。在oracle 数据库中至少存在一个表空间，即SYSTEM的表空间。一个表空间可以包含多个段，以及区，以及最小的块，同时也可以包含多个数据文件（物理结构）。

oracle 可以根据表空间对相关的用户配额以及磁盘空间都有极大的保护作用，而且还可以灵活的存放，同时也可以把不同的数据文件分开存放，保证不同数据文件的安全行。
所以在一般数据创建后，就会创建相关的表空间，再创建相关的用户。Oracle数据库开创性地提出了表空间的设计理念，这为Oracle数据库的高性能做出了不可磨灭的贡献。可以这么说，Oracle中很多优化都是基于表空间的设计理念而实现的。

以下针对数据表空间一些相关命令进行汇总

1,表空间汇总

--创建表空间的脚本
CREATE TABLESPACE TEST_FILE
LOGGING
DATAFILE '/U02/APP/ORACLE/ORADATA/ITSDB/TEST_FILE.DBF'
SIZE 10M
AUTOEXTEND ON
NEXT 10M MAXSIZE 10240M
EXTENT MANAGEMENT LOCAL;

--查看用户的默认表空间
SELECT T.USERNAME,T.DEFAULT_TABLESPACE FROM DBA_USERS T

--修改用户的默认表空间TABLESPACE_B
ALTER USER USER_A DEFAULT TABLESPACE TABLESPACE_B

--用户在表空间上放开限制
ALTER USER USER_A QUOTA UNLIMITED ON TABLESPACE_B;

--回收权限
REVOKE UNLIMITED TABLESPACE ON TABLESPACE_A FROM USER_A

--以使USER_A帐户不能在TABLESPACE_A上创建任何对象。
ALTER USER USER_A QUOTA 0 ON TABLESPACE_A

--移动表的表空间
ALTER TABLE CQRM.CQ_FLIGHTS_SEATS_SEQUENCE MOVE TABLESPACE CQRM;COMMIT;

--批量移动表空间的语句
SELECT 'ALTER TABLE '||TABLE_NAME||' MOVE TABLESPACE CQRM;COMMIT;' FROM USER_TABLES WHERE TABLESPACE_NAME='TEST';

--在表空间上重建索引
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD TABLESPACE CQRM;COMMIT;' FROM DBA_INDEXES WHERE TABLE_NAME IN ('')
AND OWNER='TEST'

SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MOVE TABLESPACE TEST;COMMIT;' FROM DBA_TABLES WHERE TABLESPACE_NAME='TEST1' AND OWNER='TEST'

2,改变表空间状态

1.使表空间脱机

ALTER TABLESPACE GAME OFFLINE;

如果是意外删除了数据文件，则必须带有RECOVER选项

ALTER TABLESPACE GAME OFFLINE FOR RECOVER;

2.使表空间联机

ALTER TABLESPACE GAME ONLINE;

3.使数据文件脱机

ALTER DATABASE DATAFILE 3 OFFLINE;

4.使数据文件联机

ALTER DATABASE DATAFILE 3 ONLINE;

5.使表空间只读

ALTER TABLESPACE GAME READ ONLY;

6.使表空间可读写

ALTER TABLESPACE GAME READ WRITE;

删除表空间

DROP TABLESPACE DATA01 INCLUDING CONTENTS AND DATAFILES;

扩展表空间

3,首先查看表空间的名字和所属文件

SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME,
ROUND(BYTES/(1024*1024),0) TOTAL_SPACE
FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME;

1.增加数据文件
ALTER TABLESPACE GAME
ADD DATAFILE '/ORACLE/ORADATA/DB/GAME02.DBF' SIZE 1000M;

2.手动增加数据文件尺寸
ALTER DATABASE DATAFILE '/ORACLE/ORADATA/DB/GAME.DBF'
RESIZE 4000M;

3.设定数据文件自动扩展
ALTER DATABASE DATAFILE '/ORACLE/ORADATA/DB/GAME.DBF'
AUTOEXTEND ON NEXT 100M
MAXSIZE 10000M;
4.更改数据文件的位置
ALTER TABLESPACE APP_DATA RENAME DATAFILE
'' TO ''

--整合表空间的碎片
ALTER TABLESPACE TABLESPACENAME COALESCE
此语句是整合表空间的碎片增加表空间的连续性，但是他不会收缩一个文件的大小的。

设定后查看表空间信息

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

--查看表空间使用状况

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(G)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(G)",
CASE
WHEN D.TOT_GROOTTE_MB = 0 THEN
0
ELSE
TO_NUMBER(TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) /
D.TOT_GROOTTE_MB * 100,
2),
'990.99'))
END "使用比",
F.TOTAL_BYTES "空闲空间(G)",
F.MAX_BYTES "最大块(G)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC
--也可以从视图DBA_TABLESPACE_USAGE_METRICS 查询如果表空间不是自动扩展则两者查询结果相同，如果是自动扩展的话则两者有偏差，以上面的查询为准

--查看用户使用了那些表空间
SELECT OWNER, OBJECT_TYPE, TABLESPACE_NAME
FROM (SELECT DISTINCT 'TABLE' OBJECT_TYPE, OWNER, TABLESPACE_NAME
FROM DBA_TABLES
UNION
SELECT DISTINCT 'INDEX' OBJECT_TYPE, OWNER, TABLESPACE_NAME
FROM DBA_INDEXES)
WHERE TABLESPACE_NAME IS NOT NULL
AND OWNER = 'EZOFFICE'
ORDER BY 1, 2, 3;

SELECT T.SEGMENT_NAME, T.TABLESPACE_NAME, BYTES / 1024 / 1024
FROM DBA_SEGMENTS T
WHERE T.SEGMENT_NAME IN
('')
AND OWNER = 'USER'

--可以查看回收的数据文件大小
SELECT 'ALTER DATABASE DATAFILE ''' || A.FILE_NAME || ''' RESIZE ' ||
ROUND(A.FILESIZE - (A.FILESIZE - C.HWMSIZE - 100) * 0.8) || 'M;',
A.FILESIZE || 'M' AS "数据文件的总大小",
C.HWMSIZE || 'M' AS "数据文件的实用大小"
FROM (SELECT FILE_ID, FILE_NAME, ROUND(BYTES / 1024 / 1024) AS FILESIZE
FROM DBA_DATA_FILES) A,
(SELECT FILE_ID, ROUND(MAX(BLOCK_ID) * 8 / 1024) AS HWMSIZE
FROM DBA_EXTENTS
GROUP BY FILE_ID) C
WHERE A.FILE_ID = C.FILE_ID

AND A.FILESIZE - C.HWMSIZE > 100;