on 04-26-2016 10:54 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
User | Count |
---|---|
93 | |
11 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.