Re: How to determine used blocks in an extent.

From: Samuel ADENIJI <sam_at_shoestrings.demon.co.uk>
Date: 1997/12/01
Message-ID: <3483174F.802177FF@shoestrings.demon.co.uk>#1/1

There is an Oracle package in Orqacle 7.3 onwards called DBMS_SPACE.

I have an sql script called space.sql that runs the DDMS_SPACE package. This is set out below together with a sample of the result. This scripts shows the total space, total blocks, used and unused blocks.

space.sql

REM Script space: Determine space allocation within Each segment. (7.3)
REM USE THIS SCRIPT TO CHECK FOR UNUSED SPACE SET SERVEROUTPUT ON declare

        OP1 number;
        OP2 number;
        OP3 number;
        OP4 number;
        OP5 number;
        OP6 number;
        OP7 number;

begin
dbms_space.unused_space('&USERNAME','&TABLENAME','TABLE',

                          OP1,OP2,OP3,OP4,OP5,OP6,OP7);
   dbms_output.put_line('OBJECT_NAME       = TABLE');
   dbms_output.put_line('---------------------------');
   dbms_output.put_line('TOTAL_BLOCKS      = '||OP1);
   dbms_output.put_line('TOTAL_BYTES       = '||OP2);
   dbms_output.put_line('UNUSED_BLOCKS     = '||OP3);
   dbms_output.put_line('UNUSED_BYTES      = '||OP4);
   dbms_output.put_line('LAST_USED_EXTENT_FILE_ID  = '||OP5);

   dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||OP6);    dbms_output.put_line('LAST_USED_BLOCK = '||OP7); end;
/

SAMPLE OF SPACE.SQL AND ITS RESULT 16:26:06 SQL> @space
Enter value for username: SYSADM
Enter value for tablename: PSRELENT
old 10: dbms_space.unused_space('&USERNAME','&TABLENAME','TABLE', new 10: dbms_space.unused_space('SYSADM','PSRELENT','TABLE', OBJECT_NAME = TABLE

---

TOTAL_BLOCKS      = 3760
TOTAL_BYTES       = 7700480
UNUSED_BLOCKS     = 119
UNUSED_BYTES      = 243712
LAST_USED_EXTENT_FILE_ID  = 15

LAST_USED_EXTENT_BLOCK_ID = 6682
LAST_USED_BLOCK = 3641 PL/SQL procedure successfully completed.

Roy Varghese wrote:

> I have a table that has been allocated a very large initial
> extent. Something like 1.7GB. It is contained in a tablespace
> of 2 GB. Only an ANALYZE TABLE would show the actual number of
> blocks which are free or used-up in the extent. However the
> ANALYZE TABLE command takes quite a long time to complete
> since there are too many rows in the table.
>
> Can someone suggest a faster way of determining the blocks'
> usage within the extent?
>
> All answers appreciated.
Received on Mon Dec 01 1997 - 00:00:00 CST