テーブルスペース（表領域）の変更

2023.04.26

テーブルスペースの変更をするには「ALTER TABLESPACE」を使います。
「ALTER DATABASE」を使う場合もあります。

Oracleへのログイン

テーブルスペース（表領域）を変更するには、OracleへSQL*Plusで接続して「SYS」ユーザーでログインします。

$ whoami
oracle
$ sqlplus sys/xxxxxxxx@PDB1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 19 09:38:50 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

テーブルスペースの拡張（データファイルの拡張）

既存のテーブルスペースの領域を拡張します。
「ALTER TABLESPACE」ではなく、「ALTER DATABASE」を使用します。

SQL> SET SQLCO ''
SQL> COL TABLESPACE_NAME FOR A15
SQL> COL FILE_NAME FOR A50
SQL> SELECT TABLESPACE_NAME, BYTES/1024/1024 AS "BYTES(MB)", FILE_NAME FROM DBA_DATA_FILES -
 WHERE TABLESPACE_NAME='USR_DATA1';

TABLESPACE_NAME  BYTES(MB) FILE_NAME
--------------- ---------- --------------------------------------------------
USR_DATA1              512 /u06/app/oracle/oradata/CDB1/PDB1/USR_DATA11.dbf

SQL> ALTER DATABASE DATAFILE '/u06/app/oracle/oradata/CDB1/PDB1/usr_data11.dbf' -
 RESIZE 1024M; ･･･ 増量分を指定するのではなく全量分を指定する

Database altered.

SQL> SELECT TABLESPACE_NAME, BYTES/1024/1024 AS "BYTES(MB)", FILE_NAME FROM DBA_DATA_FILES -
 WHERE TABLESPACE_NAME='USR_DATA1';

TABLESPACE_NAME  BYTES(MB) FILE_NAME
--------------- ---------- --------------------------------------------------
USR_DATA1             1024 /u06/app/oracle/oradata/CDB1/PDB1/usr_data11.dbf

テーブルスペースの縮小（データファイルの縮小）

テーブルスペースを縮小することは、個人的にはあまりお勧めしませんが、縮小することは可能です。

テーブルスペースを縮小するには、連続した縮小できる空きスペースを確認して、その容量分を縮小します。

SQL> COL NAME FOR A15
SQL> COL VALUE FOR A10
SQL> SELECT NAME, VALUE FROM V$PARAMETER2 WHERE NAME='db_block_size'; ･･･ ブロックサイズ確認

NAME            VALUE
--------------- ----------
db_block_size   8192

SQL> SELECT TABLESPACE_NAME, BYTES/1024/1024 AS "BYTES(MB)", FILE_NAME FROM DBA_DATA_FILES -
 WHERE TABLESPACE_NAME='USR_DATA1'; ･･･ データファイル名の確認

TABLESPACE_NAME  BYTES(MB) FILE_NAME
--------------- ---------- --------------------------------------------------
USR_DATA1             1024 /u06/app/oracle/oradata/CDB1/PDB1/usr_data11.dbf

SQL> COL NAME FOR A50
SQL> SELECT FILE#, NAME FROM V$DATAFILE;

     FILE# NAME
---------- --------------------------------------------------
         9 /u06/app/oracle/oradata/CDB1/PDB1/system01.dbf
        10 /u06/app/oracle/oradata/CDB1/PDB1/sysaux01.dbf
        11 /u06/app/oracle/oradata/CDB1/PDB1/undotbs01.dbf
        12 /u06/app/oracle/oradata/CDB1/PDB1/users01.dbf
        60 /u06/app/oracle/oradata/CDB1/PDB1/usr_data11.dbf

6 rows selected.

SQL> SET SQLCO ''
SQL> COL TABLESPACE_NAME FOR A15
SQL> SELECT TABLESPACE_NAME, FILE_ID, BLOCK_ID, BLOCKS FROM DBA_FREE_SPACE -
 WHERE TABLESPACE_NAME='USR_DATA1';

TABLESPACE_NAME    FILE_ID   BLOCK_ID     BLOCKS
--------------- ---------- ---------- ----------
USR_DATA1               60        128      65408 ･･･ (BLOCK_IDの値 + 8 - 1) x 8192 / 1024= 1080KB

SQL> ALTER DATABASE DATAFILE '/u06/app/oracle/oradata/CDB1/PDB1/usr_data11.dbf' -
 RESIZE 2M;

Database altered.

SQL> SELECT TABLESPACE_NAME, FILE_ID, BLOCK_ID, BLOCKS FROM DBA_FREE_SPACE -
 WHERE TABLESPACE_NAME='USR_DATA1';

