Category Archives: Scripts

Move objects including lobs, xml to new tablespace with dynamic SQL

Posted on 3 May 2010 by Andrew Fraser

Here objects are moved from tablespace users to tablespace users1: set pages 9999 lines 132 spool m2.sql select ‘alter table ‘||owner||’.'||table_name||’ move lob(‘||column_name||’) store as ( tablespace users1);’ from dba_lobs where tablespace_name = ‘USERS’ order by 1 / select ‘alter … Continue reading →

Posted in Scripts, Space | Leave a comment

PL/SQL to count from all tables in a schema

Posted on 12 March 2010 by Andrew Fraser

Replace SYSTEM with the name of the schema you are interested in: set serverout on size 999999 declare cnt number ; begin for c1 in (select owner, table_name from all_tables where owner = ‘SYSTEM’) loop execute immediate ‘select count(1) from … Continue reading →

Posted in Scripts | Leave a comment

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