Hello All,
DB : Oracle 12.1.0.2.0
OS: Redhat Linux (2.6.32-573.12.1.el6.x86_64)
Please find the below screenshot for the failure of ETL job though there are enough space in the tablespace.
Tablespace Size status taken using sm$ts_avail, sm$ts_used, sm$ts_free :-
Tablespace Size status taken using sys.dba_data_files:-
select b.tablespace_name tablespace_name
, b.totaal total\_MB
, b.totaal - nvl(a.vrij,0) - nvl(c.nonallocated,0) used\_MB
, nvl(a.vrij,0) + nvl(c.nonallocated,0) free\_MB
, round((nvl(a.vrij,1) + nvl(c.nonallocated,0)) / (b.totaal/100)) free\_percent
from (select tablespace_name, round(sum(bytes)/(1024*1024),2) vrij
from sys.dba\_free\_space
group by tablespace\_name) a
, (select tablespace\_name, sum(decode(autoextensible,'NO',round(bytes/(1024\*1024),2),
round(decode(sign(maxbytes-bytes),-1,bytes,maxbytes)/(1024\*1024) ,2)) ) totaal
from sys.dba\_data\_files
group by tablespace_name) b,
(select tablespace_name, sum(round(decode(sign(maxbytes-bytes),-1,bytes,maxbytes )/(1024*1024),2) - round(bytes/(1024*1024),2)) nonallocated
from dba_data_files
where autoextensible='YES'
group by tablespace_name) c
Where a.tablespace_name(+) = b.tablespace_name
and c.tablespace_name(+) = b.tablespace_name
group by b.tablespace_name, b.totaal, a.vrij, c.nonallocated
order by b.tablespace_name;
Tablespace Creation code:-
CREATE TABLESPACE GSW_RPT_DATA DATAFILE
'/u03/oradata/odsua/ODSUA3/data/u03/oradata/odspd/GSW_RPT_DATA001.dbf' SIZE 65535M AUTOEXTEND ON NEXT 16K MAXSIZE 65535M,
'/u03/oradata/odsua/ODSUA3/data/u03/oradata/odspd/GSW_RPT_DATA002.dbf' SIZE 65535M AUTOEXTEND ON NEXT 16K MAXSIZE 65535M
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
Please suggest the solution for the above mentioned issue which we are encountering frequently.
Thanks,
Pinto