Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Populating sample table SH.COSTS on Windows database

tonibony7Nov 7 2020 — edited Nov 7 2020

Today I used the official scripts (from https://github.com/oracle/db-sample-schemas)) to create sample schemas in Oracle database. In the log files I noticed the following error:

INSERT /*+ append */ INTO costs
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04020: found record longer than buffer size supported, 524288, in
C:\app\oracle\product\12.2.0\db-sample-schemas-12.2.0.1\sales_history\\sale1v3.d
at (offset=0)

As a result, the table SH.COSTS was left empty.
It was very easy to diagnose and fix the problem. The table SH.COSTS is populated from the external table SH.SALES_TRANSACTIONS_EXT, created by the following command:

CREATE TABLE sales_transactions_ext
...
ORGANIZATION external 
...
  RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
...
 LOCATION
 ('sale1v3.dat')
...

The source data file (sale1v3.dat) uses [LF] ('\n') as a record separator. And the Oracle loader driver ignores this separator, because my database is on Windows. On Windows "NEWLINE" separator is [CR][LF] ('\r\n'), which is not found in the file. This works on Linux/Unix, because "NEWLINE" separator there is exactly [LF] ('\n').
The fix was obvious - I changed the record delimiter in the definition of the external table SALES_TRANSACTIONS_EXT (in file "sales_history/lsh_v3.sql"), like this:

  RECORDS DELIMITED BY '\n' CHARACTERSET US7ASCII

Then I restarted the scripts, the error disappeared and the table SH.COSTS was populated successfully. This definition will work both on Windows and on Linux/Unix.
Now, here is my (tough) question: Where should I address this finding, so that this silly bug can be fixed in the source? In this GitHub project there is file "CONTRIBUTING.md" which explicitly says "Due to widespread dependence on these scripts in their current form, no pull requests for changes can be accepted". And I doubt whether Oracle Support covers open source stuff like these scripts.

Comments

Dave Rabone
What data do you need to undo an insert? No data, just the rowid that was created.

What data do you need to undo a delete? The complete row.
Mihael
SQL> truncate table q;
SQL> insert into q select * from dba_objects ;

Statistics
---------------------------------------------------
         62  recursive calls
       9017  db block gets
       2716  consistent gets
          3  physical reads
    7636776  redo size   
      64851  rows processed
      

SQL> delete from q;

Statistics
---------------------------------------------------
         22  recursive calls
      72934  db block gets
       1072  consistent gets
          0  physical reads
   25324064  redo size
      64851  rows processed
See difference in redo size.
just a DBA
What data do you need to undo an insert? No data, just the rowid that was created.

What data do you need to undo a delete? The complete row.
But "complete row" is inserted also to data tablespace.
Dave Rabone
... and that at first glance is counterintuitive because the reverse argument should apply to redo, until you recognize that undo is also protected by redo.
just a DBA
Dave Rabone wrote:
... and that at first glance is counterintuitive because the reverse argument should apply to redo, until you recognize that undo is also protected by redo.
Sorry, not clear. For example, 'Insert' inserts 100 Mbytes to data segments. 'Delete' just moves this data into undo. Why redo is so different?
Dave Rabone
Because the operations that change both the undo segments and data segments that are also recorded in redo.

Go back and reread the concepts guide (several times) and understand the beautifully elegant way that the undo mechanism provides read consistency, rollback and statement level flashback and the redo mechanism gives recovery capabilities against software and hardware failure.

The key thing is that data and undo segments use the same recovery mechanism, the redo.
Fran
 'Delete' just moves this data into undo
No, both, Redo and undo are generated with insert and delete. Redo and Undo, are blocks and works equally.

The undo generated will be enough information to make the data "go away", the redo generated will be enough information to make the insert "happen again".

In your example, imagine that you create the new table inserting data from other table. Your table haven't anything "before" so undo is less than redo. When you delete the table, you have info "before" and "data to save" for "re-write" the table.

The delete makes more undo information because it has "before" data. That's why it need more time to finish.


from:
http://www.google.es/url?sa=t&rct=j&q=insert%20redo&source=web&cd=2&cad=rja&ved=0CD4QFjAB&url=http%3A%2F%2Fasktom.oracle.com%2Fpls%2Fapex%2Fz%3Fp_url%3DASKTOM.download_file%253Fp_file%253D1435150530862588762%26p_cat%3Dundo_redo.pdf%26p_company%3D822925097021874&ei=45wsUcDpKIuJhQfYroDQDQ&usg=AFQjCNFtDLW6XvP2aDN8zDCPki92UcK95A&bvm=bv.42965579,d.ZG4
just a DBA
Good document, but it doesn't explain in detail.
For example, 'insert' inserts 100Mb into data blocks. Undo size for this is small, assume, 1Mb. So total redo will be about 101Mb.
'Delete' just copies these 100Mb blocks into undo segments and marks data blocks as free. So total redo should be also about 100Mb ?! But it is several times more! Why?
Mihael
For example, 'insert' inserts 100Mb into data blocks. Undo size for this is small, assume, 1Mb. So total redo will be about 101Mb.
'Delete' just copies these 100Mb blocks into undo segments and marks data blocks as free. So total redo should be also about 100Mb ?! But it is several times more! Why?
1.When you insert rows, database populates data blocks. In this case size of redo will be about size of inserted blocks plus some small amount of undo blocks.
2.When you delete rows, database does not just copy original rows to undo, but creates undo records. Undo record for delete contains before images for all columns and is larger in size that usual data record. Therefore total amount of undo blocks will be more than total data blocks.
3.When you update rows, redo will include undo records and new data. The more columns are updated, the larger size of undo record and more new data. Therefore update operation can even generate more redo than delete. Updating all columns will generate almost the same redo as for delete + insert.
Jonathan Lewis
Answer
just a DBA wrote:
Dave Rabone wrote:
... and that at first glance is counterintuitive because the reverse argument should apply to redo, until you recognize that undo is also protected by redo.
Sorry, not clear. For example, 'Insert' inserts 100 Mbytes to data segments. 'Delete' just moves this data into undo. Why redo is so different?
There's an optimisation that Oracle can use on the insert that it doesn't use on the delete.
Imagine you insert 80,000 rows of 100 bytes each - for a total of about 80MB raw data, with 80 rows per block, 1,000 blocks.

On the insert Oracle can optimise the undo and redo sizes by creating "array based" undo records and redo vectors, so the overheads amount to about 200 or so bytes per block in the table.

On the delete this optimisation doesn't apply - so the overheads amount to about 200 or so bytes per row.

If you check your figures you'll probably see that the difference in redo is about 200 to 250 bytes per row inserted.

Regards
Jonathan Lewis
Marked as Answer by just a DBA · Sep 27 2020
1 - 10

Post Details

Added on Nov 7 2020
1 comment
580 views