Remote CLient Copy

[SAPSupport]

We are performing our Quality Client Refresh via Remote client copy and now the status is all the tables has been copied but there are warning in deletion because my PSAPUNDO tablespace having less space (10 GB) now I have extended the tablespace by 51 GB and the space is consuming very fast. Now my query is as is there any option to release those free space into disk (example like reorganization etc) after completed the Client Refresh.

------------------------------------------------------------------------------------------------------------------------------------------------
Learn more about the SAP Support user and program here.

[SAPSupport · Accepted solution]

You can reduce the size of the tablespace 'PSAPUNDO' by recreate it with a smaller size.

Following is the steps:

1) Check current Undo tablespace information.

SELECT TS.TABLESPACE_NAME, DF.FILE_NAME FROM DBA_TABLESPACES TS
INNER JOIN DBA_DATA_FILES DF
ON TS.TABLESPACE_NAME = DF.TABLESPACE_NAME
WHERE CONTENTS = 'UNDO'
ORDER BY DF.FILE_ID;


2) Create temporarily Undo tablespace. (with proper size)

CREATE UNDO TABLESPACE temp_undotbs
DATAFILE '<datafile path and name>'
SIZE 100M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

3) Switch to temporarily Undo tablespace which is created above step 2.
ALTER SYSTEM SET UNDO_TABLESPACE = 'temp_undotbs';


4) delete original undo tablespace.

DROP TABLESPACE PSAPUNDO;


5) Re-create PSAPUNDO(with proper size)

CREATE UNDO TABLESPACE PSAPUNDO
DATAFILE '<datafile path and name>'
SIZE <size> AUTOEXTEND ON NEXT <size> MAXSIZE <size>;

6) switch to PSAPUNDO tablespace.
ALTER SYSTEM SET UNDO_TABLESPACE = 'PSAPUNDO';

7) drop temporally undo tablespace.
DROP TABLESPACE temp_undotbs;