Need to get count of records in partition that has been dropped.
I have an Oracle database (11gR2) that processes records on a weekly basis. One of the business requirements is that the records be maintained for the current processing year plus the previous six years. An additional requirement is that records prior to that period be deleted. This is all relatively easy to manage as the records are maintained in tables that are partitioned by processing year, so each new year the partition, for the processing year that is seven years prior to the current year, is dropped. Now a new requirement has been added and that is to determine the number of records that were in the dropped partition. Going forward this can be readily determined prior to the new year. Unfortunately, there is also a need to know the number of records dropped at the beginning of this year. Considering that we are currently in processing year 2019 and the previous six years were for 2013-2018, we dropped the partition for 2012 before getting a count of the records. Therefore, my questions are, "Is there any kind of log or audit file that may have captured this information?" or, if not, "Is there any other means of getting this count?"
As Mark said, in the current database, no. If you can restore a copy of the database to a different location just before the drop, then you can count the records in the partition. You shouldn't need to restore the entire database, just the necessary Oracle tablespaces (SYSTEM, etc) and the tablespace(s) that hold the table(s) involved.
If you don't have an old backup that still contains the data, you are probably out of luck.
We copied the data from the partition to be dropped to an archive table in a new tablespace. Then dropped the partition, then use transportable tablespaces to take the tablespace out of the database. That way we had a copy that we could transport back in to answer these kinds of questions. Even though they say they won't need it after 7 years, they always come up with a way. If you have the old data that you can easily transport back it, you're a hero.
If you don't have an old backup that still contains the data, you are probably out of luck.
We copied the data from the partition to be dropped to an archive table in a new tablespace. Then dropped the partition, then use transportable tablespaces to take the tablespace out of the database. That way we had a copy that we could transport back in to answer these kinds of questions. Even though they say they won't need it after 7 years, they always come up with a way. If you have the old data that you can easily transport back it, you're a hero.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
We have a custom "table_rows" table that can answer that question in our systems, We insert or update records in that custom table each weekend based on data in dba_tables and dba_tab_partitions that gets updated by statistics-gathering jobs that we run at least weekly. We run a custom PL\SQL procedure late each weekend that queries these two data dictionary views for: table_owner, table_name, partition_name, blocks, num_rows and last_analyzed. It then checks the latest record in our custom "table_rows" table for that table_owner, table_name and partition_name. If the number of records or the number of blocks is different from last week, it inserts a new row. If those quantities are the same it increments a column on the latest "table_rows" record for that owner, table_name, and partition_name to indicate that the number of blocks and records has remained the same for another week.
You could set up something like this to answer this question going forward. But without a custom table like this, I'm not aware that Oracle keeps this information anywhere by default for the long term.