aliases: [Oracle表空間]
tags: ???? [資料庫,Oracle,Blog]
summary: [Oracle表空間常用操作，包括查詢、分析、擴容、洗掉、優化等]
date: ???? [2022-09-06]

links:

- aliases: [Oracle表空間]
  tags: ???? [資料庫,Oracle,Blog]
  summary: [Oracle表空間常用操作，包括查詢、分析、擴容、洗掉、優化等]
  date: ???? [2022-09-06]
一、表空間查詢
- 查詢表空間
- 表空間名字及資料檔案位置
- 查詢及修改用戶默認表空間
- 查詢指定表的表空間
- 查詢表空間下的用戶
- 查詢用戶下所有表使用的表空間
- 查詢表空間下面的所有表
- 表空間使用情況
- 表空間資料檔案使用情況
二、表空間擴容
- M1：改變資料檔案大小
  - 獲取調整資料檔案的sql
- M2：允許資料檔案自動增長
- M3：增加資料檔案
三、洗掉表空間及資料檔案
四、表空間優化
- Q1 system表空間過大（使用率95%以上）
- Q2 sysaux表空間過大（使用率95%以上）

一、表空間查詢

相關表和視圖：

dba_data_files
dba_tablespaces，v$tablespace
dba_free_space
dba_users
dba_segments
dba_tables
all_tables

查詢表空間

-- 1 ）方式1：dba_tablespaces
select * from dba_tablespaces;
-- 2 ）方式2：v$tablespace
select * from v$tablespace;

表空間名字及資料檔案位置

select tablespace_name,
       file_id,
       file_name,
       round(bytes / (1024 * 1024), 0) total_space
  from sys.dba_data_files
 order by tablespace_name;

查詢及修改用戶默認表空間

--查詢
select username,default_tablespace, temporary_tablespace, t.* from dba_users t;
--修改默認表空間
ALTER USER $usercode DEFAULT TABLESPACE $tablespacename;
ALTER USER $usercode TEMPORARY TABLESPACE temp;

查詢指定表的表空間

select table_name,tablespace_name from user_tables where TABLE_NAME='表名';

查詢表空間下的用戶

/*查看表空間下有多少用戶，tablespace_name表空間 的名字一定要大寫 */
select distinct s.owner from dba_segments s where s.tablespace_name ='TMS21';

查詢用戶下所有表使用的表空間

select owner,table_name,tablespace_name from dba_tables where owner='用戶名';

查詢表空間下面的所有表

select * from all_tables where tablespace_name='表空間名'

表空間使用情況

