To Find Used and Free Space in Datafiles :
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;
SELECT CEIL((NVL(e.hwm, 1) * 8192)/1024/1024) as "Mb"
FROM dba_data_files f
LEFT
JOIN (SELECT file_id, max(block_id + blocks - 1) hwm FROM dba_extents GROUP BY file_id) e ON f.file_id = e.file_id
WHERE
f.file_name = '/a01/sysaux01.dbf';
SELECT file_name, tablespace_name , CEIL((NVL(e.hwm, 1) * 8192)/1024/1024) as "Mb"
FROM dba_data_files f
LEFT
JOIN (SELECT file_id, max(block_id + blocks - 1) hwm FROM dba_extents GROUP BY file_id) e ON f.file_id = e.file_id
WHERE
f.tablespace_name = 'SYSAUX';
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;
To reduce/resize the tablespace :
We can resize the datafile like below
ALTER DATABASE DATAFILE '/a01/sysaux01.dbf' RESIZE 1024M;
of course if you already used some space above 1024M then you will get an error
ORA-03297: file contains used data beyond requested RESIZE value
We can use the below querry to find the minimum/least value to reduce a datafile.
SQL >SELECT CEIL((NVL(e.hwm, 1) * 8192)/1024/1024) as "Mb"
FROM dba_data_files f
LEFT
JOIN (SELECT file_id, max(block_id + blocks - 1) hwm FROM dba_extents GROUP BY file_id) e ON f.file_id = e.file_id
WHERE
f.file_name = '/a01/sysaux01.dbf';
/
OR
SQL >SELECT file_name, tablespace_name , CEIL((NVL(e.hwm, 1) * 8192)/1024/1024) as "Mb"
FROM dba_data_files f
LEFT
JOIN (SELECT file_id, max(block_id + blocks - 1) hwm FROM dba_extents GROUP BY file_id) e ON f.file_id = e.file_id
WHERE
f.tablespace_name = 'SYSAUX';
/
No comments:
Post a Comment