# 表領域のサイズを拡張する
表領域にデータファイルを追加しサイズを拡張する
ALTER TABLESPACE tablespace_name ADD DATAFILE filespec SIZE nnm;
既存のデータファイルを追加のサイズを拡張
ALTER TABLESPACE tablespace_name DATAFILE filespec RESIZE nnm;
＊filespecは、データファイルの絶対パスを指定
#----------------------------------------------------------------------------------------------------------------
# 表領域の記憶領域パラメータを表示
SELECT tablespace_name, initial_extent "INITIAL", next_extent "NEXT",
min_extents, max_extents, pct_increase, status
FROM dba_tablespaces;
TABLESPACE_NAME INITIAL NEXT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS
--------------- ---------- ---------- ----------- ----------- ------------ ---------------------------
SYSTEM 65536 1 2147483645 ONLINE
UNDOTBS1 65536 1 2147483645 ONLINE
TEMP 1048576 1048576 1 0 ONLINE
INDX 65536 1 2147483645 ONLINE
USERS 65536 1 2147483645 ONLINE
logtable 65536 1 2147483645 ONLINE
XPRT 131072 131072 1 2147483645 0 ONLINE
# 表領域のサイズと使用量
select a.TABLESPACE_NAME
, min(a.BYTES)/1024/1024 "現サイズ(MB)"
, round(min(a.BYTES)/(1024*1024) - sum(b.BYTES)/ (1024*1024),2) "使用量(MB)"
, round((min(a.BYTES)/(1024*1024) - sum(b.BYTES)/(1024*1024))/ (min(a.BYTES)/1024/1024)*100,2) "使用率(%)"
, round(sum(b.BYTES)/(1024*1024),2) "空き容量(MB)"
from dba_data_files a, dba_free_space b
where a.FILE_ID = b.FILE_ID
group by a.TABLESPACE_NAME;
TABLESPACE_NAME 現サイズ(MB) 使用量(MB) 使用率(%) 空き容量(MB)
--------------- ------------ ---------- ---------- ------------
INDX 2046 .06 0 2045.94
SYSTEM 3070 283.13 9.22 2786.88
UNDOTBS1 2046 15.31 .75 2030.69
USERS 2046 1699.75 83.08 346.25
XPRT 400 358.63 89.66 41.38
logtable 8190 .06 0 8189.94
# 表領域のサイズ
SELECT tablespace_name, SUM(bytes)/(1024*1024) "SIZE[M]"
FROM dba_data_files GROUP BY tablespace_name;
TABLESPACE_NAME SIZE[M]
--------------- ----------
INDX 2046
SYSTEM 3070
UNDOTBS1 2046
USERS 2046
XPRT 450
logtable 8190
# 表領域の空きサイズ
SELECT tablespace_name, SUM(bytes)/(1024*1024) "Free[M]"
FROM dba_free_space GROUP BY tablespace_name;
TABLESPACE_NAME Free[M]
--------------- ----------
INDX 2045.9375
SYSTEM 2786.875
UNDOTBS1 2030.6875
USERS 346.25
XPRT 41.375
logtable 8189.9375
# データファイルのサイズ
col FILE_NAME for a50
SELECT b.tablespace_name, a.file_name, a.bytes/(1024*1024) "SIZE[M]",
a.autoextensible, a.increment_by/(1024*1024) "INC[M]",
a.maxbytes/(1024*1024) "MAXSIZE[M]"
FROM dba_data_files a, dba_tablespaces b
WHERE a.tablespace_name=b.tablespace_name
ORDER BY 1, 2;
TABLESPACE_NAME FILE_NAME SIZE[M] AUTOEXTEN INC[M] MAXSIZE[M]
--------------- -------------------------------------------------- ---------- --------- ---------- ----------
INDX /gdbu_s/hito/oracle/index/idx_hito_01.dbf 2046 NO 0 0
SYSTEM /gdbu_s/hito/oracle/data/sys_hito_01.dbf 3070 NO 0 0
UNDOTBS1 /gdbu_s/hito/oracle/undo/undo_hito_01.dbf 2046 NO 0 0
USERS /gdbu_s/hito/oracle/data/tbl1_hito_01.dbf 2046 NO 0 0
XPRT /gdbu_s/insight/hito/xprt01.dbf 400 YES .012207031 4000
XPRT /gdbu_s/insight/hito/xprt02.dbf 50 NO 0 0
logtable /gdbu_s/hito/oracle/data/logtab_01.dbf 8190 NO 0 0
#--------------------------------------------------------------------------------------------------------------
-- 単位はMByte
col tablespace_name for a25
col total for 999,999
col reak for 999,999
col creat for 999,999
col free for 999,999
col incremnt for 999,999
col extent for 999,999
col z_extent for 999,999
set lines 300
pro 'Mbyte unit'
pro+-- TABLESPACE_NAME ----+-- Max --+-- Real --+- Init -+- Free -+ Extent +- Now --+ Remain +
select a.tablespace_name
, (a.maxbytes/(1024*1024)) as total -- 最大割当容量
, (b.bytes/(1024*1024)) as real -- 実容量
, (b.create_bytes/(1024*1024)) as creat -- 初期容量
, c.free -- 残容量(次extentまでの)
, (a.increment_by * b.block_size)/(1024*1024) as incremnt -- EXTENT単位
, decode( (b.bytes/(1024*1024)) - (b.create_bytes/(1024*1024))
, 0, 0, ((b.bytes/(1024*1024)) - (b.create_bytes/(1024*1024))) / ((a.increment_by * b.block_size)/(1024*1024))
) as extent -- 既EXTENT回数
, decode( a.increment_by, 0, 0,
decode( (a.maxbytes/(1024*1024)) - (b.bytes/(1024*1024)), 0, 0
, round(((a.maxbytes/(1024*1024)) - (b.bytes/(1024*1024))) / decode(((a.increment_by * b.block_size)/(1024*1024))
, 0, 1, ((a.increment_by * b.block_size)/(1024*1024))) - 0.5, 0)
)
) as z_extent -- 残EXTENT回数
from dba_data_files a
, v$datafile b
, ( select file_id
, sum(bytes)/1024/1024 free
from dba_free_space
group by file_id
) c
where a.file_id = b.file#
and a.file_id = c.file_id
union
select tablespace_name
, (BYTES_USED + BYTES_FREE)/(1024*1024) as total
, BYTES_USED/(1024*1024) as real
, 0 as creat
, BYTES_FREE/(1024*1024) as free
, 0 as incremnt
, 0 as extent
, 0 as z_extent
from V$TEMP_SPACE_HEADER
TABLESPACE_NAME TOTAL REAL CREAT FREE INCREMNT EXTENT Z_EXTENT
------------------------- -------- ---------- -------- -------- -------- -------- --------
INDX 0 2046 2,046 2,046 0 0 0
SYSTEM 0 3070 3,070 2,787 0 0 0
TEMP 0 2046 2,046 0 0 0 0
UNDOTBS1 0 2046 2,046 2,009 0 0 0
USERS 0 2046 2,046 502 0 0 0
XPRT 0 50 50 5 0 0 0
XPRT 4,000 300 300 15 100 0 37
logtable 0 8190 8,190 8,190 0 0 0
8行が選択されました。