Geek Answers Handbook

ORA remove / truncate

I use the SQL loader to load my data into the database.

Before inserting data, I need to delete existing data in the table:

options(skip=1,load=250000,errors=0,ROWS=30000,BINDSIZE=10485760) load data infile 'G:1.csv' "str '^_^'" replace into table IMPORT_ABC fields terminated by "," OPTIONALLY ENCLOSED BY '"' trailing nullcols( . . . .)

But I got an error, for example:

SQL * LOADER-926: OCI error while performing delete / truncation for IMPORT_ABC table ORA-30036: cannot extend segment by 8 in undo tablespace 'undo1'

How to delete data, for example, on 10000 lines? I know that I have a certain limit for my DB.

+4
source share
2 answers

Deleting records in batches can be performed in a PL / SQL cycle, but is usually considered bad practice, since all deletion should usually be considered as a single transaction; and this cannot be done from the SQL * Loader control file. Your database administrator must be UNDO sized to do the work you need to do.

If you delete the entire table, you will almost certainly be better off truncating, either in the control file :

 options(skip=1,load=250000,errors=0,ROWS=30000,BINDSIZE=10485760) load data infile 'G:1.csv' "str '^_^'" truncate into table IMPORT_ABC ...

Or as a separate truncate SQL * Plus / SQL Developer / statement / some other client before starting the download:

 truncate table import_abc;

The disadvantage is that your table will appear empty to other users while new rows are loading, but if this is a special import area (guessing on behalf of), which may not matter at all.

If your UNDO really so small, you may need to run several downloads, and in this case - perhaps obviously - you need to make sure that you only have truncate in the control file for the first (or use the separate truncate operator) and append instead in subsequent control files, as you noted in the comments.

You can also consider external tables if you use this data as a base to populate something else, since there is no UNDO when replacing an external data source. You probably need to talk to your database administrator about setting this option and grant you the necessary permissions to access directories.

+1
source

Your table space is canceled to small in order to store all the cancellation information, and it seems that it cannot be expanded.

You can split the import into smaller packages and release a commit after each batch or get your own DBA to increase the table space for undo1

And use truncate instead of replacing before you start using immports

0
source

More articles:


All Articles