Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to find the actual space being consumed by indexes on a table in oracle 10g . I do not intend to include the space reserved by oracle for future usage. (The overhead by oracle should not be considered.) I want the used bytes not the allocated bytes.

Can you help me in going ahead?

Also, is there a way to find the actual size of a long field in a table.

PS: vsize() and dbms_lob.getlength() do not work.

share|improve this question
    
Can you share why those two methods did not work for you? –  jcolebrand Feb 9 '11 at 15:50
    
You should ask your second question separately. –  Leigh Riffel Feb 9 '11 at 16:53
    
@Leigh good point. Let's see if the user realizes they've been migrated and goes through all the steps to get answered here. Crossing my fingers on their behalf. –  jcolebrand Feb 9 '11 at 16:57
add comment

migrated from stackoverflow.com Feb 9 '11 at 16:35

This question came from our site for professional and enthusiast programmers.

3 Answers

SELECT idx.index_name, SUM(bytes)
  FROM dba_segments seg,
       dba_indexes  idx
 WHERE idx.table_owner = <<owner of table>>
   AND idx.table_name  = <<name of table>>
   AND idx.owner       = seg.owner
   AND idx.index_name  = seg.segment_name
 GROUP BY idx.index_name

will show you the amount of space actually consumed by each index. I'm not clear if that's exactly what sorts of overhead you're trying to account for and how you are distinguishing "used" and "allocated" in the context of an index. If you want to account for free space in the index, you can use the DBMS_SPACE.SPACE_USAGE procedure to determine how many partially empty blocks are in the index.

share|improve this answer
1  
I believe the OP understands allocated to be what your query returns (and that doesn't {automatically} shrink after a delete <<name of table>>) as opposed to the the used size which would vary with the number of entries in the index. –  René Nyffenegger Feb 9 '11 at 21:55
    
This command is possible without accessing the DBA tables: SELECT idx.index_name, SUM(bytes) FROM user_segments seg, user_indexes idx WHERE idx.table_name = 'EMERGE_REPORTING_DETAIL' AND idx.index_name = seg.segment_name GROUP BY idx.index_name –  Richard Dingwall Jan 16 '13 at 11:40
    
@RichardDingwall - Assuming that you are logged in as the Oracle user that owns the index, that will work. If you are a DBA interested in how much space is being used, though, you'll generally be logging in as a user other than the one that owns the table. –  Justin Cave Jan 16 '13 at 15:18
add comment

To measure the (what I believe is your understanding of the) allocated and used size of the index, I'd probably use dbms_space

create or replace procedure tq84_index_size_proc 
as

  OBJECT_OWNER_in         varchar2(30) :=  user;
  OBJECT_NAME_in          varchar2(30) := 'TQ84_SIZE_IX';
  OBJECT_TYPE_in          varchar2(30) := 'INDEX';
  SAMPLE_CONTROL_in       number       :=  null;
  SPACE_USED_out          number;
  SPACE_ALLOCATED_out     number;
  CHAIN_PCENT_out         number;

  SUM_SEGMENT             number;

begin

  dbms_space.object_space_usage (
    OBJECT_OWNER           => OBJECT_OWNER_in        ,
    OBJECT_NAME            => OBJECT_NAME_in         ,
    OBJECT_TYPE            => OBJECT_TYPE_in         ,
    SAMPLE_CONTROL         => SAMPLE_CONTROL_in      ,
    SPACE_USED             => SPACE_USED_out         ,
    SPACE_ALLOCATED        => SPACE_ALLOCATED_out    ,
    CHAIN_PCENT            => CHAIN_PCENT_out
  );

  select sum(bytes) into SUM_SEGMENT 
    from user_segments
   where segment_name = OBJECT_NAME_in;


  dbms_output.put_line('Space Used:      ' || SPACE_USED_out);
  dbms_output.put_line('Space Allocated: ' || SPACE_ALLOCATED_out);
  dbms_output.put_line('Segment:         ' || SUM_SEGMENT);

end;
/

This procedure measures the allocated and used size of an index named *TQ84_SIZE_IX*. For completeness' sake, I have also added the count of bytes as reported by user_segments.

Now, this procedure can be seen in action:

create table tq84_size (
  col_1 varchar2(40),
  col_2 number
);

create index tq84_size_ix on tq84_size(col_1);

insert into tq84_size values ('*', 0);
commit;
exec tq84_index_size_proc;

With one entry in the index, the following figures are returned:

Space Used:      1078
Space Allocated: 65536
Segment:         65536

Filling up the index...

insert into tq84_size 
select substr(object_name || object_type, 1, 40),
       rownum
  from dba_objects,
       dba_types
 where rownum < 500000;
commit;

...and getting the figures again...

exec tq84_index_size_proc;

...reports:

Space Used:      25579796
Space Allocated: 32505856
Segment:         32505856

Then, if the index is "emptied":

delete from tq84_size;
commit;
exec tq84_index_size_proc;

it shows:

Space Used:      4052714
Space Allocated: 32505856
Segment:         32505856

which demonstrates that the allocated size doesn't shrink while the used size does.

share|improve this answer
add comment

In case someone comes here looking for a way to find the size of a long field, below is a way to do it. I will remove this answer if the question is separated.

Sample data...

CREATE TABLE TLONG 
(
  C1 Number(3),
  C2 LONG 
);

INSERT INTO TLONG VALUES (1,'abcd');
INSERT INTO TLONG VALUES (2,'abc');
INSERT INTO TLONG VALUES (3,'ab');
INSERT INTO TLONG VALUES (4,'1234567890');

Function to do the work... (For production this should be in a package)

CREATE OR REPLACE FUNCTION GetLongLength (pKey Number) RETURN Number Is
   vLong Long;
BEGIN
   SELECT C2 INTO vLong FROM TLONG WHERE C1 = pKey;
   Return Length(vLong);
END;
/

SHOW ERRORS;

Test the function...

SELECT rownum, GetLongLength(rownum) FROM dual CONNECT BY rownum<=4;

ROWNUM                 GETLONGLENGTH(ROWNUM)  
---------------------- ---------------------- 
1                      4                      
2                      3                      
3                      2                      
4                      10                   
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.