Query for Database Schema Size

select OWNER,round (sum(bytes/1024/1024/1024)) "SIZE_IN_GB" from dba_segments
group by owner
order by SIZE_IN_GB desc;

Sql Query to locate the long running operations in database

select sid
      , username
      , opname
      ,target
      , decode(totalwork,0,0,null,0,sofar*100/totalwork) progress
      , totalwork
      , units
      , start_time
      , elapsed_seconds
      ,sql_id
from v$session_longops
order by sid, start_time

Displays List of Sequence in Oracle Database

select  substr(sequence_owner,1,10)             "OWNER",
        substr(sequence_name,1,25)              "SEQ NAME",
        substr(to_char(min_value),1,3)          "MIN",
 max_value    "MAX",
 increment_by    "INC",
      cache_size    "CACHE",
 last_number    "LAST NUM"
from  all_sequences
order by 1 asc, 2 asc;

Users executing which type of command

SELECT
 sid,serial#,osuser,machine,module,action, v.schemaname,
 DECODE(COMMAND
 ,0,'None'
 ,2,'Insert'
 ,3,'Select'
 ,6,'Update'
 ,7,'Delete'
 ,8,'Drop'
 ,26,'Lock Table'
 ,44,'Commit'
 ,45,'Rollback'
 ,47,'PL/SQL Execute'
 ,'Other') command
FROM V$SESSION v
order by command

Useful script to show primary / foreign key relationships of all tables and views in a given schema

SELECT
D.TABLE_NAME "Table name",
D.CONSTRAINT_NAME "Constraint name",
DECODE(D.CONSTRAINT_TYPE,
 'P','Primary Key',
 'R','Foreign Key',
 'C','Check/Not Null',
 'U','Unique',
 'V','View Cons') "Type",
D.SEARCH_CONDITION "Check Condition",
P.TABLE_NAME "Ref Table name",
P.CONSTRAINT_NAME "Ref by",
M.COLUMN_NAME "Ref col",
M.POSITION "Position",
P.OWNER "Ref owner"
FROM
 DBA_CONSTRAINTS D
LEFT JOIN
 DBA_CONSTRAINTS P
 ON (D.R_OWNER=P.OWNER AND
D.R_CONSTRAINT_NAME=P.CONSTRAINT_NAME)
LEFT JOIN
 DBA_CONS_COLUMNS M
 ON (D.CONSTRAINT_NAME=M.CONSTRAINT_NAME)
WHERE
 D.TABLE_NAME
 IN (
 SELECT TABLE_NAME FROM DBA_TABLES WHERE
OWNER=UPPER(:b1)
 UNION ALL
 SELECT VIEW_NAME FROM DBA_VIEWS WHERE OWNER=UPPER(:b1)
 )
ORDER BY 1,2,3

Index size is more than table size to rebuild

select 'alter index '||a.index_owner||'.'||a.index_name||' rebuild online;' from
(select i.OWNER "INDEX_OWNER",i.INDEX_NAME,i.table_name,s.bytes/1024/1024 "INDEX_SIZE"
from dba_indexes i,dba_segments s
where i.index_name=s.SEGMENT_NAME and i.owner=s.owner and s.bytes/1024/1024 > 20) a,
(select t.OWNER "TABLE_OWNER",t.table_name,s.bytes/1024/1024 "TABLE_SIZE"
from dba_tables t,dba_segments s where t.table_name=s.SEGMENT_NAME and t.owner=s.owner and s.bytes/1024/1024 > 20) b
where a.index_owner=b.table_owner and a.table_name=b.table_name and a.index_size > b.table_size;

Which Session Generating More Redo

SELECT s.sid, s.serial#, s.username,s.osuser, s.machine,s.module, s.program,i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid ORDER BY 5 desc;

Oracle Database Size

SELECT    ROUND(SUM(USED.BYTES) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
,    ROUND(SUM(USED.BYTES) / 1024 / 1024 / 1024 ) -
    ROUND(FREE.P / 1024 / 1024 / 1024) || ' GB' "Used space"
,    ROUND(FREE.P / 1024 / 1024 / 1024) || ' GB' "Free space"
FROM    (SELECT    BYTES
    FROM    V$DATAFILE
    UNION    ALL
    SELECT    BYTES
    FROM     V$TEMPFILE
    UNION     ALL
    SELECT     BYTES
    FROM     V$LOG) USED
,    (SELECT SUM(BYTES) AS P
    FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;

Session blocking

select (select osuser from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select osuser from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

Check the Session accessing objects

SELECT /* + RULE */ LS.OSUSER OS_USER_NAME, LS.USERNAME USER_NAME,
DECODE (LS.TYPE, 'RW', 'ROW WAIT ENQUEUE LOCK', 'TM', 'DML ENQUEUE LOCK', 'TX',
'TRANSACTION ENQUEUE LOCK', 'UL', 'USER SUPPLIED LOCK') LOCK_TYPE,
O.OBJECT_NAME OBJECT, DECODE (LS.LMODE, 1, NULL, 2, 'ROW SHARE', 3,
'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', NULL)
LOCK_MODE, O.OWNER, LS.SID, LS.SERIAL# SERIAL_NUM, LS.ID1, LS.ID2
FROM SYS.DBA_OBJECTS O, (SELECT S.OSUSER, S.USERNAME, L.TYPE,
L.LMODE, S.SID, S.SERIAL#, L.ID1, L.ID2 FROM V$SESSION S,
V$LOCK L WHERE S.SID = L.SID) LS WHERE O.OBJECT_ID = LS.ID1 AND O.OWNER
<> 'SYS' ORDER BY O.OWNER, O.OBJECT_NAME

Find UDUMP file through SQL

select p.value || '/' || i.instance_name || '_ora_' || spid || '.trc' as "trace_file_name"
from v$parameter p,
v$process pro,
v$session s,
(Select sid from v$mystat where rownum = 1) m,
v$instance i
where lower (p.name) = 'user_dump_dest'
and pro.addr = s.paddr
and m.sid = s.sid;

Redo Generated per day

SELECT trunc(first_time) DAY,
count(*) NB_SWITCHS,
trunc(count(*)*log_size/1024) TOTAL_SIZE_KB,
to_char(count(*)/24,'9999.9') AVG_SWITCHS_PER_HOUR
FROM v$loghist,
(select avg(bytes) log_size from v$log) GROUP BY trunc(first_time),log_size
order by 1 desc

Sql Query to know the Oracle Database Uptime

select 'Hostname      : ' || host_name "HOST NAME"
      ,'Instance Name : ' || instance_name "INSTANCE NAME"
      ,'Started At    : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS')  "STARTED TIME"
      ,'Uptime        : ' || floor(sysdate - startup_time) || ' days(s) ' ||
       trunc( 24*((sysdate-startup_time) -
       trunc(sysdate-startup_time))) || ' hour(s) ' ||
       mod(trunc(1440*((sysdate-startup_time) -
       trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
       mod(trunc(86400*((sysdate-startup_time) -
       trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from v$instance