INFORMATICA REPOSITORY QUERIES – PART III

INFORMATICA
REPOSITORY QUERIES – PART II

13
GROUPS & USERS

Power Center Version 8 onwards Users and Groups details are stored
in Domain database and in unreadable format. On execution of below attached
scripts on domain database v_users and v_user_group views will be created and
you can query on these views.

Steps to Follow:

Step 1 : Log into Domain db with create view, procedure & Function user Privileges
Step 2 : Execute below function

create or replace FUNCTION xblob_to_clob(l_blob BLOB) RETURN CLOB IS l_clob CLOB;
l_src_offset NUMBER;
l_dest_offset NUMBER;
l_blob_csid NUMBER := dbms_lob.default_csid;
v_lang_context NUMBER := dbms_lob.default_lang_ctx;
l_warning NUMBER;
l_amount NUMBER;
BEGIN

IF dbms_lob.getlength(l_blob) > 0 THEN
    dbms_lob.createtemporary(l_clob,   TRUE);
    l_src_offset := 1;
    l_dest_offset := 1;
    l_amount := dbms_lob.getlength(l_blob);
    dbms_lob.converttoclob(l_clob,   l_blob,   l_amount,   l_src_offset,   l_dest_offset,   1,   v_lang_context,   l_warning);
    RETURN l_clob;
ELSE
    l_clob := to_clob(”);
    RETURN l_clob;
END IF;

dbms_lob.freetemporary(l_clob);
END;
/

Step 3 : Execute below sql to create v_user view

