Faydalı SQL'ler

 

Tools üzerinde lock'lı objectlerin lock'ını kaldırma:

Business Component:

UPDATE siebel.s_buscomp SET obj_locked_flg = 'N', obj_locked_date = NULL, obj_locked_by = null, obj_locked_lang = null WHERE NAME = 'BC_ADI'

Applet:

UPDATE siebel.s_applet SET obj_locked_flg = 'N', obj_locked_date = NULL, obj_locked_by = null, obj_locked_lang = null WHERE NAME = 'APPLET_ADI';

View:

UPDATE siebel.s_view SET obj_locked_flg = 'N', obj_locked_date = NULL, obj_locked_by = null, obj_locked_lang = null WHERE name = 'VIEW_ADI';

Table:

UPDATE siebel.s_table SET obj_locked_flg = 'N', obj_locked_date = null, obj_locked_by = null, obj_locked_lang = null WHERE tab.name = 'TABLO_ADI';

Business Service:

UPDATE siebel.s_service SET obj_locked_flg = 'N', obj_locked_date = NULL, obj_locked_by = null, obj_locked_lang = null WHERE NAME = 'BS_ADI';

Project:

UPDATE siebel.s_project SET locked_flg = 'N', locked_date = NULL, locked_by = NULL WHERE name = 'PROJECT_ADI';

Appletin yeraldığı view'ları bulan sorgu:

select w.name from siebel.S_VIEW_WTMPL_IT wti, siebel.S_VIEW_WEB_TMPL wt, siebel.S_VIEW w
where
wti.applet_name = 'APPLET_ADI'
and wti.VIEW_WEB_TMPL_ID = wt.row_id
and wt.view_id = w.row_id;

Parametreye göre job bulan sorgu:

select * from siebel.S_SRM_REQUEST 
where status = 'ACTIVE' 
and action_id in (
                  select row_id from siebel.S_SRM_ACTION 
                  where row_id in (
                                  select action_id from siebel.S_SRM_ACT_PARAM 
                                  where param_value like '%Job_Parametresi (WF Adı vs.)%'
                                  )
                  );

Hata mesajına göre ruleset ve action set bulma:

SELECT e.obj_type_cd AS object_type, e.obj_name AS object_name,
       e.evt_name AS event, e.evt_sub_name AS sub_event,
       e.actn_cond_expr AS conditional_expr, s.NAME AS action_set,
       a.NAME AS action, rs.NAME AS rule_set, r.NAME AS RULE, l.msg_text
  FROM siebel.S_CT_ACTION_SET s,
        siebel.S_CT_ACTION a,
        siebel.S_VALDN_RL_SET rs,
        siebel.S_VALDN_RULE r,
        siebel.S_ISS_VALDN_MSG m,
        siebel.S_ISS_VMSG_LANG l,
        siebel.S_CT_EVENT e
WHERE a.ct_actn_set_id = s.row_id
   AND a.svc_context LIKE
                     '"Rule Set Name", "' || rs.NAME || '", "Enable Log", "%"'
   AND rs.status_cd = 'Active'
   AND r.rule_set_id = rs.row_id
   AND r.valdn_msg_id = m.row_id
   AND l.par_row_id = m.row_id
   AND e.ct_actn_set_id = s.row_id 
   AND l.lang_id = 'ENU' --hata mesajının dili
 AND L.MSG_TEXT like '%HATA_MESAJI%'

Workflow'un çağırıldığı actionset'i bulma:

select * from siebel.S_CT_ACTION_SET acs,siebel.S_CT_ACTION ac 
where ac.SVC_CONTEXT like '%WF_ADI%' 
and acs.ROW_ID=ac.CT_ACTN_SET_ID;

Profile Attribute'un set edilidği actionset'i bulma:

select * from siebel.S_CT_ACTION_SET acs,siebel.S_CT_ACTION ac 

where ac.SET_ATTR like '%PROFILE_ATTRIBUTE_ADI%' 

and acs.ROW_ID=ac.CT_ACTN_SET_ID;

Sorumluluğa sahip pozisyon sayısını gösteren sorgu:

SELECT   resp.row_id, resp.NAME, COUNT (*) AS users_count

    FROM siebel.s_per_resp per_resp, siebel.s_resp resp

   WHERE resp.row_id = per_resp.resp_id(+)

GROUP BY resp.row_id, per_resp.resp_id, resp.NAME

ORDER BY COUNT (*) DESC;

Database Versiyonu:

select * from v$version;

Belirli tarih aralığında ortamda çalışan jobları gösteren sorgu:

SELECT
    srm_req.row_id,
    srm_act.display_name,
    srm_req.status,
    srm_req.ACTL_START_DT,
    srm_req.ACTL_END_DT
FROM
    siebel.s_srm_request   srm_req,
    siebel.s_srm_action    srm_act
WHERE
    srm_act.row_id = srm_req.action_id
    and srm_req.ACTL_END_DT<=:bitis_tarihi
    and srm_req.ACTL_START_DT>=:baslangic_tarihi
    and srm_req.ACTL_START_DT is not null
ORDER BY
    srm_req.ACTL_START_DT DESC;

Belirlen view'ı hangi user'ların görebileceğini gösteren sorgu:

SELECT
  u.login AS user_id,
  v.name AS view_name,
  resp.name AS responsibility
FROM
  siebel.s_resp resp,
  siebel.s_app_view v,
  siebel.s_app_view_resp view_resp,
  siebel.S_PER_RESP per_resp,
  siebel.S_PARTY p,
  siebel.S_USER u
WHERE v.row_id = view_resp.view_id
AND view_resp.resp_id = resp.row_id
AND per_resp.per_id = p.row_id
AND resp.row_id = per_resp.resp_id
AND u.row_id = p.row_id
AND v.name ='view_name'
ORDER BY u.login desc;

Database'deki tabloları size'ına göre sıralama

select segment_name,segment_type, sum(bytes/1024/1024/1024) GB

 from dba_segments

 where segment_type = 'TABLE'

group by segment_name,segment_type

order by GB desc; 

Belirli bir şemadaki tüm tabloların kayıt sayısını bulma

DECLARE

val NUMBER;

BEGIN

FOR I IN (SELECT TABLE_NAME FROM ALL_TABLES where owner = 'şema_name') LOOP

EXECUTE IMMEDIATE 'SELECT count(*) FROM şema_name.' || i.table_name || '@prod_link' INTO val;

DBMS_OUTPUT.PUT_LINE(i.table_name || ' & ' || val );

END LOOP;

END;

Tools User oluşturma 

CREATE USER TESTUSER

            IDENTIFIED BY "TESTUSER_PASSWORD" 

            DEFAULT TABLESPACE SIEBEL_DATA

            PROFILE DEFAULT

            ACCOUNT UNLOCK;

            GRANT SSE_ROLE TO TESTUSER;

            GRANT CONNECT TO TESTUSER;

            ALTER USER TESTUSER DEFAULT ROLE ALL;

ALTER USER TESTUSER QUOTA UNLIMITED ON SIEBEL_DATA;

"Administration - User" ekranından test user eklenir ve gerekli sorumluluk ve pozisyon verilir.

ps: db seviyesinde bir kısıtlama var ise, aşağıdaki select sorgusundan benzer user örnek alınarak insert sorgusu hazırlanır.

select * from DBMAin.db_login_params order by insert_date desc;

insert into DBMAin.db_login_params (username,osuser,ip_address,machine,db,trigger_control,insert_date) values (VALUES);