reason lack of tablespace and how to deal with it

oracle tablespace isn’t enough，there are 2 reasons：

1. original tablespace is too small, hadn’t been set autoextend；

error code: ORA-01653: unable to extend table

2. tablespace had been set autoextend and enough large, but the data size

is greater than the maximum size of current tablespace (32GB)

I am trying to import a dmp file to a specifically tablespace, but the size of dmp file is 217GB.

So I encountered an error “ORA-01658: unable to create INITIAL extent for segment in

tablespace string” , as shown below.

 

There are 2 solutions to solve this problems:

for reason 1 ,we can modify the size of tablespace.

alter database datafile ‘../../tablespace.dbf’ resize 2048M;

or

alter database datafile ‘../../tablespace.dbf’ AUTOEXTEND ON NEXT 200m;

 

for reason 2, we can add a new datafile to extend tablesapce.

alter tablespace test.dbf add datafile ‘../../_TEST1’ SIZE 1000M AUTOEXTEND ON NEXT 1000M

maxsize unlimited;

alter tablespace test.dbf add datafile ‘../../_TEST2’ SIZE 1000M AUTOEXTEND ON NEXT 1000M

maxsize unlimited;

………………..

alter tablespace test.dbf add datafile ‘../../_TEST5’ SIZE 1000M AUTOEXTEND ON NEXT 1000M

maxsize unlimited;

result as shown in the following figure

continue to import data

 

Attachment:

1. check utlization of tablespace：

set line 500;

SELECT total.tablespace_name,

Round(total.MB, 2) AS Total_MB,

Round(total.MB – free.MB, 2) AS Used_MB,

Round(( 1 – free.MB / total.MB ) * 100, 2)

|| ‘%’ AS Used_Pct

FROM (SELECT tablespace_name,

Sum(bytes) / 1024 / 1024 AS MB

FROM dba_free_space

GROUP BY tablespace_name) free,

(SELECT tablespace_name,

Sum(bytes) / 1024 / 1024 AS MB

FROM dba_data_files

GROUP BY tablespace_name) total

WHERE free.tablespace_name = total.tablespace_name;

 

1. check which tablespace that current user belong to.

select * from user_users;

2. check the details about tablespace

select f.* from dba_data_files f where f.tablespace_name=’test.dbf’;