Home » RDBMS Server » Server Administration » Can I remove the synonym STATS$SNAPSHOT_ID?
Can I remove the synonym STATS$SNAPSHOT_ID? [message #266866] Tue, 11 September 2007 20:47 Go to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Dear all!

I've got to collect statistics by DBMS_STAT.

For the first time, the spcreate.sql is to be called, however, it returned one error following:

......
If this script is automatically called from spcreate (which is
the supported method), all STATSPACK segments will be created in
the PERFSTAT user's default tablespace.

Using perfstat tablespace to store Statspack objects

... Creating STATS$SNAPSHOT_ID Sequence

Sequence created.

create public synonym STATS$SNAPSHOT_ID  for STATS$SNAPSHOT_ID
                      *
ERROR at line 1:
ORA-00955: name is already used by an existing object


Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options


And then, I checked it again with its owner:

SQL> select owner, synonym_name
  2  from dba_synonyms
  3  where synonym_name='STATS$SNAPSHOT_ID';

OWNER                          SYNONYM_NAME
------------------------------ ------------------------------
PUBLIC                         STATS$SNAPSHOT_ID


Then, I checked again after dropping schema PERFSTAT:

SQL> conn /as sysdba
Connected.
SQL>
SQL> select username, account_status
  2  from dba_users
  3  where username='PERSTAT';

no rows selected
SQL>
SQL>
SQL> set pagesize 9999
SQL> set linesize 200
SQL> col owner format a20
SQL> col table_owner format a20
SQL> select owner, synonym_name, table_name, table_owner
  2  from dba_synonyms
  3  where synonym_name='STATS$SNAPSHOT_ID';

OWNER                SYNONYM_NAME         TABLE_NAME           TABLE_OWNER
-------------------- -------------------- -------------------- -----------------
---
PUBLIC               STATS$SNAPSHOT_ID    STATS$SNAPSHOT_ID    PERFSTAT


Tried to drop synonym STATS$SNAPSHOT_ID:
SQL> drop synonym STATS$SNAPSHOT_ID;
drop synonym STATS$SNAPSHOT_ID
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist


Hey, where is table_name PERFSTAT....

SQL>select TB.owner, TB.table_name,
2  SY.table_owner, SY.synonym_name
3  from dba_tables TB, dba_synonyms SY
4  where TB.owner=SY.owner
5* and tb.table_name='PERFSTAT'
no rows selected


What's the STATS@SNAPSHOT_ID synonym? Can I remove it from PUBLIC owner and recreate?

I've searched it on Oracle online document, but I did not find.

Thank you!

[Updated on: Tue, 11 September 2007 21:31]

Report message to a moderator

Re: Can I remove the synonym STATS$SNAPSHOT_ID? [message #266872 is a reply to message #266866] Tue, 11 September 2007 21:55 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

You can delete this synonym. I believe that you manually created it(May be oracle generated this name).
Re: Can I remove the synonym STATS$SNAPSHOT_ID? [message #266876 is a reply to message #266872] Tue, 11 September 2007 22:06 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, Arju!

But I did not create this synonym, the first time when I executed spcreate.sql for generating collection and its packages which returned errors.

I tried to delete this synonym, however, I can not:

SQL> drop synonym STATS$SNAPSHOT_ID;
drop synonym STATS$SNAPSHOT_ID
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist

SQL>Show user
USER is "SYS"


Would you like to guide me about resolution?


Thank you!

[Updated on: Tue, 11 September 2007 22:08]

Report message to a moderator

Re: Can I remove the synonym STATS$SNAPSHOT_ID? [message #266878 is a reply to message #266866] Tue, 11 September 2007 22:16 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

The error I checked in your 1st thread of this topic you can simply ignore that because it is already there.


However to delete it you have to log on as perfstat and then provide a password.

spcusr.sql is a script that assign privileges to perfstat .

Or to drop this synonym use
as a sys
drop synonym perfstat.STATS$SNAPSHOT_ID;
Re: Can I remove the synonym STATS$SNAPSHOT_ID? [message #266887 is a reply to message #266878] Tue, 11 September 2007 22:43 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thanks for your reply, Arju!

If only remove this synonym as simply to do. My privilege is sys, however, I tried following your guide:

C:\>set oracle_sid=2003dbclone
C:\>
C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 12 10:29:16 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn /as sysdba
Connected.
SQL> drop synonym perfstat.STATS$SNAPSHOT_ID
  2  ;
drop synonym perfstat.STATS$SNAPSHOT_ID
                      *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist


SQL> conn perfstat/tuananhtran
Connected.
SQL> drop synonym STATS$SNAPSHOT_ID;
drop synonym STATS$SNAPSHOT_ID
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist


The synonym has relative execution to spreport.sql, I tried to call spreport.sql, you will see some errors:

SQL>show user
User is "PERFSTAT'
SQL> @C:\oracle\product\10.2.0\NeoDb\RDBMS\ADMIN\spreport.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 2058405159 NEO86               1 neo86



Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  from stats$database_instance
       *
ERROR at line 7:
ORA-01775: looping chain of synonyms



Using 2058405159 for database Id
Using          1 for instance number
       from stats$database_instance
            *
ERROR at line 5:
ORA-06550: line 5, column 13:
PL/SQL: ORA-01775: looping chain of synonyms
ORA-06550: line 4, column 6:
PL/SQL: SQL Statement ignored
ORA-06550: line 11, column 13:
PL/SQL: ORA-04020: deadlock detected while trying to lock object
PERFSTAT.STATS$SNAPSHOT
ORA-06550: line 10, column 6:
PL/SQL: SQL Statement ignored


Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\>


I am wondering about these errors.
Re: Can I remove the synonym STATS$SNAPSHOT_ID? [message #266903 is a reply to message #266887] Tue, 11 September 2007 23:51 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello trantuananh24hg,

My environment for tests:
  • win2003r2 sp2 32bit;
  • oracle 32bit 10.2.0.1;


What I did to try to reproduce your test (sys as sysdba):
1) A have a test database (sid=pnet);
2) This database has 01 tablespace (users);
3) I create another tablespace (ts=> perfstat) to collect snapshot;

4) I ran: spcreate.sql
... provide password
... tablespace ==> perfstat (the one I have created before)

5) I setup snapshot:
SQL> exec statspack.snap(i_snap_level => 6, i_modify_parameter => 'true');

Now using perfstat/password:

1) took same snapshots: SQL> exec statspack.snap;
2) SQL> select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS') "Date/Time" from stats$snapshot,v$database;

