asked on

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?

ASKER CERTIFIED SOLUTION

mohammadzahid

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PsychoDazey

ASKER

Thank you thats good to know. I have another question I am going to post regarding "resue existing" command...stay tuned!

sonicefu

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.

sonicefu

mohammadzahid !
Its really very good answer, i was thinking with different frame of mind.