Home » RDBMS Server » Server Administration » Archiving Tables (Oracle 9.2.0.4 on Windows 2000)
Archiving Tables [message #309987] Sun, 30 March 2008 22:11 Go to next message
Lucky A
Messages: 65
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 #309989 is a reply to message #309987] Sun, 30 March 2008 22:25 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
If you are running Enterprise Edition, I'd suggest using table partioning.
Re: Archiving Tables [message #310005 is a reply to message #309989] Sun, 30 March 2008 23:24 Go to previous messageGo to next message
Lucky A
Messages: 65
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 #310008 is a reply to message #309987] Sun, 30 March 2008 23:30 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>Can you please lead me to the best possible method to get this achieved.
Answers can be found at
http://tahiti.oracle.com
http://asktom.oracle.com

You need to be smarter than than the equipment you are using.
Are you smarter than the glorified sand which constitute the Integrated Circuits which make these systems?

[Updated on: Sun, 30 March 2008 23:31] by Moderator

Report message to a moderator

Re: Archiving Tables [message #310311 is a reply to message #310008] Mon, 31 March 2008 21:03 Go to previous messageGo to next message
Lucky A
Messages: 65
Registered: October 2007
Member
Thanks for your suggestions and your input.

Lucky
Re: Archiving Tables [message #311516 is a reply to message #309987] Fri, 04 April 2008 12:23 Go to previous messageGo to next message
Lucky A
Messages: 65
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 #311519 is a reply to message #309987] Fri, 04 April 2008 12:26 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines!
Re: Archiving Tables [message #311592 is a reply to message #311519] Fri, 04 April 2008 21:48 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
Lucky A
Messages: 65
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 #314137 is a reply to message #309987] Tue, 15 April 2008 20:25 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm

It appears you could benefit from Reading The Fine Manual above.

If you are willing & able to RTFM, you find answers you seek.
Re: Archiving Tables [message #314192 is a reply to message #314134] Wed, 16 April 2008 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: Archiving Tables [message #314375 is a reply to message #314134] Wed, 16 April 2008 08:34 Go to previous messageGo to next message
joy_division
Messages: 4642
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 #314376 is a reply to message #314134] Wed, 16 April 2008 08:40 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Have a quick read the following link. It's just another way of doing it. But you should consider partitioning the table.

http://www.oracle-developer.net/display.php?id=325

Regards

Raj
Re: Archiving Tables [message #316857 is a reply to message #314376] Mon, 28 April 2008 10:45 Go to previous messageGo to next message
Lucky A
Messages: 65
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 Go to previous messageGo to next message
joy_division
Messages: 4642
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 Go to previous messageGo to next message
Lucky A
Messages: 65
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 #316946 is a reply to message #316891] Mon, 28 April 2008 22:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try and try again in Test forum at the bottom of the page and then come back.
I'm too lazy to download files.

Regards
Michel
Re: Archiving Tables [message #330746 is a reply to message #309987] Tue, 01 July 2008 01:56 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
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 Go to previous message
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.

Previous Topic: How to register archive log in second destination after failure of 2nd destination
Next Topic: deleting records
Goto Forum:
  


Current Time: Wed Dec 07 18:48:17 CST 2016

Total time taken to generate the page: 0.07658 seconds