Skip to Main Content
  • Questions

  • Temporarily hide specific partitions from users

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Yves.

Asked: January 16, 2006 - 10:15 am UTC

Last updated: January 16, 2006 - 8:57 pm UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

Tom,

In my data warehouse, I have a set of tables that are setup with a range-list partition. These tables are queried by users through JDBC and ODBC.
The range partition is a day partition that is created every day and rolled over after 90 days.

I've been given a new requirement to provide a feature that will hide a partition from x days ago from the users, and restore it to the users when management decides to do so, or never restore it at all and let it rollover after 90 days.

What suggestions do you have for implementing this requirement?

Note that dropping/restoring the data is not an option as we are talking about massive amount of data for each day.

Thanks.

and Tom said...

Two things pop into my head right away:

a) fine grained access control would do this. You could have a predicate added to every query precluding that data from being selected. If simple enough, even just a view would do this.

b) exchanging the FULL partitions with EMPTY tables. If you have only local indexes, this will will nicely. The partitions will be there, the data will be there, only the partitions will be empty and the tables full of the partition data. Later, when you want the data back in the table, you exchange the full tables with the empty tables and it is "there" again.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Another way...

Tamil, January 16, 2006 - 2:37 pm UTC

How about offlining the tablespace where unwanted partitions are stored?

Tamil

Tom Kyte
January 16, 2006 - 3:43 pm UTC

unless the end users specify "I don't want that partition", they would get an error.

So, we need to "hide" them, not make the queries fail, in general.

both?

Gabe, January 16, 2006 - 4:27 pm UTC

Exchanging partitions, even with just local indexes, would still require refreshing the global stats though. So, maybe a mixture of a) [immediate/quick access change] and b) [slower data/metadata change] … ?!?!?

Tom Kyte
January 16, 2006 - 8:57 pm UTC

it would only "require" that if you wanted the plans to change - depends on the volume of data here as to whether it would be necessary or not. but yes, something to consider. (thanks)

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.