Thursday, October 21, 2021

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.



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

round(,2) Free_space_MB,

round( (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,;

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.


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.

Tuesday, October 19, 2021

OIC - Unable to convert a string in the xml to a - 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

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

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

Monday, October 18, 2021

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;
    return false;

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

Sunday, October 17, 2021

ODI 12c - Loading Flat file to SQL table

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


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.


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.

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...