2 Replies Latest reply: Apr 7, 2013 4:39 PM by rp0428 RSS

    How can keep my ALL_TABLES information upto date?

    942728
      Hello,

      I am running the query to get the table counts from sys table ALL_TABLES, I am not getting the latest counts. Meaning , if I insert a row into a table in one of the schema table, it supposed to updated ALL_TABLES to number of rows column. But it is not doing so. I really appreciate any help.

      Regards,
      VR

      rp0428      

      Posts: 8,768
      Registered: 03/05/99
           
           Re: How can keep my ALL_TABLES information upto date?
      Posted: Apr 7, 2013 12:39 PM in response to: user7690206 in response to: user7690206           
      Click to report abuse...           Click to reply to this thread      Reply
      WRONG FORUM!
      I am running the query to get the table counts from sys table ALL_TABLES, I am not getting the latest counts. Meaning , if I insert a row into a table in one of the schema table, it supposed to updated ALL_TABLES to number of rows column. But it is not doing so. I really appreciate any help.
      You'll get the best help if you post in the correct forum - please post the question in the Database General forum.
      General Database Discussions

      When you post provide your 4 digit Oracle version.

      The system views are only updated when statistics are collected; they are NOT updated when DML is performed on a table.

      Please mark this thread ANSWERED. If you need more help then repost the question in the proper forum.
      user7690206      

      Posts: 11
      Registered: 06/10/12
           
           Re: How can keep my ALL_TABLES information upto date?
      Posted: Apr 7, 2013 2:22 PM in response to: rp0428 in response to: rp0428           
      Click to edit this message...      Edit      Click to report abuse...           Click to reply to this thread      Reply
      Thanks and I really appreciate your reply. When will and who (which job) will ( only updated when statistics are collected) collect the statistics? (Once in when DB is created? or once in a day? or once in every 12 hours? or do we need to set something to do this?)

      The version of oracle is 11g release2 (I assume 10g release2 should have same)

      Anyways I will post this again in forum you mentioned above.

      Regards,
      VR

      Reference link:

      Re: How can keep my ALL_TABLES information upto date?
        • 1. Re: How can keep my ALL_TABLES information upto date?
          sybrand_b
          It appears your question has already been correctly answered, by rp0428. Those columns are updated only by collecting statistics, not automatically.
          Please close this thread and refrain from reposting this question.

          ----------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: How can keep my ALL_TABLES information upto date?
            rp0428
            >
            When will and who (which job) will ( only updated when statistics are collected) collect the statistics? (Once in when DB is created? or once in a day? or once in every 12 hours? or do we need to set something to do this?)

            The version of oracle is 11g release2 (I assume 10g release2 should have same)
            >
            You, or your DBA, needs to create a job to collect statistics periodically. How often you collect them, what tables/indexes you collect them on and the type of statistics you collect depend on your particular needs.

            Many organizations have a job that runs nightly and/or weekly to gather statistics to include that days activities.

            For batch processing a full set of stats if often generated just prior to large batch runs.