TABLESPACE_NAME    FILE_ID   BLOCK_ID     BLOCKS
--------------- ---------- ---------- ----------
USR_DATA1               60        128        128

テーブルスペースの拡張（データファイルの追加）

既存のテーブルスペースにデータファイルを追加して領域を拡張する方法です。

SQL> SET SQLCO ''
SQL> COL TABLESPACE_NAME FOR A16
SQL> COL FILE_NAME FOR A50
SQL> SELECT TABLESPACE_NAME, BYTES/1024/1024 AS "BYTES(MB)", FILE_NAME FROM DBA_DATA_FILES -
 WHERE TABLESPACE_NAME='USR_DATA1';

TABLESPACE_NAME   BYTES(MB) FILE_NAME
---------------- ---------- --------------------------------------------------
USR_DATA1              1024 /u06/app/oracle/oradata/CDB1/PDB1/usr_data11.dbf

SQL> ALTER TABLESPACE USR_DATA1 -
 ADD DATAFILE '/u06/app/oracle/oradata/CDB1/PDB1/usr_data12.dbf' SIZE 512M;

Tablespace altered.

SQL> SELECT TABLESPACE_NAME, BYTES/1024/1024 AS "BYTES(MB)", FILE_NAME FROM DBA_DATA_FILES -
 WHERE TABLESPACE_NAME='USR_DATA1';

TABLESPACE_NAME   BYTES(MB) FILE_NAME
---------------- ---------- --------------------------------------------------
USR_DATA1              1024 /u06/app/oracle/oradata/CDB1/PDB1/usr_data11.dbf
USR_DATA1               512 /u06/app/oracle/oradata/CDB1/PDB1/usr_data12.dbf

SQL> SELECT TABLESPACE_NAME, FILE_ID, BLOCK_ID, BLOCKS FROM DBA_FREE_SPACE -
 WHERE TABLESPACE_NAME='USR_DATA1';

TABLESPACE_NAME     FILE_ID   BLOCK_ID     BLOCKS
---------------- ---------- ---------- ----------
USR_DATA1                60        128     130944
USR_DATA1                61        128      65408

SQL> SET LINE 32767
SQL> COL TABLESPACE_NAME FOR A16
SQL> COL AUTOEXTENSIBLE FOR A15
SQL> COL FILE_NAME FOR A50
SQL> SELECT -
 TABLESPACE_NAME, AUTOEXTENSIBLE, INCREMENT_BY, FILE_NAME -
FROM -
 DBA_DATA_FILES WHERE TABLESPACE_NAME='USR_DATA1' -
;

TABLESPACE_NAME  AUTOEXTENSIBLE  INCREMENT_BY FILE_NAME
---------------- --------------- ------------ --------------------------------------------------
USR_DATA1        YES                    16384 /u06/app/oracle/oradata/CDB1/PDB1/usr_data11.dbf
USR_DATA1        NO                         0 /u06/app/oracle/oradata/CDB1/PDB1/usr_data12.dbf

テーブルスペースの拡張（データファイルの追加（自動拡張あり））

既存のテーブルスペースにデータファイルを追加し、自動拡張の設定する方法です。

SQL> SET SQLCO ''
SQL> COL TABLESPACE_NAME FOR A16
SQL> COL FILE_NAME FOR A50
SQL> SELECT TABLESPACE_NAME, BYTES/1024/1024 AS "BYTES(MB)", FILE_NAME FROM DBA_DATA_FILES -
 WHERE TABLESPACE_NAME='USR_DATA1';

TABLESPACE_NAME   BYTES(MB) FILE_NAME
---------------- ---------- --------------------------------------------------
USR_DATA1              1024 /u06/app/oracle/oradata/CDB1/PDB1/usr_data11.dbf

SQL> ALTER TABLESPACE USR_DATA1 -
 ADD DATAFILE '/u06/app/oracle/oradata/CDB1/PDB1/usr_data12.dbf' SIZE 512M -
 AUTOEXTEND ON NEXT 128M MAXSIZE 1024M;

Tablespace altered.

SQL> SELECT TABLESPACE_NAME, BYTES/1024/1024 AS "BYTES(MB)", FILE_NAME FROM DBA_DATA_FILES -
 WHERE TABLESPACE_NAME='USR_DATA1';

TABLESPACE_NAME   BYTES(MB) FILE_NAME
---------------- ---------- --------------------------------------------------
USR_DATA1              1024 /u06/app/oracle/oradata/CDB1/PDB1/usr_data11.dbf
USR_DATA1               512 /u06/app/oracle/oradata/CDB1/PDB1/usr_data12.dbf

