Home » RDBMS Server » Server Administration » sppurge script -- Batch mode not working
sppurge script -- Batch mode not working [message #151268] Wed, 14 December 2005 00:48 Go to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member


Oracle : 9iR2
OS : Solaris 9, Fedora core 4

I am trying to automate the statspack's "sppurge.sql" script in batch mode but its not accepting the defined values for "losnapid" and "hisnapid".

I tried it on both the above mentioned OSs.

SQL> select snap_id from STATS$SNAPSHOT ;

 Snap Id
--------
       3
       4
       6

3 rows selected.

SQL> define losnapid=3
SQL> define hisnapid=4
SQL>  @$ORACLE_HOME/rdbms/admin/sppurge.sql


Database Instance currently connected to
========================================

                                Instance
   DB Id    DB Name    Inst Num Name
----------- ---------- -------- ----------
  480457815 DEVDB             1 devdb


Snapshots for this database instance
====================================

          Snap
 Snap Id Level Snapshot Started      Host            Comment
-------- ----- --------------------- --------------- -------------------------
       3     5  13 Dec 2005 18:31:31 localhost.local
                                     domain

       4     5  13 Dec 2005 18:33:32 localhost.local
                                     domain

       6     5  13 Dec 2005 18:36:55 localhost.local
                                     domain



Warning
~~~~~~~
sppurge.sql deletes all snapshots ranging between the lower and
upper bound Snapshot Id's specified, for the database instance
you are connected to.

You may wish to export this data before continuing.


Specify the Lo Snap Id and Hi Snap Id range to purge
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for losnapid:


As per the documentation it should not ask to enter the values of "losnapid" and "hisnapid" but its prompting for the same.

What am i doing wrong?

Thanks & Regards,
Tarun

Report message to a moderator

Re: sppurge script -- Batch mode not working [message #151280 is a reply to message #151268] Wed, 14 December 2005 01:38 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
If you look at sppurge.sql, it starts with
"undefine dbid inst_num losnapid hisnapid"

If you'd remove the desired variables from this line, it should work fine.

hth

Report message to a moderator

Re: sppurge script -- Batch mode not working [message #151288 is a reply to message #151280] Wed, 14 December 2005 01:52 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

Thanks for the reply Frank.

I was planning to create the shell script which will inturn call this "sppurge " script provided by Oracle. And thought to make it database version independent i.e. the shell script should call this "sppurge" script from "$ORACLE_HOME/rdbms/admin location" wherever i use.

But it seems that i have to hardcode it.

Or if there is any other idea then its welcome.

regards,
tarun

Report message to a moderator

Re: sppurge script -- Batch mode not working [message #151292 is a reply to message #151288] Wed, 14 December 2005 02:10 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Starting to sound more like server-admin.
Do you want the topic to be moved there?

Report message to a moderator

Re: sppurge script -- Batch mode not working [message #151307 is a reply to message #151292] Wed, 14 December 2005 03:41 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

Yeah you can move it, i just want more comments on the topic.

Report message to a moderator

Re: sppurge script -- Batch mode not working [message #151340 is a reply to message #151307] Wed, 14 December 2005 06:49 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
The reason for the issue is exactly as stated by Frank.
Removing undefine statements from the beginning of script to end of script will solve the issue.
But i do not understand the issue behind this~.
>> I was planning to create the shell script which will inturn call this "sppurge " script provided by Oracle. And thought to make it database version independent i.e. the shell script should call this "sppurge" script from "$ORACLE_HOME/rdbms/admin location" wherever i use.

You can create your own mysppurge.sql , do the changes and use it in your script.

Report message to a moderator

Re: sppurge script -- Batch mode not working [message #151342 is a reply to message #151340] Wed, 14 December 2005 07:09 Go to previous message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member


I agree Mahesh and i got that point to edit my own sppurge.sql .

But something else was running in my mind i.e. use the same shell script on any Oracle server by just calling the sppurge.sql script present on the server.

( actually writing scripts for a DBA tool that's why thought to do it that way)

And now it seems that its not possible that way .

thanks & regards,
tarun

Report message to a moderator

Previous Topic: Downgrade Oracle 9.2.0.6 to 9.2.0.1 error (ORA-00600)
Next Topic: Database Shutdown/Startup Procedure
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Feb 13 17:08:20 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.