oracle資料表空間查詢維護命令大全之一(資料資料表空間)史上最全，oracle命令大全

oracle資料表空間查詢維護命令大全之一(資料資料表空間)史上最全，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;

下一篇會繼續講述uodo資料表空間

oracle 資料表空間與資料檔案是1對多的關係問怎查詢一個資料表空間，具體包含那幾個資料檔案？

select s.name, d.name from v$datafile d, v$tablespace s where d.ts# = s.ts#;

這是資料表空間與資料檔案的對應關係.如果想看某個資料表空間有哪些資料檔案, 可在where後面加條件.
 
查詢一個資料表空間的擁有的資料檔案的oracle命令

用具有dba許可權的使用者查詢

select
b.file_name 物理檔案名稱,
b.tablespace_name 資料表空間,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name