Rollback disabling

[Manuel Gómez González]

Hi all:

Does anyone knows how to prevent session or statement form rollback? Some way to disabling?

We are planning to delete a 25 million register table and don´t want to have trouble with filled rollback segments.

I guess deleting speed would be improved too.

Thank

[Printaporn_1]

I guess that what best you can do in Oracle is assign your transaction to a specific RBS segment.
insert always use rollback segment.

[ALPER ONEY]

Hi,
Regardless of which RDMS(sybase,oracle, ms sql server etc) you use, I guess that you can use minimally logged Transact Sql commands or some trace flags defined by your RDMS such as truncate table command, appropriate taceflags in sybase ASE to avoid filling up the logsegment.
HTH
ALPER Ã NEY
SYBASE DBA & REPLICATION ADMIN
I.S.E TAKASBANK I

[Manuel Gómez González]

Database is ORACLE,

thanks

[Yogeeraj_1]

hi,
-
if you are deleting all the records, you would just do:
truncate table ;
-
If not and if hopefully you are using partitioning. You can do a mass delete in parallel using parallel DML (see the server concepts guide)
-
Otherwise, if 25 millions is a large percentage of the table, it is sometimes better to:
-
create table temp nologging as select * from t where id not in ( select id from a );
-
(keep the rows you want)
-
index temp (unrecoverable, in parrallel )
grant on temp (as you had for t)
drop table t;
rename temp to t;
-
If you need any further clarifications, please let us know.
-
regards
Yogeeraj

[Graham Cameron_1]

As per Yogeeraj, you can't disable rollback.

We have similar problems here with 600m row tables, and our approach is (similar as per Yogeeraj) to create a temporary table containing just the rows we want, then truncate the original, and copy back the rows, using either exp/imp or "select * from" - we're still testing.

Later we will be partitioning by date - these are date ordered records, 1 day = 1 partition, so in future we can just drop the partitions containing old data, and avoid rollback that way.

GOod luck

-- Graham

[Manuel Gómez González]

Thanks guys:

Our target was disabling rollback because we have a great disk space lack, therefore we can´t operate as you suggested (creating temporary tables, etc.).

We´ll create a PL/SQL for deleting small pieces of information each time.

Tha

[Graham Cameron_1]

Manuel

You can create a table with NOLOGGING, so it doesn't generate rollback. The NOLOGGING applies only to the table creation, but it will hold for create as select.

ie
CREATE TABLE mycopy
STORAGE (etc)
NOLOGGING
AS SELECT * FROM bigtable where ....

TRUNCATE bigtable ;

exp myycopy
imp into bigtable.

TRUNCATE doesn't create rollback, and insert generates little rollback (because there's no 'before' data to save), so you should get away with it.

-- Graham

[Claudio Cilloni]

Do you have 1 or more columns that let you to subdivide you 25 million records into smaller subsets? So you could delete each subset separately and commit after each deletetion. This will reduce the need of rollback space,
but the deleting speed isn't good.

Keep an eye on the archive logs if you DB is in ARCHIVELOG mode.

hth,
Claudio

[Yogeeraj_1]

hi again,

of course you can do it using BULK COLLECT, however beware that if you have a table with more some number of indexes, deleting just does alot of work. Maintaining the index structures for xxx,xxx deletes
can be "cumbersome". Additionally there is lots of UNDO and REDO generated.

good luck
regards
Yogeeraj