Home » SQL & PL/SQL » SQL & PL/SQL » Insert into target table and delete from source (Oracle 11g)
Insert into target table and delete from source [message #573746] Wed, 02 January 2013 23:24 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

We have a requirement to archive and purge the tables dynamically based on the control table input.
For that we have to design a control table to gather the necessary information and passed to generate the queries.

I have designed the table as below.But in this case I am not able to handle the parent and child relation ship.

Suppose one table needs to be archived and purged and that table is parent table and it is having 2 child tables,
so first required data will be inserted into target table and delete from source parent and child tables.
so before deleting from parent we have to delete data from all 2 child tables.

Suppose one table needs to be purged and that table is parent table and it is having 5 child tables,
so before deleting from parent we have to delete data from all 5 child tables.

To handle this scenario how can I design my control table.

For archive and purge the query like this.
INSERT INTO towner_name.ttable_name
(SELECT * FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30));
DELETE FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30);


for purge the quey is like this.
DELETE FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30);


This is my control table and I have 300 tables list to archive and purge.

CID  SOWNER_NAME STABLE_NAME       TOWNER_NAME   TTABLE_NAME       CONDITION_COLUMN PERIOD UNIT TYPE
1     wedb_au    OFFER_HEADER       wedb_au      OFFER_HEADER       LAST_DATE        30     D    A
1     wedb_sa    OFFER_CUSTOMER     wedb_sa      OFFER_CUSTOMER     LAST_DATE        60     D    A
1     wedb_au    OFFER_SERVICE                                      LAST_DATE         1     Y    P 
1     wedb_us    OFFER_CUSTOMER                                     LAST_DATE        90     D    P
1     wedb_cn    OFFER_CARDS                                        UPDATE_DT        2      Y    P
2     wedb_au    ORDER_HEAD         wedb_au      ORDER_HEAD         LAST_DATE        120    D    A 
2     wedb_us    ORDER_CUSTOMER     wedb_us      ORDER_CUSTOMER     LAST_DATE        150    D    A
2     wedb_sa    ORDER_HEAD         wedb_sa      ORDER_HEAD         CREATION_DT      1      Y    A
3     wedb_us    DELIVERY_HEAD      wedb_us      DELIVERY_HEAD      UPDATE_DT        50     D    A
3     wedb_au    DELIVERY_CARDS     wedb_au      DELIVERY_CARDS     UPDATE_DT        200    D    A
3     wedb_au    DELIVERY_SERVICE   wedb_au      DELIVERY_SERVICE   LAST_DT          100    D    A



WHERE TYPE=P means insert and delete
TYPE=A means only delete

wedb_au.OFFER_HEADER is Parent Table.
child tables for wedb_au.OFFER_HEADER are wedb_au.OFFER_SERVICE,wedb_au.OFFER_BODY,wedb_au.OFFER_EMAIL,OFFER_TAX.

wedb_au.OFFER_SERVICE is child table and parent for this table is wedb_au.OFFER_HEADER

wedb_sa.OFFER_CUSTOMER Stand alone table no relationship

wedb_us.OFFER_CUSTOMER Stand alone table no relationship

wedb_cn.OFFER_CARDS is parent table.
child tables for wedb_cn.OFFER_CARDS are wedb_cn.OFFER_OPTION,wedb_cn.OFFER_SERIES

wedb_au.ORDER_HEAD is parent table.
child tables for wedb_au.ORDER_HEAD are wedb_au.ORDER_CUSTOMER,wedb_au.ORDER_SERVICE

wedb_us.ORDER_CUSTOMER is parent table.
child tables for wedb_us.ORDER_CUSTOMER are wedb_us.ORDER_TAx,wedb_us.ORDER_SERIES.

wedb_sa.ORDER_HEAD is stand alone table no relationship.

wedb_us.DELIVERY_HEAD is parent table
child tables for wedb_us.DELIVERY_HEAD are wedb_us.DELIVERY_SERVICE,wedb_us.DELIVERY_BODY

wedb_au.DELIVERY_CARDS is child table and parent for this table is wedb_au.DELIVERY_OPTION

wedb_au.DELIVERY_SERVICE is child table and parent for this table is wedb_au.DELIVERY_HEAD

Please help me to design the control table.

Thanks in advance.
Re: Insert into target table and delete from source [message #573749 is a reply to message #573746] Wed, 02 January 2013 23:31 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
ajaykumarkona wrote on Wed, 02 January 2013 23:24
Hi Experts,

Suppose one table needs to be purged and that table is parent table and it is having 5 child tables,
so before deleting from parent we have to delete data from all 5 child tables.



1/ How about Cascade Delete ?
2/ Referential Integrity Constraints
Re: Insert into target table and delete from source [message #573986 is a reply to message #573746] Fri, 04 January 2013 13:14 Go to previous message
spacebar
Messages: 26
Registered: February 2007
Junior Member
Quote:
Please help me to design the control table.

Examples:

Use some type of archive master like below for your control:

*** archive_master
column_name       type     length   comments
archive_master_id number(   10,0  ) Primary Key via a sequence
owner             varchar2( 30    ) Schema owner                               
table_name        varchar2( 30    ) Name of table to be archived/purged        
type              varchar2( 1     ) 'A' to archive rows to history table OR 'P' to remove rows
age_column        varchar2( 30    ) Column name containg date to determine age
useful_life       number(   10,0  ) Number of days to keep rows
frequency         varchar2( 10    ) Frequency of archive/purge processing
run_flag          varchar2( 1     ) Run Flag - 'Y' to process file
order             number(   5,0   ) Order to process tables



And optionally a log so as to be able to see archive/purge history:

*** archive_log
column_name       type     length    comments
archive_log_id    number(   10,0   ) Primary Key from a sequence
archive_master_id number(   10,0   ) Foriegn key(i.e. archive_master_id )
archive_date      date               Date used for selecting rows
start_time        date               Start Time
end_time          date               End Time
rows              number(   10,0   ) Rows processed              
status_message    varchar2( 2000   ) Error/completion message( if any )

Previous Topic: Add Range Subpartition to a hash Parition table
Next Topic: Check for create table permission
Goto Forum:
  


Current Time: Wed Nov 26 16:00:41 CST 2014

Total time taken to generate the page: 0.06482 seconds