adding datafiles and autoextend

I am running Oracle 10g on windows server 2003.  I have an instance that has a system datafile that is almost full.  I added another datafile to a different drive because there is not much room left on the original drive.  My question is this: should I stop the original data file from auto-extending now?
LVL 6
PsychoDazeyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mohammadzahidCommented:
It will be a good idea to stop datafile from growing but it is not necessary since you have added a datafile to the tablespace. Oracle allocates space as needed and does not autoextend if it can find enough room to create extent.


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PsychoDazeyAuthor Commented:
Thank you thats good to know.  I have another question I am going to post regarding "resue existing" command...stay tuned!
0
sonicefuCommented:
1) If you've created non-system tables in system tablespace then move them to non-system tablespace,
do not allow user objects to be stored in SYSTEM, SYSAUX etc.  This tablespace should only store system specific objects.
Move tables using ALTER TABLE MOVE command.

2)
Relocating SYSAUX Occupants

If there is a severe space pressure on the SYSAUX tablespace, you may decide to move components out of the SYSAUX tablespace to a different tablespace.

*) Query the column SPACE_USAGE_KBYTES in the V$SYSAUX_OCCUPANTS to how much of the
    SYSAUX tablespaces space each of its occupants is currently using.

**) Query the column MOVE_PROCEDURE to obtain the specific procedure you must use    in order to
    move a given occupant out of the SYSAUX tablespace.

    SQL> exec dbms_wm.move_proc('DRSYS');

3) You should add datafile if there is space pressure on SYSTEM tablespace.
0
sonicefuCommented:
mohammadzahid !
Its really very good answer, i was thinking with different frame of mind.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.