Object Management Scripts

Reconstruct DDL for triggers

set feedback off set head off set echo off set recsep off set pages 50000 set long 5000 set lines 200 column trigger_body format a9999 wrap word; — Write the script to a file, otherwise it is useless spool mktrig.run — Write the SQL stastements to rebuild the triggers select ‘create or replace trigger ‘…

Reconstruct DDL for snapshot logs

conn /@&1 set feed off echo off pagesize 50000 trimspool on head off line 500 col global_name new_value global_name select global_name from global_name / spool snap_&global_name..sql select ‘drop snapshot log on ‘||log_owner||’.’||master||’;’ from sys.dba_snapshot_logs / select ‘create snapshot log on ‘||log_owner||’.’||master|| ‘ storage (initial 1M next 1M maxextents unlimited pctincrease 0);’ from sys.dba_snapshot_logs / select…

Reconstruct DDL for indexes

set arraysize 1 set echo off set heading off set feedback off set verify off set pagesize 0 set linesize 79 define 1 = &&SCHEMA_NAME spool ind_&&SCHEMA_NAME set termout off col y noprint col x noprint col z noprint select ‘rem **** Create Index DDL for ‘||chr(10)|| ‘rem **** ‘||username||””||’s tables’||chr(10)||chr(10) from dba_users where username…

Sets an existing sequence to a value of choice without dropping it

CREATE OR REPLACE PROCEDURE set_sequence (seqname IN VARCHAR2, newnumber IN INTEGER) as curr_val INTEGER; curr_inc INTEGER; curr_min INTEGER; BEGIN SELECT INCREMENT_BY, MIN_VALUE into curr_inc, curr_min from user_sequences where sequence_name = seqname; EXECUTE IMMEDIATE ‘ALTER SEQUENCE ‘ ||seqname||’ MINVALUE ‘ || LEAST((newnumber – curr_inc – 1) , curr_min) ; EXECUTE IMMEDIATE ‘SELECT ‘ ||seqname ||’.nextval FROM…

Drop a column from a table

— drop table x — / create table x(a date, b date, c date) / — Drop column B: alter table x set unused column b — Mark col as UNUSED / select * from sys.dba_unused_col_tabs / alter table x drop unused columns / — Drop column C (different method): alter table x drop column…

Copy table from one database to another

SET SERVEROUTPUT ON PROMPT Enter the table’s name you want to copy DEFINE tname = &table_name PROMPT PROMPT Enter the FROM database id DEFINE dbname = &database_id PROMPT PROMPT Enter the FROM user id DEFINE uname = &user_id SPOOL TAB_COPY.SQL DECLARE v_table_name VARCHAR2( 30) := ‘&&tname’; v_user_name VARCHAR2( 30) := ‘&&uname’; v_db_name VARCHAR2( 30) :=…

Re-build all non-system indexes on-line

set serveroutput on size 1000000 set line 1024 feed off trimspool on echo off spool index_rebuild_run.sql declare INDEX_TABLESPACE_NAME constant varchar2(30) := ‘INDX’; — Set to your Index TS!!! begin for c1 in ( select i.owner, i.index_name, s.tablespace_name, i.initial_extent, i.next_extent, i.min_extents, i.max_extents, i.pct_increase, s.bytes from sys.dba_segments s, sys.dba_indexes i where s.segment_type = ‘INDEX’ and i.index_name =…