SOME USEFUL ORACLE DATABASE QUERY

Checking When Database Startup
SELECT  TO_CHAR(STARTUP_TIME, 'DD-MM-YYYY HH24:MI:SS') "STARTUP TIME" FROM V$INSTANCE;

How To Check Database Size
SELECT (A.DATA_SIZE+B.TEMP_SIZE+C.REDO_SIZE)/1024/1024/1024 "TOTAL SIZE [GB]"
FROM ( SELECT SUM(BYTES) DATA_SIZE FROM DBA_DATA_FILES ) A,                      
     ( SELECT NVL(SUM(BYTES),0) TEMP_SIZE FROM DBA_TEMP_FILES ) B,                          
     ( SELECT SUM(BYTES) REDO_SIZE FROM SYS.V_$LOG ) C;

Show Five Largest Objects in Database
COL     OWNER FORMAT a10
COL     SEGMENT_NAME FORMAT a30
COL     SEGMENT_TYPE FORMAT a10
COL     MB FORMAT 999,999,999
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, MB FROM (SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, BYTES / 1024 / 1024 "MB" FROM DBA_SEGMENTS ORDER BY BYTES DESC) WHERE ROWNUM < 6;

Session status associated with the specified os process id
SELECT  S.USERNAME, S.SID, P.SPID, STATUS FROM V$SESSION S, V$PROCESS P WHERE S.PADDR = P.ADDR;

Lock or unlock a user
ALTER USER <username> ACCOUNT LOCK;
ALTER USER <username> ACCOUNT UNLOCK;

Show tablespace usage
COL     TABLESPACE_NAME FORMAT a16
SELECT A.TABLESPACE_NAME, A.STATUS,
ROUND(C.SIZE_GB/1024/1024,2) SIZE_MB,
ROUND((C.SIZE_GB-B.FREE_GB)/1024/1024,2) USAGE_MB,
ROUND(B.FREE_GB/1024/1024,2) FREE_MB,
ROUND(100*(1-(FREE_GB/SIZE_GB)),2) PCT_USAGE
FROM (SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES) A,
(SELECT tablespace_name,
SUM(BYTES) FREE_GB
FROM DBA_FREE_SPACE
GROUP BY tablespace_name)B,
(SELECT TABLESPACE_NAME, SUM(BYTES) SIZE_GB
FROM dba_data_files
GROUP BY tablespace_name) C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME;