Skip to Content

To extend Tablespace

Hi,

In one of our production system, the tablespace size of one of the tablespace named as PSAPSR3 is showing as 94.

If we check it in DB02 -> tablespace -> overview -> main data , the information is as below -

Tablespace name PSAPSR3

Size(MB) 58,600.00

Free(MB) 3,801.81

Used(%) 94

Autoextend YES

Total size considering Autoextend(MB) 66,000.00

Total free space considering Autoextend( 11,201.81

Total used (%) 83

Number of datafiles 16

Number of Segments 74,338

Number of Extents 141,021

Status ONLINE

Contents PERMANENT

Default table compression DISABLED

Compression for operation type n.a.

Tablespace encryption n.a.

Tablespace encryption algorithm n.a.

While i we check in Data/Temp files for these tablespace, there are data1,2,....16 files are there

for data 1 to 6 information is as follows

File name /oracle/BID/sapdata3/sr3_5/sr3.data5

File Id 17

Tablespace name PSAPSR3

Size(MB) 4,180.00

Number of blocks 535,040

Status AVAILABLE

Relative file number 17

Autoextensible YES

Maxsize(MB) 6,000.00

Maxblocks 768,000

Increment by 2,560

User size(MB) 4,179.94

User blocks 535,032

For data 7 to 16, information is as follows

File name /oracle/BID/sapdata3/sr3_14/sr3.data14

File Id 26

Tablespace name PSAPSR3

Size(MB) 2,000.00

Number of blocks 256,000

Status AVAILABLE

Relative file number 26

Autoextensible NO

User size(MB) 1,999.94

User blocks 255,992

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Aug 01, 2012 at 06:47 AM

    If we see tablespace is showing as autoextendable , size is 58,600 and max size 66,000.

    But in data file 1-6, autoextend is yes, size is 4180 and

    maxsize is 6000

    increment by is 2560

    So if we calculate it 4180 + 2560 = 6740 which is more than maxsize.

    Last week also , it was showing size as 94% but on wednesday suddenly it reached 89% itslef

    What i hv to do in this case.

    Should i increase it manually at O.S level thru BRTOOLS. if yes then please send me the steps to proceed for the same.

    Please help.

    Waiting for reply

    Add a comment
    10|10000 characters needed characters exceeded

    • You can always add a new datafile to a tablespace

      Create new datafiles with size 8192 MB max size 16384 and increment by 2048 MB. Usually this tablespace is the one which grows rapidly comapred to the other ones.

      The way the datafiles for tablespace SAPSR3 has been created is not the best practice. You should follow the points when you create datafiles for a tablespace

      1. Analyze how fast a tablespace grows.

      For example :

      PSAPSR3 -- Contains most of the user data in an SAP system and is typically the largest tablespace and grows the maximum.

      PSAPSR3<XXX> --- XXX is the SAP BASIS release and is generally not very large and typically has the release specific data . Approximately 30-40 GB . Does not grow much and the growth is typically seen during import of support packages.

      PSAPSR3USR -- Usually is of small size if customer tables are not created within this tablespace.

      2. Make sure if you have autoextend on for a tablespace , all the datafiles should be autoextend on.

      3. Create all datafiles of a tablespace of the same size and characteristics.

      Since your system is already a live system , so go ahead with the creation of new datafile as I mentioned above.

      Regards

      Ratnajit

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.