SQL Scripts

Jobs Related
-- -----------------------------------------------------------------------------------
-- Description : Displays information about all scheduled jobs.
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

SELECT a.job "Job",
Substr(a.log_user,1,15) "Log User",
Substr(a.priv_user,1,15) "Priv User",
Substr(a.schema_user,1,15) "Schema User",
Substr(To_Char(a.last_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "Last Date",
Substr(To_Char(a.this_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "This Date",
Substr(To_Char(a.next_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "Next Date",
a.total_time "Total Time",
a.broken "Broken",
a.interval "Interval",
a.failures "Failures",
a.what "What",
a.nls_env "NLS Env",
a.misc_env "Misc Env"
FROM dba_jobs a;

SET PAGESIZE 14
SET VERIFY ON

-----------------------------------------------------------------------------------
-- Description : Displays information about all jobs currently running.
-----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

SELECT a.job "Job",
a.sid,
a.failures "Failures",
Substr(To_Char(a.last_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "Last Date",
Substr(To_Char(a.this_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "This Date"
FROM dba_jobs_running a;

SET PAGESIZE 14
SET VERIFY ON
-----------------------------------------------------------------------------------


Session Related

_____________________________________________________________________
Provides all the sid,osuser,serial#,machine name from which they have connected.
_____________________________________________________________________
SET LINES 130
SET PAGESIZE 30
COL FROM_MACHINE FORMAT A20
COL OSUSER FORMAT a10
COL PROCESS FORMAT a10
COL STATUS FORMAT a10
COL ORA_USER FORMAT a10
COL PROGRAM FORMAT a20
COL SID,SERIAL_NO FORMAT a20
select MACHINE "FROM_MACHINE" ,
OSUSER ,
USERNAME "ORA_USER",
PROCESS,
STATUS,
PROGRAM,
to_char(sid) || ',' || ltrim(to_char(serial#)) "SID,SERIAL_NO"
FROM v$SESSION ORDER BY OSUSER;

_____________________________________________________________________

_____________________________________________________________________
SHOWS ALL ACTIVE USERS
_____________________________________________________________________
SET LINES 130
SET PAGESIZE 30
COL FROM_MACHINE FORMAT A20
COL OSUSER FORMAT a10
COL PROCESS FORMAT a10
COL STATUS FORMAT a10
COL ORA_USER FORMAT a10
COL SID,SERIAL_NO FORMAT a20
select MACHINE "FROM_MACHINE" ,
OSUSER ,
USERNAME "ORA_USER",
PROCESS,
STATUS,
to_char(sid) || ',' || ltrim(to_char(serial#)) "SID,SERIAL_NO"
FROM v$SESSION WHERE STATUS='ACTIVE' ORDER BY OSUSER;


_____________________________________________________________________
SHOWS ALL IN ACTIVE USERS
_____________________________________________________________________
SET LINES 140
SET PAGESIZE 30
COL FROM_MACHINE FORMAT A20
COL OSUSER FORMAT a10
COL PROCESS FORMAT a10
COL STATUS FORMAT a10
COL ORA_USER FORMAT a10
COL SID,SERIAL_NO FORMAT a20
select MACHINE "FROM_MACHINE",
OSUSER ,
USERNAME "ORA_USER",
PROCESS,
STATUS,
to_char(sid) || ',' || ltrim(to_char(serial#)) "SID,SERIAL_NO"
FROM v$SESSION WHERE STATUS='INACTIVE' ORDER BY OSUSER;

_______________________________________________________________


SET LINES 140
SET PAGESIZE 30
COL FROM_MACHINE FORMAT A20
COL OSUSER FORMAT a10
COL PROCESS FORMAT a10
COL STATUS FORMAT a10
COL ORA_USER FORMAT a10
COL SID,SERIAL_NO FORMAT a20
COL LOCKWAIT FORMAT A15
select MACHINE "FROM_MACHINE",
OSUSER ,
USERNAME "ORA_USER",
PROCESS,
STATUS,
to_char(sid) || ',' || ltrim(to_char(serial#)) "SID,SERIAL_NO",
lockwait
FROM v$SESSION WHERE STATUS='INACTIVE' ORDER BY OSUSER;

_______________________________________________________________

_______________________________________________________________
non tested:

SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
AND s.status = 'ACTIVE'
ORDER BY s.username, s.osuser;

SET PAGESIZE 14
______________________________________________________





______________________________________________________
______________________________________________________
Displays session usage for licensing purposes.
______________________________________________________
SELECT * FROM v$license;
______________________________________________________


SELECT BLOCK FROM V$lock;

___________________________________________________________________________
set pagesize 85

col username format a10
col osuser format a15
col sid format 9999
col serial format 99999
col type format a2
col request format 9
col lmode format 9
col lmode_desc format a16
col type_desc format a30 wrap

SELECT /*+ FIRST_ROWS ORDERED */ username,
s.osuser osuser , s.sid sid , s.serial# serial, l.lmode lmode ,
decode(L.LMODE,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive','NONE') lmode_desc, l.type type ,
decode(l.type,
'BL','Buffer hash table instance lock',
'CF',' Control file schema global enqueue lock',
'CI','Cross-instance function invocation instance lock',
'CS','Control file schema global enqueue lock',
'CU','Cursor bind lock',
'DF','Data file instance lock',
'DL','Direct loader parallel index create',
'DM','Mount/startup db primary/secondary instance lock',
'DR','Distributed recovery process lock',
'DX','Distributed transaction entry lock',
'FI','SGA open-file information lock',
'FS','File set lock',
'HW','Space management operations on a specific segment lock',
'IN','Instance number lock',
'IR','Instance recovery serialization global enqueue lock',
'IS','Instance state lock',
'IV','Library cache invalidation instance lock',
'JQ','Job queue lock',
'KK','Thread kick lock',
'MB','Master buffer hash table instance lock',
'MM','Mount definition gloabal enqueue lock',
'MR','Media recovery lock',
'PF','Password file lock',
'PI','Parallel operation lock',
'PR','Process startup lock',
'PS','Parallel operation lock',
'RE','USE_ROW_ENQUEUE enforcement lock',
'RT','Redo thread global enqueue lock',
'RW','Row wait enqueue lock',
'SC','System commit number instance lock',
'SH','System commit number high water mark enqueue lock',
'SM','SMON lock',
'SN','Sequence number instance lock',
'SQ','Sequence number enqueue lock',
'SS','Sort segment lock',
'ST','Space transaction enqueue lock',
'SV','Sequence number value lock',
'TA','Generic enqueue lock',
'TD','DDL enqueue lock',
'TE','Extend-segment enqueue lock',
'TM','DML enqueue lock',
'TT','Temporary table enqueue lock',
'TX','Transaction enqueue lock',
'UL','User supplied lock',
'UN','User name lock',
'US','Undo segment DDL lock',
'WL','Being-written redo log instance lock',
'WS','Write-atomic-log-switch global enqueue lock') type_desc ,
request , block
FROM v$lock l, v$session s
WHERE s.sid = l.sid
AND l.type <> 'MR'
AND s.type <> 'BACKGROUND'
ORDER BY username
/
___________________________________________________________________________

test

SET LINES 130
SET PAGESIZE 30
COL FROM_MACHINE FORMAT A20
COL OSUSER FORMAT a10
COL PROCESS FORMAT a10
COL PROCESS FORMAT a10
COL USERNAME FORMAT a10
COL SID,SERIAL_NO FORMAT a20
select MACHINE "FROM_MACHINE" ,
OSUSER ,
PROCESS,
NVL(username, '(oracle)') AS username,
STATUS,
to_char(sid) || ',' || ltrim(to_char(serial#)) "SID,SERIAL_NO"
FROM v$SESSION ORDER BY OSUSER;



SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
ORDER BY s.username, s.osuser;

_________________________________________________________________


SELECT DISTINCT s.sql_address, s.sql_hash_value
FROM v$session s, v$open_cursor c
WHERE s.sql_address = c.address
AND s.sql_hash_value = c.hash_value
AND s.sid = 26

_________________________________________________________________

Extract DBMS_METADATA.GET_DDL , GET_DEPENDENT_DDL , GET_GRANTED_DDL etc


Below info extracted from http://www.orafaq.com/articles/archives/000063.htm

Get DDL for a single named object, using
DBMS_METADATA.GET_DDL (Object_type IN VARCHAR2, Name IN VARCHAR2, Schema IN VARCHAR2)

EX1:
set pagesize 0
set long 90000
SELECT DBMS_METADATA.GET_DDL('TABLE','') FROM dual;

If you wanted to get the DDL for all tables in a particular schema you need only join the function to the DBA_TABLES view:
Just keep in mind that the GET_DDL call takes in other valid objects types such as USER,TRIGGER, SEQUENCE, etc.

EX2:
SELECT DBMS_METADATA.GET_DDL('TABLE', table_name)
FROM DBA_TABLES WHERE OWNER = ''

____________________________________________________________________________
Get DDL for dependent objects against the base object supplied.
DBMS_METADATA.GET_DEPENDENT_DDL (Object_type IN VARCHAR2,Base_object_name IN VARCHAR2,Base_object_schema IN VARCHAR2)

With this function it is easy to determine such things as the grants on an object, the referential integrity between objects, and the indexes on an object.

Suppose you wanted to extract all source code for triggers where a particular table was used in? You no longer need to search through DBA_SOURCE you need only issue the following SQL. Please note that it is the object_type that is dependent on the named object . Extend this query a bit and you can see how you might be able to extract all source code for an object with a simple changing of TRIGGER to include PROCEDURE, PACKAGE, FUNCTION, and JAVA_SOURCE.

EX1:
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('TRIGGER','','') from dual;

EX2:
To find all indexes on a particular table and generate the DDL
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','','') from dual;

Sometimes when we migrate objects between schemas or databases we often forget about the constraints that need to be put in place.
EX3:
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','','') from dual

____________________________________________________________________________
Get DDL for grants issued agains an object.
DBMS_METADATA.GET_GRANTED_DDL (Object_type IN VARCHAR2,Grantee IN VARCHAR2,
Schema IN VARCHAR2)

To generate the DDL required around grants given to a particular user.

--If you wanted to generate the system grants given to a user.
select DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','') from dual;

--If you wanted to generate the roles granted to a user.
select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','') from dual;

--If you wanted all the object grants given to a user.
select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','') from dual;


____________________________________________________________________________

sql to show active work area size (RAM usage) (9i only)

select
to_number(decode(sid,65535,NULL,sid)) sid,
operation_type OPERATION,
trunc(worl_area_size/1024) WSIZE,
trunc(expected_size/1024) ESIZE,
trunc(actual_mem_used/1024) MEM,
trunc(max_mem_used/1024) "MAX MEM",
number_passes PASS
from
v$sql_waorkarea_active
oreder by 1,2;

_______________________________________________________________________________

Script which shows shared pool size, and recomended size, cmd to free shared pool

Below command is useful when you receive
ORA-04031: unable to allocate xxxx bytes of shared memory ("sharedpool" --------
#ALTER SYSTEM FLUSH SHARED_POOL;

set serverout on
set ver off
set timing off
prompt Claculate Shared Pool Size
prompt
DECLARE
l_uplift CONSTANT NUMBER := 0.3; /* i.e. 30% above calculated */
l_numusers NUMBER DEFAULT 0; /* Change this to a predicted number existing database */
l_avg_uga NUMBER;
l_max_uga NUMBER;
l_sum_sql_shmem NUMBER;
l_sum_obj_shmem NUMBER;
l_total_avg NUMBER(14);
l_total_max NUMBER(14);
pool_size varchar2(512);

BEGIN
dbms_output.enable(20000);
IF ( l_numusers = 0)
THEN
SELECT sessions_highwater
INTO l_numusers
FROM v$license;
dbms_output.put_line('Maximum concurrent users on this database = '||TO_CHAR(l_numusers));
ELSE
dbms_output.put_line('Calculating SGA for = '||TO_CHAR(l_numusers)||' concurrent users');
END IF;
dbms_output.new_line;

SELECT avg(value)*l_numusers
,max(value)*l_numusers
INTO l_avg_uga, l_max_uga
FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name = 'session uga memory max';

SELECT sum(sharable_mem)
INTO l_sum_sql_shmem
FROM v$sqlarea;

SELECT sum(sharable_mem)
INTO l_sum_obj_shmem
FROM v$db_object_cache;

select DECODE(SUBSTR(value,LENGTH(VALUE)),'M',SUBSTR(value,1,LENGTH(VALUE)-1)*1048576,value)
into pool_size
from v$parameter
where name='shared_pool_size';

l_total_avg := l_avg_uga + l_sum_sql_shmem + l_sum_obj_shmem;
l_total_max := l_max_uga + l_sum_sql_shmem + l_sum_obj_shmem;

dbms_output.put_line('Current Shared_pool Size :' || pool_size);
dbms_output.put_line('Recommended Shared_pool Size between :' || TO_CHAR(ROUND(l_total_avg + (l_total_avg * l_uplift), 0) ) ||' and '|| TO_CHAR(ROUND(l_total_max + (l_total_max * l_uplift), 0) ) ||' bytes');

end;
/


________________________________________________________________________________

Compile invalid objects script

COMPILE OBJECTS--IF YOU ARE LOGGED IN AS A USER OTHER THAN (SYS,SYSTEM)
------------------------------------------------------
set pages 0;
set lines 100;
select 'Alter '||decode(object_type,'PACKAGE BODY','PACKAGE',object_type)||' '||object_name||' compile;' from useR_objects
where status='INVALID';

COMPILE OBJECTS--IF YOU LOGGED IN AS SYS/SYSTEM USER
-----------------------------------
select 'alter '||decode(object_type,'PACKAGE BODY','PACKAGE','UNDEFINED','MATERIALIZED VIEW',object_type)
||' '||object_name||' compile;' from dba_objects where status='INVALID' and owner= '&TB';



___________________________________________________________________________________

constrains enable/disable script;

IF LOGGED IN AS USER OTHER THAN SYS OR SYSTEM.

ENABLE CONSTRAINTS
---------------------------------------------------------------
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints
where constraint_type in ('P','R') order by constraint_type desc;

DISABLE CONSTRAINTS
---------------------------------------------------------------
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints
where constraint_type in ('P','R') order by constraint_type desc;



IF LOGGED IN AS SYS OR SYSTEM.

ENABLE CONSTRAINTS
---------------------------------------------------------------
select ' alter table '||owner||'.'||table_name|| ' enable constraint '||CONSTRAINT_NAME|| ' ; '
from dba_constraints where owner='XXXXXXXXXXX' and constraint_type='R'
/

DISABLE CONSTRAINTS
---------------------------------------------------------------
select ' alter table '||owner||'.'||table_name|| ' disable constraint '||CONSTRAINT_NAME|| ' ; '
from dba_constraints where owner='XXXXXXXXXXX' and constraint_type='R'
/

_______________________________________________________________________________

Segments that cannot extend

PROMPT
PROMPT ************************************************
PROMPT *** SEGMENTS WITHIN 10 EXTENTS OF MAXEXTENTS ***
PROMPT ************************************************

column segment format a40 wrap
column segment_type format a10 heading "SEG TYPE" wrap
column max_extents format 9999999 heading "MAXEXTS"
column next_extent heading "NEXTEXT"
column initial_extent heading "INITEXT"
column tablespace_name format a15 wrap

set linesize 1000
set pagesize 32000
set trimspool on

select extents
, max_extents
, owner||'.'||segment_name segment
, segment_type
, bytes
, next_extent
, initial_extent
, tablespace_name
from sys.dba_segments
where max_extents - extents < 10
/

----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
PROMPT
PROMPT ***********************************
PROMPT *** SEGMENTS THAT CANNOT EXTEND ***
PROMPT ***********************************

select a.owner||'.'||a.segment_name segment
, a.segment_type
, b.tablespace_name
, decode(ext.extents,1,b.next_extent, a.bytes*(1+b.pct_increase/100)) nextext
, freesp.largest
from dba_extents a
, dba_segments b
, (select owner
, segment_name
, max(extent_id) extent_id
, count(*) extents
from dba_extents
group by owner
, segment_name) ext,
(select tablespace_name
, max(bytes) largest
from dba_free_space
group by tablespace_name) freesp
where a.owner = b.owner
and a.segment_name = b.segment_name
and a.owner = ext.owner
and a.segment_name = ext.segment_name
and a.extent_id = ext.extent_id
and b.tablespace_name = freesp.tablespace_name
and decode(ext.extents,1,b.next_extent, a.bytes*(1+b.pct_increase/100)) > freesp.largest
/

column segment clear
column segment_type clear
column max_extents clear
column next_extent clear
column initial_extent clear
column tablespace_name clear


____________________________________________________________________________