DBMS_Stats error message
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: DBMS_Stats error message

  1. #1
    Join Date
    Sep 2008
    Posts
    13

    DBMS_Stats error message

    Hi All,

    Recently I realized that when I run the DBMS_Stat in new schema , gives me error message, but is working fine in some other schema which I have.
    here is the spool of SQLplus:


    SQL> exec dbms_stats.gather_schema_stats(ownname=>'HR');

    PL/SQL procedure successfully completed.
    SQL> exec dbms_stats.gather_schema_stats(ownname=>'TEST-12');
    BEGIN dbms_stats.gather_schema_stats(ownname=>'TEST-12'); END;

    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "SYS.DBMS_ASSERT", line 163
    ORA-06512: at "SYS.DBMS_STATS", line 926
    ORA-06512: at "SYS.DBMS_STATS", line 947
    ORA-06512: at "SYS.DBMS_STATS", line 1005
    ORA-06512: at "SYS.DBMS_STATS", line 11236
    ORA-06512: at "SYS.DBMS_STATS", line 11214
    ORA-06512: at line 1


    SQL>


    If some one can help it would be appreciated.

    regards,
    Keb

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    TEST-12 is not a valid user account.


    SQL> create user TEST-12 identified by t8_pass;
    create user TEST-12 identified by t8_pass
    *
    ERROR at line 1:
    ORA-00922: missing or invalid option

  3. #3
    Join Date
    Sep 2008
    Posts
    13
    Test-12 is user name and schema name, the problem is not what you said, because:

    SQL> CREATE USER "TEST-12" PROFILE "DEFAULT"
    2 IDENTIFIED BY "123" DEFAULT TABLESPACE "USERS"
    3 ACCOUNT UNLOCK;

    User created.

    SQL>


    The problem is why DBMS_Stat has different behaviour for different schema.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    So every time you use "TEST-12", you should not create an account using ".

    Then try


    SQL> exec dbms_stats.gather_schema_stats(ownname=>'"TEST-12"');

    PL/SQL procedure successfully completed.

  5. #5
    Join Date
    Sep 2008
    Posts
    13
    I think we have misunderstanding.

    There is built in Schema in oracle in the name of HR and there is another shcmea which was created by me in the name of "TEST-12".
    when ever that I use the DBMS_Stat for that schema , I get the error message as above. the problem is not the name , foeget the name,
    if I create another schema in the name of XXX, still I have the same problem.
    DBMS_Stat is working fine for some schema and doesn't for the one that I mentioned before.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Then it's a priv issue.

    Login as sys and execute dbms_stats.

  7. #7
    Join Date
    Sep 2008
    Posts
    13

    Angry

    I ran this as sysdba.
    the problem is not privilege.

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    prove the other examples then

  9. #9
    Join Date
    Sep 2008
    Posts
    13
    Please read the first email carefully and if you know the reason, I would be glad to have your Idea.

    The question is clear, I am using Oracle 9i. I logined as SYSdBA
    and ran the DBMS_Stat for my shcema I got the error message.
    After verifing I found that it is working form some shcema but for
    the one that I mentioned.

    I exported the schema, removed the tablespace and created again,
    impoted the schema, but still DBMS_Stat gives me error message.

    that's it.

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

Posting Permissions

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



Click Here to Expand Forum to Full Width