Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

EXTENDING A TABLESPACE IN SAP 4.6C

EXTENDING A TABLESPACE IN SAP 4.6C

One of my tablespace is full, unfortunately i have reached the maximum of 254 i.e i cannot create any more datafiles.How can i extend this tablespace without adding the datafile (since i have reached the maximum) or which parameter do i need to adjust to allow me create more datafile to extend space in the tablespace.
The set is SAP r/3 4.6c, Oracle 8i running on Tru64 Unix.

I would appreciate any assistance since work is suffering.

Thanking you in advance.

Regards,
Eric.
3 REPLIES
Stephan.
Honored Contributor

Re: EXTENDING A TABLESPACE IN SAP 4.6C

Volker Borowski
Honored Contributor

Re: EXTENDING A TABLESPACE IN SAP 4.6C

Hello.

Use brtools and the "resize datafile"
function on an existing file to get rid of the space shortage first.

For 8i it might be neccessary to create new controlfiles to increase maxdatafiles and db_files in init.ora.
There is SAP note describing in detail
how to proceed.

Basicly it is an

alter database backup controlfile to trace;

Check user dump destination for the result
and adjust the number ob datafiles and save
as script.

Startup nomount the DB and run the script.

Check out for all precautions named in the SAP Note BEFORE you start.

Volker
Eric Antunes
Honored Contributor

Re: EXTENDING A TABLESPACE IN SAP 4.6C

Hi Duncan,

You can extend an existing datafile from that tablespace with the following sql commands (you need to have the DBA role):


select tblspc.name, tblspc.included_in_database_backup, df.name datafile_name, df.bytes, df.blocks,df.block_size
from v$tablespace tblspc, v$datafile df
where tblspc.name in ('')
and df.ts# = tblspc.ts#
order by tblspc.name, df.name

alter database datafile '' resize M

Best Regards,

Eric
Each and every day is a good day to learn.