Re: Resize tablespace SYSTEM

In article <351d10a0.1978392708_at_news.istar.ca>, Robert Prendin <rprendin_at_magi.com> wrote:
>This can be done while the DB is open.. Either add a datafile or
>resize the current one..
>
>alter database datafile '/oradata1/sys01.dbf' resize 200M;
>
> Just modify what's between parentheses to point to the system
>tablespace file you want to resize.. (select * from dba_data_files)..
>
>Cheers, Robert Prendin
>Olivier Bercovitz <oberco_at_club-internet.fr> wrote:
>
>>This is a multi-part message in MIME format.
>>--------------494E3FC84A3BBB435811AB55
>>Content-Type: text/plain; charset=us-ascii
>>Content-Transfer-Encoding: 7bit
>>
>>Hello everybody,
>>
>>Can I resize the SYSTEM tablespace without risk ??
>>And if yes, has the database to be opened or just mounted for this
>>operation ??
>>(We work on 7.3.4 on AIX 4.1)

During a previous discussion about this, Jared Still noted there had been cases of the tablespace corrupted. Oracle generally won't let you reduce the tablespace below where things are being used in it. It worked ok for me (7.3.4 Solaris 2.6), although it gave me back much less than I expected. I suspect a plan_table had been created after some other stuff that was later deleted.

Do a dejanews search on the "old" database for RESIZE for more info, such as Wolfgang Brietling noted:
>
> Be careful with shrinking datafiles. Oracle stores the original file size in
> the file header, as well as the current size. Unfortunately, if you ever later
> want/need to use the "create controlfile ..." command, Oracle checks the file
> size that it retrieves from the OS against the create file size and gives you
> an ORA-01200 error if the actual file size is smaller than the size it was
> originally created as. Oracle doesn't mind if the current size is larger than
> the size it was created as, so expansion is OK.
> This is true for 7.3.2 at least and may be fixed in later versions.
 

>>
>>Thanks for your precisions.
>>Regards. Olivier.
>>
>>--------------494E3FC84A3BBB435811AB55
>>Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
>>Content-Transfer-Encoding: 7bit
>>Content-Description: Card for Olivier Bercovitz
>>Content-Disposition: attachment; filename="vcard.vcf"
>>
>>begin: vcard
>>fn: Olivier Bercovitz
>>n: Bercovitz;Olivier
>>org: Steria
>>adr: ;;;Maisons-Alfort;;94700;France
>>email;internet: oberco_at_club-internet.fr
>>x-mozilla-cpt: ;0
>>x-mozilla-html: FALSE
>>version: 2.1
>>end: vcard
>>
>>
>>--------------494E3FC84A3BBB435811AB55--
>>
>

-- 
These opinions are my own and not necessarily those of Information Quest
jgarry@eiq.com                           http://www.informationquest.com
http://ourworld.compuserve.com/homepages/joel_garry
"See your DBA?"  I AM the @#%*& DBA!