cancel
Showing results for 
Search instead for 
Did you mean: 

table space is full

Former Member
0 Kudos

Hi

tablespace(PSAPHOD) is used 99%, how can i increase, there is any option for auto ext

Tablespace Size (kb) Free (kb) Used (%) Tab/ind Extents AutoExt (kb) Used (%) Status Backup

PSAPHOD 30,670,848 15,680 99 100,449 143,618 122.880.000 25 ONLINE NOT ACTIVE

Accepted Solutions (1)

Accepted Solutions (1)

peter_dzurov
Contributor
0 Kudos

You can use brtools as ora<sid> user then: Space management. Furthermore I guess it is quite easy to continue...

Former Member
0 Kudos

how can i increase space. right i have not more then 10 gb space availabel

Former Member
0 Kudos

how much i can increaes

peter_dzurov
Contributor
0 Kudos

You can only increase up to disk space limit i.e. how much free space do you have in sapdataX dir.

Former Member
0 Kudos

11GB, there is any limit in oracle, i read maximum we can incrase maximum 10gb

peter_dzurov
Contributor
0 Kudos

If you think about Oracle datafile size limitations, then it is depending on size of Oracle block (in most cases 8k) then this limit is 32 GB, but if you have 10 GB of free space on disk, of course you cannot create bigger datafile.

Former Member
0 Kudos

so how much i can increase tablespace size. there is any option to autoextend size

peter_dzurov
Contributor
0 Kudos

If you have 10 GB free space, you can extend tablespace (add datafile) by 10 GB maximum. But it can be lower value as well (depending on your needs, expectations of tablespace growing). As you are going through brtools menus, you can find autoextend option as well.

Former Member
0 Kudos

Hello Kumar,

If your DB_BLOCK_SIZE is 8kb, then the limit of your datafils is 32GB by the following calculation.

4,194,303 multiplied by the value of the DB_BLOCK_SIZE ie 4194303 x 8kb = 33554424 kb = 32 GB.

suppose if your datafile is of size 10GB and your server has more 15GB it can auto extend to 15GB. You can do that by using brtools and extending tablespace by adding datafiles.

But in case if you sapdata1,2,...N are alomost 100% full you can do one of the following.

1) Get the space increased on your server.

2) You can increse the mount points, have seen this is one of the posts.

Stop the SAP and database. Perform an offline backup of filesystem which are full

Ask the Os team to extend the Mount point, after the restore the backup and start the same.

3) Or moving the data files by the below procedure.

A) The current location of the data files can be seen from the out of the query on the sql prompt.

SQL> SELECT name FROM v$datafile;

B) Shutdown SAP system and database.

SQL> SHUTDOWN ; or SQL> SHUTDOWN IMMEDIATE;

C) Move the required files from one file system to the other file system by executing the following command.

Eg: SQL> host mv /oracle/SID/sapdata3/btabi_9/btabi.data9 /oracle/SID/sapdata6/btabi_9/btabi.data9

D) Start the database in mount mode.

SQL> STARTUP MOUNT

E) Issue the ALTER DATABASE RENAME FILE command to move the file within the Oracle dictionary.

SQL> ALTER DATABASE RENAME FILE '/oracle/SID/sapdata3/btabi_9/btabi.data9' TO '/oracle/SID/sapdata6/btabi_9/btabi.data9'

F) Open the database.

SQL> ALTER DATABASE OPEN;

G) Repeating the initial query shows that the the datafile has been renamed in the data dictionary.

SQL> SELECT name FROM v$datafile;

H) Start SAP system.

Thanks,

Nick S

Former Member
0 Kudos

thanks nike,

how can i check autoexted in oracle, this is my development server, and also check db_block_size,

now should i increase 1gb to 2 gb ..?

former_member204746
Active Contributor
0 Kudos

to increase data file size or to put it autoextendable:

brspace -c -u / -f dfalter

to add a datafile, which will be needed real soon:

brspace -c -u / -f tsextend

Former Member
0 Kudos

