SOLVED

ORA-01466: Unable To Read Data

Asked by OracleDba••23 views•oracle

ORA-01466: Unable To Read Data

#oracle#error

Solutions(1)

Accepted Solution
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
Generally ,

This error happens when a DDL change happened on that object and we are trying to flashback to a timestamp, before that DDL change

.

Suppose , DDL change happened to the database at 5 PM, and if you are trying to flashback to 4 PM,( before the DDL change), then flashback will fail with

ORA-01466

error.

So flashback is possible only to a timestamp after the DDL change.

Let’s see with Example :

1. Let’s truncate a table ( TRUNCATE is a DDL statement)

16:00:00 SQL> select count(*) from test1;

COUNT(*)

----------

135932

17:00:00 SQL> truncate table test1;

Table truncated.

Here I have truncated the table at 17:00 hrs. Now we will try to flashback to a timestamp before the DDL, say 16:30 hrs.

SQL> FLASHBACK TABLE test1 TO TIMESTAMP TO_TIMESTAMP('2023-07-12 16:30:00', 'YYYY-MM-DD HH24:MI:SS');

FLASHBACK TABLE test1 TO TIMESTAMP TO_TIMESTAMP('2023-07-12 16:30:00', 'YYYY-MM-DD HH24:MI:SS')

*

ERROR at line 1:

ORA-08189: cannot flashback the table because row movement is not enabled

16:00:00 SQL> select count(*) from test1;

COUNT(*)

----------

135932

17:00:00 SQL> truncate table test1;

Table truncated.

Here I have truncated the table at 17:00 hrs. Now we’ll try to flashback to a timestamp before the DDL, say 16:30 hrs.

SQL> FLASHBACK TABLE test1 TO TIMESTAMP TO_TIMESTAMP('2023-07-12 16:30:00', 'YYYY-MM-DD HH24:MI:SS');

FLASHBACK TABLE test1 TO TIMESTAMP TO_TIMESTAMP('2023-07-12 16:30:00', 'YYYY-MM-DD HH24:MI:SS')

*

ERROR at line 1:

ORA-08189: cannot flashback the table because row movement is not enabled

Here we got different error, because row movement was not enabled for the table. Let’s enable it.

SQL> alter table test1 enable row movement;

Table altered.

SQL> FLASHBACK TABLE test1 TO TIMESTAMP TO_TIMESTAMP('2023-07-12 16:30:00', 'YYYY-MM-DD HH24:MI:SS');

FLASHBACK TABLE test1 TO TIMESTAMP TO_TIMESTAMP('2023-07-12 16:30:00', 'YYYY-MM-DD HH24:MI:SS')

*

ERROR at line 1:

ORA-01466: unable to read data - table definition has changed

As expected we got the error ORA-01466.

So flashback for a table is not possible if the timestamp is prior to the DDL change time.
OracleDba•

Post Your Solution