Category Archives: Scripts

Clone a database

Posted on 9 February 2007 by Andrew Fraser

The below SQL generates a SQL script that can be used to clone a database, putting tablespaces into hot backup mode one at a time. Run it on the source database, and edit the output to specify new target file … Continue reading →

Posted in Rman, Scripts | Leave a comment

Slow SQL report

Posted on 7 February 2007 by Andrew Fraser

Lists slow SQL in library cache for tuning investigations: set pages 9999 spool c.lst select elapsed_time/1000000 secs, executions, elapsed_time/1000000/greatest(executions,1) secs_per_exec, sql_text from v$sql where executions > 50 and elapsed_time/1000000/greatest(executions,1) > 1 order by 3 desc / spool off ed c.lst … Continue reading →

Posted in Performance tuning, Scripts | Leave a comment

Using 10g datapump and scheduler to copy schemas

Posted on 1 February 2007 by Andrew Fraser

Update 13-Feb-07: Setting the datapump table_exists_action to replace does not overwrite views, sequences, plsql objects. They have to be dropped separately before datapump is called, see gotchas and code below. Update 02-Mar-07: dba_ views rather than all_ views have to … Continue reading →

Posted in RAC, Scripts | 7 Comments

Identify what auditing is on

Posted on 17 January 2007 by Andrew Fraser

Suspect too much auditing is on on a database? This script will show you what auditing is on: set pages 9999 select * from DBA_STMT_AUDIT_OPTS ; select * from DBA_PRIV_AUDIT_OPTS ; set termout off lines 192 spool 1.tmp select * … Continue reading →

Posted in Audit, Scripts | Leave a comment

Pre 9202/9014: Advanced queuing runs away with server memory.

Posted on 15 January 2007 by Andrew Fraser

Using the below memory listing script, I found two databases with large PGAs. That was strange since pga_aggregate_target was unset, sort_area_size was just 64k, and there were very few sessions. Checking PGA at session level showed the QMN sessions had … Continue reading →

Posted in Bugs, Scripts | Leave a comment

Tablespace space with sm$ views + database level space

Posted on 15 January 2007 by Andrew Fraser

The sm$ views are an easy way of seeing tablespace space usage. There is also an sm$ts_free view. set pages 9999 col tot_mb form 999,999 col use_mb form 999,999 col pct_used form 999 select t.tablespace_name, t.bytes/1024/1024 tot_mb, u.bytes/1024/1024 use_mb, 100*u.bytes/t.bytes … Continue reading →

Posted in Scripts, Space | Leave a comment

rman crosscheck archivelog all

Posted on 12 January 2007 by Andrew Fraser

If archived redo logs are (wrongly) deleted/moved/compressed from disk without being backed up, the rman catalog will not know this has happened, and will keep attempting to backup the missing archived redo logs. That will cause rman archived redo log … Continue reading →

Posted in Rman, Scripts | 4 Comments

Is database using pfile (init.ora), or spfile?

Posted on 11 January 2007 by Andrew Fraser

Very useful SQL, from Tom Kyte: select decode(count(*), 1, ‘spfile’, ‘pfile’ ) from v$spparameter where rownum=1 and isspecified=’TRUE’ ; If it it using an spfile, an easy way to see the parameters is to generate an init.ora copy: SQL> conn … Continue reading →

Posted in Scripts | 1 Comment

9i unix startup / shutdown with listener password

Posted on 11 January 2007 by Andrew Fraser

Thanks to Laurent Schneider for tips on how to handle listener passwords in scripts. [Update 12-Jan-2007 - ammended script following Laurent's comment, and update 09-Feb-2007 - ammended script following Kevin's comment] A system startup/shutdown shell script I just used that … Continue reading →

Posted in Installs, Scripts, SQL*Net | 6 Comments

Script run against every running database, listing total oracle memory (sga+pga)

Posted on 11 January 2007 by Andrew Fraser

This shell script runs against every database that is up and running (has an entry in the ps list). Which I think is better than searching through oratab (since not all of those may be up and running, esp. on … Continue reading →

Posted in Performance tuning, Scripts | 1 Comment