Blocking Session-2

set serveroutput on
BEGIN
 dbms_output.enable(1000000);
 for do_loop in (select session_id, a.object_id, xidsqn, oracle_username, b.owner owner,
 b.object_name object_name, b.object_type object_type
 FROM v$locked_object a, dba_objects b
 WHERE xidsqn != 0
 and b.object_id = a.object_id)
 loop
 dbms_output.put_line('.');
 dbms_output.put_line('Blocking Session : '||do_loop.session_id);
 dbms_output.put_line('Object (Owner/Name): '||do_loop.owner||'.'||do_loop.object_name);
 dbms_output.put_line('Object Type : '||do_loop.object_type);
 for next_loop in (select sid from v$lock
 where id2 = do_loop.xidsqn
 and sid != do_loop.session_id)
 LOOP
 dbms_output.put_line('Sessions being blocked : '||next_loop.sid);
 end loop;
 end loop;
 END;
 /

Output
=====
.
Blocking Session : 4929
Object (Owner/Name): SYS.OBJ$
Object Type : TABLE



PL/SQL procedure successfully completed.

Blocking Sessions

SELECT /*+ RULE */ 
 LPAD('--->',DECODE(A.request,0,0,5))||A.SID SID, B.serial#,
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') LOGON, 
b.status, b.SCHEMANAME SCHEMA, 
DECODE(o.owner || '.' || o.object_name, '.', NULL, o.owner || '.' || o.object_name) OBJECT, o.object_type,
 b.osuser, b.machine, b.module, b.program, 
 DECODE(BLOCK, 0, NULL, 'BLOKER' ) || DECODE(request, 0, NULL, '-->WAITER' ) BLOKER,
 DECODE (A.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 
'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', A.lmode) lmode,
DECODE(TRUNC(ctime/86400), 0, TO_CHAR(TO_DATE(ctime, 'SSSSS'), 'HH24:MI:SS'), 
TRUNC(ctime/86400) || ' Days + ' || TO_CHAR(TO_DATE(ctime - (TRUNC(ctime/86400))*86400 , 'SSSSS'), 'HH24:MI:SS')) TIME, 
A.TYPE, 'alter system kill session ' || '''' || a.SID || ', ' || b.serial# ||'''' || ' immediate;' kill_session, 
DECODE(object_type, NULL, NULL, 'Dbms_Rowid.rowid_create(1, ' || row_wait_obj# || ', ' 
|| row_wait_file# ||', ' || row_wait_block#||', ' || row_wait_row# ||')') row_id 
 FROM v$lock A, v$session b, dba_objects o
 WHERE A.SID = b.SID 
  AND (lmode = 0 OR BLOCK = 1) 
  AND o.object_id (+) = DECODE(b.ROW_WAIT_OBJ#, -1, NULL, b.ROW_WAIT_OBJ#)
 ORDER BY A.id1,A.request;

Calculate Export dump size

The following query calculates how much table data each schema takes on your database

SELECT owner, ROUND(SUM(size_mb)) MBytes FROM 
(
SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name 
FROM DBA_SEGMENTS 
WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION') 
--AND TABLESPACE_NAME LIKE 'COSTE%' 
--AND SEGMENT_NAME LIKE 'OE_ORDER_LINES_ALL%' 
--AND partition_name LIKE 'USAGE_FCT_NEW%'
--AND OWNER = 'TARGET_DW' 
--AND ROUND(bytes/(1024*1024),2) > 1000)
) 
GROUP BY owner 
ORDER BY MBytes DESC;

Create a super user like DBA but with no administration power

Summary 

Some time you have the requirement to create a user to the database that has access to all v$session, v$lock, dba_free_space and other dictionary views to monitor performance issues, sql tuning, and other DBA operations. 

But you don't want to have real power to change things like a DBA can do. 

The quick and easy word around is to grant the select_catalog_role role to the new user.

create user dba_monitor identified by dba_monitor;
grant connect, select_catalog_role to dba_monitor;

Now with the new user you can use tools like TOAD or Grid Control to monitor the database as a DBA but with no real power to change things

When did a user change his/her password?

SELECT NAME, ptime AS "LAST TIME CHANGED", ctime "CREATION TIME", ltime "LOCKED" 
FROM USER$ 
WHERE ptime IS NOT NULL 
ORDER BY ptime DESC;

Explain plan and statistics with SQL*Plus

SQL> set timing on
SQL> set autotrace traceonly explain
SQL> select * from dual;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> set autotrace traceonly statistics
SQL> /

Elapsed: 00:00:00.01

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          2  physical reads
          0  redo size
        522  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace traceonly explain

SQL> /

Elapsed: 00:00:00.00

Execution Plan

----------------------------------------------------------

Plan hash value: 272002086

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------

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;