Hi Kumar,

you can find whether the tables space's autoextend is of/off in DB02 transaction. And the DB_BLCOK_SIZE is a parameter usually 8k is found in initSID.ora file.

If your server has enough space you can extend the table space upto 10GB(Recommonded). If you have only2GB, you can extend the same by adding some datafiles.

1) You can extend the autoextend to 10GB (Recommonded) using brtools.

( I guessin ECC all the autoextend is of 10GB)

2) You can add a datafile using brtools.

Thanks,

Nick S

Former Member
0 Kudos

Hi Kumar,

Here I want you to analyse DB growth on daily basis. To check this, goto T-code DB02 --> select the tablespace "PSAPHOD" --> select option "History of tablespaces" --> select "days". Below you will shown space statistics for this particular tablespace for last few days. Now you can analyse how much space is being utilized on an average per day.

Now, as the total size of tablespace "PSAPHOD" is 30 GB that means, 0.3 GB increment in tablespace will bring down % used of tablespace by 1%. (5 GB increment in tablespace would bring down %used space to 83%).

I think you should go for tablespace extension if space utilization is at large scale because auto extend space will be taken into consideration if you have enough space in the filesystem. In this tablespace, after autoextend, 120 GB space is the total space in filesystem but you have only 10 GB.

If you are not perfect in oracle SQL queries, I would suggest you to use BRTOOLS (as recommended by SAP) for tablespace extension as missing a parameter can cause database corruption.

Regards,

Ankit

Former Member
0 Kudos

HOW CAN I CHECK AUTOEXTEND ON OR OFF. CAN WE ON AUTOEXTEND ALL TABLE

kishore_soma
Active Participant
0 Kudos

hi pramod,

You can check the auto extend in DB02 tablespace ,where u see the tablespace utilization.

Regards,

Kishore Soma

Former Member
0 Kudos

sorry i could not found

kishore_soma
Active Participant
0 Kudos

You have a column autoext in DB02, there you can fine if autoextend is enable or not.

Former Member
0 Kudos

if i find max autoextensible/KB in dev; there is giving size

if i check in prd there is giving unlimited

i am little bit confuse, pls. clearfy

Answers (1)

Answers (1)

Vivek_Hegde
Active Contributor
0 Kudos

To add data file to the Tablspace.

1. Logon to the systems as ora<sid>

2. Execute brtools

3. Select u201C2 u2013 Space managementu201D

4. Select u201C1 u2013 Extend tablespaceu201D

5. Select u201C3 u2013 tablespace name 

6. Select u201Cc u2013 Continue

7. Select u201C3 u2013 give the path for the new file to be added

8. Select u201C5 u2013 Size of the new file in MB

9. To continue select u201Ccu201D

10. Select continue and confirm the return to BRTOOLS menu.

11. Exit from the u201CSpace managementu201D menu using b (back)

12. Now it is possible to exit from BRTOOLS using option u201C9 u2013 Exit programu201D.

(a) BR*Tools main menu:

1 = Instance management

2 - Space management

3 - Segment management

4 - Backup and database copy

5 - Restore and recovery

6 - Check and verification

7 - Database statistics

8 - Additional functions

9 - Exit program

(b) BR0662I Enter your choice: 2

Database space management

1 = Extend tablespace

2 - Create tablespace

3 - Drop tablespace

4 - Alter tablespace

5 - Alter data file

6 - Move data file

7 - Additional space functions

8 - Reset program status

(c) BR0662I Enter your choice: 1

(d) BR0662I Enter your choice: 3

Give the tablespace name :

***********************

BR0662I Enter your choice: c, c

(f) BR0662I Enter your choice: 3

Select the correct path i.e. where you want to create the data file (must be in sapdata folder)

(g) BR0662I Enter your choice: 5

Select the size of the new data file.

(h) BR0662I Enter your choice: c, c, c

-


END----


I guess adding 4 GB to PSAPHOD table space is more than sufficient.

Regards

Vivek Hegde