Adding Datafiles to Tablepspaces URGENT !!

[vr76413]

Hello all..

I have ORacle 817 database. We are trying to load around 1.2GB data from a third party tool into the database thru ODBC Connection.

I have created 2 datafiles for this purpose in USERS TS and given that TS as default to the user who loads the data...

Datafile 1 has 700MB and Datafile 2 has 700MB Space allocated. oth the datafiles are in Autoextend mode.

Question is once datafile 1 is full will ORacle automatically switch to DATAFILE 2 while loading the data ?...

what are the things i need to do?...

Urgent Please help.

Thanks
Ron

[raghud]

The files are in autoextend mode, you need not worry about how many GB's you are loading until and unless you got disk space. If you are loading lots of data. Make sure you got enough rollbacks for your transactions.

[vr76413]

Thanks Raghu...

Right now i have 7 RBS each with 4MB....So can i increase the size of RBS from 4mb to say 16MB ?....

Also coming back to my earlier question, If the Datafiles are in Autoextend mode, when exactly will the swith happen from Datafile 1 to datafile 2 ?...

Thanks
Ron

[raghud]

I guess if you got 2 data files for your tablespace . Oracle doesn't wait for one datafile full and will try to use 2 datafile. Both will be written.
Also make sure you got enough number of extents for your tablespace/datafile.
Always bigger and enough rollback segments will be helpful for faster ....

[abhaysk]

Originally posted by vr76413
Thanks Raghu...

Right now i have 7 RBS each with 4MB....So can i increase the size of RBS from 4mb to say 16MB ?....

Ron

Well i can say ... u handle this in loading part only ....
I mean commit the TRAN at times ( Say after every 100000 reocrds updated/inserted )....

Rather than getting rollback segment full or so errors....

Now

Also coming back to my earlier question, If the Datafiles are in Autoextend mode, when exactly will the swith happen from Datafile 1 to datafile 2 ?...

As raghu said it would use both datafiles more or less equally & aat same time...

But dont give only Autoextend On...try limiting the datafile size with maxsize option...

Abhay.

[stmontgo]

Originally posted by abhaysk
Well i can say ... u handle this in loading part only ....
I mean commit the TRAN at times ( Say after every 100000 reocrds updated/inserted )....

Rather than getting rollback segment full or so errors....

Now


As raghu said it would use both datafiles more or less equally & aat same time...

But dont give only Autoextend On...try limiting the datafile size with maxsize option...

Abhay.

please explain to me how frequent commits reduce the possibility of rbs errors

[abhaysk]

Consider if u have a rollback segment of 50M...

now u want to update data thas size (avg) equal to 1.5Gb....

say u have 10 million records....
u r looping until all records have been updated....
and then commit

here u get a problem, after updating about 49M of data ( accordingly in rollback u have so much data ) and now rollback segment cant be extended....it throws an error cannot extend rollback segment %rollname%....

to avoid...if u commit for every 10000 records .... the rollback segement's space will be reused after commit

Abhay.