Re: SQL for determine free space in tablespaces / files

From: <info_at_revealnet.com>
Date: 1998/04/03
Message-ID: <6g2ohp$mr9$1_at_nnrp1.dejanews.com>#1/1

In article <35221540.0_at_d2o19.telia.com>,   "Johan Nilsson"
<jni_at_esrange.ssc.se.---> wrote:
>
> Hi,
>
> I'd be grateful if anyone could
 help me out on how to query the database for
> information like :
>
> *

 Tablespaces in database
> * Datafiles in tablespaces
> * Tables in
 tablespaces
> * Max size of tablespace / current usage total / usage per
 table
> * Max size of datafiles / current usage
>
> Platform is Oracle
 Workgroup Server 7.3 on Win NT 4.0. SQL samples are
> appreciated, or any

 hints on where to find them.
>
> T.I.A. // Johan
>

Johan, here's a
script for reporting free space from the "Oracle Administration Knowledge Base" http://www.revealnet.com

rem*************** RevealNet Oracle

Administration ***********************
rem
rem	File: db_fspc.sql

rem
rem
This is a part of the RevealNet Oracle Administration library. rem Copyright
(C) 1996-97 RevealNet, Inc.
rem All rights reserved.
rem
rem For more
information, call RevealNet at 1-800-REVEAL4 rem or check out our Web page:
www.revealnet.com
rem
rem FUNCTION: Provide data on tablespace extent status rem this report uses the free_space view rem
rem Modifications (Date, Who,
Description)
rem
rem
SET FEED OFF
SET FLUSH OFF
SET VERIFY OFF
set pages 58
LINES 130
column tablespace heading Name format a30 column file_id heading
File# format 99999
column pieces heading Frag format 9999 column
free_bytes heading 'Free Byte'
column free_blocks heading 'Free Blk'
column
largest_bytes heading 'Biggest Bytes'
column largest_blks heading 'Biggest
Blks'
column ratio heading 'Percent' format 999.999 start title132 "FREE
SPACE REPORT"
define 1 = 'rep_out\&db\fre_spc'
spool &1
select
tablespace,count(*) files,sum(pieces) pieces,sum(free_bytes) free_bytes, sum(free_blocks) free_blocks,sum(largest_bytes) largest_bytes, sum(largest_blks) largest_blks, sum(largest_bytes)/sum(free_bytes)*100 ratio from free_space
group by tablespace;
spool off
clear columns
pause Press
Enter to Continue
SET FEED ON
SET FLUSH ON
SET VERIFY ON
set pages 22 LINES
80
clear columns
ttitle off

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Fri Apr 03 1998 - 00:00:00 CEST