How to find all child tables of a table in Oracle??

Sometimes it’s useful to find out all the dependent/child tables for a particular table. The SQL statement below will work for an Oracle database:

SELECT
  a.owner,a.table_name, a.constraint_name
FROM
  sys.all_constraints a,
  (SELECT owner,constraint_name from sys.all_constraints
    WHERE
     owner = 'OWNER' and
     table_name = 'TABLE_NAME' and
     constraint_type in ('P','U')
  ) b
WHERE
  a.constraint_type = 'R' and
  a.r_constraint_name = b.constraint_name and
  a.r_owner = b.owner

Where  OWNER is the owner/schema to which the table belongs andTABLE_NAME is the table to be reported on.

How to get PASSWORD changed time for an Oracle user??

SELECT name, ctime, ptime
FROM sys.user$
WHERE name = 'TEST_SUH';

How to find total number of Oracle schema objects and its size??

select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "size MB" from (select owner, count(*) obj_cnt from dba_objects group by owner) obj, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1;

Long Running Jobs/Queries

Long Running JOBS
Select sid,serial#,username,opname,to_char(start_time, 'hh24:mi:ss dd/mm/yy') started, totalwork,sofar,time_remaining remaining, message
from v$session_longops
where time_remaining = 0 order by time_remaining desc
Long Query Progress
SELECT a.sid, a.serial#, b.username , opname OPERATION, target OBJECT,
TRUNC(elapsed_seconds, 5) "ET (s)", TO_CHAR(start_time, 'HH24:MI:SS') start_time,
ROUND((sofar/totalwork)*100, 2) "COMPLETE (%)"
FROM v$session_longops a, v$session b
WHERE a.sid = b.sid AND b.username not IN ('SYS', 'SYSTEM') AND totalwork > 0
ORDER BY elapsed_seconds
Long running Queries, Total, So far and Remaining time 
SELECT SID, username, SERIAL#, opname, start_time,elapsed_seconds, TOTALWORK,SOFAR,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE, time_remaining
FROM   V$SESSION_LONGOPS
WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK order by 1

List of tables available in Database

Execute below query as sys or system, it will give all available tables in database owner wise
SELECT owner, table_name FROM dba_tables
If you don't have sys or system privileges use below queries to find available tables in schema
SELECT owner, table_name FROM all_tables
SELECT table_name FROM user_tables

How To Find Out What Privileges a User Currently Has?

Privileges granted to users are listed in two system views: DBA_SYS_PRIVS and USER_SYS_PRIVS.
We can find out what privileges a user currently has by running below query - as normal user

We can find out what privileges a user currently has by running below query - as SYS user

Database present status

SELECT SYSDATE-logon_time "Days", (SYSDATE-logon_time)*24 "Hours", status
from  sys.v_$session where  sid=1

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'),'HH24:MI:SS') UPTIME FROM v$session WHERE SID = 1;

Monthly database growth

select to_char(creation_time, 'YYYY Month') "Month",
sum(bytes)/1024/1024/1024 "Growth in GB"
from sys.v$datafile
where creation_time > SYSDATE-365*3 /* 3 years growth details */   
group by to_char(creation_time, 'YYYY Month') ORDER BY to_char(creation_time, 'YYYY Month')                      

TEMP tablespace usage

Temporary Segments in Database
SELECT A.tablespace_name tablespace, D.mb_total,SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A,(SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total;

Database Session that is using space in a sort segment

SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,S.program,SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,COUNT(*) sort_ops FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE    T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,S.program, 
TBS.block_size, T.tablespace ORDER BY sid_serial;
Sort Space Usage by Statement
SELECT   S.sid || ',' || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used,  T.tablespace,T.sqladdr address, Q.hash_value, Q.sql_text  FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.sqladdr = Q.address (+) AND T.tablespace = TBS.tablespace_name ORDER BY S.sid;

Who is using UNDO tablespace??

select s.sid, 
       s.username,
       sum(ss.value) / 1024 / 1024 as undo_size_mb
from  v$sesstat ss
  join v$session s on s.sid = ss.sid
  join v$statname stat on stat.statistic# = ss.statistic#
where stat.name = 'undo change vector size'
and s.type <> 'BACKGROUND'
and s.username IS NOT NULL
group by s.sid, s.username order by undo_size_mb desc

To find UNDO tablespace active transactions

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
       NVL(s.username, 'None') orauser,
       s.program,
       r.name undoseg,
       t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
  FROM sys.v_$rollname    r,
       sys.v_$session     s,
       sys.v_$transaction t,
       sys.v_$parameter   x
 WHERE s.taddr = t.addr
   AND r.usn   = t.xidusn(+)
   AND x.name  = 'db_block_size'

Extract tablespace DDL

set heading off;
set echo off;
Set pages 999;
set long 90000;
spool ddl_tbs_list.sql
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) || '/'
from dba_tablespaces tb;
spool off

KILL Session

SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'username'

ALTER SYSTEM KILL SESSION '14,30';

