4 Replies Latest reply: Feb 14, 2014 1:19 AM by NightWing RSS

    analyze table works however dbms_stats doesn't work

      Hello All,

       

      I have very simple question, although analyze command have been deprecated many years ago, when I use analyze command I get result of empty_blocks (user_tables) however when I use dbms_stat package I get nothing.

       

      SQL> select * from v$version;

      BANNER
      ----------------------------------------------------------------
      Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
      PL/SQL Release 10.2.0.1.0 - Production
      CORE    10.2.0.1.0      Production
      TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
      NLSRTL Version 10.2.0.1.0 - Production

       

       

      SQL> create table deneme2 as select * from all_objects;

      table created


      SQL> exec dbms_stats.gather_table_stats(USER, 'DENEME2');

       

      PL/SQL procedure successfully completed.

       

      SQL> select blocks, empty_blocks from user_tables where table_name = 'DENEME2';

          BLOCKS EMPTY_BLOCKS
      ---------- ------------
              13            0

       

      SQL> analyze table deneme2 compute statistics;

      Table analyzed.

      SQL> select blocks, empty_blocks from user_tables where table_name = 'DENEME2';

          BLOCKS EMPTY_BLOCKS
      ---------- ------------
              13            3

       

       

      Whats wrong wtih dbms_stats? Did I miss somewhere? Do anyone has got any idea that why dbms_stat cannot work?

       

      Thanks in advance.