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

查看臨時(shí)表空間使用情況,如何擴(kuò)展表空間

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

查看臨時(shí)表空間使用情況,如何擴(kuò)展表空間

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(+)當(dāng)通過(guò)ORACLE中的create table … as select 語(yǔ)句創(chuàng)建一張新表時(shí)，新表的數(shù)據(jù)量為比較大，如10億，這時(shí)SQL*Plus很可能就會(huì)提示“ORA-01653: …”錯(cuò)誤信息。這個(gè)錯(cuò)誤信息暗示表空間大小不夠，需要為表空間增加數(shù)據(jù)文件。科普生活

如何修改臨時(shí)表空間大小

查看所有用戶(hù)的臨時(shí)表空間及相應(yīng)的數(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;修改臨時(shí)表空間tmp的大?。篴lter database tempfile \’/home/oracle/oradata/trade/temp01.dbf\’ resize 4096m;將系統(tǒng)的默認(rèn)臨時(shí)表空間設(shè)為tmp：alter database default temporary tablespace tmp;修改用戶(hù)aa的默認(rèn)表空間為tmp：alter user aa temporary tablespace tmp;刪除用戶(hù)aa以前的臨時(shí)表空間ex_aa：drop tablespace ex_aa including contents and datafiles;

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

sel

ORACLE 臨時(shí)表空間TEMP 滿(mǎn)了怎么辦？

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

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

MYSQL存儲(chǔ)引擎InnoDB（三十五）：臨時(shí)表空間

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

從 MySQL 8.0.16 開(kāi)始，用于磁盤(pán)內(nèi)部臨時(shí)表的存儲(chǔ)引擎固定為InnoDB。

（之前，存儲(chǔ)引擎由internal_tmp_disk_storage_engine的值決定 ） 在**次請(qǐng)求創(chuàng)建磁盤(pán)臨時(shí)表時(shí)會(huì)話(huà)臨時(shí)表空間從臨時(shí)表空間池中被分配給會(huì)話(huà)。一個(gè)會(huì)話(huà)最多分配兩個(gè)表空間，一個(gè)用于用戶(hù)創(chuàng)建的臨時(shí)表，另一個(gè)用于優(yōu)化器創(chuàng)建的內(nèi)部臨時(shí)表。分配給會(huì)話(huà)的臨時(shí)表空間用于會(huì)話(huà)創(chuàng)建的所有磁盤(pán)臨時(shí)表。當(dāng)會(huì)話(huà)斷開(kāi)連接時(shí)，其臨時(shí)表空間將被截?cái)嗖⑨尫呕爻刂小?/p>

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

會(huì)話(huà)臨時(shí)表空間文件在創(chuàng)建時(shí)大小為 5 頁(yè)，并且具有.ibt文件擴(kuò)展名。 InnoDB為會(huì)話(huà)臨時(shí)表空間保留了40 萬(wàn)個(gè)空間 ID。因?yàn)槊看螁?dòng)服務(wù)器時(shí)都會(huì)重新創(chuàng)建會(huì)話(huà)臨時(shí)表空間池，所以會(huì)話(huà)臨時(shí)表空間的空間 ID 在服務(wù)器關(guān)閉時(shí)不會(huì)保留，并且可以重復(fù)使用。

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

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

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

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

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

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

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

例如：配置 innodb_temp_data_file_path 需要重新啟動(dòng)服務(wù)器。

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

以MySQL 8.0 來(lái)說(shuō)，通過(guò)查看 8.0 的**文檔得知，8.0 的臨時(shí)表空間分為會(huì)話(huà)臨時(shí)表空間和全局臨時(shí)表空間，會(huì)話(huà)臨時(shí)表空間存儲(chǔ)用戶(hù)創(chuàng)建的臨時(shí)表和當(dāng) InnoDB 配置為磁盤(pán)內(nèi)部臨時(shí)表的存儲(chǔ)引擎時(shí)由優(yōu)化器創(chuàng)建的內(nèi)部臨時(shí)表，當(dāng)會(huì)話(huà)斷開(kāi)連接時(shí)，其臨時(shí)表空間將被截?cái)嗖⑨尫呕爻刂?也就是說(shuō)，在 8.0 中有一個(gè)專(zhuān)門(mén)的會(huì)話(huà)臨時(shí)表空間，當(dāng)會(huì)話(huà)被殺掉后，可以回收磁盤(pán)空間;而原來(lái)的 ibtmp1 是現(xiàn)在的全局臨時(shí)表空間，存放的是對(duì)用戶(hù)創(chuàng)建的臨時(shí)表進(jìn)行更改的回滾段，在 5.7 中 ibtmp1 存放的是用戶(hù)創(chuàng)建的臨時(shí)表和磁盤(pán)內(nèi)部臨時(shí)表;
也就是在 8.0 和 5.7 中 ibtmp1 的用途發(fā)生了變化，5.7 版本臨時(shí)表的數(shù)據(jù)存放在 ibtmp1 中，在 8.0 版本中臨時(shí)表的數(shù)據(jù)存放在會(huì)話(huà)臨時(shí)表空間，如果臨時(shí)表發(fā)生更改，更改的 undo 數(shù)據(jù)存放在 ibtmp1 中;

實(shí)驗(yàn)驗(yàn)證：將之前的查詢(xún)結(jié)果保存成臨時(shí)表，對(duì)應(yīng)會(huì)話(huà)是 45 號(hào)，通過(guò)查看對(duì)應(yīng)字典表，可知 45 號(hào)會(huì)話(huà)使用了 temp_8.ibt 這個(gè)表空間，通過(guò)把查詢(xún)保存成臨時(shí)表，可以用到會(huì)話(huà)臨時(shí)表空間，如下圖：

下一步殺掉 45 號(hào)會(huì)話(huà)，發(fā)現(xiàn) temp_8.ibt 空間釋放了，變?yōu)榱顺跏即笮?，狀態(tài)為非活動(dòng)的，證明在 mysql8.0 中可以通過(guò)殺掉會(huì)話(huà)來(lái)釋放臨時(shí)表空間。

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

如果要釋放空間，需要重啟數(shù)據(jù)庫(kù);在 mysql8.0 中可以通過(guò)殺掉會(huì)話(huà)來(lái)釋放臨時(shí)表空間。

相關(guān)推薦