Category Archives: Scripts

shell script to clean old oracle trace and log files

Posted on 26 February 2010 by Andrew Fraser

This code cleans up old trace files, log files, core dumps, etc. It is designed to be run from cron. It takes a somewhat brutal approach by deleting files after just 7 days – good for e.g. dev/test servers, but … Continue reading →

Posted in Scripts | 3 Comments

schema moves by the magic of partition exchange

Posted on 17 April 2009 by Andrew Fraser

Here’s an example of how to use partition exchange to move partitions or even entire unpartitioned tables from one schema to another. Is mean to be very fast and generate very little redo. Even more so if the partitions and … Continue reading →

Posted in Performance tuning, Scripts, Space | 1 Comment

Move script for ORA-03297: file contains used data beyond requested RESIZE value

Posted on 5 March 2009 by Andrew Fraser

Attempting to shrink files which have a lot of empty space can fail with this error: SQL> alter database datafile ‘/ora1data/TEST/TEST_ts_data1_f1.dbf’ resize 2g ; ORA-03297: file contains used data beyond requested RESIZE value Provided you can get an outage, one … Continue reading →

Posted in Scripts | Leave a comment

Using tar with compress or gzip

Posted on 5 March 2009 by Andrew Fraser

1) To tar files up into a tarball: tar cvfp – file1 file2 file3 > tarfile.tar And to untar: tar xvfp tarfile.tar 2) Now, same thing but with compress added in: tar cvfp – file1 file2 file3 | compress > … Continue reading →

Posted in Scripts | Leave a comment

Installing dbms_profiler

Posted on 22 June 2007 by Andrew Fraser

I used the following to install dbms_profiler, using a central set of system owned tables rather than each user having its own tables: conn / as sysdba @?/rdbms/admin/profload.sql conn system/password alter user system default tablespace users ; — [or any … Continue reading →

Posted in Performance tuning, Scripts | Leave a comment

What SQL and sessions are running?

Posted on 20 April 2007 by Andrew Fraser

GUIs like TOAD and Enterprise Manager can show this better, but they’re not always to hand. Every session active now or within the last minute is shown. set pages 9999 lines 132 col username form a20 col osuser form a20 … Continue reading →

Posted in Performance tuning, Scripts | 3 Comments

Bug? with wrong results from all_objects in stored plsql procedures

Posted on 2 March 2007 by Andrew Fraser

Wouldn’t it be nice if sql code produced the same result, regardless of whether it is inside a stored procedure or is a standalone plsql block? But that’s not what happens when the all_ views are used. Run this code … Continue reading →

Posted in Bugs, Scripts | 3 Comments

10046 tracing in another session

Posted on 14 February 2007 by Andrew Fraser

To switch 10046 tracing on in another session (first setting on timed statistics and making max_dump_file_size very large): set pages 9999 verify off col serial new_value serial noprint select serial# serial from v$session where sid = &1 ; exec sys.dbms_system.set_bool_param_in_session(&1,&serial,’timed_statistics’,true) … Continue reading →

Posted in Performance tuning, Scripts | Leave a comment

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