OraFAQ Forum: Server Administration » Not able to gather Statistics of neither schema nor table

Home » RDBMS Server » Server Administration » Not able to gather Statistics of neither schema nor table (Oracle 10g 10.2.0.1.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Not able to gather Statistics of neither schema nor table [message #515745]

Wed, 13 July 2011 03:07

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

Hi All,

I am connect to remote database with a user named 'TEST', this user has dba privileges. I am not able to gather the statistics of neither test schema nor for any table that exists in this schema.

Please find below the screenshot for the errors:

SQL> EXEC dbms_stats.gather_schema_stats('TEST', cascade=>TRUE);
BEGIN dbms_stats.gather_schema_stats('TEST', cascade=>TRUE); END;

*
ERROR at line 1:
ORA-44004: invalid qualified SQL name
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13634
ORA-06512: at "SYS.DBMS_STATS", line 13593
ORA-06512: at line 1

SQL> EXEC dbms_stats.gather_TABLE_stats(OWNNAME=>'TEST',TABNAME=>'BUG341');
BEGIN dbms_stats.gather_TABLE_stats(OWNNAME=>'TEST',TABNAME=>'BUG341'); END;

*
ERROR at line 1:
ORA-44004: invalid qualified SQL name
ORA-06512: at "SYS.DBMS_STATS", line 13056
ORA-06512: at "SYS.DBMS_STATS", line 13076
ORA-06512: at line 1

Any reason why I am not able to do so and getting these errors.

Thanks & Regards
Deepak

Report message to a moderator

Re: Not able to gather Statistics of neither schema nor table [message #515755 is a reply to message #515745]

Wed, 13 July 2011 03:18

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

show user

Use SQL*Plus and copy and paste your session, the WHOLE session, statring with the connection.

Regards
Michel

Report message to a moderator

Re: Not able to gather Statistics of neither schema nor table [message #515796 is a reply to message #515755]

Wed, 13 July 2011 05:06

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

below is the result for "show user" and session details.


SQL> /

SADDR                   SID    SERIAL#     AUDSID PADDR                 USER# USERNAME                  COMMAND    OWNERID TADDR            LOCKWAIT         STATUS
---------------- ---------- ---------- ---------- ---------------- ---------- ------------------------------ ---------- ---------- ---------------- ----------------
00000000A13B78A8        305       7805     130620 000000009E29E910        596 TEST                            3 2147483644                                   ACTIVE
00000000A13C2EA0        323      10689     130634 00000000A12A96C0        596 TEST                            0 2147483644                                   INACTIVE

SQL>  EXEC dbms_stats.gather_TABLE_stats(OWNNAME=>'TEST',TABNAME=>'BUG341');
BEGIN dbms_stats.gather_TABLE_stats(OWNNAME=>'TEST',TABNAME=>'BUG341'); END;

*
ERROR at line 1:
ORA-44004: invalid qualified SQL name
ORA-06512: at "SYS.DBMS_STATS", line 13056
ORA-06512: at "SYS.DBMS_STATS", line 13076
ORA-06512: at line 1


SQL> EXEC dbms_stats.gather_schema_stats('TEST', cascade=>TRUE);
BEGIN dbms_stats.gather_schema_stats('TEST', cascade=>TRUE); END;

*
ERROR at line 1:
ORA-44004: invalid qualified SQL name
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13634
ORA-06512: at "SYS.DBMS_STATS", line 13593
ORA-06512: at line 1


SQL> show user
USER is "TEST"
SQL>

Thanks
Deepak

Report message to a moderator

Re: Not able to gather Statistics of neither schema nor table [message #515799 is a reply to message #515755]

Wed, 13 July 2011 05:18

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

Hello Sir,

Is the reply of the above mentioned procedures dependent on the values of the 'NLS_COMP' and 'NLS_SORT'.

Regards
Deepak

Report message to a moderator

Re: Not able to gather Statistics of neither schema nor table [message #515818 is a reply to message #515796]

Wed, 13 July 2011 08:03

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

dsharma wrote on Wed, 13 July 2011 06:06

below is the result for "show user" and session details.


SQL> /

SADDR                   SID    SERIAL#     AUDSID PADDR                 USER# USERNAME                  COMMAND    OWNERID TADDR            LOCKWAIT         STATUS
---------------- ---------- ---------- ---------- ---------------- ---------- ------------------------------ ---------- ---------- ---------------- ----------------
00000000A13B78A8        305       7805     130620 000000009E29E910        596 TEST                            3 2147483644                                   ACTIVE
00000000A13C2EA0        323      10689     130634 00000000A12A96C0        596 TEST                            0 2147483644                                   INACTIVE

No it is not. Please show your COMPLETE SESSION, including your connect string. If you do not know what this means, please ask.

Report message to a moderator

Re: Not able to gather Statistics of neither schema nor table [message #515821 is a reply to message #515818]

Wed, 13 July 2011 08:16

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

Do you mean something like this "sqlplus test/test@xyz101" if not please explain.

Thanks
Deepak

Report message to a moderator

Re: Not able to gather Statistics of neither schema nor table [message #515826 is a reply to message #515821]

Wed, 13 July 2011 08:25

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Here is an example of a short session:

SQL> connect scott/tiger@orcltest
Connected.
SQL> show user
USER is "SCOTT"
SQL>

Then you can show any additional code you executed, like your EXEC command. Or if you want to even show the sqlplus command, you can do that too. I was already connected to sql*plus, so I just showed the connect.

Report message to a moderator

Re: Not able to gather Statistics of neither schema nor table [message #515827 is a reply to message #515826]

Wed, 13 July 2011 08:29

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

RESULT

SQL> conn test/test@alpsdev101
Connected.
SQL> show user
USER is "TEST"
SQL> set lin 22222
SQL> select * from v$session where username like 'TEST';

SADDR                   SID    SERIAL#     AUDSID PADDR                 USER# USERNAME                  COMMAND    OWNERID TADDR            LOCKWAIT         STATUS   SERVER       SCHEMA# SCHEMANAME
---------------- ---------- ---------- ---------- ---------------- ---------- ------------------------------ ---------- ---------- ---------------- ---------------- -------- --------- ---------- -------
00000000A13C2EA0        323      10695     130638 00000000A12A8ED8        596 TEST                            3 2147483644                                   ACTIVE   DEDICATED        596 TEST

SQL> EXEC dbms_stats.gather_schema_stats('TEST', cascade=>TRUE);
BEGIN dbms_stats.gather_schema_stats('TEST', cascade=>TRUE); END;

*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13634
ORA-06512: at "SYS.DBMS_STATS", line 13593
ORA-06512: at line 1

Thanks
Deepak

Report message to a moderator

Re: Not able to gather Statistics of neither schema nor table [message #515828 is a reply to message #515827]

Wed, 13 July 2011 08:32

cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester

Senior Member

Your error message has changed.
Personally I think you need to take this up with oracle support.
If TEST is a real user, and it appears to be, then such errors would indicate an oracle bug.

Report message to a moderator

Re: Not able to gather Statistics of neither schema nor table [message #515832 is a reply to message #515828]

Wed, 13 July 2011 08:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user silly identified by silly;

User created.

SQL> grant create session to silly;

Grant succeeded.

SQL> connect silly/silly
Connected.
SQL> exec dbms_stats.gather_schema_stats('SILLY', cascade=>TRUE);

PL/SQL procedure successfully completed.

Report message to a moderator

Re: Not able to gather Statistics of neither schema nor table [message #515850 is a reply to message #515827]

Wed, 13 July 2011 09:49

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

as SYS:

col rowid new_value rid
select rowid from sys.dual;
-- repeat the following until you get "0 rows deleted."
delete sys.dual where rowid != '&rid';
select * from sys.dual where rowid='&rid';
select  dummy, count(*) from sys.dual group by dummy;

Post the session.

Regards
Michel

[Edit: Fix missing quotes]

[Updated on: Thu, 14 July 2011 03:17]

Report message to a moderator

Re: Not able to gather Statistics of neither schema nor table [message #515877 is a reply to message #515850]

Thu, 14 July 2011 00:00

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

Sir, Since it's the client database I do not know the password for the sys user. They have given me a user "scott1" with dba privileges.

I tried connecting as sysdba and follow the steps you mentioned but got error:

SQL> conn scott1/tiger@alpsdev101 as sysdba
ERROR:
ORA-01031: insufficient privileges


Warning: You are no longer connected to ORACLE.
SQL> conn scott1/tiger@alpsdev101 as sysdba
ERROR:
ORA-01031: insufficient privileges


SQL> conn scott1/tiger@alpsdev101
Connected.
SQL> col rowid new_value rid
SQL> select rowid from sys.dual;

ROWID
------------------
AAAAECAABAAAAgiAAA

SQL> delete sys.dual where rowid != '&rid';
old   1: delete sys.dual where rowid != '&rid'
new   1: delete sys.dual where rowid != 'AAAAECAABAAAAgiAAA'
delete sys.dual where rowid != 'AAAAECAABAAAAgiAAA'
           *
ERROR at line 1:
ORA-01031: insufficient privileges

Thanks
Deepak

Report message to a moderator

Re: Not able to gather Statistics of neither schema nor table [message #515880 is a reply to message #515877]

Thu, 14 July 2011 00:56

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

So it seems that DUAL is OK (as it contains only 1 record).

Report message to a moderator

Re: Not able to gather Statistics of neither schema nor table [message #515895 is a reply to message #515799]

Thu, 14 July 2011 02:34

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

dsharma wrote on Wed, 13 July 2011 15:48

Hello Sir,

Is the reply of the above mentioned procedures dependent on the values of the 'NLS_COMP' and 'NLS_SORT'.

Regards
Deepak

Bug 3945156 - ORA-979 from DBMS.GATHER_TABLE_STATS when NLS_COMP=LINGUISTIC [ID 3945156.8]

Sriram

Report message to a moderator

Re: Not able to gather Statistics of neither schema nor table [message #515904 is a reply to message #515880]

Thu, 14 July 2011 02:51

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Littlefoot wrote on Thu, 14 July 2011 07:56

So it seems that DUAL is OK (as it contains only 1 record).

No because Oracle now bypasses dual table and use "fast dual" access (x$dual).
This is the reason I posted a so complicated script to check the content of the real table.

Regards
Michel

Report message to a moderator

Re: Not able to gather Statistics of neither schema nor table [message #515905 is a reply to message #515895]

Thu, 14 July 2011 02:52

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

If its a bug then why I am getting two different errors.

SQL> EXEC dbms_stats.gather_schema_stats('TEST', cascade=>TRUE);
BEGIN dbms_stats.gather_schema_stats('TEST', cascade=>TRUE); END;

*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13634
ORA-06512: at "SYS.DBMS_STATS", line 13593
ORA-06512: at line 1

SQL> EXEC dbms_stats.gather_schema_stats('TEST', cascade=>TRUE);
BEGIN dbms_stats.gather_schema_stats('TEST', cascade=>TRUE); END;

*
ERROR at line 1:
ORA-44004: invalid qualified SQL name
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13634
ORA-06512: at "SYS.DBMS_STATS", line 13593
ORA-06512: at line 1

Regards
Deepak

Report message to a moderator

Re: Not able to gather Statistics of neither schema nor table [message #515906 is a reply to message #515877]

Thu, 14 July 2011 02:52

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Sir, Since it's the client database I do not know the password for the sys user. They have given me a user "scott1" with dba privileges.

So you can't make my test, ask the local DBA to do it and report you the (complete) result.

Anyway (it seems) the problem is not in your side but in database one, and so the local DBA must do something, at least open a SR.

Regards
Michel

[Updated on: Thu, 14 July 2011 02:53]

Report message to a moderator

Re: Not able to gather Statistics of neither schema nor table [message #515940 is a reply to message #515906]

Thu, 14 July 2011 04:47

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Quote:

If its a bug then why I am getting two different errors.

Because Its a bug.(fixed in 10.2.0.4)
and Quote:

ORA-06512: at "SYS.DBMS_STATS", line 13593

This package does n`t contain that no of lines.

As per Oracle Metalink this is Bug which is fixed in 10.2.0.4.
Ask your DBA about it.

sriram

[Updated on: Thu, 14 July 2011 04:48]

Report message to a moderator

Previous Topic:	difference between V_$ and V$ views
Next Topic:	Access Logs via SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 01 13:04:23 CST 2024