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?
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)
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
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
DBA_SEGMENTS table.
– Pierre
Mar 9 '17 at 10:06
