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;
