Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
select a.table_name, b.bytes /1048576 as MB, sum(b.bytes/1048576) over () as Total
from user_tables a, user_segments b
where a.table_name = b.segment_name
order by a.table_name
reminds me of a technique I learned to handle my custom sql scripts while leaving the Oracle directories untouched..willif said:I leave this in my OracleBin
<right-click>SQL*Plus icon -> Properties -> "Start in:" D:\MyCode.
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 40
set heading on
col fname heading "Filename" format a60
col fnum heading "#" format 999
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 percentfree heading "Pct|Free" format 999
col autoext heading "Auto|Extend" format a6
break on report
compute sum of tb used free on 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
,(nvl(freebytes,0)/d.bytes)*100 percentfree
,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
Tablespace Total Pct Auto
Name # File Size Bytes Used Bytes Free Free Extend Filename
--------------- ---- ---------------- ---------------- ---------------- ---- ------ --------------------------------------------------
DATA1 4 104,857,600 43,581,440 61,276,160 58 Yes 'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA01.DBF'
RBS 2 73,400,320 27,394,048 46,006,272 63 Yes 'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTRBS01.DBF'
SYSTEM 1 142,606,336 135,118,848 7,487,488 5 Yes 'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTSYS01.DBF'
TEMP 3 10,485,760 8,192 10,477,568 100 Yes 'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTTEMP01.DBF'
---------------- ---------------- ----------------
sum 331,350,016 206,102,528 125,247,488
**************************************************************************************************************************************