[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 594
  • Last Modified:

how to create procedure to delete rows from STATS$SNAPSHOT in Oracle

Hi Experts,

I'm about to create a simple stored procedure in oracle that should delete data in the table called STATS$SNAPSHOT where snap_time is older than x days.

when i run this query:
delete from STATS$SNAPSHOT where snap_time < (sysdate - 2);
 it works fine, but when I run my procedure (EXECUTE DelStatspackData(2);) then i get some error:
Error starting at line 2 in command:
EXECUTE DelStatspackData(1);
Error report:
ORA-06550: line 1, column 7:
PLS-00905: object PRODUKTION.DELSTATSPACKDATA is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:


Could you please help me with that?


create or replace PROCEDURE DelStatspackData (i IN NUMBER) IS

begin

  delete from STATS$SNAPSHOT where snap_time < (sysdate - i);

end DelStatspackData;
/

Open in new window

0
delmandiyar
Asked:
delmandiyar
  • 2
  • 2
2 Solutions
 
Franck PachotCommented:
Hi,
- After creating the procedure, use 'show errors' to see errors.
- you should use sppurge.sql script to purge statspack data.
Regards,
Franck.
0
 
shru_0409Commented:
may by your procedure is invalid check and compile it.. for more information check this link

http://www.database-expert.com/statspack_admin.asp
http://www.dba-oracle.com/tips_oracle_statspack_purge_utility.htm
0
 
delmandiyarAuthor Commented:
Hi, thanks for your answers,

I'm using Oracle SQL Developer. When i create my procedure, it tells me that procedure created with warning, but when  i complied i get this error:
Error(7,4): PL/SQL: SQL Statement ignored.
Error(7,16): PL/SQL: ORA-00942: table or view does not exist.

what i need is a procedure that deletes data in STATS$SNAPSHOT older than x days. x should be a parameter for my procedure. could u please help me with that??
0
 
Franck PachotCommented:
Hi,
The procedure owner must have direct delete grant on that table (direct - nor via a role)
Regards,
Franck.
0
 
delmandiyarAuthor Commented:
Franckpachot you right, I realised that just 5 minutes before I saw your answer.

But thanks anyway to all of you.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now