3) create report:
SQL> @?/rdbms/admin/spreport.sql

4) purge some snapshots: (provide low_snap_id/high_snap_id)
SQL> @?/rdbms/admin/sppurge;

...

Now I cleanup my database:
sqlplus "/ as sysdba"
SQL> @?/rdbms/admin/spdrop.sql
SQL> DROP TABLESPACE perfstat INCLUDING CONTENTS AND DATAFILES;


Did you do any step different from above?

Regards,


mson77

Re: Can I remove the synonym STATS$SNAPSHOT_ID? [message #266935 is a reply to message #266866] Wed, 12 September 2007 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I've got to collect statistics by DBMS_STAT.

For the first time, the spcreate.sql is to be called,

There is no relation between these two points.

dbms_stats is to collect statistics on objects for the optimizer to generate SQL execution plan.
StatsPack is to collect statistics on the current instance performances.
No relation at all. They are fully independent.

Regards
Michel

Re: Can I remove the synonym STATS$SNAPSHOT_ID? [message #266944 is a reply to message #266935] Wed, 12 September 2007 01:17 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, Mr Michael Cadot and Mr mson77!

I will try again following your guide, and then, I will post my result.

Thank you very much!

[Updated on: Wed, 12 September 2007 01:20]

Report message to a moderator

Re: Can I remove the synonym STATS$SNAPSHOT_ID? [message #266945 is a reply to message #266944] Wed, 12 September 2007 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I wanted to collect all of Database Instance, System Resource, SQL. First time, I execute DBMS_STAT, but this errors encountered.

But DBMS_STATS is NOT for this. StatsPack is.

Regards
Michel
Re: Can I remove the synonym STATS$SNAPSHOT_ID? [message #266976 is a reply to message #266945] Wed, 12 September 2007 02:35 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, Michel Cadot!

I have some questions:

- How do I use Enterprise Manager from my Client to monitor Database Server with:
+ Client's IP: 10.252.77.89
+ Server's IP: 10.252.77.5

- In Metalink, I have found a tool named as SQLTXPLAIN which execute by sqcreate.sql in %ORACLE/HOME/DB/RDBMS/ADMIN. But I did not find this script sqcreate.sql. Would you like to tell me about it?

Thank you!

[Updated on: Wed, 12 September 2007 02:37]

Report message to a moderator

Re: Can I remove the synonym STATS$SNAPSHOT_ID? [message #266991 is a reply to message #266976] Wed, 12 September 2007 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
New questions implies new topics.
Don't mix them or they will be unreadable and useless.

Regards
Michel
Re: Can I remove the synonym STATS$SNAPSHOT_ID? [message #474224 is a reply to message #266878] Fri, 03 September 2010 12:05 Go to previous messageGo to next message
ephrak
Messages: 1
Registered: September 2010
Junior Member
Hi, you may end up having those synonyms in your database if statspack (perfstat user ) has not been cleaned up or imported correctly.
If you want to remove them, you must issue:
DROP PUBLIC SYNONYM xxx;
You can list them has they show as invalid in dba_objects, all start with STATS$ and refer to non existing segments.
Re: Can I remove the synonym STATS$SNAPSHOT_ID? [message #560292 is a reply to message #266866] Wed, 11 July 2012 20:46 Go to previous messageGo to next message
oracle_awen
Messages: 5
Registered: July 2012
Location: china
Junior Member

you must have two or more instanceļ¼Œ so you hava to set ORACLE_SID = your sid

then it's ok
Re: Can I remove the synonym STATS$SNAPSHOT_ID? [message #560293 is a reply to message #560292] Wed, 11 July 2012 20:51 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Earth to oracle_awen:

You never get a second chance to make a first impression.
So why & how did you decide to make your first post in responding to FOUR+ year old thread?
Previous Topic: How to set cursor_sharing refresh interval
Next Topic: Create a new control file
Goto Forum:
  


Current Time: Tue Mar 19 06:37:25 CDT 2024