Borndba

We have an requirement, where we need to purge portion of data from huge table. After much discussion, we have decided go to like
1. create temp table
2. populate only relevant data from main table to temp table
3. drop main table.
4. rename temp table to main table and recreate missing indexes.

there are various approach to do this. But instead we have gone with something like split the big table in to logical split using rowid and with each logical split populate the temp table parallel.

create table scuser.tab1_bk as select * from scuser.tab1 where 1=2;

alter table scuser.tab1_bk nologging;

truncate table scuser.tab1_bk;

Drop table split_rowid purge;

CREATE TABLE split_rowid as select distinct grp,
first_value(relative_fno) over
(partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) lo_fno,
first_value(block_id ) over
(partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) lo_block,
last_value(relative_fno) over
(partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) hi_fno,
last_value(block_id+blocks-1) over
(partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) hi_block,
sum(blocks) over (partition by grp) sum_blocks
from (
select relative_fno,
block_id,
blocks,
trunc( (sum(blocks) over (order by relative_fno, block_id)-0.01) /
(sum(blocks) over ()/&chunk) ) grp
from dba_extents
where segment_name = ('TAB1')
and owner = '&owner' order by block_id
)
/

Create or replace procedure purge_clock(p_no number,lo_rowid rowid,hi_rowid rowid) as
type array is table of scuser.tab1_bk%rowtype;
l_data array;
cursor c is select /+* first_rows */ * from tab1 a where (exists
(select 1 from probsummarym1
where probsummarym1.numberprgn = a.key_char
and a.type = 'problem')
or a.datetime_last_upd > (sysdate-3)
or type <> 'problem')
and substr(name,0,7) <> 'JPASRPT' and rowid between lo_rowid and hi_rowid;;

lv_cnt number:=0;
lv_cnt1 number:=0;
BEGIN
open c;
loop
fetch c bulk collect into l_data LIMIT 10000;
lv_cnt:=lv_cnt+1;
lv_cnt1 := lv_cnt*10000;
dbms_application_info.set_client_info(p_no||' of Clock:'||lv_cnt1);
forall i in 1 .. l_data.count
Insert into scuser.tab1_bk values l_data(i);
COMMIT;
exit when c%notfound;
END LOOP;
CLOSE c;
commit;
END;
/

alter system set job_queue_processes=20;

set serveroutput on;
Declare
cursor c1 is
select rownum no,
dbms_rowid.rowid_create(1,
(select data_object_id from dba_objects where object_name='tab1' and object_type='TABLE'),lo_fno,lo_block,0) lo_rowid,
dbms_rowid.rowid_create(1,
(select data_object_id from dba_objects where object_name='tab1' and object_type='TABLE'),hi_fno,hi_block,32767) hi_rowid
from split_rowid order by no;

lv_job number;
begin
for i in c1 loop
dbms_job.submit(lv_job,'purge_clock('||i.no||','||chr(39)||i.lo_rowid||chr(39)||','||chr(39)||i.hi_rowid||chr(39)||');');
dbms_output.put_line('Job#'||lv_job);
commit;
end loop;
commit;
end;
/

convert this post to pdf.

This is undocumented sp to read the logfile.

select * from fn_dblog(null,null)

This is one more undocumeted sp to find out number of VLF.
DBCC LOGINFO

Status 2 => VLF in use
Status 0 => VLF not in use.

convert this post to pdf.

If you have hundreds of DB it will be usefull to have all the tns entry in your desktop. So for small task you don’t have to get into server. This will be saving lot of times. There are various way to create tns entry. The approach what i have used is from OEM.
If your shop is monitored by OEM, then you can use below script to generate tns entry and put it in your tns_admin location.
This procedure needs be compiled in sysman schema.

create or replace procedure rpt_tns_generation as
cursor c1 is
select distinct a.target_guid from mgmt_target_properties a,mgmt_targets b
where a.target_guid = b.target_guid
and b.target_type=’oracle_database’;

lv_sid varchar2(30);
lv_host varchar2(100);
lv_port number;
lv_service_name varchar2(100);
begin
for i in c1 loop
select property_value
into lv_sid
from mgmt_target_properties
where property_name=’SID’
and target_guid=i.target_guid;

select property_value
into lv_host
from mgmt_target_properties
where property_name=’MachineName’
and target_guid=i.target_guid;

select property_value
into lv_port
from mgmt_target_properties
where property_name=’Port’
and target_guid=i.target_guid;

dbms_output.put_line(lv_sid||’='||chr(10)||’(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=’||lv_host||’)(PORT=’||lv_port||’)))(CONNECT_DATA=(sid=’||lv_sid||’)))’);

end loop;

exception
when others then
dbms_output.put_line(‘error for ‘||sqlerrm);
end;
/

convert this post to pdf.