Thursday, January 17, 2013

[Informatica] Error : ora-01653 unable to extend table in tablespace


While loading the data in Bi Apps thru informatica, I do sometimes get the error ora-01653 . This is because of value of “AUTOEXTENSIBLE” column is NO. So one can check the how many bytes are used by Oracle and what max size of the tables space by using following queries.
Used by the application or the DB: Extend your data file size in the database.
SELECT  tablespace_name,  SUM(bytes/1024/1024)  FROM  dba_segments  GROUP BY  tablespace_name;   Max  size of the tablespace:   select tablespace_name,sum(bytes)/1024/1024 “Max Size in MB” from dba_data_files  group by tablespace_name 
Max Size available for the tablspaces:
select tablespace_name,sum(bytes)/1024/1024 “Max Size in MB” from dba_data_files  where tablespace_name=’TBS_DS_OWNER’  group by tablespace_name
If the difference between this value and max size value is small then we need to add datafile or increse the size of the the datafile to insert more records.
When there is a difference between value used by the application / db  and max size value then there is a need to add datafile or increase the size of the the datafile.