CREATE OR REPLACE FORCE VIEW “V_USERS” (“USER_ID”, “USER_NAME”, “NAMESPACE”, “FULL_NAME”, “DESCRIPTION”, “EMAIL”, “PHONE”, “READ_ONLY”, “DISABLE”) AS
SELECT id user_id,
     extractvalue(xmltype(xblob_to_clob(metadata)),
               ‘/metadata:User/userName’,
               ‘xmlns:common=”http://www.informatica.com/pcsf/common” xmlns:usermanagement=”http://www.informatica.com/pcsf/usermanagement” xmlns:domainservice=”http://www.informatica.com/pcsf/domainservice” xmlns:logservice=”http://www.informatica.com/pcsf/logservice” xmlns:domainbackup=”http://www.informatica.com/pcsf/domainbackup” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:metadata=”http://www.informatica.com/pcsf/metadata” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:domainconfigservice=”http://www.informatica.com/pcsf/domainconfigservice” xmlns:alertservice=”http://www.informatica.com/pcsf/alertservice” xmlns:licenseusage=”http://www.informatica.com/pcsf/licenseusage” xmlns:webserviceshub=”http://www.informatica.com/pcsf/webserviceshub”‘
              ) userName,
     extractvalue(xmltype(xblob_to_clob(metadata)),
               ‘/metadata:User/nameSpace’,
               ‘xmlns:common=”http://www.informatica.com/pcsf/common” xmlns:usermanagement=”http://www.informatica.com/pcsf/usermanagement” xmlns:domainservice=”http://www.informatica.com/pcsf/domainservice” xmlns:logservice=”http://www.informatica.com/pcsf/logservice” xmlns:domainbackup=”http://www.informatica.com/pcsf/domainbackup” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:metadata=”http://www.informatica.com/pcsf/metadata” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:domainconfigservice=”http://www.informatica.com/pcsf/domainconfigservice” xmlns:alertservice=”http://www.informatica.com/pcsf/alertservice” xmlns:licenseusage=”http://www.informatica.com/pcsf/licenseusage” xmlns:webserviceshub=”http://www.informatica.com/pcsf/webserviceshub”‘
              ) nameSpace,
     extractvalue(VALUE(i),
               ‘info/fullName’,
               ‘xmlns:common=”http://www.informatica.com/pcsf/common” xmlns:usermanagement=”http://www.informatica.com/pcsf/usermanagement” xmlns:domainservice=”http://www.informatica.com/pcsf/domainservice” xmlns:logservice=”http://www.informatica.com/pcsf/logservice” xmlns:domainbackup=”http://www.informatica.com/pcsf/domainbackup” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:metadata=”http://www.informatica.com/pcsf/metadata” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:domainconfigservice=”http://www.informatica.com/pcsf/domainconfigservice” xmlns:alertservice=”http://www.informatica.com/pcsf/alertservice” xmlns:licenseusage=”http://www.informatica.com/pcsf/licenseusage” xmlns:webserviceshub=”http://www.informatica.com/pcsf/webserviceshub”‘
              ) fullName,
     extractvalue(VALUE(i),
               ‘info/description’,
               ‘xmlns:common=”http://www.informatica.com/pcsf/common” xmlns:usermanagement=”http://www.informatica.com/pcsf/usermanagement” xmlns:domainservice=”http://www.informatica.com/pcsf/domainservice” xmlns:logservice=”http://www.informatica.com/pcsf/logservice” xmlns:domainbackup=”http://www.informatica.com/pcsf/domainbackup” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:metadata=”http://www.informatica.com/pcsf/metadata” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:domainconfigservice=”http://www.informatica.com/pcsf/domainconfigservice” xmlns:alertservice=”http://www.informatica.com/pcsf/alertservice” xmlns:licenseusage=”http://www.informatica.com/pcsf/licenseusage” xmlns:webserviceshub=”http://www.informatica.com/pcsf/webserviceshub”‘
              ) description,
     extractvalue(VALUE(i),
               ‘info/email’,
               ‘xmlns:common=”http://www.informatica.com/pcsf/common” xmlns:usermanagement=”http://www.informatica.com/pcsf/usermanagement” xmlns:domainservice=”http://www.informatica.com/pcsf/domainservice” xmlns:logservice=”http://www.informatica.com/pcsf/logservice” xmlns:domainbackup=”http://www.informatica.com/pcsf/domainbackup” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:metadata=”http://www.informatica.com/pcsf/metadata” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:domainconfigservice=”http://www.informatica.com/pcsf/domainconfigservice” xmlns:alertservice=”http://www.informatica.com/pcsf/alertservice” xmlns:licenseusage=”http://www.informatica.com/pcsf/licenseusage” xmlns:webserviceshub=”http://www.informatica.com/pcsf/webserviceshub”‘
              ) email,
     extractvalue(VALUE(i),
               ‘info/phone’,
               ‘xmlns:common=”http://www.informatica.com/pcsf/common” xmlns:usermanagement=”http://www.informatica.com/pcsf/usermanagement” xmlns:domainservice=”http://www.informatica.com/pcsf/domainservice” xmlns:logservice=”http://www.informatica.com/pcsf/logservice” xmlns:domainbackup=”http://www.informatica.com/pcsf/domainbackup” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:metadata=”http://www.informatica.com/pcsf/metadata” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:domainconfigservice=”http://www.informatica.com/pcsf/domainconfigservice” xmlns:alertservice=”http://www.informatica.com/pcsf/alertservice” xmlns:licenseusage=”http://www.informatica.com/pcsf/licenseusage” xmlns:webserviceshub=”http://www.informatica.com/pcsf/webserviceshub”‘
              ) phone,
     extractvalue(VALUE(i),
               ‘info/readOnly’,
               ‘xmlns:common=”http://www.informatica.com/pcsf/common” xmlns:usermanagement=”http://www.informatica.com/pcsf/usermanagement” xmlns:domainservice=”http://www.informatica.com/pcsf/domainservice” xmlns:logservice=”http://www.informatica.com/pcsf/logservice” xmlns:domainbackup=”http://www.informatica.com/pcsf/domainbackup” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:metadata=”http://www.informatica.com/pcsf/metadata” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:domainconfigservice=”http://www.informatica.com/pcsf/domainconfigservice” xmlns:alertservice=”http://www.informatica.com/pcsf/alertservice” xmlns:licenseusage=”http://www.informatica.com/pcsf/licenseusage” xmlns:webserviceshub=”http://www.informatica.com/pcsf/webserviceshub”‘
              ) readOnly,
     extractvalue(VALUE(i),
               ‘info/disable’,
               ‘xmlns:common=”http://www.informatica.com/pcsf/common” xmlns:usermanagement=”http://www.informatica.com/pcsf/usermanagement” xmlns:domainservice=”http://www.informatica.com/pcsf/domainservice” xmlns:logservice=”http://www.informatica.com/pcsf/logservice” xmlns:domainbackup=”http://www.informatica.com/pcsf/domainbackup” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:metadata=”http://www.informatica.com/pcsf/metadata” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:domainconfigservice=”http://www.informatica.com/pcsf/domainconfigservice” xmlns:alertservice=”http://www.informatica.com/pcsf/alertservice” xmlns:licenseusage=”http://www.informatica.com/pcsf/licenseusage” xmlns:webserviceshub=”http://www.informatica.com/pcsf/webserviceshub”‘
              ) disable
