OraFAQ Forum: SQL & PL/SQL » Moving data older than 2 years in all fact tables to respective archive tables & purging data

Home » SQL & PL/SQL » SQL & PL/SQL » Moving data older than 2 years in all fact tables to respective archive tables & purging data (Oracle 11g , SQL Developer, Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Moving data older than 2 years in all fact tables to respective archive tables & purging data [message #651625]

Sat, 21 May 2016 00:53

krisc2499
Messages: 4
Registered: May 2016
Location: Hyderabad

Junior Member

Hi,

I have a requirement where i have to copy data older than 2 years from all fact tables to their respective archive table (one archive table for each fact table)
And then validate if data has been inserted into Archive table and delete data older than 2 years in main fact tables.

I don't have knowledge in pl/sql.
I need your advise on how to achieve this.

Thanks
Krishna

Report message to a moderator

Re: Moving data older than 2 years in all fact tables to respective archive tables & purging data [message #651626 is a reply to message #651625]

Sat, 21 May 2016 00:59

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

The good news is that you do not need any PL/SQL for this. You need only a set of INSERT INTO ... SELECT ... FROM ... WHERE ... statements, followed by a set of DELETE FROM ... WHERE ... statements. You can run a few SELECT statements to validate that the rows were moved successfully.

What SQL have you tried so far?

Report message to a moderator

Re: Moving data older than 2 years in all fact tables to respective archive tables & purging data [message #651627 is a reply to message #651626]

Sat, 21 May 2016 01:12

krisc2499
Messages: 4
Registered: May 2016
Location: Hyderabad

Junior Member

Thanks for quick response.

So far , i have created a Control Table with structure as
CREATE TABLE "W_CONTROL_TABLE" ("TABLE_NAME" VARCHAR2(500 BYTE),"COL_USED_FOR_ARCH" VARCHAR2(500 BYTE),"PERIOD_IN_MONTHS" NUMBER(10,0),"ARCHIVED_TO_TABLE" VARCHAR2(500 BYTE))
I have to do this for many tables(around 750) and have to do this every week, so we have to write procedure to do this dynamically rather than doing for each table.
In above control table, we are storing Original Table name - TABLE_NAME , Column which acts as a filter in each table to get data older than specific period - COL_USED_FOR_ARCH ,
column to know for how older data should be purged - PERIOD_IN_MONTHS , column to know to what archive table should be used to copy data from original table -ARCHIVED_TO_TABLE.

I hope you got my full scenario.

So i guess i have to write a PL/SQL procedure with one or two cursors to do this dynamically.
But i am not able to implement this, since i am very new to pl/sql.

Thanks

Report message to a moderator

Re: Moving data older than 2 years in all fact tables to respective archive tables & purging data [message #651628 is a reply to message #651627]

Sat, 21 May 2016 01:18

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

You need to to write the SQL statements first. Worry about automating the process later.

Don't you need a DATE column somewhere?

And please use [code] tags when you post code.

[Updated on: Sat, 21 May 2016 01:19]

Report message to a moderator

Re: Moving data older than 2 years in all fact tables to respective archive tables & purging data [message #651637 is a reply to message #651627]

Sat, 21 May 2016 08:15

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

krisc2499 wrote on Sat, 21 May 2016 01:12

Thanks for quick response.

So far , i have created a Control Table with structure as
CREATE TABLE "W_CONTROL_TABLE" ("TABLE_NAME" VARCHAR2(500 BYTE),"COL_USED_FOR_ARCH" VARCHAR2(500 BYTE),"PERIOD_IN_MONTHS" NUMBER(10,0),"ARCHIVED_TO_TABLE" VARCHAR2(500 BYTE))
I have to do this for many tables(around 750) and have to do this every week,

First, for your own sanity you need to learn to format your code. And for the sanity of those from whom you seek assistance, you should learn to preserve that format by using the code tags when you post. Also, remove the double-quotes. They serve no purpose but to add clutter. Also putting everything in upper case also makes it harder to read. I haven't used all upper-case since I got off the IBM mainframe back in the '90s. Also, there is no need to explicitly define the varchars as BYTE, as that is the default. Again, just adds clutter to your code. Also, why define columns that are being used to store the names of oracle objects as varchar2(500) when then data being stored there can never exceed 30 bytes (the defined limit for table/column names)?

create table w_control_table
(table_name        varchar2(30),
 col_used_for_arch varchar2(30),
 period_in_months  number(10,0),
 archived_to_table varchar2(30)
)

Now back your question -- are you really defining 750 new tables every week? Sounds to me like a very flawed approach to some unknown (to us) problem. Perhaps if you were to step back a bit. Explain the business problem, the reasoning behind this approach. I'll bet someone can find a much more elegant solution.

Report message to a moderator

Re: Moving data older than 2 years in all fact tables to respective archive tables & purging data [message #651639 is a reply to message #651637]

Sat, 21 May 2016 08:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>so we have to write procedure to do this dynamically rather than doing for each table.
NO, you do not HAVE do this dynamically. You have decided that you think dynamic SQL is the better choice.

Does all 750+ tables have a date column that can be used to filter new & modified rows to be archived?

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: Moving data older than 2 years in all fact tables to respective archive tables & purging data [message #651647 is a reply to message #651639]

Sun, 22 May 2016 00:38

krisc2499
Messages: 4
Registered: May 2016
Location: Hyderabad

Junior Member

@EdSteven - Thanks for the response.
I will keep in mind all your suggestions.
We have around 750 tables. Every week we don't have to define 750 tables every week.
We have to check if we have data older than 2 years in all 750 tables and if exists we have to move that data to their respective archive tables.
So , in control table we are maintaining all those 750 tables and their respective archive tables, column to use as filter, and period to know how old data needs to be archived & purged.

@BlackSwan - Thanks for response.
All 750 tables are having some date columns. I don't know exactly what dynamically mean in Pl/SQL code.

So now my plan is to Create two procedures
Proc 1 - Writing a cursor to fetch data from control table one at a time and calling second proc with parameters as Main Table Name, Column used for archive, Period, Archive table name.
Proc 2 - a) Inserting data into archive table with filter condition form main table
b) taking counts from both Main table & archive and comparing both counts
c) if counts match then delete same data from Main Table
After writing & testing these two procs , i have to schedule this to run every week.

Can i know if there is any better approach to this.

Thanks

Report message to a moderator

Re: Moving data older than 2 years in all fact tables to respective archive tables & purging data [message #651656 is a reply to message #651647]

Sun, 22 May 2016 07:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Nobody here prevent you from writing the code to meet your requirements.
Nobody here can write the code for you since we don't have your tables or data.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: Moving data older than 2 years in all fact tables to respective archive tables & purging data [message #651667 is a reply to message #651656]

Mon, 23 May 2016 00:29

krisc2499
Messages: 4
Registered: May 2016
Location: Hyderabad

Junior Member

@BlackSwan - Thanks for response

I already wrote how i am implementing this.
But just asking for suggestions or any better approach.

Thanks
Krishna

Report message to a moderator

Re: Moving data older than 2 years in all fact tables to respective archive tables & purging data [message #651702 is a reply to message #651667]

Mon, 23 May 2016 06:46

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

krisc2499 wrote on Mon, 23 May 2016 00:29

@BlackSwan - Thanks for response

I already wrote how i am implementing this.
But just asking for suggestions or any better approach.

Thanks
Krishna

One better approach would be to ask why the data has to be moved at all. Sounds to me like a textbook case for a partitioned tables.

Report message to a moderator

Re: Moving data older than 2 years in all fact tables to respective archive tables & purging data [message #651706 is a reply to message #651667]

Mon, 23 May 2016 07:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

krisc2499 wrote on Sun, 22 May 2016 22:29

@BlackSwan - Thanks for response

I already wrote how i am implementing this.

If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.

Report message to a moderator

Previous Topic:	IN (or) Equality Operator
Next Topic:	Difference between Global Temporary Table and Collections in PLSQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 22:12:54 CDT 2024