ORA-01658: unable to create INITIAL extent for segment in tablespace PSAPOD

[Former Member]

Hi

Found many logs of type ORA-01658: unable to create INITIAL extent for segment in

tablespace PSAPODSD in sm21 in our prodn system

can anybody help me inresolving this

[Former Member]

Note 3155 - Termination due to tablespace overflow

for you. Mr KVR offers you the right path.

Regards.

[Former Member]

Hi KVR,

Thanks for the info.

Tablespace is having enough free space and is only 80% used

but still logs found in sm21 "ORA-01658: unable to create INITIAL extent for segment in

tablespace PSAPODSD"

Any other sugestions please....

[sebastian_sebaraj]

Even if you see 80% full , it is possible Tablespace is Fragmented.

Check the size of the INITIAL EXTENT.

[sebastian_sebaraj]

Tablespace "PSAPOD" is it Locally Managed?

Is it Uniform Allocation/Automatic Allocation?

What is the Oracle Version?

The problem is caused by the Oracle unable or fails to find sufficient contiguous space to allocate INITIAL extent for segment being created

What is the Size of the INITIAL EXTENT Oracle is trying to allocate?

[Former Member]

Hi,

Our oracle version 10G but still the tablespaces are showing as Dictionary managed.

could you please tell how to check what extent size it is trying to allocate

i can only see below logs in sm21....and also dont see anthing regd PSAPODSD in checkdb.

"ORA-01658: unable to create INITIAL extent for segment in

tablespace PSAPODSD"

However i would also like to how to know how to check uniform or automatic extent allocation in case of Locally mnagd TS.

Please provide me with steps.

Thanks in Advance

Rasool

[sebastian_sebaraj]

If your tablespace is DICTIONARY MANAGED tablespace Oracle will use the size of the INITIAL EXTENT spaecified in the CREATE TABLE Statement.

You can also run the following query and see the biggest chunk of free extent available.

select file_id, bytes as free_chunk from dba_free_space

where tablespace_name = 'PSAPOD'

order by bytes desc;

ALTER TABLESPACE PSAPOD COALESCE;

run again the select statement to see any difference in extent size

select file_id, bytes as free_chunk from dba_free_space

where tablespace_name = 'PSAPOD'

order by bytes desc;

Try to create a test table by using the same user one who tries to create a table like

CREATE TABLE Test( test_col NUMBER) DEFAULT TABLESPACE;

Let me know what happens

Make sure CREATE TABLE statement uses TABELSPACE PSAPOD. If not table is created in the default tablespace of the user. I do not know which user tries to create the table. Check the whether the user tries to create a table has access to the tablespace PSAPOD.

[Former Member]

Hi,

This had occured in our PRD system.can i check the above said by you when sap is running?

Anywayz i will check and update the results...

[Former Member]

Hi,

Does PSAPODSD tablespace has enough free space? If not extend the tablespace.

Regards

KVR