Every once in a while one of my test jobs fails, throwing this error that the table-space its created in has no further space to allocate for this table. And then comes the next sets of steps. Find out the other table space which is free. Here is a very basic SQL scripts which I use to list down all the table-spaces and their size in human readable format.
Update: Replacing with Github Gist
Great Thank you so much for this script
ReplyDeleteThis will run quickly ;)
ReplyDeleteSELECT USED.TABLESPACE_NAME, USED.USED_BYTES AS "USED SPACE(IN MB)", FREE.FREE_BYTES AS "FREE SPACE(IN MB)"
FROM
(SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS USED_BYTES FROM USER_SEGMENTS GROUP BY TABLESPACE_NAME) USED
INNER JOIN
(SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS FREE_BYTES FROM USER_FREE_SPACE GROUP BY TABLESPACE_NAME) FREE
ON (USED.TABLESPACE_NAME = FREE.TABLESPACE_NAME);
And this one is Great..!!
ReplyDeletecol tablespace_name format A22 heading "Tablespace"
col objects format 999999 heading "Objects"
col files format 9999
col allocated_mb format 9,999,990.000 heading "Allocated Size|(Mb)"
col used_mb format 9,999,990.000 heading "Used Size|(Mb)"
col ts_type format A6 heading "TS|type"
col max_size_mb format 9,999,990.000 heading "Max Size|(Mb)"
col max_free_mb format 9,999,990.000 heading "Max Free|(Mb)"
col max_free_pct format 999 heading "Max Free|%"
BREAK ON REPORT
COMPUTE SUM LABEL "Total SUM:" OF objects files allocated_mb used_mb max_size_mb MAX_FREE_MB ON REPORT
COMPUTE AVG LABEL "Average %:" OF FREE_PCT MAX_FREE_PCT ON REPORT
SELECT ts.tablespace_name, status,
DECODE(dt.contents,'PERMANENT',DECODE(dt.extent_management,'LOCAL',DECODE(dt.allocation_type,'UNIFORM','LM-UNI','LM-SYS'),'DM'),'TEMPORARY','TEMP',dt.contents) ts_type,
NVL(s.count,0) objects,
ts.files,
ts.allocated/1024/1024 allocated_mb,
ROUND((ts.allocated-nvl(ts.free_size,0))/1024/1024,3) used_mb,
ROUND(maxbytes/1024/1024,3) max_size_mb,
ROUND((maxbytes-(ts.allocated-nvl(ts.free_size,0)))/1024/1024,3) max_free_mb,
ROUND((maxbytes-(ts.allocated-nvl(ts.free_size,0)))*100/maxbytes,2) max_free_pct
FROM
(
SELECT dfs.tablespace_name,files,allocated,free_size,maxbytes
FROM
(SELECT fs.tablespace_name, sum(fs.bytes) free_size
FROM dba_free_space fs
GROUP BY fs.tablespace_name)
dfs,
(SELECT df.tablespace_name, count(*) files, sum(df.bytes) allocated,
sum(DECODE(df.maxbytes,0,df.bytes,df.maxbytes)) maxbytes, max(autoextensible) autoextensible
FROM dba_data_files df
WHERE df.status = 'AVAILABLE'
GROUP BY df.tablespace_name)
ddf
WHERE dfs.tablespace_name = ddf.tablespace_name
UNION
SELECT dtf.tablespace_name,files,allocated,free_size,maxbytes
FROM
(SELECT tf.tablespace_name, count(*) files, sum(tf.bytes) allocated,
sum(DECODE(tf.maxbytes,0,tf.bytes,tf.maxbytes)) maxbytes, max(autoextensible) autoextensible
FROM dba_temp_files tf
GROUP BY tf.tablespace_name)
dtf,
(SELECT th.tablespace_name, SUM (th.bytes_free) free_size
FROM v$temp_space_header th
GROUP BY tablespace_name)
tsh
WHERE dtf.tablespace_name = tsh.tablespace_name
) ts,
( SELECT s.tablespace_name, count(*) count
FROM dba_segments s
GROUP BY s.tablespace_name) s,
dba_tablespaces dt,
v$parameter p
WHERE p.name = 'db_block_size'
AND ts.tablespace_name = dt.tablespace_name
AND ts.tablespace_name = s.tablespace_name (+)
ORDER BY 1
This comment has been removed by the author.
ReplyDelete