SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Insert into target table and delete from source

user9077483Jan 3 2013 — edited Jan 29 2013

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.

BalaGuddeti-Oracle

In the earlier post, you have set the Maximum recipients to 100, hence Email server control mechanism is activated to control spam emails. if you expect 500 emails, change the Maximum number of recipients to 500

Gianni Ceresa

4.3.2 The maximum number of concurrent server connections has exceeded a per-source limit, closing transmission channel
This is not an OBIEE/OAS error message, the issue is not here, you are looking in the wrong place....
This is a mail server error message.
You should speak to your mail server administrators giving them that error and explaining what you were (or what the tool was doing on your behalf) when the error appears in the logs.
Do not touch the maximum recipients number, leave it to 0 as Joel said in the other thread: 0 means unlimited. That setting is used more for other reasons, like when you want to avoid somebody setup an agent spamming your entire company sending an agent delivery to 1000+ people, it's a way to limit the freedom of people creating agents based on company's rules.
Your issue is that OBIEE/OAS is sending the emails in a way that is outside the limits of the mail server, not on the OBIEE/OAS side, but on the mail server side.
Speak your mail server administrators, also telling them you don't really have options to slow down the sending process on the OBIEE/OAS side.
Maybe they will ask you to send these emails in batches, and for that you will need some creativity (like using 2 agents running at separate times doing each half the list of emails).

1 - 2

Locked Post

New comments cannot be posted to this locked post.

Locked on Feb 26 2013

Added on Jan 3 2013

27 comments

1,254 views

SQL & PL/SQL

Insert into target table and delete from source

Comments

Post Details