Some Useful EIM and PL/SQL Operations:
Create Squence Syntax:
CREATE SEQUENCE "<SCHEMA>"."<SEQ_NAME>" MINVALUE 1 MAXVALUE 100000000000 INCREMENT BY 1 START WITH 68309 NOCACHE NOORDER NOCYCLE ;
DBMS Gather Stats Script:
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 Database 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 Oracle Optimization Mode:
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = ‘OPTIMIZER_MODE’;
Putting Table in Cache:
ALTER TABLE S_LST_OF_VAL CACHE;
Creating a DB Link from Production to Dev Environment:
CREATE [PUBLIC] DATABASE LINK <link_name> CONNECT TO <user_name> IDENTIFIED BY <password> USING '<service_name>';
Eg. create public database link PROD_DEV CONNECT TO MARIF IDENTIFIED BY MARIF123 USING 'xyz.abc.com';
Check Locked Users:
select USERNAME,ACCOUNT_STATUS
from DBA_USERS
where account_status like '%LOCK%';
To Unlock Oracle account:
alter user SIEBEL account unlock;
To Change Password of Oracle Account:
alter user <username> identified by <new_password>;
GRANT on Table SYNTAX:
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON <table_name> TO <schema>;
or
GRANT ALL ON <table_name> TO <schema>;
CREATE TABLE FROM ANOTHER TABLE:
CREATE TABLE <TABLE_NAME1> AS SELECT * FROM <TABLE_NAME2>;
Compile DB Objects:
exec dbms_ddl.alter_compile ('<OBJECT>','OBJECT_NAME,'<SCHEMA>');
Eg. exec dbms_ddl.alter_compile ('PACKAGE','DUMMY_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 SQL Session:
alter system kill session 'sid,serial#';
Generating/Exporting Explain Plan for an SQL Statement:
explain plan for <SQL_Stmt>
Eg. select * from SIEBEL.S_EVT_ACT where row_id = '1-EVTACT';
select PLAN_TABLE_OUTPUT
from table(dbms_xplan.display('plan_table',null,'basic'));
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>';
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';
Some Oracle Date 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;
---------------------MORE TO COME---------------------------
CREATE SEQUENCE "<SCHEMA>"."<SEQ_NAME>" MINVALUE 1 MAXVALUE 100000000000 INCREMENT BY 1 START WITH 68309 NOCACHE NOORDER NOCYCLE ;
DBMS Gather Stats Script:
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 Database 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 Oracle Optimization Mode:
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = ‘OPTIMIZER_MODE’;
Putting Table in Cache:
ALTER TABLE S_LST_OF_VAL CACHE;
Creating a DB Link from Production to Dev Environment:
CREATE [PUBLIC] DATABASE LINK <link_name> CONNECT TO <user_name> IDENTIFIED BY <password> USING '<service_name>';
Eg. create public database link PROD_DEV CONNECT TO MARIF IDENTIFIED BY MARIF123 USING 'xyz.abc.com';
Check Locked Users:
select USERNAME,ACCOUNT_STATUS
from DBA_USERS
where account_status like '%LOCK%';
To Unlock Oracle account:
alter user SIEBEL account unlock;
To Change Password of Oracle Account:
alter user <username> identified by <new_password>;
GRANT on Table SYNTAX:
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON <table_name> TO <schema>;
or
GRANT ALL ON <table_name> TO <schema>;
CREATE TABLE FROM ANOTHER TABLE:
CREATE TABLE <TABLE_NAME1> AS SELECT * FROM <TABLE_NAME2>;
Compile DB Objects:
exec dbms_ddl.alter_compile ('<OBJECT>','OBJECT_NAME,'<SCHEMA>');
Eg. exec dbms_ddl.alter_compile ('PACKAGE','DUMMY_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 SQL Session:
alter system kill session 'sid,serial#';
Generating/Exporting Explain Plan for an SQL Statement:
explain plan for <SQL_Stmt>
Eg. select * from SIEBEL.S_EVT_ACT where row_id = '1-EVTACT';
select PLAN_TABLE_OUTPUT
from table(dbms_xplan.display('plan_table',null,'basic'));
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>';
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';
Some Oracle Date 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;
---------------------MORE TO COME---------------------------