Data Files

Viewing data files – You need more options?  Please add a comment.

List of data files and sizes

col file_name for a70
set linesize 200
set pagesize 50
select FILE_NAME,BYTES/1024/1024/1024 "SizeGB", MAXBYTES/1024/1024/1024 "MaxSizeGB",AUTOEXTENSIBLE from dba_data_files;

List of data files and sizes – tablespace wise

col file_name for a70
set linesize 200
set pagesize 50
break on TABLESPACE_NAME 
compute sum of SizeGB on TABLESPACE_NAME
select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024/1024 "SizeGB", MAXBYTES/1024/1024/1024 "MaxSizeGB",AUTOEXTENSIBLE 
from dba_data_files
order by TABLESPACE_NAME;

List of data files and sizes – for a single tablespace

col file_name for a70
set linesize 200
set pagesize 50
break on TABLESPACE_NAME 
compute sum of SizeGB on TABLESPACE_NAME
select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024/1024 "SizeGB", MAXBYTES/1024/1024/1024 "MaxSizeGB",AUTOEXTENSIBLE 
from dba_data_files
where TABLESPACE_NAME='&TablesapceName';

Auto extensible file growth statistics

col file_name for a70
set linesize 200
set pagesize 50
break on TABLESPACE_NAME 
compute sum of SizeGB on TABLESPACE_NAME
compute sum of SizeToGrowGB on TABLESPACE_NAME
select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024/1024 "SizeGB", MAXBYTES/1024/1024/1024 "MaxSizeGB",AUTOEXTENSIBLE,
decode(AE,1,(MAXBYTES - BYTES)/1024/1024/1024,0) "SizeToGrowGB"
from dba_data_files df,
     ( select FILE_ID,
     (case  AUTOEXTENSIBLE 
     when 'YES' THEN 1 
     WHEN 'NO' THEN 0 
     END) ae
     from dba_data_files ) Adbs
where df.file_id = adbs.file_id
order by TABLESPACE_NAME;

Auto extensible growth for a single tablespace

col file_name for a70
set linesize 200
set pagesize 50
break on TABLESPACE_NAME 
compute sum of SizeGB on TABLESPACE_NAME
compute sum of SizeToGrowGB on TABLESPACE_NAME
select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024/1024 "SizeGB", MAXBYTES/1024/1024/1024 "MaxSizeGB",AUTOEXTENSIBLE,
decode(AE,1,(MAXBYTES - BYTES)/1024/1024/1024,0) "SizeToGrowGB"
from dba_data_files df,
     ( select FILE_ID,
     (case  AUTOEXTENSIBLE 
     when 'YES' THEN 1 
     WHEN 'NO' THEN 0 
     END) ae
     from dba_data_files ) Adbs
where df.file_id = adbs.file_id and df.TABLESPACE_NAME='&TablespaceName'
order by TABLESPACE_NAME;
Advertisements
%d bloggers like this: