topic Re: Wanted: Good oracle scripts. in Operating System - HP-UX

Wanted: Good oracle scripts.

Steven E. Protter — Mon, 20 Jan 2003 16:19:29 GMT

You can submit sql or scripts that submit sql.

10 points for attachments, 5 points for anything longer than 10 lines that isn't an attachement.

Only tested scripts please.

Make sure if your scripts have user id and password in them that you change them please.

Extra points(make it a two post) for any script that can read password out of an encrypted file that only the dba user(usually oracle) has access to. The point of that specific issue is to have scripts that can get password from a file that isn't plain text.

If you recommend some kind of encryption software not found in the OS, please provide a link and detailed installation instructions.

P

Re: Wanted: Good oracle scripts.

Justo Exposito — Mon, 20 Jan 2003 16:39:47 GMT

Hi,

This is a script to switch the logs to the archive directory in order to backup them.

Regards,

Justo.

Re: Wanted: Good oracle scripts.

Justo Exposito — Mon, 20 Jan 2003 16:41:02 GMT

Hi,

This is the second part needed in order to use the switch script.

Regards,

Justo.

Re: Wanted: Good oracle scripts.

Justo Exposito — Mon, 20 Jan 2003 16:43:30 GMT

Hi,

This is a script to remove the archive log files that was backed up.

Regards,

Justo.

Re: Wanted: Good oracle scripts.

Ian Dennison_1 — Mon, 20 Jan 2003 16:59:15 GMT

Generates a SQL Script to rebuild the control file from Database SID to Database NEWSID (good for automating the rehosting of a database overnight).

Used for SAP and non-SAP Oracle Database rehosts, well bedded in.

Share and Enjoy! Ian

Re: Wanted: Good oracle scripts.

T G Manikandan — Tue, 21 Jan 2003 06:30:54 GMT

http://www.dbasupport.com/oracle/scripts/

Re: Wanted: Good oracle scripts.

T G Manikandan — Tue, 21 Jan 2003 06:32:36 GMT

http://www.dbasupport.com/oracle/scripts/

Re: Wanted: Good oracle scripts.

Indira Aramandla — Tue, 21 Jan 2003 07:07:51 GMT

Attached are the scripts to make a copy of an existing database. Can be used to refresh a warehouse, or make a copy of the database on the same / different server.
Here they are. (Refer attachements)

For more scripts on oracle Database administration please refer this metalink.http://metalink.oracle.com/metalink/plsql/ml2_gui.startup

Re: Wanted: Good oracle scripts.

Yogeeraj_1 — Tue, 21 Jan 2003 11:03:55 GMT

hi,

Please allow me to contribute the one i like most:

Filename: alerts.ksh
Description: Automatic monitoring of the production database's ALERT LOG file for exceptions.

Running it as the Oracle User, reschedules daily execution of script - morning, evening and night everyday (uses at)

Makes monitoring of the Alert log easy task!

Hope this helps!

Best Regards
Yogeeraj

PS. Don't forget to modify lines:
a. DBA1='myname@mymailserver.mu'
b. SID=pfs

to suit your own enviroment

Re: Wanted: Good oracle scripts.

Jean-Luc Oudart — Tue, 21 Jan 2003 17:20:29 GMT

The attached script is to be run as the schema owner and list all tables with their record count.
You must set utl_file_dir in your init file.
Change database owner to meet your requirement.

Jean-Luc

Re: Wanted: Good oracle scripts.

Volker Borowski — Tue, 21 Jan 2003 17:46:54 GMT

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

Re: Wanted: Good oracle scripts.

Barbara Spadaro_1 — Tue, 21 Jan 2003 22:53:50 GMT

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

Re: Wanted: Good oracle scripts.

Brian Crabtree — Wed, 22 Jan 2003 00:06:17 GMT

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

Re: Wanted: Good oracle scripts.

Brian Crabtree — Wed, 22 Jan 2003 00:07:13 GMT

Password Body Script

Re: Wanted: Good oracle scripts.

Yogeeraj_1 — Wed, 22 Jan 2003 04:38:01 GMT

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

Re: Wanted: Good oracle scripts.

Andreas D. Skjervold — Wed, 22 Jan 2003 07:48:02 GMT

Hi

Just a basic info script which inventory the database.

Andreas

Re: Wanted: Good oracle scripts.

Jean-Luc Oudart — Wed, 22 Jan 2003 13:05:01 GMT

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

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

JL

Re: Wanted: Good oracle scripts.

Gene Kornacki_3 — Wed, 22 Jan 2003 17:20:25 GMT

A nice system stats/performance script.

Re: Wanted: Good oracle scripts.

Gene Kornacki_3 — Wed, 22 Jan 2003 17:21:17 GMT

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?

Re: Wanted: Good oracle scripts.

Gene Kornacki_3 — Wed, 22 Jan 2003 17:23:01 GMT

Looking for free space?

Here is a little script to help.