Tablespace PSAPUNDO (resizing of reserved space)

[Former Member]

Hello,

Tell me one thing... it´s possible to resize the space of the tablespace PSAPUNDO through the BR*Tools?

I need to resize that because as you can see in the following image, this tablespace have a lot of space reserved and I need to decrease that reserved space for PSAPUNDO in 135 Gb! (This is due to a copy system that we done some weeks ago):

How can I do that through the BR*Tools? Can you guide me please?

Kind regards,

samid raif

[joo_migueldimas · Accepted solution]

Hello,

As already suggested, the better way is to create another PSAPUNDO!

For this procedure you will have to take the SAP system down because PSAPUNDO is dropped and recreated, which is not possible if there are active transactions.

Log in to the database server as user ora<sid>.

sqlplus /nolog

connect as sysdba

-- Stop/restart to make sure there is no more activity

shutdown immediate;

startup;

-- Create a temporary undo tablespace
create undo tablespace PSAPUNDO2 datafile '/oracle/<SID>/sapdata3/undo2.data1' size 1G;

-- Make this the default undo tablespace
alter system set undo_tablespace=PSAPUNDO2 scope=memory;

-- Drop PSAPUNDO
drop tablespace PSAPUNDO including contents and datafiles;

-- Recreate PSAPUNDO, e.g. with a single datafile of 2 GB
create undo tablespace PSAPUNDO datafile '/oracle/<SID>/sapdata3/undo_1/undo.data1' size 2G;

-- Make PSAPUNDO again the default undo tablespace
alter system set undo_tablespace=PSAPUNDO scope=both;

-- Get rid of the temporary undo tablespace
drop tablespace PSAPUNDO2 including contents and datafiles;

-- Just another stop/start to end cleanly
shutdown immediate;
startup

Give us a feedback

BR

João Dimas

[Former Member · Accepted solution]

Hello Dimas,

You are the man!!   I followed all your instructions and now everything is ok!

Thank you very much,

Kind regards,

samid raif

[Former Member]

I don't think you can 'decrease' the size of a tablespace. BRTools helps you 'extend' (to increase the size, by adding data files) the tablespace.

So the option left is, as suggested by another friend, is to drop the current psapundo tablespace and create a new one with size around 20 GB. Search for the correct process.

If you still want to explore brtools, run it by orasid and check 'instance management' or some similar options.

[tomislav_bobanovic]

Hi,

I think that the fastest way will be to create new UNDO tablespace with proper size, change DB parameters to use the new one and after this you can drop old undo tablespace.

you will nedd to restart the DB after parameter changing.

BR

Tomislav

[Former Member]

Hello Tomislav,

Hum... yes, but as you mentioned, that way I will need to restart the oracle DB... and because of that I wanted to do a resizing of PSAPUNDO, I believe the resizing way it won´t require the oracle db restart, am I right?

regards,

samid raif

[Former Member]

And for you latest question, yes, resizing doesn't need DB restart. But, again, resizing ~ extending.