Wanted: Good oracle scripts.

[Volker Borowski]

Hi,

sorry, this might sound contra-productive, but I have to vote against the record-count script.

Gathering the record count of each table by issuing a
SELECT COUNT(*) FROM TABLE;
against a big productive SAP database might run for days(!) and give your database a big performance impact, because a "select count(*)" can be a very expensive statement (It might require a full index scan of the primary key, which can be several GB in big databases).

It is a nice script, but I'd recommend to use it on small databases only.

Sorry for being negative.
Volker

[Barbara Spadaro_1]

Hi,

Here's a script that runs SQL against a databse to find tables that are approaching their maxextents values (defined as current extents being at 75% of maxextents - but you can alter this to suit) and email the results (specify in the $MAILTO variable).

Edit the script accordingly for your own schema owner.

cheers

Barbara

[Brian Crabtree]

This is a simple password script that will write a password into a file, and read it from the file. It is split into two pieces (on request) as package header and package body.

The createpassfile is simple (just provide a location, filename, and password). The readpassfile requires a variable to write to, so you have to code a pl/sql block to run it. The following should work:

set serveroutput on
declare
outpass varchar2(50);
begin
filepassword.readpassfile('/home/oracle','password.txt',outpass);
end;
/

You are welcome to modify this script as needed. This defaults the key to "password".

Thanks,

Brian

[Brian Crabtree]

Password Body Script

[Yogeeraj_1]

Hi,

this is not one of my own, but one provided by Oracle.

I keep it will all my Oracle Scripts and run it each time i make a change to my system hence updating my system configuration.

I am posting this for people who do not have Metalink access.

Below a quote from Metalink about this script:

RDA is a set of Unix shell scripts to gather detailed information about an Oracle environment. The scripts are focused to collect information that will aid in problem diagnosis, however the output is also very useful to see the overall system configuration.

We encourage the use of RDA because it gives a comprehensive picture of the customer's environment. This can greatly reduce tar resolution time by minimizing the number of requests from Oracle Support Services for more information. We have specifically designed RDA to be as unobtrusive as possible. RDA does not modify your system in any way, it merely collects data useful for Oracle Support Services.



NB. you do have RDA for windows environment but this is a Unix world! So am posting only the Unix flavour of it ;)
Current Unix Version: 3.10, November 25, 2002
Also, please go through the readme first.

Hope this helps!

Best Regards
Yogeeraj

[Andreas D. Skjervold]

Hi

Just a basic info script which inventory the database.

Andreas

[Jean-Luc Oudart]

Scripts can be found in this Web site :
http://www.ixora.com.au/tips/

http://www.ixora.com.au/scripts/

JL

[Gene Kornacki_3]

A nice system stats/performance script.

[Gene Kornacki_3]

set pause on
set pages 24

col owner format a12
col object_name format a30

SELECT owner,
object_name,
object_type,
status
FROM dba_objects
WHERE status != 'VALID'
ORDER BY 1,3,2


Want to look for invalid objects?

[Gene Kornacki_3]

Looking for free space?

Here is a little script to help.