SQL> SELECT TABLESPACE_NAME, FILE_ID, BLOCK_ID, BLOCKS FROM DBA_FREE_SPACE -
 WHERE TABLESPACE_NAME='USR_DATA1';

TABLESPACE_NAME     FILE_ID   BLOCK_ID     BLOCKS
---------------- ---------- ---------- ----------
USR_DATA1                60        128     130944
USR_DATA1                61        128      65408

SQL> SET LINE 32767
SQL> COL TABLESPACE_NAME FOR A16
SQL> COL AUTOEXTENSIBLE FOR A15
SQL> COL FILE_NAME FOR A50
SQL> SELECT -
 TABLESPACE_NAME, AUTOEXTENSIBLE, INCREMENT_BY, FILE_NAME -
FROM -
 DBA_DATA_FILES WHERE TABLESPACE_NAME='USR_DATA1' -
;

TABLESPACE_NAME  AUTOEXTENSIBLE  INCREMENT_BY FILE_NAME
---------------- --------------- ------------ --------------------------------------------------
USR_DATA1        YES                    16384 /u06/app/oracle/oradata/CDB1/PDB1/usr_data11.dbf
USR_DATA1        YES                    16384 /u06/app/oracle/oradata/CDB1/PDB1/usr_data12.dbf

データファイルをリネームする

データファイル名を変更したい場合にする方法です。ここでは、追加したデータファイル名を間違えたと仮定して、「usr_data22.dbfをusr_data12.dbf」に変更します。

SQL> SET SQLCO ''
SQL> SET LINE 32767
SQL> COL TABLESPACE_NAME FOR A16
SQL> COL AUTOEXTENSIBLE FOR A15
SQL> COL FILE_NAME FOR A50
SQL> SELECT -
 TABLESPACE_NAME, AUTOEXTENSIBLE, INCREMENT_BY, FILE_NAME -
FROM -
  DBA_DATA_FILES WHERE TABLESPACE_NAME='USR_DATA1' -
;

TABLESPACE_NAME  AUTOEXTENSIBLE  INCREMENT_BY FILE_NAME
---------------- --------------- ------------ --------------------------------------------------
USR_DATA1        YES                    16384 /u06/app/oracle/oradata/CDB1/PDB1/usr_data11.dbf
USR_DATA1        YES                    16384 /u06/app/oracle/oradata/CDB1/PDB1/usr_data22.dbf

SQL> ALTER TABLESPACE USR_DATA1 OFFLINE;

Tablespace altered.

SQL> !cp '/u06/app/oracle/oradata/CDB1/PDB1/usr_data22.dbf' '/u06/app/oracle/oradata/CDB1/PDB1/usr_data12.dbf'

SQL> !ls '/u06/app/oracle/oradata/CDB1/PDB1/usr_data12.dbf'
/u06/app/oracle/oradata/CDB1/PDB1/usr_data12.dbf

SQL> ALTER TABLESPACE USR_DATA1 -
 RENAME DATAFILE '/u06/app/oracle/oradata/CDB1/PDB1/usr_data22.dbf' -
TO -
 '/u06/app/oracle/oradata/CDB1/PDB1/usr_data12.dbf' -
;

Tablespace altered.

SQL> ALTER TABLESPACE USR_DATA1 ONLINE;
ALTER TABLESPACE USR_DATA1 ONLINE
*
ERROR at line 1:
ORA-01113: file 64 needs media recovery
ORA-01110: data file 64: '/u06/app/oracle/oradata/CDB1/PDB1/usr_data12.dbf'

SQL> RECOVER DATAFILE '/u06/app/oracle/oradata/CDB1/PDB1/usr_data12.dbf'; ･･･「ORA-01113」が出た時の対応
Media recovery complete.
SQL> ALTER TABLESPACE USR_DATA1 ONLINE;

Tablespace altered.

SQL> SELECT -
 TABLESPACE_NAME, AUTOEXTENSIBLE, INCREMENT_BY, FILE_NAME -
FROM -
  DBA_DATA_FILES WHERE TABLESPACE_NAME='USR_DATA1' -
;

TABLESPACE_NAME  AUTOEXTENSIBLE  INCREMENT_BY FILE_NAME
---------------- --------------- ------------ --------------------------------------------------
USR_DATA1        YES                    16384 /u06/app/oracle/oradata/CDB1/PDB1/usr_data11.dbf
USR_DATA1        YES                    16384 /u06/app/oracle/oradata/CDB1/PDB1/usr_data12.dbf

SQL> !rm ’/u06/app/oracle/oradata/CDB1/PDB1/usr_data22.dbf' ･･･ いらなくなったので削除する
タイトルとURLをコピーしました