ORACLE Tuning Tips

1. move the redo log to separated disk.

2. Delete huge table, but need to keep some records

accept UPASS prompt 'Password for SUMMA user->`'hide
accept CALIAS prompt 'Connect string->`'

set echo on
set serveroutput on
spool renamest.log
disconnect;
connect summa/&UPASS@&CALIAS

rem Renaming tables

alter table st_product_new rename to st_product_new_old;
alter table st_customer_new rename to st_customer_new_old;
alter table st_employee_clock_in_new rename to st_employee_clock_in_new_old;
alter table st_product_new_ren rename to st_product_new;
alter table st_customer_new_ren rename to st_customer_new;
alter table st_employee_clock_in_new_ren rename to st_employee_clock_in_new;
exit;

accept UPASS prompt 'Password for SUMMA user->`'hide
accept CALIAS prompt 'Connect string->`'

set echo on
set serveroutput on
spool addingstdata.log
disconnect;
connect summa/&UPASS@&CALIAS

rem Adding data to tables by direct-path insert
rem not intended for normal data inserting

insert /*+append*/ into st_product_new
select * from st_product_new_old o
where o.transaction_status_id=1;
commit;

insert /*+append*/ into st_customer_new
select * from st_customer_new_old o
where o.transaction_status_id=1;
commit;

insert /*+append*/ into st_employee_clock_in_new
select * from st_employee_clock_in_new_old o
where o.transaction_status_id=1;
commit;

insert /*+append*/ into st_product_new
select * from st_product_new_old o
where o.last_modified > (sysdate-7);
commit;

insert /*+append*/ into st_customer_new
select * from st_customer_new_old o
where o.last_modified > (sysdate-7);
commit;

insert /*+append*/ into st_employee_clock_in_new
select * from st_employee_clock_in_new_old o
where o.last_modified > (sysdate-7);
commit;

exit;