Adding Datafiles to Tablepspaces URGENT !!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Adding Datafiles to Tablepspaces URGENT !!

  1. #1
    Join Date
    Oct 2002
    Posts
    284

    Adding Datafiles to Tablepspaces URGENT !!

    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

  2. #2
    Join Date
    Aug 2001
    Posts
    267
    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.
    Raghu

  3. #3
    Join Date
    Oct 2002
    Posts
    284
    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

  4. #4
    Join Date
    Aug 2001
    Posts
    267
    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 ....
    Raghu

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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
    I'm stmontgo and I approve of this message

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width