ORA-XXXX: unable to extend ...in tablexpace XYZ
The error will always have a tablespace name defined at the place of XYZ above.
The error code may be different as the issue may be with table, index, temp segment etc, but the reason and resolution are same.
Reason:- Running short of space
Resolution:- Add space
A deeper level of solution is to check whether its a temp or undo related issue or a usual table segment error.
For temp and undo the query may need to be tuned to use less of undo space.
Well! that needs a higher level of understanding, and expertise.
Right now what I have are few queries that may help you to identify and resolve the issue (Considering you are already aware of Tablespace and Datafiles).
NOTE:- Please beware of free space available on your machine where Oracle is running before executing these commands.
1. Find out what tablespace have the issue.
You can check that in the error mentioned above.
To verify state of any tablespace you can user below query.
This makes a round so give you fair estimation but not accurate to points (which is anyway not needed).
If you want to check utilization for all tablespaces in the db then just press enter without giving tablespace name. The top rows in the result will be the tablespaces with maximum utilization.
2. Here we have the details for a particular Tablespace. Now time to go deeper. Lets see the datafiles the tablespace have.
You may see zero at Max_MB if the data file does not have autoextensible ON. It's DBA's choice to keep it what way he feels good to manage.
So if it is not autoextensible, or if it is and all the max, user and total bytes are equal or different is less then incr_by; that means we may need to add space here.
3. Now we have the tablespace and related datafiles details. Its time to add space and for that we have two options.
a. Add space to a datafile.
This is even possible if you have any idea about how big you can make a datafile in your database. This depends on some parameters of db and OS, but usually kept decided and being followed as an standard to make a symmetry and for performance.
So if you know what is the max size you can make for the datafiles, or in case of multiple files if you find a file of smaller size, then that can be increased to the size of others.
To increase the size of a database file use below command.
Consider the example below:
New size should be greater then existing size, as compression is not supported unless file is empty,( do it in rare cases).
b. Adding new datafile
So what all files you have go in the output, you can add one more to that list. Use below command.
alter tablespace <TableSpaceName> add datafile '<File Name>' size <Some Size>;
See below Example:
Remember the file name should be unique here.
Now the case of Autoextensible as saw in our query. Below example shows something.
So whenever this will reach allocated size the size will increase by 100mb ( like 2100mb, 2200mb and so on), and this will go till it reaches size 5GB.
After adding or increasing file size you can see the tablespace status by queries at 1st step.
So this is all basic info about Tablespace issue and resolution.
Related Posts:-
Tablespace Management with ASM
The error will always have a tablespace name defined at the place of XYZ above.
The error code may be different as the issue may be with table, index, temp segment etc, but the reason and resolution are same.
Reason:- Running short of space
Resolution:- Add space
A deeper level of solution is to check whether its a temp or undo related issue or a usual table segment error.
For temp and undo the query may need to be tuned to use less of undo space.
Well! that needs a higher level of understanding, and expertise.
Right now what I have are few queries that may help you to identify and resolve the issue (Considering you are already aware of Tablespace and Datafiles).
NOTE:- Please beware of free space available on your machine where Oracle is running before executing these commands.
1. Find out what tablespace have the issue.
You can check that in the error mentioned above.
To verify state of any tablespace you can user below query.
column Size_MB format 99,999,999
column Free_MB format 99,999,999
select t.tablespace_name "Tablespace Name", t.tbs_size Size_MB, NVL(f.free_space,0) Free_Mb,
NVL(round(100 - ((f.free_space/tbs_size)*100)),100) Prcnt_Used from
(select tablespace_name, sum(bytes)/1024/1024 as free_space
from dba_free_space group by tablespace_name) f,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name ) t
where t.tablespace_name=f.tablespace_name(+)
and t.tablespace_name like UPPER('%&TablespaceName%')
order by Prcnt_Used desc;
column Free_MB format 99,999,999
select t.tablespace_name "Tablespace Name", t.tbs_size Size_MB, NVL(f.free_space,0) Free_Mb,
NVL(round(100 - ((f.free_space/tbs_size)*100)),100) Prcnt_Used from
(select tablespace_name, sum(bytes)/1024/1024 as free_space
from dba_free_space group by tablespace_name) f,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name ) t
where t.tablespace_name=f.tablespace_name(+)
and t.tablespace_name like UPPER('%&TablespaceName%')
order by Prcnt_Used desc;
This makes a round so give you fair estimation but not accurate to points (which is anyway not needed).
If you want to check utilization for all tablespaces in the db then just press enter without giving tablespace name. The top rows in the result will be the tablespaces with maximum utilization.
2. Here we have the details for a particular Tablespace. Now time to go deeper. Lets see the datafiles the tablespace have.
set linesize 120
column File_Name format a45
select File_Name, maxbytes/1024/1024 as Max_MB, round(bytes/1024/1024) as Total_MB,
round(user_bytes/1024/1024) as Allocated_MB, autoextensible as AutoEx, Increment_by as Incr_By
from dba_data_files where tablespace_name = '&tablespace_name';
column File_Name format a45
select File_Name, maxbytes/1024/1024 as Max_MB, round(bytes/1024/1024) as Total_MB,
round(user_bytes/1024/1024) as Allocated_MB, autoextensible as AutoEx, Increment_by as Incr_By
from dba_data_files where tablespace_name = '&tablespace_name';
You may see zero at Max_MB if the data file does not have autoextensible ON. It's DBA's choice to keep it what way he feels good to manage.
So if it is not autoextensible, or if it is and all the max, user and total bytes are equal or different is less then incr_by; that means we may need to add space here.
3. Now we have the tablespace and related datafiles details. Its time to add space and for that we have two options.
NOTE:- Warning again to check space available on your file system before executing any of below commands.
a. Add space to a datafile.
This is even possible if you have any idea about how big you can make a datafile in your database. This depends on some parameters of db and OS, but usually kept decided and being followed as an standard to make a symmetry and for performance.
So if you know what is the max size you can make for the datafiles, or in case of multiple files if you find a file of smaller size, then that can be increased to the size of others.
To increase the size of a database file use below command.
alter database datafile '<File Name>' Resize <New Size>;
Consider the example below:
alter database datafile '/oracle/apps/db/MyFile01.dbf' resize 1000M;
New size should be greater then existing size, as compression is not supported unless file is empty,( do it in rare cases).
b. Adding new datafile
So what all files you have go in the output, you can add one more to that list. Use below command.
alter tablespace <TableSpaceName> add datafile '<File Name>' size <Some Size>;
See below Example:
alter tablespace MY_TABLESPACE add datafile '/oracle/apps/db/MyFile02.dbf' size 2G;
Remember the file name should be unique here.
Now the case of Autoextensible as saw in our query. Below example shows something.
alter database datafile '/oracle/apps/db/MyFile02.dbf' autoextend on next 100M maxsize 5G;
So whenever this will reach allocated size the size will increase by 100mb ( like 2100mb, 2200mb and so on), and this will go till it reaches size 5GB.
After adding or increasing file size you can see the tablespace status by queries at 1st step.
So this is all basic info about Tablespace issue and resolution.
Related Posts:-
Tablespace Management with ASM
No comments:
Post a Comment