DAC: Fail to create Index during execution plan run

Hello again

Here is something that a beginner may run into every so often when they use DAC to run informatica workflow, which is when the DAC execution plan runs fine except it fails to create table Index after the load. Or it could be shown as the following scenario:

The execution plan is completed with several failures of its tasks. We look at the detail of the task and find out that it fails at the last step of creating table Index:

We found out from the error log that it is complaining about a table space 'USERS' during Index Creation attemp:

As far as when 'USERS' got into this, I have no idea. Usually when we create table in a DB, we use Tablespace. I won't go into detail about what is tablespace and all that. In our case, we use a Tablespace called 'ETL_DW_INDX' for all of the index creation when we create these tables in the target DB. I am assuming that DAC is still looking for tablespace 'USERS' for all of its tasks during run time.

This leads our investigation of this issue to a new direction. Is there a place in DAC that specifies what Index Tablespace it is using for the given Database? Well, it turns out that is yes. It is defined at the physical data source under 'SET UP' tab, so let's go there!

As we can see, it is originally empty for the fleid 'Default Index Space'. This may explains why we see 'USERS' in the error log while we know we are creating Indexes in the Database using 'ETL_DW_INDX'. So, let's tell DAC to use 'ETL_DW_INDX':

After that, let's re-run the execution plan and see how it turns out!

It all succeeded!

So in summary, if we are using specific Index tablespace when creating Indexes in the DB, we need to tell DAC to use that tablespace by defining it in the specific Physical Data Source info. This information will be used by DAC to run create-index tasks during execution plan run.

Thanks

Until next time!