select a.tablespace_name,
       a.bytes / 1024 / 1024 "sum MB",
       (a.bytes - b.bytes) / 1024 / 1024 "used MB",
       b.bytes / 1024 / 1024 "free MB",
       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "used%"
  from (select tablespace_name, sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by ((a.bytes - b.bytes) / a.bytes) desc;

--sql2
SELECT UPPER(F.TABLESPACE_NAME)"表空間名",
　　D.TOT_GROOTTE_MB "表空間大小(M)",
　　D.TOT_GROOTTE_MB-F.TOTAL_BYTES "已使用空間(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99')||'%'"使用比",
　　F.TOTAL_BYTES "空閑空間(M)",
　　F.MAX_BYTES "最大塊(M)"
　　FROM(SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_BYTES,
　　ROUND(MAX(BYTES)/(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),2)TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME)D
　　WHERE D.TABLESPACE_NAME=F.TABLESPACE_NAME

--包含臨時表空間使用情況
select *
  from (Select a.tablespace_name,
               to_char(a.bytes / 1024 / 1024, '99,999.999') total_bytes,
               to_char(b.bytes / 1024 / 1024, '99,999.999') free_bytes,
               to_char(a.bytes / 1024 / 1024 - b.bytes / 1024 / 1024,
                       '99,999.999') use_bytes,
               to_char((1 - b.bytes / a.bytes) * 100, '99.99') || '%' use
          from (select tablespace_name, sum(bytes) bytes
                  from dba_data_files
                 group by tablespace_name) a,
               (select tablespace_name, sum(bytes) bytes
                  from dba_free_space
                 group by tablespace_name) b
         where a.tablespace_name = b.tablespace_name
        union all
        select c.tablespace_name,
               to_char(c.bytes / 1024 / 1024, '99,999.999') total_bytes,
               to_char((c.bytes - d.bytes_used) / 1024 / 1024, '99,999.999') free_bytes,
               to_char(d.bytes_used / 1024 / 1024, '99,999.999') use_bytes,
               to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' use
          from (select tablespace_name, sum(bytes) bytes
                  from dba_temp_files
                 group by tablespace_name) c,
               (select tablespace_name, sum(bytes_cached) bytes_used
                  from v$temp_extent_pool
                 group by tablespace_name) d
         where c.tablespace_name = d.tablespace_name)
 order by tablespace_name ;

表空間資料檔案使用情況

 select b.file_id　　檔案ID,
　　b.tablespace_name　　表空間,
　　b.file_name　　　　　物理檔案名,
　　b.bytes　　　　　　　總位元組數,
　　(b.bytes-sum(nvl(a.bytes,0)))　　　已使用,
　　sum(nvl(a.bytes,0))　　　　　　　　剩余,
　　sum(nvl(a.bytes,0))/(b.bytes)*100　剩余百分比
　　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.file_id,b.bytes
　　order by b.tablespace_name;

二、表空間擴容

可以通過以下三種方法實作表空間的擴容（M1,M2,M3）

單個資料檔案最大不超過32G，

M1：改變資料檔案大小

alter database datafile 'D:\APP\ADMINISTRATOR\ORADATA\XYSHARE\SGSHARE.DBF' resize 4096m;

獲取調整資料檔案的sql

select a.file# as "資料檔案id",
       a.name as "資料檔案路徑",
       a.bytes / 1024 / 1024 as "當前資料檔案大小(MB)",
       ceil(HWM * a.block_size) / 1024 / 1024 as "可調整至大小(MB)",
       (a.bytes - HWM * a.block_size) / 1024 / 1024 AS "釋放空間大小(MB)",
       'alter database datafile ''' || a.name || ''' resize ' ||
       ceil(ceil(HWM * a.block_size) / 1024 / 1024) || 'M;' as "SQL陳述句"
  from v$datafile a,
       (SELECT file_id, MAX(block_id + blocks - 1) HWM
          FROM DBA_EXTENTS
         GROUP BY file_id) b
 where a.file# = b.file_id(+)
   And (a.bytes - HWM * a.block_size) > 0
   and rownum < 30
 order by "釋放空間大小(MB)" desc

參考鏈接：ORACLE調整資料檔案大小

M2：允許資料檔案自動增長

ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF'
AUTOEXTEND ON NEXT 100M MAXSIZE 20480M;

M3：增加資料檔案

ALTER TABLESPACE SYSTEM ADD DATAFILE
'C:\APP\ORACLE\ORADATA\DFYYCDB\DATAFILE\O2_MF_SYSTEM_CWMNZ9XV_.DBF' 
size 7167M autoextend on ;

三、洗掉表空間及資料檔案

正常情況下，洗掉表空間的正確方法為：

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

如果沒有通過以上命令洗掉而直接洗掉了資料檔案，將導致資料庫無法打開，

Oracle 10G R2開始，可以采用來洗掉一個空資料檔案，并且相應的資料字典資訊也會清除:

Alter tablespace tablespace_name drop datafile file_name;

oracle 10g可以洗掉臨時表空間的檔案

alter database tempfile '/home/oracle/temp01.dbf' drop including datafiles;

使用offline資料檔案的方法

非歸檔模式使用：alter database datafile '...' offline drop;
歸檔模式使用： alter database datafile '...' offline;

說明：
1) 以上命令只是將該資料檔案OFFLINE，而不是在資料庫中洗掉資料檔案，該資料檔案的資訊在控制檔案種仍存在，查詢v$datafile，仍顯示該檔案，
2) 歸檔模式下offline和offline drop效果是一樣的
3) offline后，存在此datafile上的物件將不能訪問
4) noarchivelog模式下，只要online redo日志沒有被重寫，可以對這個檔案recover后進行online操作
————————————————
原文鏈接：https://blog.csdn.net/junmail/article/details/5081714

四、表空間優化

Q1 system表空間過大（使用率95%以上）

檢查aud$表大小

--查看資料庫表大小SQL
select bytes,owner,segment_name 
from dba_segments 
where segment_type='TABLE' order by bytes desc;
 
--查看aud$表大小SQL
select bytes,owner,segment_name 
from dba_segments 
where segment_type='TABLE' and segment_name = 'AUD$';

如果aud$過大，清理（匯出aud$表之后，使用truncate清理）
如果出現aud$表為空，但是system表空間的使用率照樣達到99%的情況，建議增加資料檔案

Q2 sysaux表空間過大（使用率95%以上）

修改統計資訊的保存時間

--檢查統計資訊保存時間（默認應該是31天）
select dbms_stats.get_stats_history_retention from dual;  
--如果31天將其改為7天
exec dbms_stats.alter_stats_history_retention(7); 
--驗證是否修改成功
select dbms_stats.get_stats_history_retention from dual;

洗掉AWR報告快照

補充：Oracle 10g中快斬訓保留7天，11g的快照保留8天，超出會自動洗掉，AWR快照可以從其他資料庫匯入，而這部分資料會保存時間極長，有時候也會遇到自動快照不能自動收集，而手工創建的快照又可以成功，對于這種情況就需要把以前的快照清理掉，

洗掉AWR有兩種方式進行洗掉：dbms_workload_repository，dbms_swrf_internal，dbms_workload_repository可以洗掉本地和其他資料庫的快照，可以選擇不同的快照來進行洗掉；dbms_swrf_internal只能對其他資料庫的快照來進行操作，會把所有的快照直接干掉，

使用dbms_workload_repository包洗掉：

 select dbid, retention from dba_hist_wr_control;
 select min(snap_id), max(snap_id)from dba_hist_snapshot where dbid = '得到的dbid值';
 exec dbms_workload_repository.drop_snapshot_range('得到的min(snap_id)值','得到的max(snap_id)值','得到的dbid值');
 select * from dba_hist_snapshot where dbid = '得到的dbid值';

使用dbm_swrf_internal包洗掉：

 select dbid, retention from dba_hist_wr_control;
 select min(snap_id), max(snap_id)from dba_hist_snapshot where dbid = '得到的dbid值';
 exec dbms_swrf_internal.unregister_database('得到的dbid值');
 select * from dba_hist_snapshot where dbid = '得到的dbid值';

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/504771.html

標籤：其他

上一篇：記錄一下～～～Linux配置定時任務備份資料庫dmp檔案

下一篇：傳遞/引數化函式的回傳子型別

Oracle 表空間常用操作

aliases: [Oracle表空間] tags: ???? [資料庫,Oracle,Blog] summary: [Oracle表空間常用操作，包括查詢、分析、擴容、洗掉、優化等] date: ???? [2022-09-06]

一、表空間查詢

查詢表空間

表空間名字及資料檔案位置

查詢及修改用戶默認表空間

查詢指定表的表空間

查詢表空間下的用戶

查詢用戶下所有表使用的表空間

查詢表空間下面的所有表

表空間使用情況

表空間資料檔案使用情況

二、表空間擴容

M1：改變資料檔案大小

獲取調整資料檔案的sql

M2：允許資料檔案自動增長

M3：增加資料檔案

三、洗掉表空間及資料檔案

四、表空間優化

Q1 system表空間過大（使用率95%以上）

Q2 sysaux表空間過大（使用率95%以上）

aliases: [Oracle表空間]
tags: ???? [資料庫,Oracle,Blog]
summary: [Oracle表空間常用操作，包括查詢、分析、擴容、洗掉、優化等]
date: ???? [2022-09-06]