Art of BI: ODI Configuration Issue to ORA-01653 Tablespace Update

During the Art of BI, Oracle Data Integrator training sessions, students create a full analytics project implementation from scratch as an internal project. The project includes sorting through random data, creating a dimensional model, transferring data from source data into our newly created dimensional model and finally creating meaningful art out of those tables.

This post is about sharing with you one of the errors encountered during one of our students setting up our Data Integration through Oracle Data Integrator which led to another great lesson.

After we downloaded and installed ODI, and set up our schema users, it was time to create our Master Repository. So we started up the Master Repository creation wizard, entered our DB connection parameters, entered our ODI Authentication credentials and clicked Finish.

Pretty straight forward right? Nope, not this time, we got the following Error:

Naturally, we went into the Details to figure out what was the source of the error:

Error Detailsoracle.odi.setup.RepositorySetupException: java.lang.RuntimeException: Error while executing create table SNP_LSCHEMA

(

I_LSCHEMA NUMBER(10) NOT NULL ,

I_TECHNO NUMBER(10) NOT NULL ,

LSCHEMA_NAME VARCHAR2(35 CHAR) NOT NULL ,

IND_CHANGE VARCHAR2(1 CHAR) NULL ,

EXT_VERSION VARCHAR2(35 CHAR) NULL ,

LAST_USER VARCHAR2(400 CHAR) NULL ,

FIRST_DATE DATE NULL ,

FIRST_USER VARCHAR2(400 CHAR) NULL ,

LAST_DATE DATE NULL ,

INT_VERSION NUMBER(10) NULL

)

…

… 5 more

Caused by: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1

ORA-01653: unable to extend table SYS.CON$ by 8 in tablespace SYSTEM

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)

at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)

at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)

at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)

at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)

at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1115)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)

at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3904)

at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1512)

at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java:712)

at com.sunopsis.dwg.xml.DwgXmlSession.execute(DwgXmlSession.java:86)

… 9 more

So our main error was: ORA-01653: unable to extend table SYS.CON$ by 8 in tablespace SYSTEM

Basically what this error means is that certain data is trying to be inserted into a datafile for a tablespace that is not big enough to support it. Now even though it is NOT best practice to insert your tables and data into the SYSTEM tablespace (which was the crux of this problem, thought it led to a good lesson), for the sake of this posting we will disregard that for now.

First thing you want to do is to identify whether the datafile for your tablespace is in fact small in size. Go to your SQL Developer tool and click View > DBA Connections then connect to your schema name in question, in this post we had created a system_orcl connection which points to the SYSTEM schema.

Navigate to Storage > Datafiles

Verify the size of the datafile and record the path to that datafile as we will use it later.

Now the size of the file at the time of the error was 600M, and now we need to expand this file in order to resolve the ODI error. We will do this by opening up a SQL worksheet and execute the following statement in order to update it to an arbitrary higher number such as 1500MB – Note the syntax use of ‘M’ for megabytes:

ALTER DATABASE DATAFILE ‘<Datafile_path_noted_above>’ RESIZE 1500M;

And that’s it, we’re done with the update to the tablespace. We re-ran the Master repository creations step and successfully able to continue past the error.  Of course, the student later completely re-configured their ODI system to point to a specific master repository and continued with the course successfully.