FROM pcsf_user x,
     TABLE(
      xmlsequence(
            EXTRACT(
               xmltype(xblob_to_clob(x.metadata)),
               ‘/metadata:User/info’,
               ‘xmlns:common=”http://www.informatica.com/pcsf/common” xmlns:usermanagement=”http://www.informatica.com/pcsf/usermanagement” xmlns:domainservice=”http://www.informatica.com/pcsf/domainservice” xmlns:logservice=”http://www.informatica.com/pcsf/logservice” xmlns:domainbackup=”http://www.informatica.com/pcsf/domainbackup” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:metadata=”http://www.informatica.com/pcsf/metadata” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:domainconfigservice=”http://www.informatica.com/pcsf/domainconfigservice” xmlns:alertservice=”http://www.informatica.com/pcsf/alertservice” xmlns:licenseusage=”http://www.informatica.com/pcsf/licenseusage” xmlns:webserviceshub=”http://www.informatica.com/pcsf/webserviceshub”‘)
      )
    ) i;
    /

Step 4 : Execute below sql to create v_user_group view

CREATE OR REPLACE VIEW V_USER_GROUP
(GROUP_ID, GROUP_NAME, USER_NAME)
AS
SELECT id group_id,
extractvalue(xmltype(xblob_to_clob(metadata)),   ‘/metadata:Group/groupName’,   ‘xmlns:common=”http://www.informatica.com/pcsf/common” xmlns:usermanagement=”http://www.informatica.com/pcsf/usermanagement” xmlns:domainservice=”http://www.informatica.com/pcsf/domainservice” xmlns:logservice=”http://www.informatica.com/pcsf/logservice” xmlns:domainbackup=”http://www.informatica.com/pcsf/domainbackup” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:metadata=”http://www.informatica.com/pcsf/metadata” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:domainconfigservice=”http://www.informatica.com/pcsf/domainconfigservice” xmlns:alertservice=”http://www.informatica.com/pcsf/alertservice” xmlns:licenseusage=”http://www.informatica.com/pcsf/licenseusage” xmlns:webserviceshub=”http://www.informatica.com/pcsf/webserviceshub”‘) group_name,
extractvalue(VALUE(p),   ‘userRef/userName’,   ‘xmlns:common=”http://www.informatica.com/pcsf/common” xmlns:usermanagement=”http://www.informatica.com/pcsf/usermanagement” xmlns:domainservice=”http://www.informatica.com/pcsf/domainservice” xmlns:logservice=”http://www.informatica.com/pcsf/logservice” xmlns:domainbackup=”http://www.informatica.com/pcsf/domainbackup” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:metadata=”http://www.informatica.com/pcsf/metadata” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:domainconfigservice=”http://www.informatica.com/pcsf/domainconfigservice” xmlns:alertservice=”http://www.informatica.com/pcsf/alertservice” xmlns:licenseusage=”http://www.informatica.com/pcsf/licenseusage” xmlns:webserviceshub=”http://www.informatica.com/pcsf/webserviceshub”‘) AS
user_name
FROM pcsf_group x,
TABLE(xmlsequence(EXTRACT(xmltype(xblob_to_clob(x.metadata)),   ‘/metadata:Group/userRef’,   ‘xmlns:common=”http://www.informatica.com/pcsf/common” xmlns:usermanagement=”http://www.informatica.com/pcsf/usermanagement” xmlns:domainservice=”http://www.informatica.com/pcsf/domainservice” xmlns:logservice=”http://www.informatica.com/pcsf/logservice” xmlns:domainbackup=”http://www.informatica.com/pcsf/domainbackup” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:metadata=”http://www.informatica.com/pcsf/metadata” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:domainconfigservice=”http://www.informatica.com/pcsf/domainconfigservice” xmlns:alertservice=”http://www.informatica.com/pcsf/alertservice” xmlns:licenseusage=”http://www.informatica.com/pcsf/licenseusage” xmlns:webserviceshub=”http://www.informatica.com/pcsf/webserviceshub”‘))) p
/

15.1
User ,
Group and Status of User

select distinct ‘DEV8_ABCD_REPO’ as Repository ,(select name from pcsf_domain) domain_name, group_name, a.user_name,description,

decode(disable,‘true’,‘Disabled’,‘false’,‘Enabled’,NULL) as
user_status

from v_users a, v_user_group b

where a.user_name = b.user_name

and namespace = ‘Native’

order by 1,2,3,4

15.2
Kill
User

For removing Executing lock sometimes if we search with the
workflow process id in the server we won’t be able to find it at server level.
Try the below steps.

Run the below Query:

SELECT SUBJ_NAME FOLDER,

       CNX_ID,
       DECODE (LOCK_TYPE,

1, ‘NodeInUseLock’,

2, ‘NodeWriteIntentLock’,

4, ‘ExecuteLock’,

5, ‘SharedLock’,

6, ‘ExclusiveLock’,

7, ‘SubTreeSharedLoc’,

8, ‘SubTreeExclusiveLock’,

9, ‘SubTreeISharedLock’,

10, ‘SubTreeIExclusiveLock’,

11, ‘SubTreeWriteIntentLock’,

12, ‘SubTreeIWriteIntentLock’)
          LOCK_TYPE
FROM OPB_OBJECT_LOCKS L, OPB_SUBJECT S
WHERE L.SUBJECT_ID = SUBJ_ID
and lock_type = 4
and SUBJ_NAME = ”

INFORMATICA REPOSITORY QUERIES – PART III

15.2
Kill
User

About

Legal

Trending Topics

INFORMATICA REPOSITORY QUERIES – PART III

15.2 Kill User

About

Legal

Trending Topics

15.2
Kill
User