1

I want to know the space used by one of my indexes. I query the view DBA_SEGMENTS to do it:

SELECT segment_name, sum(bytes) FROM dba_segments WHERE segment_name LIKE '%INDEX_NAME%' GROUP BY segment_name;

I got this response:

DR$INDEX_NAME$X 1369440256
DR$INDEX_NAME$I 1586495488
DR$INDEX_NAME$R 65536

What is the meaning of the prefix DR and the suffixes X, I and R in the name of my segments? What is the real size of my index : 1369440256 B, 1586495488 B or 2956001280 B?

1

The Oracle Text index consists of four basic tables, referred to as the $I, $K, $N and $R tables respectively. Other tables are used when particular indexing options are turned on. The tables exist within the schema of the text index owner, and have names concatenated from DR$, the name of the index, and the suffix (e.g. $I)

Oracle Text Whitepaper

According to the whitepaper, these are tables used by Oracle Text.

Use the following query to get the index size. For example, I have an index called I_ICOL1.

SQL> select segment_name,bytes/1024/1024 MB from dba_segments where SEGMENT_NAME='I_ICOL1';

SEGMENT_NAME        MB
------------        ----------
I_ICOL1             .4375
  • So, I cannot group by name because I have multiple segments for one index. I should use: SELECT sum(bytes)/1024/1024/1024 GB FROM dba_segments WHERE segment_name LIKE '%INDEX_NAME%'; and I got 2.7 GB, right? – Pierre Mar 9 '17 at 9:49
  • What do you mean by multiple segments for one index? One logical object such as table or index has only one segment but can have multiple extents. docs.oracle.com/cd/B28359_01/server.111/b28318/… – JSapkota Mar 9 '17 at 9:54
  • As you said: "The Oracle Text index consists of four basic tables, referred to as the $I, $K, $N and $R tables respectively." That's why my index is referenced multiple times in the DBA_SEGMENTS table. – Pierre Mar 9 '17 at 10:06
  • @Pierre LIKE '%$INDEX_NAME$%' would be better. – Jack Douglas Mar 9 '17 at 10:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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