您的位置:首頁 > 更多 > 資訊 >內(nèi)容

查看臨時表空間使用情況,如何擴展表空間

來源:教育資源網(wǎng) ? 發(fā)布時間:2023-04-21 18:44:43 ? 點擊:1173

查看臨時表空間使用情況,如何擴展表空間

SELECT D.TABLESPACE_NAME,SPACE \”SUM_SPACE(M)\”,BLOCKS SUM_BLOCKS, USED_SPACE \”USED_SPACE(M)\”,ROUND(NVL(USED_SPACE,0)/SPACE*100,2) \”USED_RATE(%)\”,NVL(FREE_SPACE,0) \”FREE_SPACE(M)\”FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKSFROM DBA_TEMP_FILESGROUP BY TABLESPACE_NAME) D,(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACEFROM V$TEMP_SPACE_HEADERGROUP BY TABLESPACE_NAME) FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)當通過ORACLE中的create table … as select 語句創(chuàng)建一張新表時，新表的數(shù)據(jù)量為比較大，如10億，這時SQL*Plus很可能就會提示“ORA-01653: …”錯誤信息。這個錯誤信息暗示表空間大小不夠，需要為表空間增加數(shù)據(jù)文件。科普生活

如何修改臨時表空間大小

查看所有用戶的臨時表空間及相應的數(shù)據(jù)文件：select d.username, t.file_name, d.temporary_tablespace from DBA_TEMP_FILES t,dba_users d where t.tablespace_name = d.temporary_tablespace;修改臨時表空間tmp的大?。篴lter database tempfile \’/home/oracle/oradata/trade/temp01.dbf\’ resize 4096m;將系統(tǒng)的默認臨時表空間設為tmp：alter database default temporary tablespace tmp;修改用戶aa的默認表空間為tmp：alter user aa temporary tablespace tmp;刪除用戶aa以前的臨時表空間ex_aa：drop tablespace ex_aa including contents and datafiles;

如何查看數(shù)據(jù)庫的默認表空間，與臨時表空間

sel

ORACLE 臨時表空間TEMP 滿了怎么辦？

打印出SQL放到PL/SQL Devoloper 執(zhí)行，報“無法通過8（在表空間XXX中）擴展 temp 段”，還有一個頁面，可以查詢出記錄，但無法統(tǒng)計數(shù)據(jù)！經(jīng)過分析產(chǎn)生原因可能是：ORACLE臨時段表空間不足，因為ORACLE總是盡量分配連續(xù)空間，一但沒有足夠的可分配空間或者分配不連續(xù)就會出現(xiàn)上述的現(xiàn)象。解決方法：知道由于ORACLE將表空間作為邏輯結構-單元，而表空間的物理結構是數(shù)據(jù)文件，數(shù)據(jù)文件在磁盤上物理地創(chuàng)建，表空間的所有對象也存在于磁盤上，為了給表空間增加空間，就必須增加數(shù)據(jù)文件。

先查看一下指定表空間的可用空間，使用視圖SYS.DBA_FREE_SPACE，視圖中每條記錄代表可用空間的碎片大小：SQL>Select file_id,block_id,blocks,bytes from sys.dba_free_space where tablespace_name=‘XXX’;返回的信息可初步確定可用空間的**塊，看一下它是否小于錯誤信息中提到的尺寸，再查看一下缺省的表空間參數(shù)：SQL>SELECT INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,PCT_INCREASE FROM SYS.DBA_TABLESPACES WHERE TABLESPACE_NAME=\’XXX\’;通過下面的SQL命令修改臨時段表空間的缺省存儲值：SQL>ALTER TABLESPACE name DEFAULT STORAGE (INITIAL 64K NEXT 64K);適當增大缺省值的大小有可能解決出現(xiàn)的錯誤問題，也可以通過修改用戶的臨時表空間大小來解決這個問題：SQL>好文分享;ALTER USER username TEMPORARY TABLESPACE new_tablespace_name;使用ALTER TABLESPACE命令，一但完成，所增加的空間就可使用，無需退出數(shù)據(jù)庫或使表空間脫機，但要注重，一旦添加了數(shù)據(jù)文件，就不能再刪除它，若要刪除，就要刪除表空間。

MYSQL存儲引擎InnoDB（三十五）：臨時表空間

InnoDB使用會話臨時表空間和全局臨時表空間。 在InnoDB配置為磁盤內(nèi)部臨時表的存儲引擎時，會話臨時表空間存儲用戶創(chuàng)建的臨時表和優(yōu)化器創(chuàng)建的內(nèi)部臨時表。

從 MySQL 8.0.16 開始，用于磁盤內(nèi)部臨時表的存儲引擎固定為InnoDB。

（之前，存儲引擎由internal_tmp_disk_storage_engine的值決定 ） 在**次請求創(chuàng)建磁盤臨時表時會話臨時表空間從臨時表空間池中被分配給會話。一個會話最多分配兩個表空間，一個用于用戶創(chuàng)建的臨時表，另一個用于優(yōu)化器創(chuàng)建的內(nèi)部臨時表。分配給會話的臨時表空間用于會話創(chuàng)建的所有磁盤臨時表。當會話斷開連接時，其臨時表空間將被截斷并釋放回池中。

服務器啟動時會創(chuàng)建一個包含 10 個臨時表空間的池。池的大小永遠不會縮小，并且表空間會根據(jù)需要自動添加到池中。臨時表空間池在正常關閉或中止初始化時被刪除。

會話臨時表空間文件在創(chuàng)建時大小為 5 頁，并且具有.ibt文件擴展名。 InnoDB為會話臨時表空間保留了40 萬個空間 ID。因為每次啟動服務器時都會重新創(chuàng)建會話臨時表空間池，所以會話臨時表空間的空間 ID 在服務器關閉時不會保留，并且可以重復使用。

