Oracle version:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
Hi Team,
While inserting data(Data is small already tried inserting into chinks) for a table into a database through DB Link encountered an error
"ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'"
So in order to investigate further to look out whether the details for undo table space(Like space allocated, free space, file allocated to it, Auto Extendable or not etc.), checked out few of the dictionary views but there were not any records for Undo table space.
Step 1:
Select NAME,VALUE,DESCRIPTION
From V$parameter where name='undo_tablespace';
Step 2:
Select NAME,VALUE,DESCRIPTION
From V$parameter where name='undo_management';
Step 3:
select
( select sum(bytes)/1024/1024 from dba\_data\_files
where tablespace\_name like 'UND%' ) allocated,
( select sum(bytes)/1024/1024 from dba\_free\_space
where tablespace\_name like 'UND%') free,
( select sum(bytes)/1024/1024 from dba\_undo\_extents
where tablespace\_name like 'UND%') USed
from dual
Step 4:
select tablespace_name, contents from dba_tablespaces where contents = 'UNDO';
select substr(file_name,1,60) FileNAme from dba_data_files where tablespace_name = 'UNDOTBS1' order by 1;
So my Questions are:
1. how i will be able to find out the details for Undo Table space.
2. how can i remove this error, most probably by setting it to auto extendable or by adding the data file to the table space.
Thanks
DK