Modify oracle table structure with low impact

--------------------------------------------------------------------------------
-- Goal: To modify a table structure with low impact
-- from DATE to TIMESTAMP assuming T1 table is a multi million row table
-- and T1 table is an -- highly available table.
--------------------------------------------------------------------------------

RMATTE@hhp3 SQL> drop table t1 purge;
Table dropped.

RMATTE@hhp3 SQL> create table t1 as select * from dba_objects;
Table created.

RMATTE@hhp3 SQL> create index t1_idx_01 on t1(object_id);
Index created.

RMATTE@hhp3 SQL> delete from t1 where object_id is NULL;
22 rows deleted.

RMATTE@hhp3 SQL> commit;
Commit complete.

RMATTE@hhp3 SQL> alter table t1 add constraint pk_t1_01 primary key(object_id);
Table altered.

RMATTE@hhp3 SQL> desc t1;
Name                                      Null?    Type
- -------- ----------------------------
OWNER                                              VARCHAR2(30)
OBJECT_NAME                                        VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                 NOT NULL NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(19)
CREATED                                            DATE
LAST_DDL_TIME                                      DATE
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)


--------------------------------------------------------------------------------
-- Create a interim table that matches your target table requirements.
-- You can specify partition option or different storage parameters.
--------------------------------------------------------------------------------
CREATE TABLE RMATTE.T1_INTERIM
(
 OWNER           VARCHAR2(30 BYTE),
 OBJECT_NAME     VARCHAR2(128 BYTE),
 SUBOBJECT_NAME  VARCHAR2(30 BYTE),
 OBJECT_ID       NUMBER,
 DATA_OBJECT_ID  NUMBER,
 OBJECT_TYPE     VARCHAR2(19 BYTE),
 CREATED         TIMESTAMP(6),
 LAST_DDL_TIME   TIMESTAMP(6),
 TIMESTAMP       VARCHAR2(19 BYTE),
 STATUS          VARCHAR2(7 BYTE),
 TEMPORARY       VARCHAR2(1 BYTE),
 GENERATED       VARCHAR2(1 BYTE),
 SECONDARY       VARCHAR2(1 BYTE)
)
/

--------------------------------------------------------------------------------
-- Check whether you can redefine your table or not?
--------------------------------------------------------------------------------
RMATTE@hhp3 SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('RMATTE', 'T1', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL procedure successfully completed.

--------------------------------------------------------------------------------
-- This function is required because to_timestamp parameter is not compatible with dbms_redifinition  package.
--------------------------------------------------------------------------------
create or replace function my_to_timestamp(p_date date) return timestamp is
 begin
 return to_timestamp(p_date);
 end;
 /

Function created.
 
--------------------------------------------------------------------------------
-- Start the redefinition process. It will move all records from source table to interim table(target table).
--------------------------------------------------------------------------------
RMATTE@hhp3 SQL> BEGIN
 2  dbms_redefinition.start_redef_table('RMATTE', 'T1','T1_INTERIM',
 3   'OWNER,
 4   OBJECT_NAME,
 5   SUBOBJECT_NAME,
 6   OBJECT_ID,
 7   DATA_OBJECT_ID,
 8 OBJECT_TYPE,
 9   my_to_timestamp(CREATED) CREATED,
10   11   my_to_timestamp(LAST_DDL_TIME) LAST_DDL_TIME,
12   13   TIMESTAMP,
14   STATUS,
15   TEMPORARY,
16   GENERATED,
17   SECONDARY',DBMS_REDEFINITION.CONS_USE_PK);
18  END;
19  /

PL/SQL procedure successfully completed.

--------------------------------------------------------------------------------
-- Move all dependent objects from source table to interim table (Target table).
-- It will create the depended objects with TMP names.
--------------------------------------------------------------------------------
RMATTE@hhp3 SQL> DECLARE
 2   RETVAL NUMBER(5);
 3  BEGIN
 4   dbms_redefinition.copy_table_dependents('RMATTE', 'T1', 'T1_INTERIM', copy_indexes=>dbms_redefinition.cons_orig_params, copy_triggers=>TRUE, copy_constraints=>TRUE,copy_privileges=>TRUE, num_errors=>RETVAL);
 5   dbms_output.put_line(RETVAL);
 6  END;
 7  /

0
PL/SQL procedure successfully completed.

--------------------------------------------------------------------------------
-- Complete the redefinition process.
-- It flips the sources table name/dependent objects with interim tablename/dependent objects (target table).
--------------------------------------------------------------------------------
RMATTE@hhp3 SQL> EXEC dbms_redefinition.finish_redef_table('RMATTE', 'T1', 'T1_INTERIM');

PL/SQL procedure successfully completed.

RMATTE@hhp3 SQL> DESC T1;
Name                                      Null?    Type
- -------- ----------------------------
OWNER                                              VARCHAR2(30)
OBJECT_NAME                                        VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                          NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(19)
CREATED                                            TIMESTAMP(6)
LAST_DDL_TIME                                      TIMESTAMP(6)
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)

RMATTE@hhp3 SQL> DESC T1_INTERIM;
Name                                      Null?    Type
- -------- ----------------------------
OWNER                                              VARCHAR2(30)
OBJECT_NAME                                        VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                 NOT NULL NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(19)
CREATED                                            DATE
LAST_DDL_TIME                                      DATE
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)

