Alias For Table 1

[Scunningham99]

This is what i want to do.

For archiving purposes we wish to say purge data from a table on date. This bit we have done!!. So at present we have two tables with the same name in two different databases. One with the Purged data and one with the normal data. (databases are called "db1" and "db2&quot

so the tables look like the followinf in the different databases.
<database>.<schema>.<tablename>
db1.apps.gl_balances --table 1
db1.arch.gl_balances --table 2

Now the clever part.....

What we want to do is when we reference db1.apps.gl_balances we want the query to be clever enough th go to table 2 if the date range falls in to the archived data range else go to table 1.

Ive thought about partitned tables, but im not sure if these will work then you partition by range because the tablespace is in another database. Also not sure if a VIEW wil work.

Any help would be greatly appreciated.







Sy UK

 

[fmorel]

A view should work!

create view my_view is (
select * from db1.apps.gl_balances
where dt<xxx
union all
select * from db1.arch.gl_balances
where dt>=xxx
)

owner of my_view should have been granted the &quot;select&quot; right on apps.gl_balances and arch.gl_balances directly (ie not through a role).

If there are 2 different databases, you may use a database link.

 

[Scunningham99]

Thats great I will have a play with it later.

Thanks alot for your help!

Sy UK