How to check datafile utilization in Oracle

When you want to shrink a data le, you must always check the single data le utilization. In case if you shrink data le more than the used size, it will fail. Below query gives the data le utilization and depending upon the datafile free space, you can shrink it

sql">col file_name for a60;
set pagesize 500;
set linesize 500;
SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb, ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;
Using aws-cli for Amazon EC2





Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.