RMATTE@hhp3 SQL> DROP TABLE T1_INTERIM;
Table dropped.

SQL loader using EXTERNAL_TABLE option (ravinder_matte.postit@blogger.com)

SQL loader testing

PERFORMANCE BENEFITS USING EXTERNAL_TABLE=EXECUTE/GENERATE_ONLY OPTION IN SQL LOADER

-- PREPARATION FOR TEST START HERE --

spool Million_Rec_File.txt

begin

for i in 1..1000000

loop

       dbms_output.put_line(round(dbms_random.value(1,1000))||chr(9)||dbms_random.string('U',10)||chr(9)||round(dbms_random.value(10000,12000))||chr(9)||dbms_random.string('X',20));

end loop;

end;

/

spool off;

create table t1 (no number,ename varchar2(20), sal number, address varchar2(30));

alter table t1 parallel 16;

oracle@bond[ORA10GR2] /export/home/oracle/>cat Million_Rec_File.ctl

load data

infile '/export/home/oracle//Million_Rec_File.txt'

into table t1

fields terminated by "|" optionally enclosed by '"'

( no, ename, sal,  address)

-- PREPARATION FOR TEST ENDS HERE --

-- TESTING START HERE --

Case - 1:

RMATTE@ORA10GR2 SQL> truncate table t1;

Table truncated.

RMATTE@ORA10GR2 SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

oracle@bond[ORA10GR2] /export/home/oracle/>

time sqlldr rmatte/<password>@ORA10GR2 control=Million_Rec_File.ctl silent=all

real    0m16.664s

user    0m2.297s

sys     0m1.208s

Case - 2:

RMATTE@ORA10GR2 SQL>  truncate table t1;

Table truncated.

RMATTE@ORA10GR2 SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

oracle@bond[ORA10GR2] /export/home/oracle/>

time sqlldr rmatte/<password>@ORA10GR2 control=Million_Rec_File.ctl silent=all  rows=1000

real    0m9.349s

user    0m2.100s

sys     0m0.547s

RMATTE@ORA10GR2 SQL> select count(*) from t1;

 COUNT(*)

----------

  1000000

Case - 3:

RMATTE@ORA10GR2 SQL> truncate table t1;

Table truncated.

RMATTE@ORA10GR2 SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

oracle@bond[ORA10GR2] /export/home/oracle/>

time sqlldr rmatte/<password>@ORA10GR2 control=Million_Rec_File.ctl silent=all parallel=true rows=1000

real    0m8.162s

user    0m2.116s

sys     0m0.603s

RMATTE@ORA10GR2 SQL> select count(*) from t1;

 COUNT(*)

----------

  1000000

Case - 4:

RMATTE@ORA10GR2 SQL>  truncate table t1;

Table truncated.

