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

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

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.

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.

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.

Then it's a priv issue.

Login as sys and execute dbms_stats.

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

prove the other examples then

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.

https://metalink.oracle.com/metalink...,1,1,helvetica

Good afternoon all,

CREATE USER "gt-12" IDENTIFIED BY oracle;

EXEC dbms_stats.gather_schema_stats(ownname=>'"gt-12"');
PL/SQL block, executed in 0.172 sec.

EXEC dbms_stats.gather_schema_stats(ownname=>'gt-12');
ORA-20001: GT-12 is an invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 1524
ORA-06512: at "SYS.DBMS_STATS", line 1540
ORA-06512: at "SYS.DBMS_STATS", line 1569
ORA-06512: at "SYS.DBMS_STATS", line 13748
ORA-06512: at "SYS.DBMS_STATS", line 13719
ORA-06512: at line 2

Make sure your schemaname doesn't start with a number. Please post the error msg if you find anything else.

gtcol

Thaks for your email.
It seems that the name shoudn't have "-" or digit.
I exported the one that has problem and imported in another schema which has just simple name (TEST). It is OK now.

I am still surprize because I have this schema since 3 years ago and I haven't faced with problem before. I did so many export and import and because of so many test environemts always I distinguished them by for example schema+code (T1-AW23-7).
After applying the patch 8 to Oracle 9i I realized that when I import the sceham, the statistics don't update and the Last-Analyzed column in EM is empty. I checked the import log and saw the DBMS_Stat problem.
I can not find the reason why its worked before and it doesn't now.

Sorry, let me correct my last email.

There isn't in restriction in the name of database by oracle.
The name can be T1-TEST. ( I am not sure about starting name with digit).

and the script for statistics is
exec dbms_stats.gather_schema_stats(ownname=>'"T1-TEST"');
I checked that and is working fine.

My problem that may be I didn't describ it correctly is import schema doesn't update the statistics and the Last-Analyzed column in EM is empty after importing. and I should do the statistics manually to have it.

This problem is new for me, I experinced so many import and export and before applying the patch (I am not sure there is any correlation between them) the import doesn't update the statistics of schema tables.

That's the problem.