Hi,

How do I extract the information gathered from running this..

execute
dbms_ddl.analyze_object('TABLE','<OWNER>','TABLE_NAME','ESTIMATE');

Regards

Matt

Matt wrote:

> 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] Sent: 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