Can I create an Oracle view that automatically checks for new monthly tables?

Question

Hi!

I'm wondering if its possible to create a view that automatically checks if there is a new monthly created table and if there is include that one?

We have a new table created each month and each one ends with the number of the month, like

table for January:  table_1
table for February: table_2
etc...

Is it possible to create a view that takes data from all those tables and also finds when there is a new one created?

Answer 1

Will be possible if you instead of creating new table each month will create new partition for existing table.

UPDATE:

If you have oracle SE without partitioning option you can create two tables: LiveTable and ArchiveTable. Then each month you need move rows from Live to ArchiveTable and clean live table. In this case you need create view just from two tables.

Answer 2

No, a view's definition is static. You would have to replace the view each month with a new copy that included the new table; you could write a dynamic PL/SQL program to do this. Or you could create all the empty tables now and include them all in the view definition; if necessary you could postpone granting any INSERT access to the future tables until they become "live".

But really, this model is flawed - see Michael Pakhantsov's answer for a better alternative - or just have one simple table with a MONTH column.

Answer 3

Another option is to create the tables in another schema with grants to the relevant user and create public synonyms to them.

As the monthly tables get created in the local schema, they'll "out-precedence" the public synonyms and the view will pick them up. It will still get invalidated and need recompiling, but the actual view text should need changing, which may be simpler from a code-control point of view.