Find and KILL Orphand Jobs - DATAPUMP

SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2

drop table SYSTEM.SYS_EXPORT_SCHEMA_01;
drop table SYSTEM.SYS_EXPORT_TABLE_01;

Schema Size in GB

SELECT owner,
  Round(Sum(CASE WHEN SEGMENT_TYPE LIKE 'TABLE%' THEN BYTES ELSE 0 END)/ (1024*1024*1024),2) DATASPACE,
  Round(Sum(CASE WHEN SEGMENT_TYPE LIKE 'INDEX%' THEN BYTES ELSE 0 END)/ (1024*1024*1024),2) INDEXSPACE
FROM dba_SEGMENTS GROUP BY owner order by owner

Schema Data Space and Index Space in GB

SELECT owner,
  Round(Sum(CASE WHEN SEGMENT_TYPE LIKE 'TABLE%' THEN BYTES ELSE 0 END)/ (1024*1024*1024),2) DATASPACE,
  Round(Sum(CASE WHEN SEGMENT_TYPE LIKE 'INDEX%' THEN BYTES ELSE 0 END)/ (1024*1024*1024),2) INDEXSPACE
FROM dba_SEGMENTS GROUP BY owner order by owner

TEMP Usage

SELECT   S.sid || ',' || S.serial# sid_serial, S.username,
         T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         T.sqladdr address, Q.hash_value, Q.sql_text
FROM     v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE    T.session_addr = S.saddr
AND      T.sqladdr = Q.address (+)
AND      T.tablespace = TBS.tablespace_name
ORDER BY S.sid

Tablespace Usage Report

col tablespace_name format A22 heading "Tablespace"
col objects format 999999 heading "Objects"
col files format 9999
col allocated_mb format 9,999,990.000 heading "Allocated Size|(Mb)"
col used_mb format 9,999,990.000 heading "Used Size|(Mb)"
col ts_type format A6 heading "TS|type"
col max_size_mb format 9,999,990.000 heading "Max Size|(Mb)"
col max_free_mb format 9,999,990.000 heading "Max Free|(Mb)"
col max_free_pct format 999 heading "Max Free|%"

BREAK ON REPORT
COMPUTE SUM LABEL "Total SUM:" OF objects files allocated_mb used_mb max_size_mb MAX_FREE_MB ON REPORT
COMPUTE AVG LABEL "Average %:" OF FREE_PCT MAX_FREE_PCT ON REPORT

SELECT ts.tablespace_name, status,
DECODE(dt.contents,'PERMANENT',DECODE(dt.extent_management,'LOCAL',DECODE(dt.allocation_type,'UNIFORM','LM-UNI','LM-SYS'),'DM'),'TEMPORARY','TEMP',dt.contents) ts_type,
NVL(s.count,0) objects,
ts.files,
ts.allocated/1024/1024 allocated_mb,
ROUND((ts.allocated-nvl(ts.free_size,0))/1024/1024,3) used_mb,
ROUND(maxbytes/1024/1024,3) max_size_mb,
ROUND((maxbytes-(ts.allocated-nvl(ts.free_size,0)))/1024/1024,3) max_free_mb,
ROUND((maxbytes-(ts.allocated-nvl(ts.free_size,0)))*100/maxbytes,2) max_free_pct
FROM
(
SELECT dfs.tablespace_name,files,allocated,free_size,maxbytes
FROM
(SELECT fs.tablespace_name, sum(fs.bytes) free_size
FROM dba_free_space fs
GROUP BY fs.tablespace_name)
dfs,
(SELECT df.tablespace_name, count(*) files, sum(df.bytes) allocated,
sum(DECODE(df.maxbytes,0,df.bytes,df.maxbytes)) maxbytes, max(autoextensible) autoextensible
FROM dba_data_files df
WHERE df.status = 'AVAILABLE'
GROUP BY df.tablespace_name)
ddf
WHERE dfs.tablespace_name = ddf.tablespace_name
UNION
SELECT dtf.tablespace_name,files,allocated,free_size,maxbytes
FROM
(SELECT tf.tablespace_name, count(*) files, sum(tf.bytes) allocated,
sum(DECODE(tf.maxbytes,0,tf.bytes,tf.maxbytes)) maxbytes, max(autoextensible) autoextensible
FROM dba_temp_files tf
GROUP BY tf.tablespace_name)
dtf,
(SELECT th.tablespace_name, SUM (th.bytes_free) free_size
FROM v$temp_space_header th
GROUP BY tablespace_name)
tsh
WHERE dtf.tablespace_name = tsh.tablespace_name
) ts,
( SELECT s.tablespace_name, count(*) count
FROM dba_segments s
GROUP BY s.tablespace_name) s,
dba_tablespaces dt,
v$parameter p
WHERE p.name = 'db_block_size'
AND ts.tablespace_name = dt.tablespace_name
AND ts.tablespace_name = s.tablespace_name (+)
ORDER BY 1
/