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
Those are the steps:
- create new load table with only 1 partition -all_rows (values less than maxvalue).
- insert all the data from source to the load table by insert statement.
- 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.
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;