EIM Useful

Some Useful code and syntax for EIM as well as Oracle PL/SQL:

Create Sequence Syntax:

CREATE SEQUENCE  "STG"."SEQ_S_SRV_REQ"  MINVALUE 10001 MAXVALUE 100000000000 INCREMENT BY 1 START WITH 68309 NOCACHE  NOORDER  NOCYCLE ;

DBMS GATHER STATS:

EXEC DBMS_STATS.GATHER_TABLE_STATS('SIEBEL','EIM_SRV_REQ',DEGREE  =>10,ESTIMATE_PERCENT =>100,METHOD_OPT =>'FOR ALL INDEXED COLUMNS SIZE AUTO',GRANULARITY =>'ALL',CASCADE=>TRUE);

To identify objects with greater than 10 extents:

SELECT segment_name, segment_type, tablespace_name, extents
FROM dba_segments
WHERE owner = (Siebel table_owner)
and extents > 9;

To Check the Optimization Mode:

SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = ‘OPTIMIZER_MODE’;

Putting Table in Cache:

ALTER TABLE S_LST_OF_VAL CACHE;

Check Locked Users:

select USERNAME,ACCOUNT_STATUS 
from DBA_USERS 
where account_status like '%LOCK%';

To Unlock Oracle account:

alter user ARIFM account unlock;

To Change Password of Oracle Account:

alter user <username> identified by <new_password>;

Siebel Workflow Change:

1. Query for WF name
2. Lock project (Alt+L)
3. Click on Revise button on top right
4. Make changes
5. Click on Publish button next to Revise button
6. Unlock the project (Alt+U)
7. Activate WF from Application
SiteMap --> Administration-Business Process --> Workflow Process Deployment
--> Active Workflow Process child tab
1. Query for the workflow name in upper list applet
2. Query for the workflow name in lower list applet
3. Click on Activate Button.

Running Siebel Workflow:

1. SiteMap --> Administration-Business Service --> Simulator
2. Create new records with name as "Workflow Process Manager" and method as "RunProcess"
3. Add input arguments with property name as "ProcessName" and property value as Workflow name.
4. Click on Run button.

GRANT on Table SYNTAX:

GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON  <table_name> TO <schema>;

Eg.

GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON  STG_EMP_RECS TO SIEBEL;

CREATE TABLE FROM ANOTHER TABLE:

CREATE TABLE <TABLE_NAME1> AS SELECT * FROM <TABLE_NAME2>

create table STG_EMP_RECS AS SELECT * FROM STG_EMP_LOGIN;

Compile DB Objects:

exec dbms_ddl.alter_compile ('PACKAGE','SAMPLE_PKG','SIEBEL');

Check the version of Oracle:

select * from v$version;

Unlocking Statistics for Gather Stats:

EXEC dbms_stats.unlock_table_stats('SIEBEL','S_EVT_ACT');

For locking:
EXEC dbms_stats.lock_table_stats('SIEBEL','S_EVT_ACT');

To kill a Session:

alter system kill session 'sid,serial#';

Generating/Exporting Explain Plan for an SQL Statement:

explain plan for
select * from STG_PRODUCT where batch = '125642890235;

select PLAN_TABLE_OUTPUT
from table(dbms_xplan.display('plan_table',null,'basic'));

Lcoking Multiple Users in Oracle Database:

declare
cursor DBA_USERS_FOR_LOCK is
select USERNAME
from DBA_USERS 
where ACCOUNT_STATUS = 'OPEN'
and USERNAME not like '%ADMIN%';
begin
  for DBA_USERS_FOR_LOCK_INST in DBA_USERS_FOR_LOCK
    LOOP
      execute immediate 'alter user '||DBA_USERS_FOR_LOCK_INST.USERNAME||' account lock';
    end LOOP;
  commit;
end;

select 'ALTER USER '||USERNAME||' ACCOUNT LOCK;' from DBA_USERS 
where USERNAME not like '%ADMIN%'
AND ACCOUNT_STATUS = 'OPEN';

--The above code will generate the scripts which can be run to lock the users.

set head off;
spool D:\lock.sql
select 'ALTER USER '||USERNAME||' ACCOUNT LOCK;' from DBA_USERS 
where USERNAME not like '%ADMIN%'
AND ACCOUNT_STATUS = 'OPEN';
spool off;

--The above code will save the output in a file but will limit to 5000 records only.

List all users who have been assigned a particular role:

select * from dba_role_privs where granted_role = '<ROLE_NAME>';

List all roles given to a User:

select * from dba_role_privs where grantee = '<USERNAME>';

Grant Role to a specific User:

grant <role_name> to <username>;

E.g. grant SSE_ROLE to ARIFM;

List all privileges given to a user:

select
  lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
  (
  /* THE USERS */
    select 
      null     grantee, 
      username granted_role
    from 
      dba_users
    where
      username like upper('%&enter_username%')
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role;

List which tables a certain role gives SELECT access to:

select * from role_tab_privs where role = '<ROLE_NAME>' and privilege = 'SELECT';

List all tables a user can SELECT from:

select * from dba_tab_privs where GRANTEE ='<USERNAME>' and privilege = 'SELECT';

List all users who can SELECT on a particular table (either through being given a relevant role or through a direct grant (ie grant select on atable to joe))? The result of this query should also show through which role the user has this access or whether it was a direct grant.

select Grantee,'Granted Through Role' as Grant_Type, role, table_name
from role_tab_privs rtp, dba_role_privs drp
where rtp.role = drp.granted_role
and table_name = '<TABLE_NAME>' 
union
select Grantee,'Direct Grant' as Grant_type, null as role, table_name
from dba_tab_privs
where table_name = '<TABLE_NAME>' ;



Some Oracle functions:

-- extracts month from date
select to_char(to_date('15-11-2010', 'DD-MM-YYYY'), 'Month') from dual;

-- extracts Year from date
select to_char(to_date('15-11-2010', 'DD-MM-YYYY'), 'YYYY') from dual;

-- extracts Quarter from date
select to_char(to_date('15-11-2010','DD-MM-YYYY'), 'Q') from dual;

-- removes a character from a string
select regexp_replace('---ar-if---so--u-r--av---', '-') from dual;


Check tablespace Memory Usage in Database:

SELECT  a.tablespace_name,
    ROUND (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES) * 100,2) percentage_used,
    c.BYTES / 1024 / 1024 space_allocated,
    ROUND (c.BYTES / 1024 / 1024 - NVL (b.BYTES, 0) / 1024 / 1024,2) space_used,
    ROUND (NVL (b.BYTES, 0) / 1024 / 1024, 2) space_free, 
    c.DATAFILES
  FROM dba_tablespaces a,
       (    SELECT   tablespace_name, 
                  SUM (BYTES) BYTES
           FROM   dba_free_space
       GROUP BY   tablespace_name
       ) b,
      (    SELECT   COUNT (1) DATAFILES, 
                  SUM (BYTES) BYTES, 
                  tablespace_name
           FROM   dba_data_files
       GROUP BY   tablespace_name
    ) c
  WHERE b.tablespace_name(+) = a.tablespace_name 
    AND C.TABLESPACE_NAME(+) = A.TABLESPACE_NAME
ORDER BY NVL (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES), 0) DESC;


MORE TO COME...