sed change entire line

Here I use sed to replace the entire 2nd line in a lot of files with a new 2nd line. The shell script: for fle in `ls *.msg` do # copy file first so as can keep permissions and ownership identical cp -p $fle test_$fle # replace entire 2nd line with a new 2nd line […]

Read More sed change entire line
June 8, 2011

Remove dba_2pc_pending records

Old entries in dba_2pc_pending can be removed by ‘rollback force’ or if that fails, with a purge: set pages 9999 spool go.tmp select ‘rollback force ”’||local_tran_id||”’ ;’ from dba_2pc_pending ; select ‘exec dbms_transaction.purge_lost_db_entry(”’||local_tran_id||”’ )’ , ‘commit;’ from dba_2pc_pending ; spool off

Read More Remove dba_2pc_pending records
December 20, 2010

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 the -delete option, so instead: # Delete files older than 15 minutes find /ORA_DISK/redoarch/ -name […]

Read More Find delete old files
October 29, 2010

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 spool go.tmp select ‘@2 ‘|| decode(type , ‘MATERIALIZED VIEW’ , ‘MATERIALIZED_VIEW’ , type) ||’ ‘||name||’ […]

Read More Select out all code refererencing some tables
August 26, 2010

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) ) ; create index af1 on af ( mycol ) ; alter index af1 unusable ; insert […]

Read More Make indexes unusable before big insert
July 30, 2010

2 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 table ‘||owner||’.’||segment_name||’ move tablespace users1;’ from dba_segments where tablespace_name = ‘USERS’ and segment_type = ‘TABLE’ […]

Read More Move objects including lobs, xml to new tablespace with dynamic SQL
May 3, 2010

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 ‘||c1.owner||’.’||c1.table_name into cnt ; dbms_output.put_line(c1.table_name||’,’||cnt) ; end loop ; end ; /

Read More PL/SQL to count from all tables in a schema
March 12, 2010

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 in production you would probably want to modify this to keep files for longer. For […]

Read More shell script to clean old oracle trace and log files
February 26, 2010

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 tables are kept in the same tablespace. Process for doing partition exchange is like this: […]

Read More schema moves by the magic of partition exchange
April 17, 2009

One Comment