Re: Using space ?

From: Violin <violin.hsiao_at_mail.pouchen.com.tw>
Date: Sat, 30 Dec 2000 04:01:50 GMT
Message-ID: <3a4d5e11.15091129@172.16.7.5>

On Fri, 29 Dec 2000 14:12:15 +0100, "Alain AUDEBERT" <alain_at_merzhin.com> wrote:

I have a script :

set head     on
set echo     off

set verify off
set feedback off
set pagesize 1000

col "Table Size (bytes)"       format 99,999,999,999
col "Index Size (bytes)"       format 99,999,999,999
col "Nb extent"                format 99,999
col "Total Table Size (bytes)" format 99,999,999,999
col "Total Table extent"       format 99,999
col "Total Index Size (bytes)" format 99,999,999,999
col "Total Index extent"       format 99,999
col "Table"                    format A30
col "Index"                    format A20
col "Asso. table"              format A20
col "Tablespace"               format a15

col spool_file_name new_value FN
define FN = ""

SELECT 'C:\TEMP\' || USER || '_USERSPACE.TXT' spool_file_name FROM DUAL /

SPOOL &FN PROMPT -- Check TABLE and INDEX Extents Size

SELECT bytes "Table Size (bytes)",

       extents "Nb extent",
       segment_name "Table"

FROM user_segments
where segment_type='TABLE'
order by 1 desc ;

SELECT  seg.bytes         "Index Size (bytes)",
        seg.extents       "Nb extent",
        seg.segment_name  "Index",
        idx.table_name    "Asso. table"
FROM user_segments  seg,
     user_indexes   idx
where seg.segment_type = 'INDEX'
      and seg.segment_name = idx.index_name

order by 1 desc;

SELECT tablespace_name "Tablespace",
       sum(bytes)      "Total Table Size (bytes)",
       sum(extents)    "Total Table extent"

FROM user_segments
where segment_type='TABLE'
group by tablespace_name;

SELECT  seg.tablespace_name "Tablespace",
        sum(seg.bytes)      "Total Index Size (bytes)",
        sum(seg.extents)    "Total Index extent"
FROM user_segments  seg,
     user_indexes   idx
where seg.segment_type = 'INDEX'
      and seg.segment_name = idx.index_name

group by seg.tablespace_name;

SELECT sum(bytes) "Total Table Size (bytes)",

       sum(extents) "Total Table extent" FROM user_segments
where segment_type='TABLE';

SELECT sum(seg.bytes) "Total Index Size (bytes)",

        sum(seg.extents) "Total Index extent" FROM user_segments seg,

     user_indexes idx
where seg.segment_type = 'INDEX'

      and seg.segment_name = idx.index_name;

SPOOL OFF
>Hi all :)
>
>I search how to calculate the using space by schema and/or by tables ! :/
>
>If somebody having any idea ...
>
>Thanks
>Alain
>
Received on Fri Dec 29 2000 - 22:01:50 CST