——————————————————-
To identify the filenames for a particular tablespace
——————————————————-
Note: change the tablespace name accordingly.
select file_name, bytes/1024/1024 MBytes, autoextensible, (increment_by*4096)/1024 NKBytes, maxbytes/1024/1024 MAX_MBytes
from dba_data_files
where tablespace_name=upper(‘UNDOTBS’);

—————————————————–
To identify the filenames for a particular tablespace
—————————————————–
Note: It will prompt to enter the tablespace name.
set lines 100
break on report
compute sum of mbytes on report
col file_name format a35
select file_id, file_name, bytes/1024/1024 MBytes, autoextensible, maxbytes/1024/1024 MaxMBytes
from dba_data_files
where tablespace_name=upper(‘&Tbs_Name’);

—————————————————————
To identify which datafiles are autoextend mode in the instance
—————————————————————

set linesize 130
select tablespace_name, file_name, bytes/1024/1024 MBytes, (increment_by*8192)/1024 NKBytes, maxbytes/1024/1024 MAX_MBytes
from dba_data_files
where autoextensible=’YES’;

————————————————————————————————-
To identify the datafiles characteristics for a particular tablespace, if the blocks size is 4096
————————————————————————————————-

select file_name, bytes/1024/1024 MBytes, autoextensible, (increment_by*4096)/1024 NKBytes, maxbytes/1024/1024 MAX_MBytes
from dba_data_files
where tablespace_name=upper(‘&Tbs_Name’);

————————————————————————————————-
To identify the datafiles characteristics for a particular tablespace, if the blocks size is 8192
————————————————————————————————-
select file_name, bytes/1024/1024 MBytes, autoextensible, (increment_by*8192)/1024 NKBytes, maxbytes/1024/1024 MAX_MBytes
from dba_data_files
where tablespace_name=upper(‘&Tbs_Name’);

————————————————————————————————–
To identify the datafiles characteristics for a particular tablespace, if the blocks size is 16384
————————————————————————————————–
select file_name, bytes/1024/1024 MBytes, autoextensible, (increment_by*16384)/1024 NKBytes, maxbytes/1024/1024 MAX_MBytes
from dba_data_files
where tablespace_name=upper(‘&Tbs_Name’);

———————————————————–
To identify the total free space available in a tablespace
———————————————————-
select max(bytes)/1024/1024 MBytes
from dba_free_space
where tablespace_name=upper(‘&Tbs_Name’);
———————————————————————
To identify the free space available in a tablespace in each datafile
———————————————————————
break on report
compute sum of bytes on report
select file_id, sum(bytes/1024/1024) mBytes
from dba_free_space
where tablespace_name=upper(‘&Tbs_Name’)
group by file_id; —————————————————-
To identify the particular segment’s characteristics
—————————————————-
select owner, tablespace_name, segment_type, extents, initial_extent/1024 KB_Ini_Ext, next_extent/1024 KB_Nxt_Ext, pct_increase, max_extents
from dba_segments
where segment_name=upper(‘&seg_name’);
———————————————————-
To identify the extents for a particular table in a schema
———————————————————-
select extents, next_extent/1024 KB_Nxt_Ext, pct_increase, max_extents
from dba_segments
where owner=upper(‘&schma_owner’) and segment_type=upper(‘&seg_type’) and max_extents=2147483645;

Scripts for space usage.

Query to check databse size in MB.

———————————-

SET SERVEROUTPUT ON

declare

total_size_Mb number;

tfree_size_Mb number;

tused_size_Mb number;

begin

dbms_output.enable(100000);

select  Sum(bytes) into total_size_b  from dba_data_files;

select  Sum(bytes) into tfree_size_b   from  dba_free_space;

select  Sum(bytes) into tused_size_b  from  dba_segments;

dbms_output.put_line(‘Total:’ || TO_CHAR(Round(total_size_b/1048576, 2), ’999,999.00′) || ‘ MB’);

dbms_output.put_line(‘Free: ‘ || TO_CHAR(Round(tfree_size_b/1048576, 2), ’999,999.00′) || ‘ MB’);

dbms_output.put_line(‘Used: ‘ || TO_CHAR(Round(tused_size_b/1048576, 2), ’999,999.00′) || ‘ MB’);

end;

/

SET SERVEROUTPUT OFF

Query to check databse size in GB.

———————————-

SET SERVEROUTPUT ON

declare

total_size_Gb number;

tfree_size_Gb number;

tused_size_Gb number;

begin

dbms_output.enable(100000);

select  Sum(bytes) into total_size_b from dba_data_files;

select  Sum(bytes) into tfree_size_b  from  dba_free_space;

select Sum(bytes) into tused_size_b  from  dba_segments;

dbms_output.put_line(‘Total:’ || TO_CHAR(Round(total_size_b/1024/1024/1024, 2), ’999,999.00′) || ‘ GB’);

dbms_output.put_line(‘Free: ‘ || TO_CHAR(Round(tfree_size_b/1024/1024/1024, 2), ’999,999.00′) || ‘ GB’);

dbms_output.put_line(‘Used: ‘ || TO_CHAR(Round(tused_size_b/1024/1024/1024, 2), ’999,999.00′) || ‘ GB’);

end;

/

SET SERVEROUTPUT OFF