Wanted: Good oracle scripts.

[Christian Gebhardt]

Script to supervise the resource limits (border is here 90 percent)

declare
cursor c1 is
select * from v$resource_limit;
i_value integer;
N_BORDER CONSTANT number DEFAULT 0.90;
s_message varchar2(2000);
begin
for c1_rec in c1
loop
begin
i_value := to_number(c1_rec.INITIAL_ALLOCATION);
if (c1_rec.CURRENT_UTILIZATION > N_BORDER * c1_rec.INITIAL_ALLOCATION) then
s_message := c1_rec.RESOURCE_NAME||' has exceeded '||to_char(N_BORDER*100)||' percent (max: '|| ltrim(c1_rec.INITIAL_ALLOCATION)||') ';
dbms_output.put_line(s_message);
end if;
exception
when value_error then
null;
when others then
raise;
end;
end loop;
end;
/

Chris

[Greg OBarr]

I run this script every 5 minutes using cron. I have caught problems on a number of occasions with tablespace sizes, etc. before users were even aware of them.
The use of the block number in the file prevents being paged more than once about the same error.

Thanks Steve!

[Yogeeraj_1]

hi,
this one allows me to check locks on my database at anytime.

Note: Need dba role or "select any table" system priviledge

Regards
Yogeeraj

[Printaporn_1]

Hi,

This is script to create procedure that you can execute to do compute statistics on all tables in that schema.

[LE_1]

Here a PL/SQL procedure
to know space in your tablespaces

Work for 8i with
locally managed tblspc and with temp files.

It goes fast !!

Use it for user system

Need:
grant select any table to system;
(do it even you are connected as system)

Use:
set serveroutput on
exec Z_espace;

[Jean-Luc Oudart]

We just don't want to re-invent the wheel , do we ?

plenty of scripts from the large Oracle community :
http://otn.oracle.com/oramag/code/tips2003/content.html

I have a browse from time to time to see what's done outthere.

JL

[IT Response]

What is a good Book to purchase on writing Oracle Scripts?

[TwoProc]

Find the leaf node depth of a bunch of indexes. For management purposes, it restricts the list per user, but easy enough to let it run for all users by commenting out the "where owner" line. Lets you know what indexes are candidates for rebuild.

[TwoProc]

Script to see what module, action and sql text are generating the highest disk i/o on the system. I like to review this often.

[Steven E. Protter]

Post your own thread please so you can reward the answers with points, if you have an independent question.

SEP

[Steven E. Protter]

RC

9 asignments in 34 responses to your question is a record that needs improvement.

This thread ceases to be useful and is being closed for that reason. I will reopen it if I receive an offline request.

SEP