RMATTE@ORA10GR2 SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

oracle@bond[ORA10GR2] /export/home/oracle/>time

sqlldr rmatte/<password>@ORA10GR2 control=Million_Rec_File.ctl external_table=execute

SQL*Loader: Release 10.2.0.4.0 - Production on Mon Mar 21 14:19:38 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

real    0m2.499s

user    0m0.029s

sys     0m0.034s

RMATTE@ORA10GR2 SQL> select count(*) from t1;

 COUNT(*)

----------

  1000000

-- TESTING ENDS HERE --

OBSERVATIONS:

Convention SQL Loader	Conventional SQL Loader with Rows=1000	Conventional SQL Loader with rows=1000 and parallel=TRUE	SQL Loader with external table	Improvements using external_table=execute option.
real    0m16.664s user    0m2.297s sys     0m1.208s	real    0m9.349s user    0m2.100s sys     0m0.547s	real    0m8.162s user    0m2.116s sys     0m0.603s	real    0m2.499s user    0m0.029s sys     0m0.034s	~ >3.26 times faster.

Google Docs makes it easy to create, store and share online documents, spreadsheets and presentations.

Disable / Enable all constraints and trigger for a given schema.

Following 2 procedures will allow you to enable/disable all constraints and triggers for a given schema.

Usage:
EXEC DISABLE_ALL_CONS_TRGS('RMATTE');
exec ENABLE_ALL_CONS_TRGS('RMATTE');


CREATE OR REPLACE PROCEDURE DISABLE_ALL_CONS_TRGS (V_OWNER in varchar2 )
IS
BEGIN
for i IN (select owner,table_name, constraint_name from dba_constraints where constraint_type ='R'and status = 'ENABLED' and owner=v_owner)
loop
dbms_output.put_line ('alter table ' ||i.owner||'.'||i.table_name|| ' disable constraint ' ||i.constraint_name||'.');
EXECUTE IMMEDIATE 'alter table ' ||i.owner||'.'||i.table_name|| ' disable constraint ' ||i.constraint_name;
end loop i;

for i IN (select owner,table_name, constraint_name from dba_constraints where status = 'ENABLED' and owner=V_OWNER)
loop
dbms_output.put_line ('alter table ' ||i.owner||'.'||i.table_name|| ' disable constraint ' ||i.constraint_name||'.');
EXECUTE IMMEDIATE 'alter table ' ||i.owner||'.'||i.table_name|| ' disable constraint ' ||i.constraint_name;
end loop i;

for i IN (select distinct owner,table_name from dba_triggers where status = 'ENABLED' and owner=V_OWNER)
loop
dbms_output.put_line('alter table ' ||i.owner||'.'||i.table_name|| ' disable ALL TRIGGERS ;');
EXECUTE IMMEDIATE 'alter table ' ||i.owner||'.'||i.table_name|| ' disable ALL TRIGGERS ';
end loop i;

EXCEPTION
WHEN OTHERS THEN
     raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/

CREATE OR REPLACE PROCEDURE ENABLE_ALL_CONS_TRGS (V_OWNER in varchar2 )
IS
BEGIN

for i IN (select owner,table_name, constraint_name from dba_constraints where status = 'DISABLED' and owner=V_OWNER)
loop
DBMS_OUTPUT.PUT_LINE ('alter table ' ||i.owner||'.'||i.table_name|| ' enable novalidate constraint  ' ||i.constraint_name||';');
EXECUTE IMMEDIATE 'alter table ' ||i.owner||'.'||i.table_name|| ' enable novalidate constraint  ' ||i.constraint_name;
end loop i;

for i IN (select distinct owner,table_name from dba_triggers where status = 'DISABLED' and owner=V_OWNER)
loop
dbms_output.put_line ('alter table ' ||i.owner||'.'||i.table_name|| ' enable ALL TRIGGERS ;');
EXECUTE IMMEDIATE 'alter table ' ||i.owner||'.'||i.table_name|| ' enable ALL TRIGGERS ';
end loop i;

EXCEPTION
WHEN OTHERS THEN
     raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/