Friday, 18 November 2016

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

You may be facing error "ORA-30036" while doing  import since it will through if your tablespace doesn't have sufficient space  in it.

let's check this error and resolve it.





From above error,its telling that in "UNDOTBS1" tablespace is full and doesn't have space to store any extra data.

You can see in below screenshot that its shows empty space.





Use below command to add data file(Space to tablespace).


ALTER tablespace  UNDOTBS1 add datafile '/mnt/devops_0/dba/oradata/APAC11R2/undotbs05.dbf'  size 10000M

--here "/mnt/devops_0/dba/oradata/APAC11R2/undotbs05.dbf'" is your data file ,which you are adding to tablespace.

Now run below  query  to see list of datafiles in your tablespace.

SQL> select df.file_name,
  2            df.tablespace_name,
  3            ROUND(sum(bytes) / 1024 / 1024 / 1024, 2) Size_In_GB
  4       from dba_data_files df
  5       join dba_TABLESPACES dt on dt.tablespace_name = df.tablespace_name
  6      Where dt.tablespace_name = 'UNDOTBS1'
  7      group by df.file_name, df.tablespace_name
  8  ;

FILE_NAME                                                   TABLESPACE_NAME                SIZE_IN_GB
-------------------------------------------------------------------------------- ------------------------------ ----------
/mnt/devops_0/dba/oradata/APAC11R2/undotbs03.dbf            UNDOTBS1                             9.77
/mnt/devops_0/dba/oradata/APAC11R2/undotbs02.dbf            UNDOTBS1                             4.88
/mnt/devops_0/dba/oradata/APAC11R2/undotbs05.dbf            UNDOTBS1                             9.77
/mnt/devops_0/dba/oradata/APAC11R2/undotbs01.dbf            UNDOTBS1                               32
/mnt/devops_0/dba/oradata/APAC11R2/undotbs04.dbf            UNDOTBS1                             4.88



Query:   select b.tablespace_name, tbs_size Size_Gb, a.free_space Free_gb
                                from (select tablespace_name,
                                   round(sum(bytes) / 1024 / 1024 / 1024, 2) as free_space
                        from dba_free_space    group by tablespace_name) a, (select tablespace_name,                                       sum(bytes) / 1024 / 1024 / 1024 as tbs_size  from dba_data_files
group by tablespace_name) b
 where a.tablespace_name(+) = b.tablespace_name
   and b.tablespace_name = 'UNDOTBS1';



No comments:

Post a Comment