Select Page

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

Sherry Milad | | March 22, 2016

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:

smodiblog1

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

smodiblog2

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.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?

JP Chen | October 1, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS