oracle資料庫資料表空間擴容方法

標籤：

 

1. 先查詢資料表空間在物理磁碟上存放的位置,注意使用sysdba的帳號登陸。

SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name; 

 

2. 需要擴容的資料表空間是DW_STG_TBS,目前的檔案分配序號是DW_STG_TBS20.dbf，

    所以在接下來的要增加的檔案的名稱從21開始，我們一次行增加20個檔案，指令碼如下。

    其中設定的每個檔案初始分配空間為7g, autoextend on為自動成長大小，oracle單個檔案大小最大不超過32g.

--增加Stage層資料表空間alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS21.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS22.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS23.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS24.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS25.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS26.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS27.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS28.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS29.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS30.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS31.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS32.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS33.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS34.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS35.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS36.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS37.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS38.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS39.dbf‘ size 7167M autoextend on ;alter tablespace DW_STG_TBSadd datafile  ‘/u01/app/oracle/oradata/crm001/DW_STG_TBS40.dbf‘ size 7167M autoextend on ;

3. 將以上SQL在PL/SQL中執行，完成後查詢結果如下：

4. 使用本部落格中另外一篇文章[ORACLE資料庫儲存空間使用方式查詢]中的SQL語句查詢資料表空間大小

 

oracle資料庫資料表空間擴容方法