Archieving and deleting [message #322161] |
Thu, 22 May 2008 12:00 |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
Hi
There are 3 tables
ACE2_LIST_STATUS
ALS_LIST_ID VARCHAR2(30)
ALS_LIST_STATUS CHAR(1)
ALS_LIST_DATE DATE
Then one table ACE2_LIST_OUTPUT
(this table has no date field,so i specified the above table,because whatever list details gets inserted into the below table a similar the list id of it gets into the status table)
The requirement is such that
Currently this table contains data of 3-4 years
we need to keep 6 months data in this table and need to create one more table that will have last 18 months data.
others need to be deleted and this should be a regular process.
How can i proceed for implementing this functionality?
Thanks
|
|
|
|
Re: Archieving and deleting [message #322165 is a reply to message #322163] |
Thu, 22 May 2008 12:08 |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
but there is no date field in the main ace2_list_output table,how to implement range partitioning?
Also this is a table used by a existing applicaton,doing partitions in this,will it have any consequences??
|
|
|
Re: Archieving and deleting [message #322168 is a reply to message #322161] |
Thu, 22 May 2008 12:20 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
First, how can you have three tables with the same name?
Second, what is the common column?
Can you try to view your question as someone other than yourself and ask, "What information could I provide that would actually be helpful and before others have to ask me for it?"
|
|
|
Re: Archieving and deleting [message #322169 is a reply to message #322168] |
Thu, 22 May 2008 12:27 |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
may i know where i specified that 3 tables are having the same name?I need clarification on your question
secondly
the ace2_list_status which is having the date field
and the ace2_list_output table where history data gets inserted
from the aplication have alo_list_id in common.
hence we need to keep the 18 months data in one more history table
and ace2_list_output table should contain last 6 months data.
|
|
|
|
Re: Archieving and deleting [message #322172 is a reply to message #322169] |
Thu, 22 May 2008 12:33 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
swas_fly wrote on Thu, 22 May 2008 13:27 | may i know where i specified that 3 tables are having the same name?
|
In your original message, here:Quote: |
Hi
There are 3 tables
ACE2_LIST_STATUS
ALS_LIST_ID VARCHAR2(30)
ALS_LIST_STATUS CHAR(1)
ALS_LIST_DATE DATE
|
|
|
|
Re: Archieving and deleting [message #322177 is a reply to message #322171] |
Thu, 22 May 2008 12:48 |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
ACE2_LIST_STATUS
ALS_LIST_ID VARCHAR2 (20)
ALS_STAGE VARCHAR2 (20)
ALS_PROCESSED_RECORDS NUMBER (10)
ALS_OUTPUT_RECORDS NUMBER (10)
ALS_PROCESS_DATE Date
The below table currently holds 3-4 ears data
It will contain 6 months data
ACE2_LIST_OUTPUT
ALO_LIST_ID VARCHAR2(20)
ALO_TITLE VARCHAR2(15)
ALO_FNAME VARCHAR2(15)
ALO_LASTNAME VARCHAR2 (30)
ALO_CONTACT_NAME VARCHAR2(62)
ALO_TELEPHONE VARCHAR2(16)
ALO_KDM N VARCHAR2(1)
ALO_ADDR_1 VARCHAR2(500)
ALO_ADDR_2 VARCHAR2(500)
ALO_ADDR_3 VARCHAR2(500)
ALO_ADDR_4 VARCHAR2(500)
ALO_POSTCODE VARCHAR2(50)
ALO_SALUTATION VARCHAR2(100)
ALO_VALEDICTION VARCHAR2(30)
ALO_FILE_NAME VARCHAR2(100)
ALO_BUSINESS_NAME VARCHAR2(100)
ALO_CAMPAIGN_CODE VARCHAR2(12)
ALO_CAMPAIGN_ID VARCHAR2(20)
ALO_CONK VARCHAR2(30)
ALO_CUG VARCHAR2(30)
ALO_DISTRICT_CODE VARCHAR2(50)
ALO_EMAIL_ADDRESS VARCHAR2(60)
ALO_EMAIL_CONSENT VARCHAR2(1)
ALO_EVOLVE_ROW_ID VARCHAR2(20)
ALO_JOB_FUNCTION VARCHAR2(40)
ALO_JOB_TITLE VARCHAR2(40)
ALO_LEGAL_ENTITY_CODE VARCHAR2(20)
ALO_MAIN_TEL_NO VARCHAR2(16)
ALO_SITE_NO VARCHAR2(50)
ALO_URN VARCHAR2(10)
ALO_CCAT_SUBSECTOR_CODE VARCHAR2(6)
ALO_REF_NO VARCHAR2(5)
ALO_SME_ID VARCHAR2(10)
ALO_SUPPRESS VARCHAR2(1)
ALO_CAS_SEGMENT VARCHAR2(1)
ALO_SM VARCHAR2(90)
ALO_AM VARCHAR2(3)
ALO_NOTES VARCHAR2(2000)
ALO_SAC VARCHAR2(6)
ALO_FILE_NAME_2 VARCHAR2(100)
ALO_DECISION_MAKER_CONTACT VARCHAR2(1)
ALO_CAMPAIGN_SUBSEGMENT_CODE VARCHAR2(2)
ALO_BT_BROADBAND VARCHAR2(1)
ALO_LAST_YEAR_REV VARCHAR2(9)
ALO_THIS_YEAR_REV VARCHAR2(9)
ALO_PRIORITY VARCHAR2(14)
ALO_CAMPAIGN_TITLE VARCHAR2(9)
ALO_KDM_FLAG VARCHAR2(100)
ALO_TELEPHONE_CONSENT VARCHAR2(1)
ALO_ADDRESS_CONSENT VARCHAR2(1)
ALO_TPS_CONSENT VARCHAR2(1)
Then we need to create this history table to hold last 18 months data
so it will be two years in all.
ACE2_LIST_OUTPUT_HISTORY
ALO_LIST_ID VARCHAR2(20)
ALO_TITLE VARCHAR2(15)
ALO_FNAME VARCHAR2(15)
ALO_LASTNAME VARCHAR2 (30)
ALO_CONTACT_NAME VARCHAR2(62)
ALO_TELEPHONE VARCHAR2(16)
ALO_KDM N VARCHAR2(1)
ALO_ADDR_1 VARCHAR2(500)
ALO_ADDR_2 VARCHAR2(500)
ALO_ADDR_3 VARCHAR2(500)
ALO_ADDR_4 VARCHAR2(500)
ALO_POSTCODE VARCHAR2(50)
ALO_SALUTATION VARCHAR2(100)
ALO_VALEDICTION VARCHAR2(30)
ALO_FILE_NAME VARCHAR2(100)
ALO_BUSINESS_NAME VARCHAR2(100)
ALO_CAMPAIGN_CODE VARCHAR2(12)
ALO_CAMPAIGN_ID VARCHAR2(20)
ALO_CONK VARCHAR2(30)
ALO_CUG VARCHAR2(30)
ALO_DISTRICT_CODE VARCHAR2(50)
ALO_EMAIL_ADDRESS VARCHAR2(60)
ALO_EMAIL_CONSENT VARCHAR2(1)
ALO_EVOLVE_ROW_ID VARCHAR2(20)
ALO_JOB_FUNCTION VARCHAR2(40)
ALO_JOB_TITLE VARCHAR2(40)
ALO_LEGAL_ENTITY_CODE VARCHAR2(20)
ALO_MAIN_TEL_NO VARCHAR2(16)
ALO_SITE_NO VARCHAR2(50)
ALO_URN VARCHAR2(10)
ALO_CCAT_SUBSECTOR_CODE VARCHAR2(6)
ALO_REF_NO VARCHAR2(5)
ALO_SME_ID VARCHAR2(10)
ALO_SUPPRESS VARCHAR2(1)
ALO_CAS_SEGMENT VARCHAR2(1)
ALO_SM VARCHAR2(90)
ALO_AM VARCHAR2(3)
ALO_NOTES VARCHAR2(2000)
ALO_SAC VARCHAR2(6)
ALO_FILE_NAME_2 VARCHAR2(100)
ALO_DECISION_MAKER_CONTACT VARCHAR2(1)
ALO_CAMPAIGN_SUBSEGMENT_CODE VARCHAR2(2)
ALO_BT_BROADBAND VARCHAR2(1)
ALO_LAST_YEAR_REV VARCHAR2(9)
ALO_THIS_YEAR_REV VARCHAR2(9)
ALO_PRIORITY VARCHAR2(14)
ALO_CAMPAIGN_TITLE VARCHAR2(9)
ALO_KDM_FLAG VARCHAR2(100)
ALO_TELEPHONE_CONSENT VARCHAR2(1)
ALO_ADDRESS_CONSENT VARCHAR2(1)
ALO_TPS_CONSENT VARCHAR2(1)
|
|
|
Re: Archieving and deleting [message #322178 is a reply to message #322171] |
Thu, 22 May 2008 12:48 |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
[B]ACE2_LIST_STATUS[/B]
ALS_LIST_ID VARCHAR2 (20)
ALS_STAGE VARCHAR2 (20)
ALS_PROCESSED_RECORDS NUMBER (10)
ALS_OUTPUT_RECORDS NUMBER (10)
ALS_PROCESS_DATE Date
The below table currently holds 3-4 ears data
It will contain 6 months data
[B]ACE2_LIST_OUTPUT[/B]
ALO_LIST_ID VARCHAR2(20)
ALO_TITLE VARCHAR2(15)
ALO_FNAME VARCHAR2(15)
ALO_LASTNAME VARCHAR2 (30)
ALO_CONTACT_NAME VARCHAR2(62)
ALO_TELEPHONE VARCHAR2(16)
ALO_KDM N VARCHAR2(1)
ALO_ADDR_1 VARCHAR2(500)
ALO_ADDR_2 VARCHAR2(500)
ALO_ADDR_3 VARCHAR2(500)
ALO_ADDR_4 VARCHAR2(500)
ALO_POSTCODE VARCHAR2(50)
ALO_SALUTATION VARCHAR2(100)
ALO_VALEDICTION VARCHAR2(30)
ALO_FILE_NAME VARCHAR2(100)
ALO_BUSINESS_NAME VARCHAR2(100)
ALO_CAMPAIGN_CODE VARCHAR2(12)
ALO_CAMPAIGN_ID VARCHAR2(20)
ALO_CONK VARCHAR2(30)
ALO_CUG VARCHAR2(30)
ALO_DISTRICT_CODE VARCHAR2(50)
ALO_EMAIL_ADDRESS VARCHAR2(60)
ALO_EMAIL_CONSENT VARCHAR2(1)
ALO_EVOLVE_ROW_ID VARCHAR2(20)
ALO_JOB_FUNCTION VARCHAR2(40)
ALO_JOB_TITLE VARCHAR2(40)
ALO_LEGAL_ENTITY_CODE VARCHAR2(20)
ALO_MAIN_TEL_NO VARCHAR2(16)
ALO_SITE_NO VARCHAR2(50)
ALO_URN VARCHAR2(10)
ALO_CCAT_SUBSECTOR_CODE VARCHAR2(6)
ALO_REF_NO VARCHAR2(5)
ALO_SME_ID VARCHAR2(10)
ALO_SUPPRESS VARCHAR2(1)
ALO_CAS_SEGMENT VARCHAR2(1)
ALO_SM VARCHAR2(90)
ALO_AM VARCHAR2(3)
ALO_NOTES VARCHAR2(2000)
ALO_SAC VARCHAR2(6)
ALO_FILE_NAME_2 VARCHAR2(100)
ALO_DECISION_MAKER_CONTACT VARCHAR2(1)
ALO_CAMPAIGN_SUBSEGMENT_CODE VARCHAR2(2)
ALO_BT_BROADBAND VARCHAR2(1)
ALO_LAST_YEAR_REV VARCHAR2(9)
ALO_THIS_YEAR_REV VARCHAR2(9)
ALO_PRIORITY VARCHAR2(14)
ALO_CAMPAIGN_TITLE VARCHAR2(9)
ALO_KDM_FLAG VARCHAR2(100)
ALO_TELEPHONE_CONSENT VARCHAR2(1)
ALO_ADDRESS_CONSENT VARCHAR2(1)
ALO_TPS_CONSENT VARCHAR2(1)
Then we need to create this history table to hold last 18 months data
so it will be two years in all.
[B]ACE2_LIST_OUTPUT_HISTORY[/B]
ALO_LIST_ID VARCHAR2(20)
ALO_TITLE VARCHAR2(15)
ALO_FNAME VARCHAR2(15)
ALO_LASTNAME VARCHAR2 (30)
ALO_CONTACT_NAME VARCHAR2(62)
ALO_TELEPHONE VARCHAR2(16)
ALO_KDM N VARCHAR2(1)
ALO_ADDR_1 VARCHAR2(500)
ALO_ADDR_2 VARCHAR2(500)
ALO_ADDR_3 VARCHAR2(500)
ALO_ADDR_4 VARCHAR2(500)
ALO_POSTCODE VARCHAR2(50)
ALO_SALUTATION VARCHAR2(100)
ALO_VALEDICTION VARCHAR2(30)
ALO_FILE_NAME VARCHAR2(100)
ALO_BUSINESS_NAME VARCHAR2(100)
ALO_CAMPAIGN_CODE VARCHAR2(12)
ALO_CAMPAIGN_ID VARCHAR2(20)
ALO_CONK VARCHAR2(30)
ALO_CUG VARCHAR2(30)
ALO_DISTRICT_CODE VARCHAR2(50)
ALO_EMAIL_ADDRESS VARCHAR2(60)
ALO_EMAIL_CONSENT VARCHAR2(1)
ALO_EVOLVE_ROW_ID VARCHAR2(20)
ALO_JOB_FUNCTION VARCHAR2(40)
ALO_JOB_TITLE VARCHAR2(40)
ALO_LEGAL_ENTITY_CODE VARCHAR2(20)
ALO_MAIN_TEL_NO VARCHAR2(16)
ALO_SITE_NO VARCHAR2(50)
ALO_URN VARCHAR2(10)
ALO_CCAT_SUBSECTOR_CODE VARCHAR2(6)
ALO_REF_NO VARCHAR2(5)
ALO_SME_ID VARCHAR2(10)
ALO_SUPPRESS VARCHAR2(1)
ALO_CAS_SEGMENT VARCHAR2(1)
ALO_SM VARCHAR2(90)
ALO_AM VARCHAR2(3)
ALO_NOTES VARCHAR2(2000)
ALO_SAC VARCHAR2(6)
ALO_FILE_NAME_2 VARCHAR2(100)
ALO_DECISION_MAKER_CONTACT VARCHAR2(1)
ALO_CAMPAIGN_SUBSEGMENT_CODE VARCHAR2(2)
ALO_BT_BROADBAND VARCHAR2(1)
ALO_LAST_YEAR_REV VARCHAR2(9)
ALO_THIS_YEAR_REV VARCHAR2(9)
ALO_PRIORITY VARCHAR2(14)
ALO_CAMPAIGN_TITLE VARCHAR2(9)
ALO_KDM_FLAG VARCHAR2(100)
ALO_TELEPHONE_CONSENT VARCHAR2(1)
ALO_ADDRESS_CONSENT VARCHAR2(1)
ALO_TPS_CONSENT VARCHAR2(1)
|
|
|
|
|
|
|
|
|
Re: Archieving and deleting [message #322186 is a reply to message #322185] |
Thu, 22 May 2008 13:18 |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
Can anyone tell me the methodology of how to partition this table
as the date filed being in ace2_lis_status field.
i am new to this partitioning concept.How to implement partition exchange here?
and seeing the requirement in what frequency this will be refreshed?
Will it have any effect on the existing application?
|
|
|
|
|
Re: Archieving and deleting [message #322196 is a reply to message #322193] |
Thu, 22 May 2008 14:11 |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
so what i can understand is
create view1 from ace2_list_outout and ace2_list_status which will have the date field
and then create view2 of same structure as view1 without data,then apply partitions and exchange partitions
am i right?
|
|
|
|
Re: Archieving and deleting [message #322245 is a reply to message #322200] |
Thu, 22 May 2008 21:44 |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
Hi Michel
This archieving needs to be done to redure the data from the main ACE2_LIST_OUTPUT table,now the existing application makes use of both ace2_list_outout and ace2_list_status extensively,
they cannot be dropped?
Please advice?
Any document to read on partition and partition exchange mechanism.
Seeing the requirement and is partitions the only way to go about it?
can you let me know how to schedule this process?
Thanks
|
|
|
|
Re: Archieving and deleting [message #322468 is a reply to message #322253] |
Fri, 23 May 2008 11:55 |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
Quote: | If you use this, they don't see the difference.
|
I am not clear in this point.The bottom line is to reduce the data from ACE2_LIST_OUTPUT and run the job in monthly basis which will do this removing job andpbut the archieved data in the backup table
nad house keeping scripts to run for both history table and ace2_list_output table for maintaining 6 months data in ace2_list_output
and months data in the history table?
How can a view help?So are you saying if we drop the views then the base tables will get affected as well.
Keeping in mind the ace2_list_ststus and ace2_list_output cannot be dropped as they hold key in the application
|
|
|
|
|
|
Re: Archieving and deleting [message #322484 is a reply to message #322481] |
Fri, 23 May 2008 13:07 |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
Quote: | Create a new table that merge columns of both current tables.
Drop the old tables.
Create views upon new table that simulate the old tables.
|
Ok i can create a view,but throuout the application there are critical transactions tasking place in ace2_list_output table.
In your second point
drop old tables--means ace2_list_output and ace2_list_status right?
You think it will be fine with the application?What if the create view fails for some unexpected reason?
Within this time when we are dropping old tables then what about the transactions taking place?
Even if we proceed for this?Do you mean now we now need to do all the application transactions on the new views simulating the old tables??
[Updated on: Fri, 23 May 2008 13:08] Report message to a moderator
|
|
|