Code: Select all
/* Formatted on 1/25/2012 2:35:51 PM (QP5 v5.139.911.3011) */
set pagesize 200
col tablespace_name for a20 heading "Tablespace Name"
col file_name format a42 heading "Datafile"
col total_bytes format 999,999,999,999.9 heading "Total|Bytes|(MB)"
col bytes_free format 999,999,999.9 heading "Bytes|Free|(MB)"
col bytes_used format 999,999,999.9 heading "Bytes|Used|(MB)"
col percent_free format 9999 heading "% Free"
col percent_used format 9999 heading "% Used"
rem break on report
break on tablespace_name skip 1 on report
compute sum of total_bytes bytes_free bytes_used on tablespace_name
compute sum label "Grand Total" of total_bytes bytes_free bytes_used on report
-- compute avg of percent_free percent_used on tablespace_name
set linesize 160
TTitle left skip 2 "****** Tablespace Space Utilisation *******" skip 2
SELECT df.tablespace_name,
df.file_name,
df.bytes / 1024 / 1024 total_bytes,
NVL (SUM (fs.bytes / 1024 / 1024), 0) bytes_free,
NVL ( (100 * ( (SUM (fs.bytes)) / df.bytes)), 0) percent_free,
(df.bytes - NVL (SUM (fs.bytes), 0)) / 1024 / 1024 bytes_used,
(100 * ( (df.bytes - NVL (SUM (fs.bytes), 0)) / df.bytes))
percent_used
FROM sys.dba_data_files df, sys.dba_free_space fs
WHERE df.file_id = fs.file_id(+)
AND df.tablespace_name = fs.tablespace_name(+)
GROUP BY df.file_name, df.tablespace_name, df.bytes
UNION ALL
SELECT tf.tablespace_name,
tf.file_name,
tf.bytes / 1024 / 1024 total_bytes,
SUM (sh.bytes_free / 1024 / 1024) bytes_free,
(100 * ( (SUM (sh.bytes_used)) / tf.bytes)) percent_free,
(tf.bytes - SUM (sh.bytes_used)) / 1024 / 1024 bytes_used,
(100 * ( (tf.bytes - SUM (sh.bytes_used)) / tf.bytes)) percent_used
FROM sys.dba_temp_files tf, v$temp_space_header sh
WHERE tf.file_id = sh.file_id(+)
AND tf.tablespace_name = sh.tablespace_name(+)
GROUP BY tf.file_name, tf.tablespace_name, tf.bytes
ORDER BY 1, 2;
TTitle off
> Depending on your Database Storage Option, you can use the following approach:
A. If the instance is using Automatic Storage Management (ASM) , check the available disk using:
SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 "% Free" FROM v$asm_diskgroup;
B. If the instance is using File System option, then verify the filesystem where the datafile is stored using:
df -hk <Filesystem>
- For Permanent and Undo Datafile:
alter database datafile <'Datafile_Absolute_Path'> resize <size>M;
SQL> alter database datafile '/u01/oradata/data06.dbf' resize 4096M;
- For Temporary Files:
SQL> alter database tempfile <'Datafile_Absolute_Path'> resize <size>M;
SQL> alter database tempfile '/u01/oradata/temp03.dbf' resize 3100M;
If you can not resize datafile and ends up with ORA-03297 error then you should first query the DBA_EXTENTS view to see if the datafile can be shrunk. As an alternative, you can look at dba_free_space.