Useful Oracle Queries

Date / Time-related queries :
1. Get the first day of the month
     SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month"
     FROM DUAL;

2. Get the last day of the month
      SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month"
      FROM DUAL;

3. Get the first day of the Year
     SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day"
     FROM DUAL;

4. Get the last day of the year
     SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day"
     FROM DUAL;

5. Get the number of days in current month
     SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days
     FROM DUAL;

6. Get the number of days left in current month
     SELECT SYSDATE,
       LAST_DAY (SYSDATE) "Last",
       LAST_DAY (SYSDATE) - SYSDATE "Days left"
      FROM DUAL;

7. Get the number of seconds passed since today (since 00:00 hr)
      SELECT (SYSDATE - TRUNC (SYSDATE)) * 24 * 60 * 60 num_of_sec_since_morning
      FROM DUAL;

8. Get the number of seconds left today (till 23:59:59 hr)
      SELECT (TRUNC (SYSDATE+1) - SYSDATE) * 24 * 60 * 60 num_of_sec_left
      FROM DUAL;
   
Database administration queries
1. Database version information
     SELECT * FROM v$version;

2. Database default information
     SELECT username,
       profile,
       default_tablespace,
       temporary_tablespace
     FROM dba_users;

3. Get the Oracle version
    SELECT VALUE
    FROM v$system_parameter
    WHERE name = 'compatible';

4. Find the Actual size of a Database
    SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB
    FROM dba_data_files;

5. Find the size of the SCHEMA/USER
    SELECT SUM (bytes / 1024 / 1024) "size"
    FROM dba_segments
    WHERE owner = '&owner';

Performance related queries:
1. CPU usage of the USER
     SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
     FROM v$session ss, v$sesstat se, v$statname sn
     WHERE     se.STATISTIC# = sn.STATISTIC#
     AND NAME LIKE '%CPU used by this session%'
     AND se.SID = ss.SID
     AND ss.status = 'ACTIVE'
     AND ss.username IS NOT NULL
    ORDER BY VALUE DESC;

2. Get the current session id, process id, client process id
      SELECT b.sid,
       b.serial#,
       a.spid processid,
       b.process clientpid
     FROM v$process a, v$session b
     WHERE a.addr = b.paddr AND b.audsid = USERENV ('sessionid');