SQL

Useful SQL Operations:


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);

Create Sequence Syntax:

CREATE SEQUENCE  "<SCHEMA>"."<SEQ_NAME>"  MINVALUE 1 MAXVALUE 100000000000 INCREMENT BY 1 START WITH 68309 NOCACHE  NOORDER  NOCYCLE ;

Identifying 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;

Checking 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>';

Example: create public database link PROD_DEV CONNECT TO XYZ IDENTIFIED BY XYZ1111 USING 'abc.xyz.com';

Check Locked Users:

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

Unlock Oracle account:

alter user SIEBEL account unlock;

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>');

Example: exec dbms_ddl.alter_compile ('PACKAGE','DUMMY_PACKAGE','SIEBEL');

Identifying Version of Oracle:

select * from v$version;

Unlocking Statistics for Gather Stats:

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

Locking Statistics for Gather Stats:

EXEC dbms_stats.lock_table_stats('SIEBEL','S_ORG_EXT');

Kill a SQL Session:

alter system kill session 'sid,serial#';

Generating/Exporting Explain Plan for an SQL Statement:

explain plan for <SQL_Stmt>

Example: select * from SIEBEL.S_EVT_ACT where row_id = '1-XYZPQR';

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';

Oracle Date functions:

Extracts month from date:
select to_char(to_date('19-12-2019', 'DD-MM-YYYY'), 'Month') from dual;

Extracts Year from date:
select to_char(to_date('19-12-2019', 'DD-MM-YYYY'), 'YYYY') from dual;

Extracts Quarter from date:
select to_char(to_date('19-12-2019','DD-MM-YYYY'), 'Q') from dual;

To remove a character from a string:

select regexp_replace('---qa-fo---do--a-l--vl---', '-') from dual;