Can you Increase SYSTEM Tablespace? 1

[marrow]

SYSTEM T/S is 99% full. Wasn't set-up correctly and originally used as a default T/S, so = 328MB with 1.75MB free. Can I simply increase the size via ALTER. (Not certain of syntax)and is OK to do on-line.

Thanks

Marrow

 

[SantaMufasa]

Marrow,

Yes, there are multiple methods to achieve your objective:

My favourite method:

ALTER DATABASE DATAFILE '<fully-qualified name of existing SYSTEM file>' AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

The above allows your existing SYSTEM tablespace data file to expand in 10MB increments (as needed) to a maximum size of 2GB.

Adding another file to the SYSTEM tablespace:

ALTER TABLESPACE SYSTEM ADD DATAFILE '<fully-qualified name of a new, additional SYSTEM ts file>' SIZE 10M AUTOEXTEND ON NEXT 10M MAXISZE 2000M;

This adds an new, 10MB file to the SYSTEM tablespace that autoextends on an as-needed basis in 10MB increments to a maximum size of 2GB.

Let us know if you are satisfied with these alternatives.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[marrow]

One final point Santa, I took a look before & afterwards and as you see The Potential has dropped to from 34Gb to 2Gb

Tablespace Potential Current Potential Total Auto
Name # File Size File Size Bytes Used Bytes Free Bytes Ext. Filename
--------------- ---- ---------------- ---------------- ---------------- ---------------- ------ ---- -----------------------------------------------

SYSTEM-before 1 34,359,721,984 346,030,080 344,195,072 34,015,526,912 98 Yes
SYSTEM-after 1 2,097,152,000 346,030,080 344,195,072 1,752,956,928 83 Yes

 

[SantaMufasa]

Yes, Marrow, that is correct. The "potential" file size has dropped from 34GB to 2GB because previously, your SYSTEM tablespace data file was set for MAXSIZE UNLIMITED (34GB). In the code I provided, the MAXSIZE is 2000M...on purpose...since some operating systems have a sad problem when data files pass the 2GB threshold: the block on the 2GB threshold and all data in the same table that follow that block become inaccessible.

Also, a 2GB maximum facilitates movement of the file for backup purposes: it is faster and easier to move multiple 2GB-maximum files than it is to move files > 2GB.

Therefore, when you begin to approach the 2GB maximum for any database datafile, just use the second piece of code to add one or more additional data files to the tablespace.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[marrow]

Thanks again Santa

Marrow