Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2003
    Posts
    8

    Unhappy Unanswered: dbms_stats.gather_schema_stats stop on error

    Does anyone have a way to get around the 'stop on error' that occurs when running dbms_stats.gather_schema_stats? We would like the procedure to continue on to the next table if one table analyze ends in error.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    can you show your command line when you execute and the error as it shows?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Nov 2003
    Posts
    8
    SQL> SQL> SQL> SQL> BEGIN dbms_stats.gather_database_stats (null,FALSE,'FOR ALL
    COLUMNS SIZE 1',dbms_stats.default_degree,'DEFAULT',TRUE); END;

    *
    ERROR at line 1:
    ORA-12801: error signaled in parallel query server P001
    ORA-01652: unable to extend temp segment by 16 in tablespace GM_TEMP
    ORA-06512: at "SYS.DBMS_STATS", line 9357
    ORA-06512: at "SYS.DBMS_STATS", line 9839
    ORA-06512: at "SYS.DBMS_STATS", line 10023
    ORA-06512: at "SYS.DBMS_STATS", line 10116
    ORA-06512: at "SYS.DBMS_STATS", line 10096
    ORA-06512: at line 1

    We actually don't care about this large table that is failing the analyze, we just want the package to continue and gather statistics on the next table in the schema. Instead, it stops and the rest of the tables in the schema do not get analyzed.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    how about increasing your temporary tablespace size, OR, assign a different temporary tbspc for that event?

    OR, why are you gathering database stats? Why not just gather schema statistics for each schema you want to analyze?

    dbms_statistics.gather_schema_stats('schema_name')


    your first post mentions 'gather_schema_stats' but the second post mentions 'gather_database_stats'
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Nov 2003
    Posts
    8
    We don't have enough disk space to increase the size of temp or create another temp for this process. The database/schema thing was confusion on my part. If the table is in the schema we want to analyze, won't we have the same issue?

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    try it out and see and post the result.
    I have never had that problem using gather_schema_stats
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    HI,

    It is worth making sure the TEMP tablespace PCTINCREASE is 0 and that it has a sensible (large) storage clause to prevent fragmentation. For TEMPORARY temp tablespaces make sure both INITIAL and NEXT are set to large values as extent sizes are taken from the NEXT clause and not the INITIAL clause.



    Originally posted by peppermintp
    SQL> SQL> SQL> SQL> BEGIN dbms_stats.gather_database_stats (null,FALSE,'FOR ALL
    COLUMNS SIZE 1',dbms_stats.default_degree,'DEFAULT',TRUE); END;

    *
    ERROR at line 1:
    ORA-12801: error signaled in parallel query server P001
    ORA-01652: unable to extend temp segment by 16 in tablespace GM_TEMP
    ORA-06512: at "SYS.DBMS_STATS", line 9357
    ORA-06512: at "SYS.DBMS_STATS", line 9839
    ORA-06512: at "SYS.DBMS_STATS", line 10023
    ORA-06512: at "SYS.DBMS_STATS", line 10116
    ORA-06512: at "SYS.DBMS_STATS", line 10096
    ORA-06512: at line 1

    We actually don't care about this large table that is failing the analyze, we just want the package to continue and gather statistics on the next table in the schema. Instead, it stops and the rest of the tables in the schema do not get analyzed.
    SATHISH .

  8. #8
    Join Date
    Nov 2003
    Posts
    8
    I tried using gather_schema_stats and it ended with the same error:
    SQL> SQL> SQL> SQL> BEGIN dbms_stats.gather_schema_stats ('gms',null,FALSE,'FOR
    ALL COLUMNS SIZE 1',dbms_stats.default_degree,'DEFAULT',TRUE); END;

    *
    ERROR at line 1:
    ORA-12801: error signaled in parallel query server P000
    ORA-01652: unable to extend temp segment by 16 in tablespace GM_TEMP
    ORA-06512: at "SYS.DBMS_STATS", line 9357
    ORA-06512: at "SYS.DBMS_STATS", line 9839
    ORA-06512: at "SYS.DBMS_STATS", line 10023
    ORA-06512: at "SYS.DBMS_STATS", line 10077
    ORA-06512: at "SYS.DBMS_STATS", line 10054
    ORA-06512: at line 1

    I don't care about the stats for this large table, I just want it to continue on and gather stats on the rest of the objects.

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    how about just
    PHP Code:
    exec dbms_stats.gather_schema_stats ('gms'); 
    try that. forget all that other hooplah.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Nov 2003
    Posts
    8
    Now I'm getting 'snapshot too old' errors. I will try to run it at a less busy time and see how it goes.

  11. #11
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    Is it consistent or did it just happen the one time? Would check the disks where file resides and see if any OS errors reported.
    SATHISH .

  12. #12
    Join Date
    Nov 2003
    Posts
    8
    It is consistent. I will check for OS errors.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •