/* Get Columns Types of a Specific Database Table */
SELECT table_name, column_name, data_type, data_length
FROM USER_TAB_COLUMNS
WHERE table_name = 'TMP_QRY_1' ; -- Put the table name here
/* Get Database Column Table Name Using a Database Column */
SELECT table_name, column_name
FROM USER_TAB_COLUMNS
where table_name in (select table_name from all_tables) and column_name in ('COUNTRY','REGION') ORDER BY 1
SELECT FOLDER_NAME,
WORKFLOW_NAME,
SESSION_NAME,
TARGET_NAME,
TARGET_TABLE_NAME
FROM (SELECT DISTINCT Z.SUBJ_NAME FOLDER_NAME,
C.TASK_NAME WORKFLOW_NAME,
A.INSTANCE_NAME SESSION_NAME,
D.INSTANCE_NAME TARGET_NAME,
B.ATTR_VALUE TARGET_TABLE_NAME
FROM OPB_TASK_INST
A,
OPB_SWIDGET_ATTR B,
OPB_TASK C,
OPB_SUBJECT Z,
OPB_SWIDGET_INST D,
OPB_TASK T,
OPB_SESS_EXTNS E
WHERE C.SUBJECT_ID = Z.SUBJ_ID
AND C.TASK_TYPE IN (70, 71
)AND C.TASK_ID = A.WORKFLOW_ID
AND A.TASK_ID = B.SESSION_ID
AND A.TASK_ID = D.SESSION_ID
AND D.WIDGET_TYPE = 2
AND D.SESS_WIDG_INST_ID = B.SESS_WIDG_INST_ID
AND B.ATTR_ID = 19
AND B.ATTR_VALUE IS NOT NULL
AND T.TASK_ID = D.SESSION_I
D
AND E.SESSION_ID = D.SESSION_ID
AND E.SESS_WIDG_INST_ID = D.SESS_WIDG_INST_ID
AND C.IS_VISIBLE = 1
AND C.VERSION_NUMBER = A.VERSION_NUMBER
AND T.IS_VISIBLE = 1
AND D.VERSION_NUMBER = T.VERSION_NUMBER
AND B.VERSION_NUMBER = T.VERSION_NUMBE
R
AND E.VERSION_NUMBER = D.VERSION_NUMBER
UNION
SELECT DISTINCT Z.SUBJ_NAME FOLDER_NAME,
C.TASK_NAME WORKFLOW_NAME,
A.INSTANCE_NAME SESSION_NAME,
D.INSTANCE_NAME TARGET_NAME,
B.ATTR_VALUE TARGET_TABLE_NAME
FROM OPB_TASK_INST A,
OPB_WIDGET_ATTR B,
OPB_TASK C,
OPB_SUBJECT Z,
OPB_SWIDGET_INST D,
OPB_TASK T,
OPB_SESS_EXTNS E,
OPB_MAPPING M
WHERE C.SUBJECT_ID = Z.SUBJ_ID
AND C.TASK_TYPE IN (70, 71)
AND C.TASK_ID = A.WORKFLOW_ID
AND A.TASK_ID = D.SESSION_ID
AND B.MAPPING_ID = D.MAPPING_ID
AND B.WIDGET_ID = D.WIDGET_ID
AND B.WIDGET_TYPE = D.WIDGET_TYPE
AND D.WIDGET_TYPE = 2
AND B.ATTR_ID = 19
AND B.ATTR_VALUE IS NOT NULL
AND T.TASK_ID = D.SESSION_ID
AND E.SESSION_ID = D.SESSION_ID
AND E.SESS_WIDG_INST_ID = D.SESS_WIDG_INST_ID
AND M.MAPPING_ID = B.MAPPING_ID
AND C.IS_VISIBLE = 1
AND C.VERSION_NUMBER = A.VERSION_NUMBER
AND T.IS_VISIBLE = 1
AND D.VERSION_NUMBER = T.VERSION_NUMBER
AND E.VERSION_NUMBER = D.VERSION_NUMBER
AND M.IS_VISIBLE = 1
AND B.VERSION_NUMBER = M.VERSION_NUMBER
AND NOT EXISTS
(SELECT *
FROM OPB_SWIDGET_ATTR
WHERE SESSION_ID = D.SESSION_ID
AND SESS_WIDG_INST_ID = D.SESS_WIDG_INST_ID
AND ATTR_ID = 19
AND VERSION_NUMBER = D.VERSION_NUMBER)) DUAL
ORDER BY 1,
2,
3,
4,
5;
/* Get Informatica Target Table Name Info */
select * from BIAPPS_INFA_REP.rep_all_targets
where parent_target_name like '%ABF%';
/* Get mapping(s) name(s) for a source table */
SELECT SUBJECT_AREA , MAPPING_NAME
FROM BIAPPS_INFA_REP.REP_ALL_MAPPINGS
where MAPPING_NAME in (
<div> SELECT MAPPING_NAME FROM BIAPPS_INFA_REP.REP_SRC_MAPPING
where SOURCE_NAME = :ENTER_SRC_TBL_NAME);
/* Number of transformation */
--transofrmations :
select WIDGET_TYPE_NAME Transformation_Type,
count (*) Number_OF_Transformations
from "BIAPPS_INFA_REP"."REP_ALL_TRANSFORMS"
group by WIDGET_TYPE_NAME ;
/* Get Informatica Mapping Info */
select mapping_name from BIAPPS_INFA_REP.OPB_mapping -- GET TARGET TABLE NAME INFO
where mapping_name like '%Daily%';
------------------ Get Infa Workflow Session run Status ----------------
select * from biapps_infa_rep.rep_sess_log
order by session_timestamp desc ;
/* Get Informatica Workflow Run Status */
select
SUBJECT_AREA folder_name,
WORKFLOW_NAME,
to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') as Current_Date_Time,
to_char(START_TIME, 'YYYY-MM-DD HH24:MI:SS') as Starting_Date_Time,
to_char(END_TIME, 'YYYY-MM-DD HH24:MI:SS')as End_Date_Time,
round((END_TIME-START_TIME) *24*60,2) Time_Taken_Min,
RUN_ERR_CODE,
RUN_ERR_MSG,
RUN_STATUS_CODE
from
biapps_infa_rep.REP_WFLOW_RUN
where workflow_name like '%SDE_Product_Dim%' --------------------- put the workflow name here or remove the linbe for all
order by start_time desc;
/* Get Informatica Failed Sessions in the Past 10 Days */
SELECT Subject_Area AS Folder,
Session_Name,
Last_Error AS Error_Message,
DECODE (Run_Status_Code, 3, 'Failed', 4, 'Stopped', 5, 'Aborted')
AS Status,
Actual_Start AS Start_Time,
Session_TimeStamp
FROM biapps_infa_rep.rep_sess_log
WHERE run_status_code <> 1
AND TRUNC (Actual_Start) BETWEEN TRUNC (SYSDATE - 10) AND TRUNC (SYSDATE)
ORDER BY 1, 2;
/* Update Multiple Rows in a database table */
Alter Table WC_AGGR_F
add month_name varchar2(9) ;
UPDATE WC_AGGR_F
SET MONTH_NAME =
CASE MONTH
WHEN 1 THEN 'January'
WHEN 2 THEN 'Febreuary'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
END ;