Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Wanted: Good oracle scripts.

SOLVED
Go to solution
Steven E. Protter
Exalted Contributor

Wanted: Good oracle scripts.

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
35 REPLIES
Justo Exposito
Esteemed Contributor

Re: Wanted: Good oracle scripts.

Hi,

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

Regards,

Justo.
Help is a Beatiful word
Justo Exposito
Esteemed Contributor

Re: Wanted: Good oracle scripts.

Hi,

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

Regards,

Justo.
Help is a Beatiful word
Justo Exposito
Esteemed Contributor
Solution

Re: Wanted: Good oracle scripts.

Hi,

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

Regards,

Justo.
Help is a Beatiful word
Ian Dennison_1
Honored Contributor

Re: Wanted: Good oracle scripts.

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
Building a dumber user
T G Manikandan
Honored Contributor

Re: Wanted: Good oracle scripts.

T G Manikandan
Honored Contributor

Re: Wanted: Good oracle scripts.

Indira Aramandla
Honored Contributor

Re: Wanted: Good oracle scripts.

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
Never give up, Keep Trying
Yogeeraj_1
Honored Contributor

Re: Wanted: Good oracle scripts.

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Jean-Luc Oudart
Honored Contributor

Re: Wanted: Good oracle scripts.

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
fiat lux
Volker Borowski
Honored Contributor

Re: Wanted: Good oracle scripts.

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.

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


If all else fails, read the instructions.
Brian Crabtree
Honored Contributor

Re: Wanted: Good oracle scripts.

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
Honored Contributor

Re: Wanted: Good oracle scripts.

Password Body Script
Yogeeraj_1
Honored Contributor

Re: Wanted: Good oracle scripts.

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Andreas D. Skjervold
Honored Contributor

Re: Wanted: Good oracle scripts.

Hi

Just a basic info script which inventory the database.

Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Jean-Luc Oudart
Honored Contributor

Re: Wanted: Good oracle scripts.

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

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

JL
fiat lux

Re: Wanted: Good oracle scripts.

A nice system stats/performance script.

Re: Wanted: Good oracle scripts.

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.

Looking for free space?

Here is a little script to help.

Julio Yamawaki
Esteemed Contributor

Re: Wanted: Good oracle scripts.

Here is a script to verify Oracle buffers.
I've been using this for a long time and it's very useful.
This script was tested in Oracle Server 9i.


Yogeeraj_1
Honored Contributor

Re: Wanted: Good oracle scripts.

this script is for both mounting and unmount Oracle Cds. Very useful.
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Wanted: Good oracle scripts.

hi,

Very often, we get question about Oracle exports exceeding 2GB. Below how we overcome this!
==============================================================================================
you can also try this to prevent the oracle dump files to exceed 2GB, hence multiple files which can esily be extracted.

use export to a PIPE and have compress and split read the pipe. The result is a couple of 500meg compressed files that consistute the export. At 500meg, any utility can deal with this files and can be moved around easier.

Here is the CSH script use to show you how it is done. It does a full export and then tests the integrity of the export by doing a full import show = y. that gives a file with all of my source code and ddl to boot.

#!/bin/csh -vx

setenv UID /
setenv FN exp.`date +%j_%Y`.dmp
setenv PIPE /tmp/exp_tmp_ora8i.dmp

setenv MAXSIZE 500m
setenv EXPORT_WHAT "full=y COMPRESS=n"

echo $FN

cd /nfs/atc-netapp1/expbkup_ora8i
ls -l

rm expbkup.log export.test exp.*.dmp* $PIPE
mknod $PIPE p

date > expbkup.log
( gzip < $PIPE ) | split -b $MAXSIZE - $FN. &
#split -b $MAXSIZE $PIPE $FN. &

exp userid=$UID buffer=20000000 file=$PIPE $EXPORT_WHAT >>& expbkup.log
date >> expbkup.log


date > export.test
cat `echo $FN.* | sort` | gunzip > $PIPE &
#cat `echo $FN.* | sort` > $PIPE &
imp userid=sys/o8isgr8 file=$PIPE show=y full=y >>& export.test
date >> export.test

tail expbkup.log
tail export.test

ls -l
rm -f $PIPE

------------ eof -------------------------

Another solution if you are running 8i would be:

E.g.
$ORACLE_HOME/bin/exp $ACC_PASS filesize=1024M file=\($DMP_PATH1/yddbexp"$dt"FULLa.dmp, $DMP_PATH1/yddbexp"$dt"FULLb.dmp, $DMP_PATH1/yddbexp"$dt"FULLc.dmp, $DMP_PATH1/yddbexp"$dt"FULLd.dmp, $DMP_PATH1/yddbexp"$dt"FULLe.dmp\) buffer=409600 log=$LOG_PATH/yddbexp"$dt"FULL.log full=Y grants=Y rows=Y compress=N direct=n



Hope this helps!

Best Regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Christian Gebhardt
Honored Contributor

Re: Wanted: Good oracle scripts.

This sql-statement shows the segments that cannot allocate a next extent depending on the size of the next extent (pctincrease, nextextent, ...)

!!! This script doesn't work correctly with locally managed tablespaces and "automatic allocation". ORACLE didn't give us the way they calculate the next extent size in this case !!!!!

-------------------------
set linesize 150
set pagesize 60

col owner format a15
col segment_name format a30
col partition_name format a15

SELECT /*+ ordered */ seg.owner,
seg.segment_name,
seg.partition_name,
seg.segment_type,
seg.tablespace_name,
seg.pct_increase,
seg.extents,
nvl( seg.next_extent, lastextent.bytes ) nextbytes
FROM dba_segments seg, dba_tablespaces ts,
( select distinct owner,
segment_name,
segment_type,
last_value(bytes)
over ( partition by owner,
segment_name,
segment_type
order by owner,
segment_name,
segment_type
) as bytes
from dba_extents ext
) lastextent
WHERE lastextent.owner = seg.owner
AND ts.TABLESPACE_NAME = seg.TABLESPACE_NAME
AND ts.STATUS != 'READ ONLY'
AND lastextent.segment_name = seg.segment_name
AND lastextent.segment_type = seg.segment_type
AND nvl( seg.next_extent, lastextent.bytes ) >
( SELECT nvl(MAX(bytes),0)
FROM dba_free_space
WHERE tablespace_name = seg.tablespace_name
)
ORDER by seg.owner,
seg.segment_name,
seg.tablespace_name,
seg.next_extent desc;


Chris
Christian Gebhardt
Honored Contributor

Re: Wanted: Good oracle scripts.

Checking for blocking locks:

SELECT /*+ ordered */ 'BLOCKER',
lck.sid,
lck.ctime,
sess.schemaname
FROM v$lock lck,
v$session sess
WHERE lck.block > 0
AND lck.ctime > 10
AND lck.sid = sess.sid
ORDER BY sess.username;

Chris