Scripts – Page 3 – Andrew Fraser DBA

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’ […]

May 3, 2010
Posted in Scripts, Space

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 ; /

March 12, 2010
Posted in Scripts

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 […]

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

April 17, 2009
Posted in Performance tuning, Scripts, Space
One 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 fix for this is to temporarily move segments to another tablespace using a scripts like […]

March 5, 2009
Posted in Scripts

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 > tarfile.tar.Z zcat tarfile.tar.Z | tar xvfp – 3) And, the same thing using gzip rather […]

March 5, 2009
Posted in Scripts

Installing dbms_profiler

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 other reasonable tablespace] @?/rdbms/admin/proftab.sql GRANT all ON plsql_profiler_runnumber TO PUBLIC; GRANT all ON plsql_profiler_data TO […]

June 22, 2007
Posted in Performance tuning, Scripts

What SQL and sessions are running?

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 col program form a50 col minutes form 999 alter session set nls_date_format = ‘Dy DD-Mon-YYYY […]

April 20, 2007
Posted in Performance tuning, Scripts
3 Comments

Bug? with wrong results from all_objects in stored plsql procedures

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 to see the anomaly for yourself: set serverout on declare var1 number ; begin select […]

March 2, 2007
Posted in Bugs, Scripts
3 Comments

10046 tracing in another session

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) exec sys.dbms_system.set_int_param_in_session(&1,&serial,’max_dump_file_size’,999999999) exec sys.dbms_system.set_ev(&1,&serial,10046,8,”) And to switch it off: set pages 9999 verify off col […]

February 14, 2007
Posted in Performance tuning, Scripts