Hi,

I have this script any ideas on how it can be adapted
for table_names ?

Regards

Matt#

COLUMN dummy NOPRINT
COLUMN pct_used FORMAT 999.9 HEADING "%|Used"
COLUMN name FORMAT a16 HEADING "Tablespace Name"
COLUMN Kbytes FORMAT 999,999,999 HEADING "KBytes"
COLUMN used FORMAT 999,999,999 HEADING "Used"
COLUMN free FORMAT 999,999,999 HEADING "Free"
COLUMN largest FORMAT 999,999,999 HEADING "Largest"
BREAK ON report
COMPUTE sum OF kbytes ON REPORT
COMPUTE sum OF free ON REPORT
COMPUTE sum OF used ON REPORT
SELECT
NVL(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name
, kbytes_alloc kbytes
, kbytes_alloc-NVL(kbytes_free,0) used
, NVL(kbytes_free,0) free
, ((kbytes_alloc-NVL(kbytes_free,0))/kbytes_alloc)*100 pct_used
, NVL(largest,0) largest
FROM ( SELECT SUM(bytes)/1024 Kbytes_free
, MAX(bytes)/1024 largest
, tablespace_name
FROM
-- sys.dba_free_space
sys.dba_segments
GROUP BY tablespace_name
) a
, ( SELECT SUM(bytes)/1024 Kbytes_alloc
, tablespace_name
FROM sys.dba_data_files
GROUP BY tablespace_name
) b
WHERE a.tablespace_name (+) = b.tablespace_name
/

-----Original Message-----
From: Matt [SMTP:[Email Address Removed] Thursday, August 30, 2001 11:00 AM
To: LazyDBA.com Discussion
Subject: Estimating table size and informing of table useage

Hi,

I am trying to estimate the space required for a table
without running into max extents problem.

I would like to see the results as
Table_Name, Rows, Used_K, Remaining_K, Precent_Unused_K

The Script below gets initial extent sizes for dd segments
and produces the calculation in K's but it does not
solve the problem.

Any ideas ?

select
s.segment_name,
s.bytes / 1024 || 'K' suggest1,
ceil(s.bytes / (10 * p.value)) * (p.value / 1024) || 'K' suggest2
from
sys.dba_segments s,
sys.v_$parameter p

Regards

Matt


>

Oracle LazyDBA home page