Useful Oracle Queries

--Day in a week
select to_char(sysdate,'d') from dual

select to_char(sysdate,'DAY') from dual

--Day in a month
select to_char(sysdate,'fmdd') from dual

--Year
select to_char(sysdate,'yyyy') from dual

--First day of the current month
select trunc(sysdate,'mm') from dual

--First day of the current year
select trunc(sysdate,'yyyy') from dual


--first day of the previous month
select trunc(add_months(sysdate,-1),'mm') from dual

--Last day of the previous month
select trunc(sysdate,'mm')-1 from dual

Calendar in Oracle

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,"Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,TO_CHAR(dt+1,'iw') week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Thu",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Fri",
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sat"
FROM ( SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),60) - TRUNC(SYSDATE,'y'))
GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), TO_CHAR( dt+1, 'iw' ))
ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week);

Number of working days in the current month

Select SUM(decode(TRIM(day_name),'SATURDAY',0,'SUNDAY',0,1))
FROM
(SELECT (LEVEL) day_nr,(LEVEL + TRUNC(SYSDATE, 'MONTH') - 1) day_date,
TO_CHAR(LEVEL + TRUNC(SYSDATE, 'MONTH') - 1, 'DAY','NLS_DATE_LANGUAGE=ENGLISH') AS day_name
FROM DUAL
CONNECT BY LEVEL <= ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 1) - TRUNC(SYSDATE, 'MONTH')
ORDER BY day_nr)

First and Last date of the current month

select trunc(last_day(add_months(sysdate,-1))+1),trunc(last_day(sysdate)) from dual

Commonly used Oracle tables

ALL_ARGUMENTS
Arguments in object accessible to the user

ALL_CATALOG
All tables, views, synonyms, sequences accessible to the user

ALL_COL_COMMENTS
Comments on columns of accessible tables and views

ALL_CONSTRAINTS
Constraint definitions on accessible tables

ALL_CONS_COLUMNS
Information about accessible columns in constraint definitions

ALL_DB_LINKS
Database links accessible to the user

ALL_ERRORS
Current errors on stored objects that user is allowed to create

ALL_INDEXES
Descriptions of indexes on tables accessible to the user

ALL_IND_COLUMNS
COLUMNs comprising INDEXes on accessible TABLES

ALL_LOBS
Description of LOBs contained in tables accessible to the user

ALL_OBJECTS
Objects accessible to the user

ALL_OBJECT_TABLES
Description of all object tables accessible to the user

ALL_SEQUENCES
Description of SEQUENCEs accessible to the user

ALL_SNAPSHOTS
Snapshots the user can access

ALL_SOURCE
Current source on stored objects that user is allowed to create

ALL_SYNONYMS
All synonyms accessible to the user

ALL_TABLES
Description of relational tables accessible to the user

ALL_TAB_COLUMNS
Columns of user's tables, views and clusters

ALL_TAB_COL_STATISTICS
Columns of user's tables, views and clusters

ALL_TAB_COMMENTS
Comments on tables and views accessible to the user

ALL_TRIGGERS
Triggers accessible to the current user

ALL_TRIGGER_COLS
Column usage in user's triggers or in triggers on user's tables

ALL_TYPES
Description of types accessible to the user

ALL_UPDATABLE_COLUMNS
Description of all updatable columns

ALL_USERS
Information about all users of the database

ALL_VIEWS
Description of views accessible to the user

DATABASE_COMPATIBLE_LEVEL
Database compatible parameter set via init.ora

DBA_DB_LINKS
All database links in the database

DBA_ERRORS
Current errors on all stored objects in the database

DBA_OBJECTS
All objects in the database

DBA_ROLES
All Roles which exist in the database

DBA_ROLE_PRIVS
Roles granted to users and roles

DBA_SOURCE
Source of all stored objects in the database

DBA_TABLESPACES
Description of all tablespaces

DBA_TAB_PRIVS
All grants on objects in the database

DBA_TRIGGERS
All triggers in the database

DBA_TS_QUOTAS
Tablespace quotas for all users

DBA_USERS
Information about all users of the database

DBA_VIEWS
Description of all views in the database

DICTIONARY
Description of data dictionary tables and views

DICT_COLUMNS
Description of columns in data dictionary tables and views

GLOBAL_NAME
global database name

NLS_DATABASE_PARAMETERS
Permanent NLS parameters of the database

NLS_INSTANCE_PARAMETERS
NLS parameters of the instance

NLS_SESSION_PARAMETERS
NLS parameters of the user session

PRODUCT_COMPONENT_VERSION
version and status information for component products

ROLE_TAB_PRIVS
Table privileges granted to roles

SESSION_PRIVS
Privileges which the user currently has set

SESSION_ROLES
Roles which the user currently has enabled.

SYSTEM_PRIVILEGE_MAP
Description table for privilege type codes. Maps privilege type numbers to type names

TABLE_PRIVILEGES
Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee

TABLE_PRIVILEGE_MAP
Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names

Number of rows in a given PS table

SELECT TABLE_NAME,NUM_ROWS FROM ALL_TABLES WHERE SUBSTR(TABLE_NAME,4,LENGTH(TABLE_NAME)) IN (select A.RECNAME from psrecfielddb A , PSRECDEFN B where A.fieldname='PROFILE_CM_SEQ' AND B.RECTYPE=0 AND A.RECNAME=B.RECNAME)

Explain plan in oracle - Query execution

Execute this statement first and tag the entire query execution with the explain plan ID 'result1'::
explain plan SET STATEMENT_ID = 'result1' for select * from psworklist

Next step: Execute this query to see how the results are actually fetched by oracle::

SELECT cardinality "Rows",
lpad(' ',level-1)operation' '
options' 'object_name "Plan"
FROM PLAN_TABLE
CONNECT BY prior id = parent_id
AND prior statement_id = statement_id
START WITH id = 0
AND statement_id = 'result1'
ORDER BY id;