Home » SQL & PL/SQL » SQL & PL/SQL » Archiving of Table data
Archiving of Table data [message #190685] Thu, 31 August 2006 14:36 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
I have a large table named SEOSDATA that needs to be archived on a regular basis based on a date column. All data for year 2005 will go to a table SEOSDATA_BKP_2005, for 2004 SEOSDATA_BKP_2004 and so on. The current year data will be in SEOSDATA table.

I was thinking of using Bulk collect so as to reduce the round trip overhead. How do we substitute the table name dynamically based on year field of the column. Below is the code I have written, but I am not able to do dynamic substition based on a criteris..
The procedure name is START_ARCHIVE

-----

create or replace package body SEODATA_BKP
as
procedure LOG_DATA(v_MSG in VARCHAR2, v_STATUS in VARCHAR2)
is

v_TERMINAL varchar2(200);
v_OSUSER varchar2(15);
n_SEQ number;
pragma autonomous_transaction;

begin
select
sys_context('userenv','os_user'),
sys_context('userenv','host'),
audit_log_seq.nextval
into
v_OSUSER,
v_TERMINAL,
n_SEQ
from
dual;

insert into AUDIT_LOG
(OPERATION, SOURCE_SCHEMA, AUDIT_ACTION, TIME_STAMP, TERMINAL, OS_USER, SEQ_NO, STATUS
)
values
('USER', USER, v_MSG, SYSDATE, v_TERMINAL, v_OSUSER, n_SEQ,v_STATUS);

commit;
end;


procedure START_ARCHIVE
is

n_START number := dbms_utility.get_time;
v_ENTRYID SEOSDATAARRAY;
v_DOMAINNAME SEOSDATAARRAY;
v_USERNAME SEOSDATAARRAY;
v_EVENTTYPE SEOSDATAARRAY;
v_LOGNAME SEOSDATAARRAY;
v_TIMSTAMP SEOSDATAARRAY;
v_SOURCE SEOSDATAARRAY;
v_COMPUTERNAME SEOSDATAARRAY;
v_EVENTID SEOSDATAARRAY;
v_EVENTCATEGORY SEOSDATAARRAY;
v_SEARCHSTRINGS SEOSDATAARRAY;
v_MSGTEXT SEOSDATAARRAY;
n_CURRYEAR NUMBER := substr(SYSDATE,8,9);

begin

--open cu_SEOSDATA;
--loop
--ar_DATA := SEOSDATAARRAY();
select * bulk collect into v_ENTRYID,v_DOMAINNAME,v_USERNAME,v_EVENTTYPE,v_LOGNAME,v_TIMSTAMP,
v_SOURCE,v_COMPUTERNAME,v_EVENTID,v_EVENTCATEGORY,v_SEARCHSTRINGS,v_MSGTEXT
from SEOSDATA;

forall i in 1 .. v_ENTRYID.count
insert
when (substr(TIMSTAMP,8,9) < n_CURRYEAR)then
into SEOSDATA_BKP
(ENTRYID,
DOMAINNAME,
USERNAME,
EVENTTYPE,
LOGNAME,
TIMSTAMP,
SOURCE,
COMPUTERNAME,
EVENTID,
EVENTCATEGORY,
SEARCHSTRINGS,
MSGTEXT
)
values (ENTRYID,
DOMAINNAME,
USERNAME,
EVENTTYPE,
LOGNAME,
TIMSTAMP,
SOURCE,
COMPUTERNAME,
EVENTID,
EVENTCATEGORY,
SEARCHSTRINGS,
MSGTEXT
)
select v_ENTRYID(i) ENTRYID,
v_DOMAINNAME(i) DOMAINNAME,
v_USERNAME(i) USERNAME,
v_EVENTTYPE(i) EVENTTYPE,
v_LOGNAME(i) LOGNAME,
v_TIMSTAMP(i) TIMSTAMP,
v_SOURCE(i) SOURCE,
v_COMPUTERNAME(i) COMPUTERNAME,
v_EVENTID(i) EVENTID,
v_EVENTCATEGORY(i) EVENTCATEGORY,
v_SEARCHSTRINGS(i) SEARCHSTRINGS,
v_MSGTEXT(i) MSGTEXT
from
DUAL;


LOG_DATA(v_ENTRYID.count ||' rows successfully for backed up','S');

-- exit when cu_SEOSDATA%notfound;
--end loop;
--close cu_SEOSDATA;

dbms_output.put_line('The archiving of SEOSDATA table completed in '||round((dbms_utility.get_time-n_START)/100,2)||' secs');
end;

end SEODATA_BKP;


Please help.
Re: Archiving of Table data [message #190686 is a reply to message #190685] Thu, 31 August 2006 14:57 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
The best way to copy the data is by using a single SQL statemet - possibly using the "append" hint. Also - if you partition the tables - you can smply exchange partitions between 2 tables and avoid having to insert/delete.

Search for "exchange partition" and see this link:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6407993912330
Re: Archiving of Table data [message #190690 is a reply to message #190686] Thu, 31 August 2006 15:05 Go to previous message
yerics
Messages: 89
Registered: August 2006
Member
Yes, I went through that. It advocates CTAS or INSERT INTO -- SELECT --- WHERE --.

The problem I have is

a) The client does not want it to be a sql statement. He wants it as part of the procedure/package so that he can have tighter control.

b) Since there are lot of rows, will not INSERT INTO slow down things, because it has to search for each record for year field and based on that populate the table.


Previous Topic: Trigger
Next Topic: Storing Alphanumeric values
Goto Forum:
  


Current Time: Tue Dec 06 08:51:33 CST 2016

Total time taken to generate the page: 0.10794 seconds