What do oracle database create in the table columns 'SYS_###########'?

Question

While investigating for solution for a task , I was triggered by the following result of my simple query

SELECT DISTINCT
    table_name,
    column_name
FROM
    all_tab_cols tc
WHERE
    upper(table_name) LIKE 'M_INOUT' 
ORDER BY
    table_name

I wasn't expecting to see columns like this ones in the RS

M_INOUT ISRECTIFIED
M_INOUT ISARCHIVED
M_INOUT POREFERENCE_CH
M_INOUT MOTIF_RECTIF_CH
M_INOUT XX_ISDEJASERVI
M_INOUT SYS_STSJJX7PS36M_99#DUT8DC2UF$
M_INOUT SYS_STSCZWMRZ1DHJ_H1WE$YCB0Z0T
M_INOUT SYS_STSHMXP5645WEJZ2#6RW8Z__21
M_INOUT ISLIVFR
M_INOUT SYS_STS#FZ$$J4XV3JMZDV#H3ZJC6K

For my curiosity I tried to google it but I can't found information about it : honestly I googled oracle creates sys columns in table but no pertinent results

somebody please explain to me what are this columns and why they are added

here is the DDL of the table

  CREATE TABLE "COMPIERE"."M_INOUT" 
   (    "M_INOUT_ID" NUMBER(10,0) NOT NULL ENABLE, 
    "AD_CLIENT_ID" NUMBER(10,0) NOT NULL ENABLE, 
    "AD_ORG_ID" NUMBER(10,0) NOT NULL ENABLE, 
    "ISACTIVE" CHAR(1 BYTE) DEFAULT 'Y' NOT NULL ENABLE, 
    "CREATED" DATE DEFAULT SYSDATE NOT NULL ENABLE, 
    "CREATEDBY" NUMBER(10,0) DEFAULT 0 NOT NULL ENABLE, 
    "UPDATED" DATE DEFAULT SYSDATE NOT NULL ENABLE, 
    "UPDATEDBY" NUMBER(10,0) DEFAULT 0 NOT NULL ENABLE, 
    "ISSOTRX" CHAR(1 BYTE) DEFAULT NULL NOT NULL ENABLE, 
    "DOCUMENTNO" NVARCHAR2(30) NOT NULL ENABLE, 
    "DOCACTION" CHAR(2 BYTE) DEFAULT 'CO' NOT NULL ENABLE, 
    "DOCSTATUS" CHAR(2 BYTE) DEFAULT 'DR' NOT NULL ENABLE, 
    "POSTED" CHAR(1 BYTE) DEFAULT NULL NOT NULL ENABLE, 
    "PROCESSING" CHAR(1 BYTE) DEFAULT 'N', 
    "PROCESSED" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, 
    "C_DOCTYPE_ID" NUMBER(10,0) NOT NULL ENABLE, 
    "DESCRIPTION" NVARCHAR2(255), 
    "C_ORDER_ID" NUMBER(10,0), 
    "DATEORDERED" DATE, 
    "ISPRINTED" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, 
    "MOVEMENTTYPE" CHAR(2 BYTE) NOT NULL ENABLE, 
    "MOVEMENTDATE" DATE NOT NULL ENABLE, 
    "DATEACCT" DATE NOT NULL ENABLE, 
    "C_BPARTNER_ID" NUMBER(10,0) DEFAULT NULL NOT NULL ENABLE, 
    "C_BPARTNER_LOCATION_ID" NUMBER(10,0) NOT NULL ENABLE, 
    "M_WAREHOUSE_ID" NUMBER(10,0) DEFAULT NULL NOT NULL ENABLE, 
    "POREFERENCE" NVARCHAR2(20), 
    "DELIVERYRULE" CHAR(1 BYTE) DEFAULT 'A' NOT NULL ENABLE, 
    "FREIGHTCOSTRULE" CHAR(1 BYTE) DEFAULT 'I' NOT NULL ENABLE, 
    "FREIGHTAMT" NUMBER DEFAULT NULL, 
    "DELIVERYVIARULE" CHAR(1 BYTE) DEFAULT 'P' NOT NULL ENABLE, 
    "M_SHIPPER_ID" NUMBER(10,0), 
    "C_CHARGE_ID" NUMBER(10,0), 
    "CHARGEAMT" NUMBER DEFAULT NULL, 
    "PRIORITYRULE" CHAR(1 BYTE) DEFAULT '5' NOT NULL ENABLE, 
    "DATEPRINTED" DATE, 
    "C_INVOICE_ID" NUMBER(10,0), 
    "CREATEFROM" CHAR(1 BYTE), 
    "GENERATETO" CHAR(1 BYTE), 
    "SENDEMAIL" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, 
    "AD_USER_ID" NUMBER(10,0), 
    "SALESREP_ID" NUMBER(10,0), 
    "NOPACKAGES" NUMBER(10,0) DEFAULT NULL, 
    "PICKDATE" DATE, 
    "SHIPDATE" DATE, 
    "TRACKINGNO" NVARCHAR2(60), 
    "AD_ORGTRX_ID" NUMBER(10,0), 
    "C_PROJECT_ID" NUMBER(10,0), 
    "C_CAMPAIGN_ID" NUMBER(10,0), 
    "C_ACTIVITY_ID" NUMBER(10,0), 
    "USER1_ID" NUMBER(10,0), 
    "USER2_ID" NUMBER(10,0), 
    "DATERECEIVED" DATE, 
    "ISINTRANSIT" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, 
    "REF_INOUT_ID" NUMBER(10,0), 
    "CREATECONFIRM" CHAR(1 BYTE), 
    "CREATEPACKAGE" CHAR(1 BYTE), 
    "ISAPPROVED" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, 
    "ISINDISPUTE" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, 
    "Z_NATURETRANS" NVARCHAR2(60), 
    "Z_REGIMETRANS" NVARCHAR2(60), 
    "MATCHREQUIREMENTR" CHAR(1 BYTE), 
    "VOLUME" NUMBER DEFAULT 0 NOT NULL ENABLE, 
    "WEIGHT" NUMBER DEFAULT 0 NOT NULL ENABLE, 
    "ISRETURNTRX" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, 
    "XX_CONTROLEUR_ID" NUMBER(10,0) DEFAULT NULL, 
    "XX_PREPARATEUR_ID" NUMBER(10,0) DEFAULT NULL, 
    "ISLIVQR" CHAR(1 BYTE) DEFAULT 'N', 
    "XX_EMBALEUR_ID" NUMBER(10,0) DEFAULT NULL, 
    "BATCHDOCUMENTNO" NVARCHAR2(100), 
    "XX_SACHET" NUMBER(10,0) DEFAULT 0, 
    "XX_BAG" NUMBER(10,0) DEFAULT 0, 
    "ISPRINTAPPROVED" CHAR(1 BYTE) DEFAULT 'Y', 
    "XX_CONTROLEUR_CH_ID" NUMBER(10,0), 
    "XX_EMBALEUR_CH_ID" NUMBER(10,0), 
    "XX_PREPARATEUR_CH_ID" NUMBER(10,0), 
    "XX_CO_CH" NUMBER(10,0) DEFAULT 0, 
    "XX_CV_CH" NUMBER(10,0) DEFAULT 0, 
    "XX_SACHET_CH" NUMBER(10,0) DEFAULT 0, 
    "XX_BAC_CH" NUMBER(10,0) DEFAULT 0, 
    "ISRECTIFIED" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, 
    "ISARCHIVED" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, 
    "MOTIF_RECTIF" NVARCHAR2(255), 
    "ISRECTIFIEDAFTER" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, 
    "POREFERENCE_CH" NVARCHAR2(20), 
    "M_WAREHOUSE_CH_ID" NUMBER(10,0), 
    "SALESREP_CH_ID" NUMBER(10,0), 
    "DOCACTION_CH" CHAR(2 BYTE) DEFAULT 'CO' NOT NULL ENABLE, 
    "ISRECTIFIEDAFTER_CH" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, 
    "ISRECTIFIED_CH" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, 
    "ISARCHIVED_CH" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, 
    "MOTIF_RECTIF_CH" NVARCHAR2(255), 
    "XX_ISDEJASERVI" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, 
    "ETIQUETE" CHAR(2 BYTE) DEFAULT NULL, 
    "ISCONTROLORMODIFIED" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, 
    "ISPREPARATORMODIFIED" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, 
    "M_PICKTABLE_ID" NUMBER(10,0), 
    "PICKSTATUS" CHAR(2 BYTE) DEFAULT 'NP', 
    "XX_CONTROLEUR2_CH_ID" NUMBER(10,0), 
    "ISCONTROLOR2MODIFIED" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, 
    "MODIF_NON_DECLARER" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, 
    "GENERATEMOVEMENT" CHAR(1 BYTE), 
    "ISLIVFR" CHAR(1 BYTE) DEFAULT 'N', 
     CHECK (SendEMail IN ('Y','N')) ENABLE, 
     CHECK (IsActive in ('Y','N')) ENABLE, 
     CHECK (IsSOTrx in ('Y','N')) ENABLE, 
     CHECK (Processed in ('Y','N')) ENABLE, 
     CHECK (IsPrinted in ('Y','N')) ENABLE, 
     CHECK (IsControlor2Modified IN ('Y','N')) ENABLE, 
     CHECK (IsLivQR IN ('Y','N')) ENABLE, 
     CHECK (GenerateTo in ('Y','N')) ENABLE, 
     CHECK (IsPreparatorModified IN ('Y','N')) ENABLE, 
     CHECK (IsControlorModified IN ('Y','N')) ENABLE, 
     CHECK (XX_IsDejaServi IN ('Y','N')) ENABLE, 
     CHECK (IsArchived_CH IN ('Y','N')) ENABLE, 
     CHECK (IsRectified_CH IN ('Y','N')) ENABLE, 
     CHECK (IsRectifiedAfter_CH IN ('Y','N')) ENABLE, 
     CHECK (IsRectifiedAfter IN ('Y','N')) ENABLE, 
     CHECK (IsArchived IN ('Y','N')) ENABLE, 
     CHECK (IsRectified IN ('Y','N')) ENABLE, 
     UNIQUE ("DOCUMENTNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 4194304 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE, 
     CHECK (IsPrintApproved IN ('Y','N')) ENABLE, 
     CHECK (CreateFrom in ('Y','N')) ENABLE, 
     CONSTRAINT "M_INOUT_KEY" PRIMARY KEY ("M_INOUT_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE, 
     CHECK (IsApproved IN ('Y','N')) ENABLE, 
     CHECK (IsInDispute IN ('Y','N')) ENABLE, 
     CHECK (IsInTransit IN ('Y','N')) ENABLE, 
     CHECK (IsReturnTrx IN ('Y','N')) ENABLE, 
     CHECK (Modif_non_declarer IN ('Y','N')) ENABLE, 
     CHECK (IsLivFR IN ('Y','N')) ENABLE, 
     CONSTRAINT "FK319_1021915" FOREIGN KEY ("XX_CONTROLEUR2_CH_ID")
      REFERENCES "COMPIERE"."C_BPARTNER" ("C_BPARTNER_ID") ENABLE, 
     CONSTRAINT "FK319_3804" FOREIGN KEY ("M_SHIPPER_ID")
      REFERENCES "COMPIERE"."M_SHIPPER" ("M_SHIPPER_ID") ENABLE, 
     CONSTRAINT "FK319_3798" FOREIGN KEY ("M_WAREHOUSE_ID")
      REFERENCES "COMPIERE"."M_WAREHOUSE" ("M_WAREHOUSE_ID") ENABLE, 
     CONSTRAINT "FK319_5402" FOREIGN KEY ("C_INVOICE_ID")
      REFERENCES "COMPIERE"."C_INVOICE" ("C_INVOICE_ID") ENABLE, 
     CONSTRAINT "FK319_3809" FOREIGN KEY ("C_ORDER_ID")
      REFERENCES "COMPIERE"."C_ORDER" ("C_ORDER_ID") ENABLE, 
     CONSTRAINT "FK319_1016823" FOREIGN KEY ("XX_CONTROLEUR_CH_ID")
      REFERENCES "COMPIERE"."C_BPARTNER" ("C_BPARTNER_ID") ENABLE, 
     CONSTRAINT "FK319_1016824" FOREIGN KEY ("XX_EMBALEUR_CH_ID")
      REFERENCES "COMPIERE"."C_BPARTNER" ("C_BPARTNER_ID") ENABLE, 
     CONSTRAINT "FK319_1016825" FOREIGN KEY ("XX_PREPARATEUR_CH_ID")
      REFERENCES "COMPIERE"."C_BPARTNER" ("C_BPARTNER_ID") ENABLE, 
     CONSTRAINT "FK319_9585" FOREIGN KEY ("C_PROJECT_ID")
      REFERENCES "COMPIERE"."C_PROJECT" ("C_PROJECT_ID") ENABLE, 
     CONSTRAINT "FK319_9586" FOREIGN KEY ("AD_ORGTRX_ID")
      REFERENCES "COMPIERE"."AD_ORG" ("AD_ORG_ID") ENABLE, 
     CONSTRAINT "FK319_3523" FOREIGN KEY ("AD_ORG_ID")
      REFERENCES "COMPIERE"."AD_ORG" ("AD_ORG_ID") ENABLE, 
     CONSTRAINT "FK319_3522" FOREIGN KEY ("AD_CLIENT_ID")
      REFERENCES "COMPIERE"."AD_CLIENT" ("AD_CLIENT_ID") ENABLE, 
     CONSTRAINT "FK319_8771" FOREIGN KEY ("SALESREP_ID")
      REFERENCES "COMPIERE"."AD_USER" ("AD_USER_ID") ENABLE, 
     CONSTRAINT "FK319_1017387" FOREIGN KEY ("SALESREP_CH_ID")
      REFERENCES "COMPIERE"."AD_USER" ("AD_USER_ID") ENABLE, 
     CONSTRAINT "FK319_3797" FOREIGN KEY ("AD_USER_ID")
      REFERENCES "COMPIERE"."AD_USER" ("AD_USER_ID") ENABLE, 
     CONSTRAINT "FK319_9583" FOREIGN KEY ("C_ACTIVITY_ID")
      REFERENCES "COMPIERE"."C_ACTIVITY" ("C_ACTIVITY_ID") ENABLE, 
     CONSTRAINT "FK319_3795" FOREIGN KEY ("C_BPARTNER_ID")
      REFERENCES "COMPIERE"."C_BPARTNER" ("C_BPARTNER_ID") ENABLE, 
     CONSTRAINT "FK319_3796" FOREIGN KEY ("C_BPARTNER_LOCATION_ID")
      REFERENCES "COMPIERE"."C_BPARTNER_LOCATION" ("C_BPARTNER_LOCATION_ID") ENABLE, 
     CONSTRAINT "FK319_9584" FOREIGN KEY ("C_CAMPAIGN_ID")
      REFERENCES "COMPIERE"."C_CAMPAIGN" ("C_CAMPAIGN_ID") ENABLE, 
     CONSTRAINT "FK319_3805" FOREIGN KEY ("C_CHARGE_ID")
      REFERENCES "COMPIERE"."C_CHARGE" ("C_CHARGE_ID") ENABLE, 
     CONSTRAINT "FK319_3792" FOREIGN KEY ("C_DOCTYPE_ID")
      REFERENCES "COMPIERE"."C_DOCTYPE" ("C_DOCTYPE_ID") ENABLE, 
     CONSTRAINT "FK319_9582" FOREIGN KEY ("USER1_ID")
      REFERENCES "COMPIERE"."C_ELEMENTVALUE" ("C_ELEMENTVALUE_ID") ENABLE, 
     CONSTRAINT "FK319_9581" FOREIGN KEY ("USER2_ID")
      REFERENCES "COMPIERE"."C_ELEMENTVALUE" ("C_ELEMENTVALUE_ID") ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 14680064 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

   COMMENT ON TABLE "COMPIERE"."M_INOUT"  IS 'Shipment & Customer Returns, Receipts & Vendor Returns';

  CREATE INDEX "COMPIERE"."M_INOUT_BPARTNER" ON "COMPIERE"."M_INOUT" ("C_BPARTNER_ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

  CREATE INDEX "COMPIERE"."M_INOUT_ORDER" ON "COMPIERE"."M_INOUT" ("C_ORDER_ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

If you don't want to see those system generated columns, use `ALL_TAB_COLUMNS` instead. They are typically created for e.g. expression based indexes (btw: the `distinct` in your query is useless) — , Oct 05 '20 at 09:16
See this [SO answer thread](https://stackoverflow.com/a/26790485/13878023) — Sujitmohanty30, Oct 05 '20 at 09:20
@a_horse_with_no_name the `distinct` is not *useless*, but in case that there are more tables in different schemas, the columns are shown only once. DISTINCT would be useless on `user_tab_cols` — Marmite Bomber, Oct 05 '20 at 09:21
SYS_STS_XXXX is a mask for virtual columns created for the extended statistics — Sayan Malakshinov, Oct 05 '20 at 09:36
@MarmiteBomber is right knowing I have more than 50 schema. thanks youallfor enlightning me — Mohammed Housseyn Taleb, Oct 05 '20 at 10:03
ALL_TAB_COLS selects from all schemas your user has access to. Perhaps if you included OWNER in the query you would see that your results come from various M_INOUT tables in different schemas. Although in this particular case it seems @SayanMalakshinov has the answer. — APC, Oct 05 '20 at 14:46
for the downvote the question is not about the query. there is no questionlike this in SOF, WHY ? I wan't to understand the nature of returned data, the syscolumns nature is what I m asking about, Of course there is lot of things on it that was mentioned bythe good poeple in comments that I thanks a lot. but really I can't see why is downvoted. I dont care about getting rid of data, I wanted to undestand IT so getting rid of it makes sense later — Mohammed Housseyn Taleb, Oct 05 '20 at 15:47
`HIDDEN_COLUMN VARCHAR2(3) Indicates whether the column is a hidden column (YES) or not (NO) VIRTUAL_COLUMN VARCHAR2(3) Indicates whether the column is a virtual column (YES) or not (NO)` — ibre5041, Oct 05 '20 at 16:56

What do oracle database create in the table columns 'SYS_###########'?

0 Answers0