sqloracle-databaseindexingsegments

SEGMENT_NAME = SYS_C004035569 what is it?

To check Index size I have executed this query :

   SELECT owner, segment_name,SEGMENT_SUBTYPE, bytes/1024/1024 mb
 FROM dba_segments
 WHERE owner = 'PROD2015'
 AND segment_type = 'INDEX' ORDER BY bytes desc;

So in results i have many indexes (segement_Name) that i don't know what they are :

SYS_C004035569,

SYS_C004035554

SYS_C004035579

BIN$CwhVTaY1SUCvWreTMryvQQ==$0

Can you please explain that to me ?

thanks & regards,

Solution

  • SEGMENT_NAME = SYS_C004035569 what is it?

    Those are system named objects when you do not explicitly create them but Oracle does it for you. It could be constraints, indexes etc.

    For example, if you create a NOT NULL constraint, you would see constraint name as SYS_C.

    For example,

    SQL> CREATE TABLE t(
  2  ID NUMBER NOT NULL
  3  );

Table created.

SQL>
SQL> SELECT constraint_name,
  2    constraint_type,
  3    table_name,
  4    search_condition
  5  FROM user_constraints
  6  WHERE table_name ='T';

CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION
--------------- --------------- ---------- ----------------
SYS_C0010726    C               T          "ID" IS NOT NULL

SQL>

    It is a CHECK constraint for the NOT NULL condition. If you see the CONSTRAINT_TYPE then you would see it as C. And the name as SYS_C you see is because it is system-generated name.

    BIN$CwhVTaY1SUCvWreTMryvQQ==$0

    These are objects which are in the recyclebin. If you purge the recyclebin, you won't see them.

    For example,

    SQL> SELECT OBJECT_NAME, ORIGINAL_NAME FROM recyclebin;

no rows selected

SQL> drop table t;

Table dropped.

SQL> SELECT OBJECT_NAME, ORIGINAL_NAME FROM recyclebin;

OBJECT_NAME                    ORIGINAL_NAME
------------------------------ ---------------
BIN$R7nbYt1KSgWqCT+OEXhaAQ==$0 T

SQL> purge recyclebin;

Recyclebin purged.

SQL> SELECT OBJECT_NAME, ORIGINAL_NAME FROM recyclebin;

no rows selected