Home » RDBMS Server » Server Administration » Data from a partition was somehow lost
Data from a partition was somehow lost [message #235953] Tue, 08 May 2007 08:57 Go to next message
mauricio@tecnologica
Messages: 4
Registered: August 2006
Junior Member
Hello Forum,

we have a big table partitioned by month and what happened is that the whole data of one of the months got lost (april). How this happened is a mistery. No log register of any operation (database logs at bdump, cdump, udump and operation system logs).

When we perform a simple query to count the records of that specific month, it returns 0, but when we make a query searching for a substring in a column which holds a string timestamp (a substring like '200704') we get an error 'invalid rowid'. Is oracle hiding/jumping/ignoring the data due to invalid rowids?

Using DBMS_ROWID package, created a procedure to evaluate the validity of rowids of records of the whole year (2007), using DBMS_ROWID.rowid_verify and all that we got is return 1 (invalid) but other months are returned by queries.

Can you give us a clue? Thanks you all in advance. Mauricio.

Re: Data from a partition was somehow lost [message #235983 is a reply to message #235953] Tue, 08 May 2007 10:36 Go to previous messageGo to next message
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
Senior Member
Mauricio, seems by mistake some one has dropped partition of April month, Check the status of local index of respective partition and you may get a information for the same.

Regards,
Harshad
Re: Data from a partition was somehow lost [message #235997 is a reply to message #235953] Tue, 08 May 2007 11:48 Go to previous message
mauricio@tecnologica
Messages: 4
Registered: August 2006
Junior Member
Harshad, thank you for the feedback.

The partitions are all there. In the beginning I thought it could be caused by our automatic create/drop partition procedure but the job that encapsulates it was broken and was not run sice march.

Our database runs in archivelog mode. Is there a way to look into the archivelogs for an operation that could have run and removed the data?

Thanks in advance.

PS: follows the queries showing all the partitions.

SQL> select distinct partition_name from user_tab_partitions;

PARTITION_NAME
------------------------------
P_2006_09
P_2006_10
P_2006_11
P_2006_12
P_2007_01
P_2007_02
P_2007_03
P_2007_04
P_MAX

9 rows selected.

SQL> select distinct partition_name from user_ind_partitions;

PARTITION_NAME
------------------------------
P_2006_09
P_2006_10
P_2006_11
P_2006_12
P_2007_01
P_2007_02
P_2007_03
P_2007_04
P_MAX

SQL> select distinct partition_name from user_tab_subpartitions;

PARTITION_NAME
------------------------------
P_2006_09
P_2006_10
P_2006_11
P_2006_12
P_2007_01
P_2007_02
P_2007_03
P_2007_04
P_MAX

9 rows selected.

SQL> select distinct partition_name from user_ind_subpartitions;

PARTITION_NAME
------------------------------
P_2006_09
P_2006_10
P_2006_11
P_2006_12
P_2007_01
P_2007_02
P_2007_03
P_2007_04
P_MAX

9 rows selected.
Previous Topic: sending mail
Next Topic: execute a job every x minutes
Goto Forum:
  


Current Time: Sat Dec 03 14:26:38 CST 2016

Total time taken to generate the page: 0.07297 seconds