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.
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.
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
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.