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;
Tuesday, September 28, 2010
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;
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
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
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
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
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
Monday, September 27, 2010
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 )
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 )
Checking explain plan of a query
set lines 180
DELETE PLAN_TABLE WHERE STATEMENT_ID = 'TEST';
commit ;
explain plan SET STATEMENT_ID='TEST' for <Your SQL Query> ;
SELECT SUBSTR (LPAD (' ', LEVEL - 1) || operation || ' (' || options
|| ')',
1,
30
) "Operation",
object_name "Object", BYTES, COST, CARDINALITY "Num Rows"
FROM plan_table
START WITH ID = 0
CONNECT BY PRIOR ID = parent_id;
scriptoutput
Operation Object BYTES COST Num Rows
------------------------------ -------------- ---------- ------- ----------
SELECT STATEMENT () 7844876 145 150863
SORT (GROUP BY) 7844876 145 150863
SORT (GROUP BY) 7844876 145 150863
TABLE ACCESS (FULL) xyz 7844876 57 150863
or use
explain plan SET STATEMENT_ID='TEST' for <Your SQL Query> ;
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.display());
scriptoutput
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 150K| 7661K| 179 | | | |
| 1 | SORT GROUP BY | | 150K| 7661K| 179 | 11,01 | P->S | QC (RAND) |
| 2 | SORT GROUP BY | | 150K| 7661K| 179 | 11,00 | P->P | HASH |
| 3 | TABLE ACCESS FULL | xyz | 150K| 7661K| 71 | 11,00 | PCWP | |
----------------------------------------------------------------------------------------------------
Note: cpu costing is off
DELETE PLAN_TABLE WHERE STATEMENT_ID = 'TEST';
commit ;
explain plan SET STATEMENT_ID='TEST' for <Your SQL Query> ;
SELECT SUBSTR (LPAD (' ', LEVEL - 1) || operation || ' (' || options
|| ')',
1,
30
) "Operation",
object_name "Object", BYTES, COST, CARDINALITY "Num Rows"
FROM plan_table
START WITH ID = 0
CONNECT BY PRIOR ID = parent_id;
scriptoutput
Operation Object BYTES COST Num Rows
------------------------------ -------------- ---------- ------- ----------
SELECT STATEMENT () 7844876 145 150863
SORT (GROUP BY) 7844876 145 150863
SORT (GROUP BY) 7844876 145 150863
TABLE ACCESS (FULL) xyz 7844876 57 150863
or use
explain plan SET STATEMENT_ID='TEST' for <Your SQL Query> ;
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.display());
scriptoutput
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 150K| 7661K| 179 | | | |
| 1 | SORT GROUP BY | | 150K| 7661K| 179 | 11,01 | P->S | QC (RAND) |
| 2 | SORT GROUP BY | | 150K| 7661K| 179 | 11,00 | P->P | HASH |
| 3 | TABLE ACCESS FULL | xyz | 150K| 7661K| 71 | 11,00 | PCWP | |
----------------------------------------------------------------------------------------------------
Note: cpu costing is off
Oracle queries to get size info
Query To get size of all the tables/indexes that are present in specific user
SELECT
segment_name,
segment_type,
SUM (BYTES)/ (1024 * 1024 * 1024) sizegb
FROM user_segments
GROUP BY segment_name, segment_type
ORDER BY sizegb DESC, segment_type, segment_name;
scriptoutput
SEGMENT_NAME SEGMENT_TYPE SIZEGB
--------------------------------------------------------------------------------------------------------
BL1_CHARGE_CYCLE TABLE 0.360015869140625
BL1_CHARGE TABLE PARTITION 0.34429931640625
BL1_TAX_ITEM TABLE PARTITION 0.150054931640625
Query To get available Tablespace sizes
SELECT b.tablespace_name, ROUND (tbs_size, 2) size_gb, a.free_space free_gb
FROM (SELECT tablespace_name,
ROUND (SUM (BYTES) / 1024 / 1024 / 1024, 2) AS free_space
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (BYTES) / 1024 / 1024
/ 1024 AS tbs_size
FROM dba_data_files
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM (BYTES) / 1024 / 1024 / 1024 tbs_size
FROM dba_temp_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name(+) = b.tablespace_name;
scriptoutput
TABLESPACE_NAME SIZE_GB FREE_GB
----------------------------------------------------------------------------
POOL_DATA 108.89 30.04
POOL_IX 29.3 16.19
SYSTEM 1.46 0.9
UNDOTBS 17.58 17.54
TEMP 14.65
SELECT
segment_name,
segment_type,
SUM (BYTES)/ (1024 * 1024 * 1024) sizegb
FROM user_segments
GROUP BY segment_name, segment_type
ORDER BY sizegb DESC, segment_type, segment_name;
scriptoutput
SEGMENT_NAME SEGMENT_TYPE SIZEGB
--------------------------------------------------------------------------------------------------------
BL1_CHARGE_CYCLE TABLE 0.360015869140625
BL1_CHARGE TABLE PARTITION 0.34429931640625
BL1_TAX_ITEM TABLE PARTITION 0.150054931640625
Query To get available Tablespace sizes
SELECT b.tablespace_name, ROUND (tbs_size, 2) size_gb, a.free_space free_gb
FROM (SELECT tablespace_name,
ROUND (SUM (BYTES) / 1024 / 1024 / 1024, 2) AS free_space
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (BYTES) / 1024 / 1024
/ 1024 AS tbs_size
FROM dba_data_files
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM (BYTES) / 1024 / 1024 / 1024 tbs_size
FROM dba_temp_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name(+) = b.tablespace_name;
scriptoutput
TABLESPACE_NAME SIZE_GB FREE_GB
----------------------------------------------------------------------------
POOL_DATA 108.89 30.04
POOL_IX 29.3 16.19
SYSTEM 1.46 0.9
UNDOTBS 17.58 17.54
TEMP 14.65
Identify Instance name
Query To Check on which oracle instance you are working
select * from global_name;
scriptoutput -
GLOBAL_NAME
------------
LSENBCNV (Name of the oracle instance)
Or use below query
SELECT SYS_CONTEXT ('USERENV', 'CURRENT_USER')
|| '@'
|| SYS_CONTEXT ('USERENV', 'DB_NAME') AS DATABASE
FROM DUAL;
scriptoutput -- CNVAR1@LSENBCNV
select * from global_name;
scriptoutput -
GLOBAL_NAME
------------
LSENBCNV (Name of the oracle instance)
Or use below query
SELECT SYS_CONTEXT ('USERENV', 'CURRENT_USER')
|| '@'
|| SYS_CONTEXT ('USERENV', 'DB_NAME') AS DATABASE
FROM DUAL;
scriptoutput -- CNVAR1@LSENBCNV
Subscribe to:
Comments (Atom)