0

I am using Oracle database 21c Express edition in Windows 10 x64. I have table

  CREATE TABLE "BICHVAN"."EMPLOYEES" 
   (    "EMPLOYEE_ID" NUMBER(6,0), 
    "FIRST_NAME" VARCHAR2(20 BYTE), 
    "LAST_NAME" VARCHAR2(25 BYTE) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE, 
    "EMAIL" VARCHAR2(25 BYTE) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE, 
    "PHONE_NUMBER" VARCHAR2(20 BYTE), 
    "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE, 
    "JOB_ID" VARCHAR2(10 BYTE) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE, 
    "SALARY" NUMBER(8,2), 
    "COMMISSION_PCT" NUMBER(2,2), 
    "MANAGER_ID" NUMBER(6,0), 
    "DEPARTMENT_ID" NUMBER(4,0), 
     CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE, 
     CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 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, 
     CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
      REFERENCES "BICHVAN"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE, 
     CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")
      REFERENCES "BICHVAN"."JOBS" ("JOB_ID") ENABLE, 
     CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
      REFERENCES "BICHVAN"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 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 UNIQUE INDEX "BICHVAN"."EMP_EMP_ID_PK" ON "BICHVAN"."EMPLOYEES" ("EMPLOYEE_ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 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" ;
ALTER TABLE "BICHVAN"."EMPLOYEES" ADD CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
  USING INDEX "BICHVAN"."EMP_EMP_ID_PK"  ENABLE;

exec dbms_stats.gather_table_stats(ownname=> 'bichvan', tabname => 'employees');

I see only result

PL/SQL procedure successfully completed.

enter image description here What is the purpose and how to see result of dbms_stats.gather_table_stats?

I try query and check stat, but see nothing

select * from bichvan.employees;

SELECT NUM_ROWS, AVG_ROW_LEN, BLOCKS, 
       EMPTY_BLOCKS, LAST_ANALYZED
FROM   DBA_TAB_STATISTICS
WHERE  OWNER='bichvan'
AND    TABLE_NAME='employees';

result

All Rows Fetched: 0 in 0,15 seconds
Vy Do
  • 46,709
  • 59
  • 215
  • 313
  • 1
    Is this a serious question from a user with 41.8k reputations? "PL/SQL procedure successfully completed." is the return of every successful procedure execution in Oracle. For your second question see [GATHER_TABLE_STATS Procedure](https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_STATS.html#GUID-CA6A56B9-0540-45E9-B1D7-D78769B7714C) or if you like to get a deeper look: [Optimizer Statistics Concepts](https://docs.oracle.com/en/database/oracle/oracle-database/21/tgsql/optimizer-statistics-concepts.html#GUID-C0E74ACE-2706-48A1-97A2-33F52207166A) – Wernfried Domscheit Nov 23 '22 at 06:39
  • Please don't paste screenshots, use formatted text. See https://meta.stackoverflow.com/q/285551 – Wernfried Domscheit Nov 23 '22 at 06:43
  • 1
    By default objects in Oracle are upper case. Try `... WHERE OWNER='BICHVAN' AND TABLE_NAME='EMPLOYEES'` In Oracle it is rather uncommon to enclose object names by double quotes, you need to do that only if you have a **strong** requirement to keep the character lower/upper case. – Wernfried Domscheit Nov 23 '22 at 07:32
  • Maybe have a look at https://stackoverflow.com/questions/7425153/reason-why-oracle-is-case-sensitive and https://www.alberton.info/dbms_identifiers_and_case_sensitivity.html – Wernfried Domscheit Nov 23 '22 at 07:38
  • @WernfriedDomscheit good catch, must use UPPERCASE, then run query with `DBA_TAB_STATISTICS` returns result. – Vy Do Nov 23 '22 at 08:08

0 Answers0