4 Replies Latest reply: Aug 22, 2008 1:28 AM by 601585 RSS

    dbms_stats / ORA-01410


      i am executing following statement on our clustered database:

      dbms_stats.gather_schema_stats(ownname => 'MY_SCHEMA', estimate_percent => 5, degree => 16, granularity => 'ALL', cascade => true, method_opt => 'FOR ALL COLUMNS SIZE AUTO', options => 'GATHER STALE');

      the statement fails, returning an "ORA-01410: invalid ROWID" error.

      how can i find out which table contains the row with the invalid ROWID? it seems to be a SYS or SYSTEM table.

      thanks for any hint.

        • 1. Re: dbms_stats / ORA-01410
          Michael C
          Hi Tom, try this and see if it reveals anything.

               for r in( select table_name from user_tables ) loop
                    dbms_output.put_line( r.table_name );
                    dbms_stats.gather_table_stats( user, r.table_name, estimate_percent => 5,
                         degree => 16, granularity => 'ALL', cascade => true,
                         method_opt => 'FOR ALL COLUMNS SIZE AUTO' );
               end loop;

          • 2. Re: dbms_stats / ORA-01410
            Hi Michael,

            thanks for your hint. the problem is that it is on the production system and the schema contains a lot of tables.
            when i execute the dbms_stats.gather_SCHEMA_stats procedure, i am sure that only tables that have changed since the last analyze will be checked.
            how about the dbms_stats.gather_TABLE_stats? i think this will analyze every table, whether it has changed or not. right?

            • 3. Re: dbms_stats / ORA-01410
              Michael C
              you are correct. If you know how to determine stale stats you get a list of tables that way. If you aren't sure how to get the list then email me and I'll send you a script that will provide that information.

              • 4. Re: dbms_stats / ORA-01410
                The easiest way to monitor stale tables is looking into
                user/all_tab_modifications view which is stored by periodically
                or calling dbms_stats.flush_database_monitoring_info.

                Another way is to call dbms_stats.gather_schema_stats with "list stale" option.

                And for dbms_stats with 1410 error refer to metalink doc# 4430245, 4343493.

                Dion Cho