Deleting all the user jobs

SET SERVEROUTPUT ON
declare
  ec NUMBER;
  et VARCHAR2(200);
    v_DOID  number;       
    DIMUSER number;
       
CURSOR jobs_cursor IS
Select job from User_Jobs where log_user = 'GDLT';
v_Found BOOLEAN;       
       
begin
  
DIMUSER := 8;
 dbms_output.put_line('Starting ..');

  --------------------------------------------------
 begin  
  dbms_output.put_line('deleting Jobs...');
  FOR Jobs IN jobs_cursor LOOP
      Dbms_Job.Remove(jobs.job);
      commit;
  end loop;
 end;
 ----------------------------------------------------
end;
/
commit; 

Using special characters in search condition when writing SQL queries

1- SELECT name FROM emp
 WHERE id LIKE '%/_%' ESCAPE '/';

2-  When using SQL*Plus, the DEFINE setting can be changed to allow &'s (ampersands) to be used in text:

 SET DEFINE ~
 SELECT 'Laurel & Hardy' FROM dual;

3-

  SET ESCAPE '\'
  SELECT '\&abc' FROM dual;
 
4-
  SET SCAN OFF
  SELECT '&ABC' x FROM dual;
 

Randomly selecting a value from a table

SELECT *
 FROM (SELECT empname, dept, salary, managerid
         FROM emp
     ORDER BY DBMS_RANDOM.VALUE)
WHERE rownum <= 4;

sample output

empname dept salary managerid
x                 IT    50000 1001
y                IT     25603 1002
z                 IT    25489   1001

Check number of objects and size of all schema users

set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999


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


sample output

Owner Object sizeMB
-------- ------- --------
A          900      700 
sys       29857   22623
B          950       9502

Show the ten largest objects in the database

col owner format a15
col segment_name format a30
col segment_type format a15
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 < 11

sample output

OWNER  SEGMENT_NAME  SEGMENT_TYPE  MB
----------- --------------------------- ------------------------  -----
A               emp                           TABLE                      90.025
A               emp_pk                     INDEX                      10.50
A               dept                            TABLE                     5.251 

Query to get cumulative count

For below query we require one more column that will hold cumulative values for salary :
select * from emp ;

NAME       SAL
----------------
lalit            5
sandeep   10
rahul         10
pravin      17

Query :


SELECT b.name,  b.sal, SUM (a.sal) AS cum_sal
    FROM emp a, emp b
   WHERE a.ROWID <= b.ROWID
GROUP BY b.name, b.ROWID, b.sal;

queryoutput

NAME  SAL CUM_SAL
------------------
lalit             5   5
rahul           10  25
pravin         17  42
sandeep       10  15

Querry to find locks

Query to find table locks

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#,
       DECODE (a.locked_mode,
               1, '1 = null',
               2, '2 = Row Share',
               3, '3 = Row Exclusive',
               4, '4 = Share',
               5, '5 Share Row Exclusive',
               6, '6 = Exclusive'
              ) lock_mode,
       b.status, b.osuser, b.machine
  FROM v$locked_object a, v$session b, dba_objects c
 WHERE b.SID = a.session_id AND a.object_id = c.object_id;

sample output

OWNER           OBJECT_NAME               OBJECT_TYP    SID     SERIAL# LOCK_MODE                      STATUS     OSUSER     MACHINE
--------------- ------------------------- ---------- ------ ----------- ------------------------------ ---------- ---------- ---------------------------------------------------------------------------------------
CNVAR1          xyz                                  TABLE          22        9945 3 = Row Exclusive                         INACTIVE        A            abc
CNVAR1          xyz                                  TABLE         344         375 3 = Row Exclusive                         ACTIVE           A            abc

Query to create kill session statements for locked tables

SELECT    'alter system kill session'
       || ''''
       || b.SID
       || ','
       || b.serial#
       || ''''
       || ';'
  FROM v$locked_object a, v$session b, dba_objects c
 WHERE b.SID = a.session_id AND a.object_id = c.object_id;

sample output
alter system kill session'4884,58701';
alter system kill session'4918,62906';

Querry to find the blocking sessions in oracle

SELECT    s1.username
       || '@'
       || s1.machine
       || ' ( SID='
       || s1.SID
       || ' )  is blocking '
       || s2.username
       || '@'
       || s2.machine
       || ' ( SID='
       || s2.SID
       || ' ) ' AS blocking_status
  FROM v$lock l1, v$session s1, v$lock l2, v$session s2
 WHERE s1.SID = l1.SID
   AND s2.SID = l2.SID
   AND l1.BLOCK = 1
   AND l2.request > 0
   AND l1.id1 = l2.id1
   AND l2.id2 = l2.id2;

sample output

BLOCKING_STATUS
--------------------------------------------------------------
a@xyz ( SID=22 )  is blocking a@xyz ( SID=344 )