Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

how to find the available space in database

Status
Not open for further replies.

navink123

Programmer
Sep 2, 2003
21
US
I want to load data from dat files into a database. But before that I want to make sure that there is enough space in the DB to hold the data.
How do I find the space in the database.

Thanks,
Navin
 
select tablespace_name, sum(bytes)
from dba_free_space
group by tablespace_name;

will tell you how much free space is available in each tablespace. But be aware that this is not necessarily CONTIGUOUS free space, so you may or may not be able to use all of it for your data loads.
 
Navink,

As an addendum to Carp's script, I use the following script that shows (for each tablespace, by file) current file sizes, current consumption, available freespace, whether the file is set to AUTOEXTEND, and file name. Then there is a sub-total of numeric values for each tablespace:

set echo on
REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
set linesize 180
set pagesize 500
set heading on
col fname heading "Filename" format a60
col fnum heading "#" format 99
col ts heading "Tablespace|Name" format a15
col tb heading "Total|File Size" format 999,999,999,999
col used heading "Bytes Used" like tb
col free heading "Bytes Free" like tb
col autoext heading "Auto|Extend" format a6
break on ts skip 1 on report
compute sum of tb used free on ts report
spool TablespaceUsage.txt
select substr(tablespace_name,1,15) ts
,d.file_id fnum
,d.bytes tb
,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) used
,decode(d.bytes,0,0,nvl(freebytes,0)) free
,decode(e.file#,null,'No','Yes') autoext
,''''||substr(file_name,1,55)||'''' fname
from sys.dba_data_files d
,(select file_id,sum(bytes) freebytes
from sys.dba_free_space
group by file_id) f
,sys.filext$ e
,v$datafile v
where d.file_id=f.file_id(+)
and d.file_id=e.file#(+)
and v.file#=d.file_id
order by tablespace_name,creation_time
/
spool off
prompt
prompt Wrote spool file "TablespaceUsage.txt".
prompt
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top