4.1
List
of source tables
SELECT
B.SUBJ_NAME,
C.DBDNAM,
D.DBTYPE_NAME,
A.SOURCE_NAME AS TABLE_NAME,
A.FILE_NAME SCHEMA_NAME,
A.OWNERNAME
FROM
OPB_SRC A,OPB_SUBJECT B, OPB_DBD C,OPB_MMD_DBTYPE D
WHERE A.SUBJ_ID = B.SUBJ_ID
AND A.DBDID = C.DBDID
AND C.DBTYPE = D.DBTYPE_ID
--AND
A.SOURCE_NAME <> A.FILE_NAME
ORDER BY 1,2,3,4,5
4.2
List
and count of tables in each folder by db type
SELECT
B.SUBJ_NAME,
D.DBTYPE_NAME,
count(*)
FROM
OPB_SRC A,OPB_SUBJECT B, OPB_DBD C,OPB_MMD_DBTYPE D
WHERE A.SUBJ_ID = B.SUBJ_ID
AND A.DBDID = C.DBDID
AND C.DBTYPE = D.DBTYPE_ID
--AND
A.SOURCE_NAME <> A.FILE_NAME
group by B.SUBJ_NAME,D.DBTYPE_NAME
order by 1,2,3
4.3
List
and count of tables overall used
SELECT SOURCE_NAME, COUNT(SOURCE_NAME) FROM
REP_TBL_MAPPING
GROUP BY SOURCE_NAME
ORDER BY 1,2 ASC
4.4
List
of source tables used in mapping
SELECT
SUBJECT_AREA,SOURCE_NAME,MAPPING_NAME FROM REP_SRC_MAPPING
ORDER
BY 1,2,3
4.5
List
of source tables using as shortcuts
SELECT DISTINCT
B.SUBJ_NAME,
C.DBDNAM,
D.DBTYPE_NAME,
A.SOURCE_NAME AS TABLE_NAME,
A.FILE_NAME SCHEMA_NAME,
A.OWNERNAME
FROM
OPB_SRC A,OPB_SUBJECT B, OPB_DBD C,OPB_MMD_DBTYPE D
WHERE A.SUBJ_ID = B.SUBJ_ID
AND A.DBDID = C.DBDID
AND C.DBTYPE = D.DBTYPE_ID
--AND
A.SOURCE_NAME <> A.FILE_NAME
and A.SOURCE_NAME like 'sc_%'
ORDER BY 1,2,3,4,5
4.6
List
Source table where all its used in Mapping with Source Database Name
This view provides a list of the latest version of sources defined
in each folder of a repository. Sources include both relational sources and
non-relational sources such as XML files and flat files.
SELECT REP_ALL_SOURCES.SOURCE_NAME,
REP_SRC_MAPPING.MAPPING_NAME,
REP_ALL_SOURCES.SOURCE_DATABASE_NAME
FROM REP_SRC_MAPPING, REP_ALL_SOURCES
WHERE REP_SRC_MAPPING.SOURCE_ID(+) = REP_ALL_SOURCES.SOURCE_ID
AND REP_SRC_MAPPING.SOURCE_NAME(+) =
REP_ALL_SOURCES.PARENT_SOURCE_NAME
AND REP_SRC_MAPPING.SUBJECT_ID(+) = REP_ALL_SOURCES.SUBJECT_ID
ORDER BY 1,2
4.7
List
All Columns, DB, Length of a Source Table
SELECT
SUBJECT_AREA,
PARENT_SOURCE_DATABASE_NAME,
PARENT_SOURCE_DATABASE_TYPE,
PARENT_SOURCE_NAME as SOURCE_TABLE_NAME,
SOURCE_FIELD_NUMBER,
SOURCE_FIELD_NAME,
SOURCE_FIELD_DATATYPE,
SOURCE_FIELD_PHYSICAL_LENGTH,
SOURCE_FIELD_KEY_TYPE,
PARENT_SOURCE_LAST_SAVED
from
REP_ALL_SOURCE_FLDS
where
parent_source_name = 'SRC_TABLE_NAME'
and
parent_subject_area = 'FOLDER_NAME'
ORDER
BY SOURCE_FIELD_NUMBER
4.8
List
all Flat files used as Source
This view provides a list of all file definitions in the
repository. Use FIRST_FIELD_ID to retrieve the fields belonging to a
non-relational source by following the links in the REP_SRC_FILE_FLDS view. Any
flat file imported through the Source Analyzer has an entry.
SELECT
*
FROM
REP_SRC_FILES
WHERE
subject_area
= 'FOLDER_NAME'
4.9
List
all Relational Tables used as Source
This view provides a list of
relational database table sources that have been analyzed through the Source
Analyzer tool or imported from a DDL (Data Definition Language) file.
SELECT
*
FROM
REP_SRC_TBLS
WHERE
subject_area
= 'FOLDER_NAME'
ORDER
BY 1
4.10
Check
where all a column is used in different tables and Folders.
SELECT
*
FROM
REP_SRC_TBL_FLDS
WHERE
Column_Name
= 'PH_DIMSN_KEY'
order
by 1
It's really interesting blog!thanks for sharing this information with us!!
ReplyDeleteIoT Training in Chennai
IoT Training
Xamarin Course in Chennai
Node JS Course in Chennai
Big Data Analytics Training in Chennai
Hadoop Admin Training in Chennai
Informatica MDM Training in Chennai
IEEE Final Year Project centers make amazing deep learning final year projects ideas for final year students Final Year Projects for CSE to training and develop their deep learning experience and talents.
DeleteIEEE Final Year projects Project Centers in India are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation.
corporate training in chennai corporate training in chennai
corporate training companies in india corporate training companies in india
corporate training companies in chennai corporate training companies in chennai
I have read your blog its very attractive and impressive. I like it your blog. Digital Marketing Company in Chennai
I’m excited to uncover this page. I need to to thank you for ones time for this particularly fantastic read !! I definitely really liked every part of it and i also have you saved to fav to look at new information in your site.
ReplyDeletedata science course in guwahati
I see some amazingly important and kept up to length of your strength searching for in your on the site
ReplyDeletedata science course in guwahati
Thanks buddy
ReplyDeleteReally an awesome blog, Informative content. Keep sharing more stuff like this. Thank you.
ReplyDeleteData Science Institute in Hyderabad