• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1092
  • Last Modified:

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?
  • 2
1 Solution
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.

PsychoDazeyAuthor Commented:
Thank you thats good to know.  I have another question I am going to post regarding "resue existing" command...stay tuned!
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.

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.
mohammadzahid !
Its really very good answer, i was thinking with different frame of mind.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now