OraFAQ Forum: Server Administration » Data from a partition was somehow lost

Home » RDBMS Server » Server Administration » Data from a partition was somehow lost

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Data from a partition was somehow lost [message #235953]

Tue, 08 May 2007 08:57

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.

Report message to a moderator

Re: Data from a partition was somehow lost [message #235983 is a reply to message #235953]

Tue, 08 May 2007 10:36

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

Report message to a moderator

Re: Data from a partition was somehow lost [message #235997 is a reply to message #235953]

Tue, 08 May 2007 11:48

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.

Report message to a moderator

Previous Topic:	sending mail
Next Topic:	execute a job every x minutes

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 23:00:02 CDT 2024