1748195 Members
4228 Online
108759 Solutions
New Discussion

Rollback disabling

 

Rollback disabling

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
9 REPLIES 9
Printaporn_1
Esteemed Contributor

Re: Rollback disabling

I guess that what best you can do in Oracle is assign your transaction to a specific RBS segment.
insert always use rollback segment.
enjoy any little thing in my life
ALPER ONEY
Advisor

Re: Rollback disabling

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
never ever give up.

Re: Rollback disabling

Database is ORACLE,

thanks
Yogeeraj_1
Honored Contributor

Re: Rollback disabling

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Graham Cameron_1
Honored Contributor

Re: Rollback disabling

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
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.

Re: Rollback disabling

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
Honored Contributor

Re: Rollback disabling

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
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Claudio Cilloni
Honored Contributor

Re: Rollback disabling

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
Honored Contributor

Re: Rollback disabling

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

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)