Re: How to find Space Used???

From: Calvin Crumrine <Calvin_Crumrine_at_dced.state.ak.us>
Date: Fri, 16 Mar 2001 08:34:37 -0900
Message-ID: <3AB24EAC.C21F8BEC@dced.state.ak.us>

First, define what you mean by total space used by my DB. That might be the total of all your datafiles, or it might be the total space used by all your tablespaces, or it might be the total space used by all your tables, or it might be just the space actually used by your data.

The below script isn't finished (I need to make the second part loop thru all the tables) but the first part shows the total space used by all your tablespaces & the total space used by all your tables (if you sum up the columns, which I haven't-I'm just interested in these figures on a per tablespace basis), and the second part shows the space actually used by your data. (At least it seems to-there's some confusion about the definition of 'used'. As soon as any data goes into a block do you consider that entire block used? Etc. This, at least, comes close to what I need.)

While I believe the totality of this is original with me, many of the original bits & pieces came from other sources. If I could track down what came from where I'd give everybody credit individually, but since I can't all I can do is not take all the credit for myself. Hope this helps.

set pagesize 0
set linesize 164
set echo off
clear scr
set heading off
prompt

---

prompt .                              FREE TABLESPACE VIEW

prompt
prompt This shows space remaining for expansion of the tables in each tablespace, plus the
prompt approximate level of fragmentation of each tablespace (implied by Largest Freespace
prompt and Number of Chunks).
prompt
prompt .

______________________(MB)_______________________
prompt .                    Total      Space          Total
Largest           No. of
prompt Tablespace           Space       Used      Freespace
Freespace           Chunks

prompt

---------------------------------------------------------------------------------------.

col tablespace format a16
col size format 99,999.9
col used_space format 99,999.9
col total_freespace format 99,999.9
col largest_freespace format 99,999.9
col Number_of_Chunks format 9,999
select F.TABLESPACE_NAME tablespace,

       F.BYTES /1000000 "size",
       (F.BYTES - sum(S.BYTES)) /1000000 used_space,
       sum(S.BYTES) /1000000 total_freespace,
       max(S.BYTES) /1000000 largest_freespace,
       count(*) Number_of_Chunks

from DBA_DATA_FILES F, DBA_FREE_SPACE S where F.TABLESPACE_NAME = S.TABLESPACE_NAME group by F.TABLESPACE_NAME, F.BYTES;
prompt

---

set serveroutput on
declare

   Total_Blocks              number;
   Total_Bytes               number;
   Unused_Blocks             number;
   Unused_Bytes              number;
   Last_Used_Extent_File_ID  number;
   Last_Used_Extent_Block_ID number;
   Last_Used_Block           number;
   Total_MB                  number;
   Unused_MB                 number;
   Used_MB                   number;
begin
   dbms_output.put_line('.                       UNUSED SPACE IN TABLES');

   dbms_output.put_line('.');
   dbms_output.put_line('This shows space allocated but unused for each table in TABLESPACE');

   dbms_output.put_line('tablespace. Tables expand automatically until the
free tablespace is gone.');
   dbms_output.put_line('.');
   dbms_output.put_line('.
___________________(MB)_________________');
   dbms_output.put_line('TABLE                      TOTAL SPACE

SPACE_USED UNUSED_SPACE');

dbms_output.put_line('-------------------------------------------------------------------');

   dbms_space.unused_space('SCHEMA','TABLENAME','TABLE',

Total_Blocks,Total_Bytes,Unused_Blocks,Unused_Bytes,

Last_Used_Extent_File_ID,Last_Used_Extent_Block_ID,Last_Used_Block);

   Total_MB := Total_Bytes /1000000;
   Unused_MB := Unused_Bytes /1000000;
   Used_MB := Total_MB - Unused_MB;
   dbms_output.put_line('TABLENAME
'||to_char(Total_MB,'99,990.000')

                                              ||'


'||to_char(Used_MB,'99,990.000')

                                             ||'

'||to_char(Unused_MB,'99,990.000'));

end;

Charles McDonald wrote:

> Hello there all
>
> I need to determine the total space used by my dB. Is there anyone out
> there who will share this know how with me please?
>
> Charles
 

Received on Fri Mar 16 2001 - 11:34:37 CST