1

In my process I have 2 tables.

target table, load table.

The target table holds the current data, and a lot of queries are fired against it.

When new data coming we use a FULL LOAD and the goal is to switch the current data that in the target table with the new data.

The new data exists in the load table.

In goal to do that we are using

Exchange partition

Those are the steps:

  1. create new load table with only 1 partition -all_rows (values less than maxvalue).
  2. insert all the data from source to the load table by insert statement.
  3. exchange partition of the all_rows (=all the new data) with the target table.

That way, we have the most new data, and there is no space that data is not available. The problem start when I want to drop my load table after the exchange. If someone send a query to the target table he can get exception:

ORA-08103: Object no longer exists.

Even the drop was only on the load_table. Same when load table truncate.

  • What does that mean?
  • Those two tables will be together forever?
  • Every truncate or drop I need to be afraid on the second one?
  • Why they are connected at all?

I'm using oracle 11g.

My actions:

CREATE TABLE tbl_ld
partition by range (col1) (partition all_rows values less than (maxvalue)
AS  SELECT * FROM tbl_tgt WHERE 1=2 ;

insert into tbl_ld
select * from tbl_temp;

alter table tbl_ld exchange partition all_rows with table tbl_tgt;

-- drop or truncate
drop table tbl_ld;
truncate table tbl_ld;
0

AskTom (answered by Connor McDonald) : ORA - 08103:Object No Longer Exists and an even more detailed explanation by Connor McDonald: A cool thing with EXCHANGE PARTITION (recommended by AskTom here).

TRUNCATE TABLE is a DDL statement, not a DML statement like delete. But what happens is a phenomena that is similar to a phenomena that can be caused by delete or update statements.

If delete or update statements are involved then we have

ORA-01555: snapshot too old: rollback segment number string with name "string" too small

There is also an AskTom article for ORA-01555


What happens:

Assume in your transaction you start a SELECT statement on a table. During this select statement another transaction manipulates the data:

  • Either it deletes or updates the table data (DML) and commits
  • or it truncates a partitions or the table or exchanges a partition (DDL)

In this case your session that executes the select statement cannot find the data on its original place because the table has already changed. But it is necessary to retrieve the table data in a consistent way, this means not need the data from the table from the point in time when the query started. YOur session it may be able to find the data in a rollback segment (in case of a DML statement) or in the table whose segment you have exchanged with the partition (in case of exchange partition) or it is a segment that is not assigned to a table anymore but is still available and unaltered on disk (DDL statement). If Oracle finds all the data it can continue with your select statement. If it does not find the data, either because the rollback segment where already overwritten (DML case) or the segment was already modified or deleted (DDL case) then Oracle will throw an error. in the DML case Oracle will throw an ORA-01555, in the DDL case it will throw a ORA-08103.

Connor McDonald writes in the first article referenced:

Partitioned tables are the most common scenario to see this error, but by no means the only way.

Anything that physically alters an underlying segment that is being queried can create the issue. Examples:

  • truncate table
  • alter table move
  • alter index rebuild

Both articles are very instructive and I recommend you to read both (the second one is a video)

So how can you handle this problem?

  1. simply repeat the action in case of this error (or rollback the transaction and repeat it)
  2. maybe it helps if you wait some time before you drop the exchanged partition. Maybe this will decrease the frequency of this error.
  • miracle173. thanks a lot for the long answer. First - I know that if one session execute a select query and another drop or truncate so this exception raise. I know that. that not what I'm asking. My question is: After I did exchange partition and I'm trying to drop one table and execute a query on the second (!!!) table, I get it. Not the same table, the second. Second - Do you think that if i will some time before the drop it will help? I'm trying to understand the realtion between those 2 tables... After some time they just back to stand alone each one? – user2671057 May 28 '17 at 19:09
  • @user2671057 I know it is the second and I wanted explain it. After the exchange the second table contains the segment that was originally assignet to the first one.I will review my post in the next days and maybe I can be clearer. But you should watch the video in the second link it describes exactly your problem – miracle173 May 28 '17 at 19:57
  • I got it, it's just realy realy strange. Ok, so exchange partition make a switch not on data but on meta data. That's why when you drop or truncate the table it can hurt the second table. It's like a bug. Because it's not supposed to work like that. That means that you shouldn't execute an drop\truncate or another alter table on those 2 tables for ever! For the chance that it will hurt the second. How this issue can be solved? Oracle didn't that issues. This exchange not work well. Is that help if we wait some time? Why? – user2671057 Jun 1 '17 at 4:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.