Oracle 表空間常用操作

来源:https://www.cnblogs.com/yaenli/archive/2022/09/06/16662103.html
-Advertisement-
Play Games

aliases: [Oracle表空間] tags: [資料庫,Oracle,Blog] summary: [Oracle表空間常用操作，包括查詢、分析、擴容、刪除、優化等] date: [2022-09-06] links: 一、表空間查詢 相關表和視圖： dba_data_files dba_t ...


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

links:

目錄

一、表空間查詢

相關表和視圖：

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;

image

查詢及修改用戶預設表空間

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

image

查詢指定表的表空間

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;

image

--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;

image

二、表空間擴容

可以通過以下三種方法實現表空間的擴容（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

image

參考鏈接：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%以上）

  1. 檢查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$';
  1. 如果aud$過大，清理（導出aud$表之後，使用truncate清理）
  2. 如果出現aud$表為空。但是system表空間的使用率照樣達到99%的情況，建議增加數據文件

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

  1. 修改統計信息的保存時間
--檢查統計信息保存時間（預設應該是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;
  1. 刪除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值';

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • zabbix自定義監控
    zabbix自定義監控 自定義監控進程 測試監控httpd，需要在監控端部署httpd，以方便監控 `` 配置監控腳本 #在監控端 [root@localhost ~]# dnf -y install httpd [root@localhost ~]# systemctl start httpd [ ...
  • 巴西針對電源產品的新法規
    巴西ANATEL近日發佈了一項新的5159法案，該法案內容主要是關於行動電話使用的有線和無線電源和充電器。 法案稱為《手機用充電器合格評定技術要求及測試程式》，涵蓋了產品安全性和EMC的要求。新規將於公佈後180天，即今年10月26日起生效，該法案將取代現行的3481法案。 5159法案的新規定確實 ...
  • 從 Linux 內核角度探秘 JDK NIO 文件讀寫本質
    1. 前言 筆者在 《從 Linux 內核角度看 IO 模型的演變》一文中曾對 Socket 文件在內核中的相關數據結構為大家做了詳盡的闡述。 又在此基礎之上介紹了針對 socket 文件的相關操作及其對應在內核中的處理流程： 並與 epoll 的工作機制進行了串聯： 通過這些內容的串聯介紹，我想大 ...
  • win10和win11 wsl2配置，以及開發環境配置
    前置條件 確認系統版本 win10 19041版本以及更高 查看方式：win + r 輸入winver win11 * 啟用或關閉Windows功能 win + r, 輸入 optionalfeatures,勾選適用於Linux的Windows子系統，和虛擬機平臺選項。然後重啟電腦。 官方文檔提示可 ...
  • MySQL第四天
    2022-09-06 1、為某個欄位設置別名（as關鍵字） 以“students”為例： students表的欄位有：id,name,age,gender,is_del select name as n,age as a from students; 說明：select 屬性名 as 新名稱，屬性名 ...
  • 硬核解析MySQL的MVCC實現原理，面試官看了都直呼內行
    MVCC全稱是Multi-Version Concurrency Control（多版本併發控制），是一種併發控制的方法，通過維護一個數據的多個版本，減少讀寫操作的衝突。 如果沒有MVCC，想要實現同一條數據的併發讀寫，還要保證數據的安全性，就需要操作數據的時候加讀鎖和寫鎖，這樣就降低了資料庫的併發... ...
  • 資料庫原理基礎一
    數據的分類： 結構化數據：有固定的格式，例如一個表格裡面的信息 非結構化數據：無固定格式，例如圖片、網頁、語音、視頻 半結構化數據：html、xml格式的數據 資料庫：存放數據的一個集合，可以是文件的方式，也可以是其他的方式。 資料庫的發展： 萌芽階段：文件系統 使用磁碟文件來存儲數據，比如一個文檔 ...
  • 記錄一下～～～Linux配置定時任務備份資料庫dmp文件
    1.創建備份目錄: mkdir -p /dcits/sx_xmz/sx_data_bak chown -R oracle18c:oinstall /dcits/sx_xmz/sx_data_bak 2.腳本文件編寫 vi databak.sh 輸入如下內容 #設置環節變數 export ORACLE ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類