Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Location
    US-AZ
    Posts
    9

    Unanswered: gather_schema_stats include indexes

    Receiving following error while trying to gather index stats via gather_schema_stats command:

    SQL> execute dbms_stats.gather_schema_stats('MP5PILOT','cascade =TRUE')
    BEGIN dbms_stats.gather_schema_stats('MP5PILOT','cascade =TRUE'); END;

    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at line 1

    Looking for proper syntax to collect ALL index stats - too many to do individually.
    Thanks for any help!!

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    the correct syntax is:

    exec dbms_stats.gather_schema_stats (ownname => 'MP5PILOT, cascade => TRUE);


    (I'm only telling you because I know that you've read the manuals & searched the internet & still couldn't find the answer - you did do all this first, didn't you?)
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Oct 2004
    Location
    US-AZ
    Posts
    9
    Thanks for the help!!

    And yes, I did check the docs and did the searching.
    I've just recently been moved to this DBA role and am still learning the nuances of the syntax, as you can attest to by the missing quote in your statement.

    Here were some of my failed attempts:

    SQL> execute dbms_stats.gather_schema_stats ('ownname=>MP5PILOT','cascade=>TRUE')
    BEGIN dbms_stats.gather_schema_stats ('ownname=>MP5PILOT','cascade=>TRUE'); END;

    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at line 1

    SQL> execute dbms_stats.gather_schema_stats('MP5PILOT','cascade =>TRUE')
    BEGIN dbms_stats.gather_schema_stats('MP5PILOT','cascade =>TRUE'); END;

    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at line 1

    SQL> execute dbms_stats.gather_schema_stats('MP5PILOT',cascade= >'TRUE')
    BEGIN dbms_stats.gather_schema_stats('MP5PILOT',cascade= >'TRUE'); END;

    *
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'GATHER_SCHEMA_STATS'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    Thanks again
    Last edited by irishmc; 01-07-05 at 10:03.

  4. #4
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    OK, so long as you can now do what you wanted.

    BTW Did you spot my 'deliberate' error? I missed a single quote off the end of the ownname parameter (it should read 'MP5PILOT'). Now where's that chalkboard? I must check my posts before submitting them, I must check my...
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

Posting Permissions

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