Category Archives: Scripts

Find delete old files

# Delete files older than 15 minutes find /ORA_DISK/redoarch/ -name ‘arch_*.dbf’ -mmin +15 -delete #Delete files older than 2 days 50 8 * * * find /ORA_DISK/redoarch/ -name ‘arch_*.dbf’ -mtime +2 -delete Old versions of unix find do not have … Continue reading

Posted in Linux, Rman, Scripts | Leave a comment

HTML formatted emails with utl_smtp

HTML format emails allows images, fonts, colours, hyperlinks. It can be done with utl_smtp (after the jump) and with utl_mail

Posted in Email, PL/SQL, Scripts | Tagged | Leave a comment

Select out all code refererencing some tables

This allows you to get code listings for all code (procedures, views, materialized views, etc.) that reference particular tables: set long 200000 pages 0 verify off lines 131 feed off column txt format a121 word_wrapped column spoolfile new_value spoolfile noprint … Continue reading

Posted in Scripts | Leave a comment

Make indexes unusable before big insert

Make indexes unusable before doing a big insert, then rebuild them at the end, is faster than insert with indexes in place and no risk of forgetting to recreate a dropped index: create table af ( mycol varchar2(100) ) ; … Continue reading

Posted in Scripts | 3 Comments

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

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

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

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 | 4 Comments

schema moves by the magic of partition exchange

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

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

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