| Archiving Tables [message #309987] |
Sun, 30 March 2008 22:11  |
Lucky A
Messages: 68
Registered: October 2007
|
Member |
|
|
Hi,
Two major tables within my database are over two million rows and growing fast. Those tables need to be archived and the archived data to be readily available when the old information is needed. In one of the tables, the client wants to keep a year's worth of data and the other should have nine months worth of data.
I also have to schedule a crontab job to run every month to keep a year or nine month's data within their respective tables.
I would really appreciate any valuable input to get the job done ASAP.
I am running 9.2.0.4 on Windows 2000
Thanks,
Lucky
|
|
|
|
|
|
| Re: Archiving Tables [message #310005 is a reply to message #309989] |
Sun, 30 March 2008 23:24  |
Lucky A
Messages: 68
Registered: October 2007
|
Member |
|
|
Thanks for your suggestion. But the client wants a table created called HISTORY_TABLE which will store the older data that is more than a year.
Can you please lead me to the best possible method to get this achieved.
Thanks Again,
Lucky
|
|
|
|
|
|
|
|
| Re: Archiving Tables [message #311516 is a reply to message #309987] |
Fri, 04 April 2008 12:23 |
Lucky A
Messages: 68
Registered: October 2007
|
Member |
|
|
The requirements of my previous posting have change. Please I need your input on how to accomplish this task.
There are two tables that have over 2 millions rows each. The client wants to reduce the size of the tables, but wants the data readily available online when needed.
TABLE_1 should have 12 months worth of data.
TABLE_2 should have 90 days worth of data.
They don't want to partition the tables. I believe that would be the ideal remedy for the client's request. However, I have to create a separate table called HISTORY_T for both Primary tables to archive the data from the primary tables to their respective HISTORY_T tables.
After I've accomplished the archiving of the primary tables, going forward I need to put together a script to perform what I described above. The script should be scheduled to run monthly. I am running 9.2.0.4 on Windows 2000.
I'll appreciate a sample script or command.
Thanks,
Lucky
|
|
|
|
|
|
| Re: Archiving Tables [message #311592 is a reply to message #311519] |
Fri, 04 April 2008 21:48 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If you cant or wont use partitioning, then it doesn't really matter how you do it, because it will be slow and it will cause subsequent queries to be slow too.
If you partition it is simple.
- Partition both the current table and the history table.
- Create a new empty partition in the history table for "new" history data.
- Create a new empty non-partitioned table identical to the partitioned tables - same indexes and constraints.
- Use exchange partition to swap the empty table with the stale partition in the current table.
- Use EXCHANGE PARTITION to swap the stale data with the new partition you created in the history table.
- Drop the empty partition in the current table.
All this should take a couple of seconds unless you have global indexes.
Ross Leishman
|
|
|
|
| Re: Archiving Tables [message #314134 is a reply to message #309987] |
Tue, 15 April 2008 19:05 |
Lucky A
Messages: 68
Registered: October 2007
|
Member |
|
|
Hi,
Any suggestion or a way to improve the below script I am working on would be of value. I have two tables:
TABLE_A has over 6,000,000 rows.
TABLE_B has over 190,000,000 rows.
Both tables need to have a correspondence ARCHIVE table to store all of the data with the database.
TABLE_A needs to keep 3 months worth of data and data older than 3 months gets purged to the ARCHIVE table monthly.
TABLE_B needs to keep 12 months of data and any data more than 12 months needs to be purged to the ARCHIVE table monthly.
Partition is not to be considered but could have been a better option. However since the tables cannot be partitioned, below is what I have put together and need suggestions.
create table TABLE_A_ARCHIVE
TABLESPACE TARPACE
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOLOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING
AS SELECT * from TABLE_A where event_time ADD_MONTHS (SYSDATE, -3);
create table TABLE_B_ARCHIVE
TABLESPACE MOONSPACE
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 41512K
NEXT 100M
MINEXTENTS 1
MAXEXTENTS 2000
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOLOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING
AS SELECT * from TABLE_B where logdate ADD_MONTHS (SYSDATE, -12);
DELETE FROM TABLE_A where event_time ADD_MONTHS (SYSDATE, -3);
DELETE FROM TABLE_B where logdate ADD_MONTHS (SYSDATE, -12);
CREATE OR REPLACE procedure Archive_data AS
DECLARE
count NUMBER := 0;
total NUMBER := 0;
CURSOR del_rec IS SELECT * from TABLE_A where event_time ADD_MONTHS (SYSDATE, -3);
BEGIN
FOR rec IN del_record_cur
LOOP
INSERT INTO TABLE_A_ARCHIVE VALUES(DEL_REC.TABLE_A,COLUMN1, COLUMN2,
COLUMN3, COLUMN4.......);
COMMIT;
DELETE FROM TABLE_A WHERE rowid = rec.rowid;
total := total + 1; count := count + 1; IF (count >= 5000) THEN COMMIT;
count := 0;
END IF;
END LOOP ;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Deleted ' || total || ' records from TABLE_A');
END;/
BEGIN FOR rec IN del_record_cur
LOOP
INSERT INTO TABLE_B_ARCHIVE VALUES(DEL_REC.TABLE_B, COLUMN1, COLUMN2, COLUMN3.....);
COMMIT;
DELETE FROM TABLE_B WHERE rowid = rec.rowid;
total := total + 1; count := count + 1; IF (count >= 5000) THEN COMMIT;
count := 0;
END IF;
END LOOP ; COMMIT;
DBMS_OUTPUT.PUT_LINE('Deleted ' || total || ' records from TABLE_B');
END; /
I keep getting this Compilation errors:
PLS-00103: Encountered the symbol “DECLARE” when expecting one of the following:: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor external language
Thanks so much,
Lucky
|
|
|
|
|
|
|
|
| Re: Archiving Tables [message #314375 is a reply to message #314134] |
Wed, 16 April 2008 08:34 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| Lucky A wrote on Tue, 15 April 2008 20:05 |
INSERT INTO TABLE_A_ARCHIVE VALUES(DEL_REC.TABLE_A,COLUMN1, COLUMN2,
COLUMN3, COLUMN4.......);
|
This code is full of syntax errors and poor formatting. Why don't you post your real code with a real session output rather than giving an error message that we are supposed to figure out where it is referring to or even if it is referring to this code.
|
|
|
|
|
|
| Re: Archiving Tables [message #316857 is a reply to message #314376] |
Mon, 28 April 2008 10:45 |
Lucky A
Messages: 68
Registered: October 2007
|
Member |
|
|
Hi,
I've gotten the go ahead from the client to archive the tables. I've done some modification to the original script, thanks to all of your suggestions. When I tested the revised script, it is taking forever, over 14 hours to run. That is way too long for a production environment. Any suggestion to tweak the script would be greatly appreciated. Below is the final version:
Create or replace procedure Archive_data is commit_interval NUMBER(10) := 5000;
count_rec NUMBER(10) := 0; BEGIN FOR DEL_REC IN(SELECT * from TABLE_A where event_time < ADD_MONTHS (SYSDATE, -3)) LOOP INSERT INTO TABLE_A_ARCHIVE VALUES(DEL_REC.column1,DEL_REC.column2,DEL_REC.column3...);
count_rec := count_rec + 1; IF count_rec >= commit_interval THEN COMMIT;
count_rec := 0; END IF; COMMIT; DELETE FROM TABLE_A WHERE event_time = DEL_REC.event_time;
count_rec := count_rec + 1; IF count_rec >= commit_interval THEN COMMIT; count_rec := 0; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('Deleted '||commit_interval||' records from TABLE_A');
END;
DECLARE commit_interval NUMBER(10) := 5000; count_rec NUMBER(10) := 0; BEGIN FOR DEL_REC IN(SELECT * from TABLE_B where log_DATE < ADD_MONTHS (SYSDATE, -12)) LOOP INSERT INTO TABLE_B_ARCHIVE VALUES(DEL_REC.column1,DEL_REC.column2,DEL_REC.column3...);
count_rec := count_rec + 1; IF count_rec >= commit_interval THEN COMMIT; count_rec := 0; END IF; COMMIT; DELETE FROM TABLE_B WHERE log_date = DEL_REC.log_date; count_rec := count_rec + 1; IF count_rec >= commit_interval THEN COMMIT; count_rec := 0; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('Deleted '||commit_interval||' records from TABLE_B'); END;
/
Thanks,
Lucky
|
|
|
|
| Re: Archiving Tables [message #316880 is a reply to message #316857] |
Mon, 28 April 2008 13:06 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
|
You have been asked numerous times to post your code in a format that people other than yourself can read. Can you really understand what you just posted? give a little effort, please.
|
|
|
|
| Re: Archiving Tables [message #316891 is a reply to message #316880] |
Mon, 28 April 2008 16:05 |
Lucky A
Messages: 68
Registered: October 2007
|
Member |
|
|
I included an attachment of the code that is easily readable. I am trying to follow the format of the forum. Please refer to the attachment for a readable form.
Thanks,
Lucky
|
|
|
|
|
|
| Re: Archiving Tables [message #330746 is a reply to message #309987] |
Tue, 01 July 2008 01:56 |
sophia.catfish
Messages: 8
Registered: July 2008
|
Junior Member |
|
|
Hi,
I have a better solution for you. You can go for SPAMMER. Through this you can easily get readily available when the old information is needed. Even I am using the same. For more information you better visit www.solix.com.
Thank you,
Sophia.
[Updated on: Fri, 19 September 2008 08:30] by Moderator Report message to a moderator |
|
|
|
| Re: Archiving Tables [message #330754 is a reply to message #330746] |
Tue, 01 July 2008 02:24 |
 |
Michel Cadot
Messages: 68667
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
One post on a product is information.
Two same kind of posts in an hour becomes doubtful.
Three posts and you will be out as spammer.
Product announcement and ad MUST be done in MarketPlace forum.
Regards
Michel
[Updated on: Tue, 01 July 2008 02:24] Report message to a moderator |
|
|
|
| Re: Archiving Tables [message #332790 is a reply to message #309987] |
Wed, 09 July 2008 13:38 |
JackyShu
Messages: 25
Registered: May 2008
|
Junior Member |
|
|
loop
insert one row into newtable
delete one row from oldtable
commit
end of loop
first, this is very slow
second, fragments or holes left in table after delete
third, hard to rollback if anything unexpected happens
you can try
insert into newtable select from oldtable
delete from oldtable
commit
or you can use view, which is on top of several monthly tables, all you need to do is to modify the definition of the view.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
