■表領域のサイズ変更
①データファイル追加
ALTER TABLESPACE SYSTEM ADD DATAFILE '/opt/oracle/oradata/isdb/system02.dbf'
SIZE 350M AUTOEXTEND ON;
②自動拡張のみ
ALTER DATABASE DATAFILE '/opt/oracle/oradata/isdb/system01.dbf' AUTOEXTEND
ON;
③データファイルの手動拡張
ALTER DATABASE DATAFILE '/opt/oracle/oradata/isdb/undotbs01.dbf' RESIZE
300M;
※実行前後で以下のSQLを実行して確認すること
---------------------------
set lines 32767
set pages 10000
set trim on
set trimspool on
column tablespace_name format a30
column file_name format a128
column %USED format 99.999999
column SIZE(KB) format 999,999,999
column USED(KB) format 999,999,999
column "NOT USE(KB)" format 999,999,999
SELECT U.FILE_ID, U.TABLESPACE_NAME,
NVL(U.FILE_NAME,U.TABLESPACE_NAME) FILE_NAME,
SUM(NVL(U.BYTES, 0)) "sz (BYTES)",
SUM(NVL(F.BYTES, 0)) "free (BYTES)",
SUM(NVL(U.BYTES, 0)-NVL(F.BYTES, 0)) "used (BYTES)",
NVL(U.STATUS,DECODE(SUM(DECODE(U.STATUS,'AVAILABLE',1,0)),COUNT(*),'ALL',0,'OFFLINE?','SOME'))
STATUS,
NVL(U.AUTOEXTENSIBLE,DECODE(SUM(DECODE(U.AUTOEXTENSIBLE,'YES',1,0)),COUNT(*),'ALL',0,'NO','SOME'))
AUTOEXTENSIBLE,
U.EXTENT_MANAGEMENT,
U.SEGMENT_SPACE_MANAGEMENT
FROM (SELECT D.FILE_NAME,
D.FILE_ID,
D.TABLESPACE_NAME,
T.EXTENT_MANAGEMENT,
T.SEGMENT_SPACE_MANAGEMENT,
D.STATUS,
D.AUTOEXTENSIBLE,
SUM(D.BYTES) BYTES,
SUM(D.MAXBYTES) MAXBYTES
FROM DBA_DATA_FILES D INNER JOIN DBA_TABLESPACES T
ON D.TABLESPACE_NAME = T.TABLESPACE_NAME
GROUP BY D.FILE_NAME, D.FILE_ID, D.TABLESPACE_NAME,
T.EXTENT_MANAGEMENT, T.SEGMENT_SPACE_MANAGEMENT, D.STATUS, D.AUTOEXTENSIBLE)
U
LEFT OUTER JOIN
(SELECT FILE_ID,
SUM(BYTES) BYTES
FROM DBA_FREE_SPACE
GROUP BY FILE_ID) F
ON U.FILE_ID = F.FILE_ID
GROUP BY GROUPING SETS(
(U.FILE_ID, U.TABLESPACE_NAME, U.FILE_NAME, U.STATUS,
U.AUTOEXTENSIBLE, U.EXTENT_MANAGEMENT, U.SEGMENT_SPACE_MANAGEMENT),
(U.TABLESPACE_NAME, U.EXTENT_MANAGEMENT, U.SEGMENT_SPACE_MANAGEMENT)
)
HAVING FILE_NAME IS NOT NULL or count(*) > 1
UNION ALL
SELECT U.FILE_ID, U.TABLESPACE_NAME,
NVL(U.FILE_NAME,U.TABLESPACE_NAME) FILE_NAME,
SUM(NVL(U.BYTES, 0)) "sz (BYTES)",
SUM(NVL(U.BYTES, 0)-NVL(F.BYTES, 0)) "free (BYTES)",
SUM(NVL(F.BYTES, 0)) "used (BYTES)",
NVL(U.STATUS,DECODE(SUM(DECODE(U.STATUS,'AVAILABLE',1,0)),COUNT(*),'ALL',0,'OFFLINE?','SOME'))
STATUS,
NVL(U.AUTOEXTENSIBLE,DECODE(SUM(DECODE(U.AUTOEXTENSIBLE,'YES',1,0)),COUNT(*),'ALL',0,'NO','SOME'))
AUTOEXTENSIBLE,
U.EXTENT_MANAGEMENT,
U.SEGMENT_SPACE_MANAGEMENT
FROM (SELECT D.FILE_NAME,
D.FILE_ID,
D.TABLESPACE_NAME,
T.EXTENT_MANAGEMENT,
T.SEGMENT_SPACE_MANAGEMENT,
D.STATUS,
D.AUTOEXTENSIBLE,
SUM(D.BYTES) BYTES,
SUM(D.MAXBYTES) MAXBYTES
FROM DBA_TEMP_FILES D INNER JOIN DBA_TABLESPACES T
ON D.TABLESPACE_NAME = T.TABLESPACE_NAME
GROUP BY D.FILE_NAME, D.FILE_ID, D.TABLESPACE_NAME,
T.EXTENT_MANAGEMENT, T.SEGMENT_SPACE_MANAGEMENT, D.STATUS, D.AUTOEXTENSIBLE)
U
LEFT OUTER JOIN
(SELECT FILE_ID,
SUM(BYTES_CACHED) BYTES
FROM V$TEMP_EXTENT_POOL
GROUP BY FILE_ID) F
ON U.FILE_ID = F.FILE_ID
GROUP BY GROUPING SETS(
(U.FILE_ID, U.TABLESPACE_NAME, U.FILE_NAME, U.STATUS,
U.AUTOEXTENSIBLE, U.EXTENT_MANAGEMENT, U.SEGMENT_SPACE_MANAGEMENT),
(U.TABLESPACE_NAME, U.EXTENT_MANAGEMENT, U.SEGMENT_SPACE_MANAGEMENT)
)
HAVING FILE_NAME IS NOT NULL or count(*) > 1
/
