Oracle: Allocating waste space


 COLUMN file# format 99999;
 COLUMN file_name format a60;
 COLUMN a_mb format 99999;
 COLUMN u_mb format 99999;
 COLUMN next format 99999;
 COLUMN ideal format 99999;
 SELECT df.file_id file#,df.file_name,ROUND(df.BYTES/1024/1024,2) a_mb, ROUND(df.user_bytes/1024/1024,2) u_mb ,ROUND(t.next_extent/1024/1024,2) NEXT, ROUND((df.user_bytes
+ t.next_extent + 2097152)/1024/1024,2) ideal ,ROUND (df.maxbytes / 1024 / 1024, 2) max_mb FROM dba_data_files df, dba_tablespaces t WHERE (df.BYTES - df.user_bytes) > 3355443
2 AND df.tablespace_name = t.tablespace_name AND t.extent_management = 'LOCAL' AND t.allocation_type = 'UNIFORM' ORDER BY substr(df.file_name,2,5), df.file_id;

No comments:

Post a Comment