innodb_temp_tablespaces_dir 變量定義了創(chuàng)建會話臨時表空間的位置。默認位置是 #innodb_temp數(shù)據(jù)目錄中的目錄。如果無法創(chuàng)建臨時表空間池，則會拒絕啟動。

在基于語句的** (SBR) 模式下，在副本上創(chuàng)建的臨時表駐留在單個會話臨時表空間中，該臨時表空間僅在 MySQL 服務器關閉時被截斷。 INNODB_SESSION_TEMP_TABLESPACES 表提供有關會話臨時表空間的元數(shù)據(jù)。 該INFORMATION_SCHEMA.INNODB_TEMP電商_TABLE_INFO表提供有關在InnoDB實例中處于活動狀態(tài)的用戶創(chuàng)建的臨時表的元數(shù)據(jù)。 全局臨時表空間 ( ibtmp1) 存儲對用戶創(chuàng)建的臨時表所做的更改的回滾段。

innodb_temp_data_file_path 變量定義了全局臨時表空間數(shù)據(jù)文件的相對路徑、名稱、大小和屬性。如果沒有為innodb_temp_data_file_path指定值 ，則默認行為是創(chuàng)建innodb_data_home_dir目錄中命名為ibtmp1的單個自動擴展數(shù)據(jù)文件。初始文件大小略大于 12MB。 全局臨時表空間在正常關閉或中止初始化時被刪除，并在每次服務器啟動時重新創(chuàng)建。

全局臨時表空間在創(chuàng)建時會收到一個動態(tài)生成的空間 ID。如果無法創(chuàng)建全局臨時表空間，則拒絕啟動。如果服務器意外停止，則不會刪除全局臨時表空間。在這種情況下，數(shù)據(jù)庫管理員可以手動刪除全局臨時表空間或重新啟動 MySQL 服務器。

重新啟動 MySQL 服務器會自動刪除并重新創(chuàng)建全局臨時表空間。 全局臨時表空間不能駐留在原始設備上。 INFORMATION_SCHEMA.FILES提供有關全局臨時表空間的元數(shù)據(jù)。

發(fā)出與此類似的查詢以查看全局臨時表空間元數(shù)據(jù)：默認情況下，全局臨時表空間數(shù)據(jù)文件會自動擴展并根據(jù)需要增加大小。 要確定全局臨時表空間數(shù)據(jù)文件是否正在自動擴展，請檢查以下 innodb_temp_data_file_path 設置：要檢查全局臨時表空間數(shù)據(jù)文件的大小，請使用與此類似的查詢來查詢INFORMATION_SCHEMA.FILES表：TotalSizeBytes顯示全局臨時表空間數(shù)據(jù)文件的當前大小。 或者，檢查操作系統(tǒng)上的全局臨時表空間數(shù)據(jù)文件大小。

全局臨時表空間數(shù)據(jù)文件位于 innodb_temp_data_file_path 變量定義的目錄中。 要回收全局臨時表空間數(shù)據(jù)文件占用的磁盤空間，請重新啟動 MySQL 服務器。重新啟動服務器會根據(jù)innodb_temp_data_file_path定義的屬性刪除并重新創(chuàng)建全局臨時表空間數(shù)據(jù)文件 。 要限制全局臨時表空間數(shù)據(jù)文件的大小，請配置 innodb_temp_data_file_path以指定**文件大小。

例如：配置 innodb_temp_data_file_path 需要重新啟動服務器。

怎么修改mysql數(shù)據(jù)庫臨時表空間大小

以MySQL 8.0 來說，通過查看 8.0 的**文檔得知，8.0 的臨時表空間分為會話臨時表空間和全局臨時表空間，會話臨時表空間存儲用戶創(chuàng)建的臨時表和當 InnoDB 配置為磁盤內(nèi)部臨時表的存儲引擎時由優(yōu)化器創(chuàng)建的內(nèi)部臨時表，當會話斷開連接時，其臨時表空間將被截斷并釋放回池中;也就是說，在 8.0 中有一個專門的會話臨時表空間，當會話被殺掉后，可以回收磁盤空間;而原來的 ibtmp1 是現(xiàn)在的全局臨時表空間，存放的是對用戶創(chuàng)建的臨時表進行更改的回滾段，在 5.7 中 ibtmp1 存放的是用戶創(chuàng)建的臨時表和磁盤內(nèi)部臨時表;
也就是在 8.0 和 5.7 中 ibtmp1 的用途發(fā)生了變化，5.7 版本臨時表的數(shù)據(jù)存放在 ibtmp1 中，在 8.0 版本中臨時表的數(shù)據(jù)存放在會話臨時表空間，如果臨時表發(fā)生更改，更改的 undo 數(shù)據(jù)存放在 ibtmp1 中;

實驗驗證：將之前的查詢結果保存成臨時表，對應會話是 45 號，通過查看對應字典表，可知 45 號會話使用了 temp_8.ibt 這個表空間，通過把查詢保存成臨時表，可以用到會話臨時表空間，如下圖：

下一步殺掉 45 號會話，發(fā)現(xiàn) temp_8.ibt 空間釋放了，變?yōu)榱顺跏即笮。瑺顟B(tài)為非活動的，證明在 mysql8.0 中可以通過殺掉會話來釋放臨時表空間。

總結：在 mysql5.7 時，殺掉會話，臨時表會釋放，但是僅僅是在 ibtmp 文件里標記一下，空間是不會釋放回操作系統(tǒng)的。

如果要釋放空間，需要重啟數(shù)據(jù)庫;在 mysql8.0 中可以通過殺掉會話來釋放臨時表空間。

相關推薦