Hi All,
I have seen lot of folks complaining about readily available utility views/tables for day to day activities ...
Most of the information is available in the system tables but not at one place.. I thought I would come up with some utility sql's that can help in day to day development/administration activities
Below are the compilation that would be helpful to developers..
Change List
select SRC_SYSTEM, CHANGE_NUMBER, PACKAGE_ID, OBJECT_NAME,OBJECT_SUFFIX
from _SYS_REPO.CHANGE_ENTRIES WHERE CHANGE_NUMBER = '12345'
List of Sub Views and Tables Used in a final View
SELECT DISTINCT DEPENDENT_OBJECT_NAME,BASE_OBJECT_NAME,COMMENTS,BASE_SCHEMA_NAME,
BASE_OBJECT_TYPE,DEPENDENCY_TYPE FROM SYS.OBJECT_DEPENDENCIES
LEFT OUTER JOIN SYS.TABLES TABLEDESCR ON BASE_OBJECT_NAME = TABLEDESCR.TABLE_NAME
AND BASE_SCHEMA_NAME = TABLEDESCR.SCHEMA_NAME
WHERE DEPENDENT_OBJECT_NAME = 'TEST/CVG_TESTQUERY' --Package/ViewName
ORDER BY BASE_OBJECT_TYPE ASC
Check a view/dependent views for change number that need to be migrated(Open)
SELECT A.*,C.* FROM
( SELECT DISTINCT DEPENDENT_OBJECT_NAME,BASE_OBJECT_NAME,COMMENTS,BASE_SCHEMA_NAME,BASE_OBJECT_TYPE,
DEPENDENCY_TYPE, SUBSTR_AFTER(BASE_OBJECT_NAME,'/') AS VIEW_CALCULATED,
SUBSTR_BEFORE(BASE_OBJECT_NAME,'/') AS PKG_CALCULATED
FROM SYS.OBJECT_DEPENDENCIES
LEFT OUTER JOIN
SYS.TABLES TABLEDESCR
ON BASE_OBJECT_NAME = TABLEDESCR.TABLE_NAME AND BASE_SCHEMA_NAME = TABLEDESCR.SCHEMA_NAME
WHERE DEPENDENT_OBJECT_NAME = 'Test/CVG_QUERY' and BASE_OBJECT_TYPE = 'VIEW') AS A
inner JOIN
(
SELECT A.* ,B.CREATED_BY, MAP(B.status,1,'OPEN',2,'RELEASED') as Status
FROM _SYS_REPO.CHANGE_ENTRIES AS A INNER JOIN
_sys_repo.changes as B ON A.CHANGE_NUMBER = B.change_number AND B.status = '1'
) AS C
ON A.VIEW_CALCULATED = C.OBJECT_NAME and A.PKG_CALCULATED = C.PACKAGE_ID
Check for the properties of a view
SELECT package_id, object_name, object_suffix, cdata,
case when locate (cdata, 'enforceSqlExecution=true') > 0 then 'TRUE' else 'FALSE'
end as IS_SQLENGINE_FORCED,
CASE WHEN locate (cdata, 'checkAnalyticPrivileges=true') > 0 THEN 'TRUE' ELSE 'FALSE'
end as IS_AP_ENABLED
FROM _sys_repo.active_object
where object_suffix='calculationview' and object_name ='CVG_QUERY' AND PACKAGE_ID LIKE 'Test%';
List of Sub Views Used in a final View including AP's
SELECT A.*,B.* FROM (SELECT DISTINCT DEPENDENT_OBJECT_NAME,BASE_OBJECT_NAME,-- COMMENTS,
BASE_SCHEMA_NAME,BASE_OBJECT_TYPE,DEPENDENCY_TYPE,
SUBSTR_AFTER(BASE_OBJECT_NAME,'/') AS VIEW_CALCULATED,
SUBSTR_BEFORE(BASE_OBJECT_NAME,'/') AS VIEW_PACKAGE
FROM SYS.OBJECT_DEPENDENCIES
WHERE DEPENDENT_OBJECT_NAME = 'Test/CVG_QUERY' --Package/ViewName
and BASE_OBJECT_TYPE = 'VIEW' ) AS A
left outer join ( SELECT package_id,object_name,object_suffix,cdata,
case when locate (cdata,'enforceSqlExecution=true') > 0 then 'TRUE'
else 'FALSE' end as IS_SQLENGINE_FORCED,
CASE WHEN locate (cdata,'checkAnalyticPrivileges=true') > 0 THEN 'TRUE'
ELSE 'FALSE' end as IS_AP_ENABLED
FROM _sys_repo.active_object
where object_suffix='calculationview'AND PACKAGE_ID LIKE 'Test%' ) --Package
as B ON A. VIEW_CALCULATED = B.object_name AND A.VIEW_PACKAGE = B.PACKAGE_ID
Check for the properties of a view -At Package level
select PACKAGE_ID,OBJECT_NAME,OBJECT_SUFFIX,VERSION_ID,ACTIVATED_AT,ACTIVATED_BY,EDIT,
CDATA,BDATA,COMPRESSION_TYPE,FORMAT_VERSION,DELIVERY_UNIT,DU_VERSION,DU_VENDOR,DU_VERSION_SP,
DU_VERSION_PATCH,OBJECT_STATUS,CHANGE_NUMBER,RELEASED_AT from _SYS_REPO.ACTIVE_OBJECT
where CDATA like '%checkAnalyticPrivileges="true"%' and PACKAGE_ID like '%Test%'
In the next post I will come up with memory utility SQL's for Memory and User Management
Thanks
Venkat
