How to release space from PSAPSR3 on 11g

[Former Member]

Dear expert

I've would like to  'shrink' PSAPSR3(LMTS and ASSM)

the situation of this tablespace is like that

It now have nearly 1T freespace , i would like to release  600G so that the disk could have more space.

Thank you

Kate

[ACE-SAP · Accepted solution]

Hello Kate

What kind of system is it ?

If it is a production system I would leave things as they are, keeping the freespace for future extensions.

If it is not a production system you could try to shrink the datafiles using the here under script.

It might not save a lot of space depending if space is over or bellow high water mark, but it's quick and easy.

To reclaim more space you will need to go through a tablespace reorg.

Regards

Select 'ALTER database datafile ''' || File_name || ''' resize ' || smallest_Mb || ' M;' FROM (
  SELECT
       file_name as File_name,  ceil( (nvl(hwm,1)*C.VALUE)/1024/1024 ) as smallest_Mb,  ceil( blocks*C.VALUE/1024/1024) as currsize_Mb,
       ceil( blocks*C.VALUE/1024/1024) - ceil( (nvl(hwm,1)*C.VALUE)/1024/1024 ) as Shrink_Mb,
       a.autoextensible as Auto_extend , a.maxbytes/1024/1024 as Extend_max_MB
  FROM dba_data_files a,
       (select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b,
       (select value from v$parameter where name = 'db_block_size') C
  WHERE a.file_id = b.file_id(+) AND a.file_name in (select FILE_NAME from  dba_data_files)
) WHERE 1=1;

[Former Member · Accepted solution]

Hello Mr Yves and everyone，

Sorry for the late reply. thank you all so much for the help.

It's a QA system , that's why i would like to reclaim the size in order to assign it to the PSAPTEMP.

Because i've just done the index compression.

I will test your command:) and give the feedback later!

Best regards,

kate

[janos_mucsi-besze]

Hi Kate,

Since this tablespace is LMTS/ASSM, fragmentation does not play a major role here.

Please perform an offline reorg with BR*Tools, so the datafiles will be recreated and so the space will be re-released to the OS.

here is an example:

brspace -p initOL1.sap -s 20 -l E -u / -f tbreorg -a reorg -s <tablespace> -m offline -p <parallel degree>

regards,

János

[Former Member]

Please do not go for table-space re-org blindly. It will not work for you. You will anyway need double the physcial storage space on your hard disk to do it because database will keep two copies of the tables and tablespace until the re-org is completed and the original tablespace dropped and the new tablespace renamed.

You need to figure out the biggest tables and indexes in this tablespace.  Check if you can archive some data out of few large tables. (idocs, log tables , temporary tables etc) . Once you have identified tables from which you can delete/archive data, do it first then re-org those tables.

For indexes, figure out the biggest indexes, which are fragmented ( you can even first try to re-org the 30-40) biggest infdexes and see if you can free some space)

I can go on and on . But please do some research. Start from here and proceed.

706478 - Preventing Basis tables from increasing considerably

Thanks

Amit

[former_member206552]

Hi Amit,

This will give you space back at tablespace level, but does not guarantee you will be able to reclaim space at filesystem level, as the HWM is not reset. I agree its not a small operation as you do require extra space as you need to rebuild the whole tablespace in to a duplicate tablespace.

also to add to you suggestion, the following SQL can be used to see where all the wasted space in tables are

SELECT * FROM

(SELECT

    SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,

    NUM_ROWS,

    AVG_ROW_LEN ROWLEN,

    BLOCKS,

    ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) NET_MB,

    ROUND(BLOCKS * (8000 - 23 * INI_TRANS) *

      (1 - PCT_FREE / 100) / 1000000, 0) GROSS_MB,

    ROUND((BLOCKS * (8000 - 23 * INI_TRANS) * (1 - PCT_FREE / 100) -

      (AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) "WASTED_MB"

  FROM DBA_TABLES

  WHERE

    NUM_ROWS IS NOT NULL AND

    OWNER LIKE 'SAP%' AND

    PARTITIONED = 'NO' AND

    (IOT_TYPE != 'IOT' OR IOT_TYPE IS NULL)

  ORDER BY 7 DESC)

WHERE ROWNUM <=20;

Best Regards

Marius

[Former Member]

Hi Marius,

Yeah, that's true, anyway, the first step has to be deletion/archive of data wherever possible and the re-org of those tables also including the indexes re-org/rebuild.

Then the table-space can be re-orged completely and its easy to estimate then how much space we can actually claim at File system/physical storage level.

Thanks

Amit

[Former Member]

Hi Kate

please run DB re-org & update statistics using Brtools.

then try to Shrink the table space .

Please find below link ..

Altering and Maintaining Tablespaces

Regards

Dishant pathak

[former_member206552]

http://docs.oracle.com/cd/B28359_01/server.111/b28310/schema003.htm

Good Morning Kate,

I usually first do a reorg on a tablespace before shrinking a tablespace as there are fragmented data in the tablespace, if not you might get a error from oracle stating that it is unable to shrink past a certain point

you can use the link to give you more information

Best Regards

Marius