Question Re: Tablespace usage in Technology Q&A

Tablespace usage

Former Member — Thu, 19 Jun 2014 09:49:03 GMT

Dear experts,

Please check my quality server tablespace details below.

PSAPPRD tablespace used 98%, what step I have to follow.

Please suggest me.

Thanks & regard,

Amit

Re: Tablespace usage

Former Member — Thu, 19 Jun 2014 09:58:26 GMT

Hi Amit,

From the screen shot I can see that PSAPPRD is 98% used.

Total size = 73GB GB out of which 1.3 GB is still free.

Total extents size = 130 GB out of which 57 GB is free.

As the data files are in auto extend mode system can grow up to another 57 GB. Post which your system can start having problems.

Suggestion

1) Check what is the daily and monthly growth of system to ensure how many days system can run without any problem with 57 GB.

2) Check space of storage where sapdata files are created. Do you have enough space to add new data files.

3) If storage has good amount to free space and DB growth is higher please add up another 50 GB into PSAPPRD . This will bring down the usage to below 90%

Hope this helps.

Regards,

Deepak Kori

Re: Tablespace usage

Former Member — Thu, 19 Jun 2014 10:19:26 GMT

Dear sir,

1. As its quality server, there is minor growth in database i.e near about 1-2GB in past 4 months.

2. We have 180GB free out of 524GB on physical disk.

3. So does we need to add another new data file ? As AUTOEXTEND option is on so it will have no problem?

Thanks & regard,

Amit

Re: Tablespace usage

Former Member — Thu, 19 Jun 2014 10:35:09 GMT

Hi Amit,

As the DB growth is not that hight, do not add any data file now.

Monitor the growth and take relevant action accordingly.

Regards,

Deepak Kori

Re: Tablespace usage

Reagan — Thu, 19 Jun 2014 11:30:30 GMT

I see the tablespace PSAPPRD is in auto extensible mode.

I would setup up CCMS monitoring or a third party monitoring tool to trigger an alert when the free space is below a certain percentage.

Regards

Re: Tablespace usage

Former Member — Thu, 19 Jun 2014 11:46:46 GMT


We have 180GB free out of 524GB on physical disk
AUTOEXTEND option is on

Cool..Check your Production from where you copied the data (as part of system refresh, if you did). Ensure you have enough space in Production as well.

Since it is QA system and you have enough partition (you hardly have TS growth), you may ignore this.

You may set an threshold for monitoring if you have option (As said by other techies).

Regards,

Nick Loy

Re: Tablespace usage

Former Member — Thu, 19 Jun 2014 11:58:56 GMT

If the tablespace is more than 95% alert is shown in RZ20 t-codes.

Check in RZ20-SAP CCMS Monitor Templates-Database-Oracle-Space management-Tablespaces.

All the tablespace are showing and if more than 95% give alert for the same.

AUTOEXTEND is on its good and u you can add new datafile via Brtools - extend tabplesapce

Regards

Naveen

Re: Tablespace usage

janos_mucsi-besze — Fri, 20 Jun 2014 05:46:19 GMT

Dear Amit.

The first thing I see is that you are still using the old tablespace layout with DMTS tablespaces. This, on its own can be the cause for this huge waste of space. As you may already know in DMTS every object can have different extent sizes, this can lead to tablespace fragmentation.

If you would reorganize your tablespaces to the new tablespace layout and so to LMTS/ASSM (sap note 355771), the fragmentation will drop due to much more less different sizes of extents (64 KB,1MB,8MB,64MB,128MB). This makes your life as an administrator not just easier, but you can use some new features as of Oracle 10g. Because some features can not be used for DMTS tablespaces.

Long story short:

Short term solution: reorganization within the same tablespace structure

positiv effect: less fragmentation

negativ effect: fragmentation and so waste of space will arise again and again in a short time

can not use some of the new features of recent Oracle releases

Long term solution: reorganize to LMTS/ASSM

positiv effect: less fragmentation, easier administration, I/O load is slightly better, new fetures can be used

negativ effect: takes time to reorganize all tablespaces

Regards,

János