ODI 12c - database Free space check sql and resize datafile

Query to check the total space in mb, free space in mb and percent free for UNDO_9 tablespace.

Select

a.tablespace_name,

sum(a.bytes)/(1024*1024) total_space_MB,

round(b.free,2) Free_space_MB,

round(b.free/ (sum(a.bytes)/(1024*1024))* 100,2) percent_free

from dba_data_files a,

(Select tablespace_name, sum(bytes)/(1024*1024) free from dba_free_space group by tablespace_name) b

where a.tablespace_name = b.tablespace_name(+)

and a.tablespace_name ='UNDO_9'

group by a.tablespace_name, b.free;

Query to manually resize db datafile for tablespace undo_9

ALTER DATABSE DATAFILE '+Data/...path/datafile/undo_9...' RESIZE 800G

The above command resize the datafile to 800 gb.

ODI 12c - Activate or Deactivate your schedule on load plan

Here we will show the steps to activate or deactivate your schedule created on load plans.

Steps:

Step1: Go to Operator tab -- Scheduling -- All Schedules -- select your schedule and expand and rightclick on Golbal/logical agent -- open

Step2: select status Active or inactive and save.

Step3: Go to Load plan and Scenarios -- double click on your load plan

Step4: Enable or disable the steps. Generally need to disable or enable 1st parent steps.

Why we need to disable or enable steps in load plan:

Sometimes we see that though you have inactivated the schedule, still its running so to avoid this scenario, we can also disable the steps in load plan as well.

OIC - Unable to convert a string in the xml to a java.sql.date - while inserting into database

Scenario: We have received an input date as YYYY/MM/DD format and when we are trying to insert it into a database, got below error:

Unable to convert a string in the xml to a java.sql.date.

Solution: Convert the YYYY/MM/ DD format to  YYYY-MM-DD format. You can use translate() function and convert the "/" to "-".

translate(datefield, "/","-")

OIC - validate date format as MM-DD-YYYY

I use this regex for validating MM-DD-YYYY:

function isValidDate(subject){
  if (subject.match(/^(?:(0[1-9]|1[012])[\- \/.](0[1-9]|[12][0-9]|3[01])[\- \/.](19|20)[0-9]{2})$/)){
    return true;
  }else{
    return false;
  }
}

It will match only valid months and you can use / - or . as separators.

ODI 12c - Loading Flat file to SQL table

Here, we will read a comma separated csv file and sends data to target Oracle table.

Steps:

File format:

Step1: Topology - Physical Architecture - File - File Generic

Step2: File data server - New Physical schema

Step3: Provide directory

Step4: Create new logical schema

Step5: Designer - new model folder - new model

Step6: provide all details like name, technology and logical schema name

Step7: create new data store

Step8: map and provide LKM and IKM and flow control as false.

ODI 12c - loading XML file to SQL table

Here, we will see how to load a XML file to SQL table.

Steps:

Step1: Topology - Physical Architecture - XML

Step2: New data server

Step3: Dataserver name

Step4: JDBC - provide schema path, file directory etc.

Step5: It will create a DTD file for that XML file.

Step6: Created data server - new physical schema.

Step7: provide schema and work schema.

Step8: Logical architecture - XML - create new logical schema.

Step9: provide the physical schemas for each contexts.

Step10: Designer - create new model

Step11: provide name, technology, logical schema etc.

Step12: Reverse engg - select System table

Step13: Selective Reverse engg - select objects and reverse engg.

Step14: datastores created.

Step15: create new project and map and mapp source and target datastores.

Step16: Physical - Choose LKM

Step17: choose IKM and flow control as false.