Geek Answers Handbook

Why did Oracle lose data during commit?

I have a pretty standard SQL query:

TRUNCATE TABLE TABLE_NAME; INSERT INTO TABLE_NAME ( UPRN, SAO_START_NUMBER, SAO_START_SUFFIX, SAO_END_NUMBER, SAO_END_SUFFIX, SAO_TEXT, PAO_START_NUMBER, PAO_START_SUFFIX, PAO_END_NUMBER, PAO_END_SUFFIX, PAO_TEXT, STREET_DESCRIPTOR, TOWN_NAME, POSTCODE, XY_COORD, EASTING, NORTHING, ADDRESS ) SELECT BASIC_LAND_AND_PROPERTY_UNIT.UPRN, LAND_AND_PROPERTY_IDENTIFIER.SAO_START_NUMBER AS SAO_START_NUMBER, LAND_AND_PROPERTY_IDENTIFIER.SAO_START_SUFFIX AS SAO_START_SUFFIX, LAND_AND_PROPERTY_IDENTIFIER.SAO_END_NUMBER AS SAO_END_NUMBER, LAND_AND_PROPERTY_IDENTIFIER.SAO_END_SUFFIX AS SAO_END_SUFFIX, LAND_AND_PROPERTY_IDENTIFIER.SAO_TEXT AS SAO_TEXT, LAND_AND_PROPERTY_IDENTIFIER.PAO_START_NUMBER AS PAO_START_NUMBER, LAND_AND_PROPERTY_IDENTIFIER.PAO_START_SUFFIX AS PAO_START_SUFFIX, LAND_AND_PROPERTY_IDENTIFIER.PAO_END_NUMBER AS PAO_END_NUMBER, LAND_AND_PROPERTY_IDENTIFIER.PAO_END_SUFFIX AS PAO_END_SUFFIX, LAND_AND_PROPERTY_IDENTIFIER.PAO_TEXT AS PAO_TEXT, STREET_DESCRIPTOR.STREET_DESCRIPTOR AS STREET_DESCRIPTOR, STREET_DESCRIPTOR.TOWN_NAME AS TOWN_NAME, LAND_AND_PROPERTY_IDENTIFIER.POSTCODE AS POSTCODE, BASIC_LAND_AND_PROPERTY_UNIT.GEOMETRY AS XY_COORD, BASIC_LAND_AND_PROPERTY_UNIT.X_COORDINATE AS EASTING, BASIC_LAND_AND_PROPERTY_UNIT.Y_COORDINATE AS NORTHING, decode(SAO_START_NUMBER,null,null,SAO_START_NUMBER||SAO_START_SUFFIX||' ') ||decode(SAO_END_NUMBER,null,null,SAO_END_NUMBER||SAO_END_SUFFIX||' ') ||decode(SAO_TEXT,null,null,SAO_TEXT||' ') ||decode(PAO_START_NUMBER,null,null,PAO_START_NUMBER||PAO_START_SUFFIX||' ') ||decode(PAO_END_NUMBER,null,null,PAO_END_NUMBER||PAO_END_SUFFIX||' ') ||decode(PAO_TEXT,null,null,'STREET RECORD',null,PAO_TEXT||' ') ||decode(STREET_DESCRIPTOR,null,null,STREET_DESCRIPTOR||' ') ||decode(POST_TOWN,null,null,POST_TOWN||' ') ||Decode(Postcode,Null,Null,Postcode) As Address From (Land_And_Property_Identifier Inner Join Basic_Land_And_Property_Unit On Land_And_Property_Identifier.Uprn = Basic_Land_And_Property_Unit.Uprn) Inner Join Street_Descriptor On Land_And_Property_Identifier.Usrn = Street_Descriptor.Usrn Where Land_And_Property_Identifier.Postally_Addressable='Y';

If I ran this query in SQL Developer, it works fine with 1.8 million functions included ( select count(*) from TABLE_NAME in the session confirms this).

But when I run the commit, the data disappears! select count(*) from TABLE_NAME now returns 0 results.

We did a few things to try and see what happens:

  • During Truncate table space is freed, and during insertion, it is filled again. There are no changes during commit. This means that the data is in the database.

  • If I execute the same query, but with the addition of and rownum < 100 to the end, the commit function works. Same thing with 1000 .

  • I found this question: oracle killings , and our database administrator tried "SQL Trace". This created a file> 4 GB in size, which, when analyzing TKPROF, produced a 120-page report, but we don’t know how to read it, and there is clearly nothing wrong with it.

  • There are no errors in our logs. And, obviously, errors during the commit itself.

  • There is a trigger / sequence that increases this process by 1.8 million.

I repeated this about 4 times, but the result is always the same.

So my question is simple - what happens to data during commit? How can we find out? Thanks.

Note. It went fine, so I do not believe that something is wrong with SQL-per-se.

Edit: The problem is resolved by recreating the table from scratch. Now when I insert it, it only takes 500 seconds compared to the previous 2000. And fulfillment happens instantly; when it was broken, fixing took 4000 seconds! I still don’t know why this happened.

For those who ask the question, the Create Table syntax:

 CREATE TABLE TABLE_NAME ( ADDRESS VARCHAR2(4000), UPRN NUMBER(12), SAO_START_NUMBER NUMBER(4), SAO_START_SUFFIX VARCHAR2(1), SAO_END_NUMBER NUMBER(4), SAO_END_SUFFIX VARCHAR2(1), SAO_TEXT VARCHAR2(90), PAO_START_NUMBER NUMBER(4), PAO_START_SUFFIX VARCHAR2(1), PAO_END_NUMBER NUMBER(4), PAO_END_SUFFIX VARCHAR2(1), PAO_TEXT VARCHAR2(90), STREET_DESCRIPTOR VARCHAR2(100), TOWN_NAME VARCHAR2(30), POSTCODE VARCHAR2(8), XY_COORD MDSYS.SDO_GEOMETRY, EASTING NUMBER(7), NORTHING NUMBER(7) ) CREATE INDEX TABLE_NAME_ADD_IDX ON TABLE_NAME (ADDRESS);
+6
source share
2 answers

Do you still lose data if you transfer a transaction to an anonymous block?

I assume that you open two SQL windows in SQL Developer, and that means two separate sessions. those. run SQL code in window 1 and execute commit; in window 2, the changes made in window 1 will not be performed.

The crop table performs implicit commit. Thus, the table will be empty until writing + commit is completed.

 begin execute immediate 'truncate table table_name reuse storage'; --use "reuse" if you know the data will be of similar size -- implicit commit has occured and the table is empty for all sessions insert into table_name (lots) select lots from table2; commit; end;

You should use truncate with reuse storage so that the database does not release all the blocks just to get the same number of blocks in the insert.

If you want / should have data available at all times, the best (but longer) method

 begin savepoint letsgo; delete from table_name; insert into table_name (lots) select lots from table2; commit; exception when others then rollback to letsgo; end;
0
source

You probably had a trigger that you did not notice. Can you check the oracle recycle bin table, which can store the history of your fallen table and trigger?

 Select * from recyclebin;

Links: http://www.oraclebin.com/2012/12/recyclebinflashback.html

-1
source

More articles:


All Articles