Question Re: Oracle Database - Shrink Tablespace in Technology Q&A

Oracle Database - Shrink Tablespace

Former Member — Tue, 26 Apr 2016 21:54:01 GMT

Hello,

I´m with some doubts regarding managing of oracle tablespaces and I don´t know if it´s possible to make some actions that I need to perform due some disk space!

I need to shrink a tablespace! It´s possible to do this action using Brtools?

As you can see in the following image I have a lot of "free" space, this is, some tablespaces has a lot of reserved space and I need some free space in this specific disk where those tablespaces (datafiles) are set! So, I guess it´s possible to shrink tablespaces with for examples TOAD oracle software... but it´s possible using the Brtools?

Thank you,

João Dimas

Re: Oracle Database - Shrink Tablespace

ACE-SAP — Tue, 26 Apr 2016 22:10:04 GMT

Hello João

You cannot shrink a tablespace by itself, , but you can shrink it's datafiles

In your case I think you could just drop a tablespace... as one of them should only contain few segments. This sometimes happens after an upgrade.

1715052 - Tablespace cannot be deleted after upgrade

Check segment left in the almost empty tablespace

Select count(1), TABLESPACE_NAME from dba_segments where owner like 'SAP%' and TABLESPACE_NAME like 'PSAPSR3702%' group by TABLESPACE_NAME;

COUNT(1) TABLESPACE_NAME

---------- ---------------

19 PSAPSR3702

991 PSAPSR3702X

You could move these segments to the appropriate tablespace using the result from these commands

SELECT 'ALTER TABLE "' || OWNER ||'"."' || SEGMENT_NAME ||'" MOVE TABLESPACE '||' PSAPSR3702X;'

FROM dba_segments

WHERE TABLESPACE_NAME = 'PSAPSR3702'

AND SEGMENT_TYPE = 'TABLE'

SELECT 'ALTER INDEX "' || OWNER ||'"."' || SEGMENT_NAME || '" REBUILD TABLESPACE PSAPSR3702X ;'

FROM dba_segments

WHERE TABLESPACE_NAME = 'PSAPSR3702'

AND SEGMENT_TYPE = 'INDEX'

And then you can drop the tablespace

drop tablespace PSAPSR3702 including contents and datafiles;

Best regards

Re: Oracle Database - Shrink Tablespace

JamesZ — Wed, 27 Apr 2016 05:37:43 GMT

Hi João,

Brtools has data file resize option:

brtools

2 - Space management

5 - Alter data file

3~ Alter data file action (action) . [] <<<<<<resize

Also for your case, tablespace reorganization may be as well, details please refer to SAP note

646681.

Best regards,
James

Re: Oracle Database - Shrink Tablespace

Former Member — Wed, 27 Apr 2016 09:05:53 GMT

Hello Yves,

Thank you for your help.

Some days ago I finished a support packages update activity in this specific SAP system (Solution Manager), I updated it to the stack 14 (SPS14) which is available in SAP Marketplace.

But let me inform you some details about these two tablespaces, PSAPSR3702 and PSAPSR3702X.

Before the importing of support packages activity the tablespace PSAPSR3702 has other size, as you can see in the following print screen that I took before that activity:

As you can see in the above image the PSAPSR3702 had ~85% of used space. During the support package activity, the shadow instance method created a new tablespace PSAPSR3702X which have now ~70% of occupied space... and now the PSAPSR3702X has only 0,76% of used space!

Kind regards,

João Dimas

Re: Oracle Database - Shrink Tablespace

Former Member — Wed, 27 Apr 2016 09:54:59 GMT

Hello again Yves,

I ran the following sql statement and this is the output:

SQL> Select count(1), TABLESPACE_NAME from dba_segments where owner like 'SAP%' and TABLESPACE_NAME like 'PSAPSR3702%' group by T

ABLESPACE_NAME;

COUNT(1) TABLESPACE_NAME

---------- ------------------------------

19 PSAPSR3702

827 PSAPSR3702X

Can you tell me what I must to do now in way to delete the old tablespace PSAPSR3702?

Thank you,

João Dimas

Re: Oracle Database - Shrink Tablespace

ACE-SAP — Wed, 27 Apr 2016 11:38:17 GMT

Hello João

I did had the same problem when applying SP stack on a solman.

As the "single system mode" option is not available in the SUM when upgrading a dual stack system a new PSAPSR3702X tablespace is created for the shadow instance.

Some objects are left in the previous version tablespace.

1819182 - Tables (TTREE*) remain in old exchange tablespace

Just move the segments (tables & indexes) to the appropriate tablespace (PSAPSR3702X) either using the commands I did provide or with the BR*Tools:

brspace -c force -u // -f tbreorg -a reorg -tablespace PSAPSR3702 -t "*" -parallel 3 -n PSAPSR3702X

When the segments are moved just check that the tablespace (PSAPSR3702) is empty, drop it and it's datafiles using the provided SQL command or BR*Tools:

brspace -c force -u // –f tsdrop -t PSAPSR3702

Best regards