How to rebuild a LOB Index in oracle

How to move a lob segment and a lob index to a differrent tablespace.
=====================================================================


How to rebuild a LOB Index in oracle
===================================
Normally you cannot move or rebuild a lob index to another tablespace like normal table. so you have to use little difference syntax. this is bellows .... 


Example:-
=================
drop table image ;




CREATE TABLE image (
dname VARCHAR2(30), 
sname VARCHAR2(30), 
fname VARCHAR2(30), 
hblob BLOB); 


ALTER TABLE TEST MOVE TABLESPACE SYSTEM; ----ITS OK 


Above command will move the table to new tablespace but will not move the 
CLOB segment and it will still be in original tablespace. This is because LOB 
data is stored outside of the table.


Check the tablespace of the CLOB column by issuing following sql.


SELECT index_name, tablespace_name
FROM user_indexes WHERE table_name = 'IMAGE';


Alter index SYS_IL0000098512C00004$$ rebuild tablespace SYSTEM; 
---ITS FAIL WITH BELLOWS ERROR 


ORA-02327: cannot create index on expression with datatype LOB


--------ITS OK 
ALTER TABLE image MOVE LOB (hblob) 
STORE AS (TABLESPACE system);


----see again 
SELECT index_name, tablespace_name
FROM user_indexes WHERE table_name = 'IMAGE';


SELECT * FROM user_lobs;


[Note : "small" LOBs stored inline (ie in the row itself) are not in a 
seperate LOB SEGMENT at all. That is called STORAGE IN ROW and is the 
default for LOBs of 4000bytes or less.]


+ Connect to the User from the user you want to move


---for generating a script 
select 'alter table '||table_name||' move tablespace YOUR_TS'||chr(10)||
'LOB ('||column_name||') store as '||segment_name||chr(10)||
'(tablespace YOUR_TS);'
from user_lobs 

Disabling and Enabling Oracle Jobs with DBMS_JOB

NOTE: only the user whom own a job can modify with the package DBMS_JOB, if you logon to the database as an administrator and want to disable jobs you must use the package DBMS_IJOB.

1. Logon to SQL*PLUS as sysdba

Mylinux> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.3.0 - Production on Tue May 25 10:19:38 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL>

2. To disable all jobs on the system use the following SQL statement to generate your SQL:
“select ‘exec dbms_ijob.broken(’||job||’,true);’ from dba_jobs;”

SQL> select ‘exec dbms_ijob.broken(’||job||’,true);’ from dba_jobs;

‘EXECDBMS_IJOB.BROKEN(’||JOB||’,TRUE);’
———————————————————————
exec dbms_ijob.broken(1,true);
exec dbms_ijob.broken(2,true);
exec dbms_ijob.broken(4,true);
exec dbms_ijob.broken(5,true);
exec dbms_ijob.broken(41,true);
exec dbms_ijob.broken(42,true);
exec dbms_ijob.broken(370,true);
exec dbms_ijob.broken(371,true);
exec dbms_ijob.broken(1605,true);
exec dbms_ijob.broken(2204,true);
exec dbms_ijob.broken(2224,true);
exec dbms_ijob.broken(2225,true);
exec dbms_ijob.broken(2245,true);

13 rows selected.

SQL>

3. Execute the SQL generated by the step above to disable all jobs.

exec dbms_ijob.broken(2224,true);
exec dbms_ijob.broken(2225,true);
exec dbms_ijob.broken(2245,true);
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.
…
…
…

4. To re-enable all jobs on the system use the following SQL statement to generate your SQL:
“select ‘exec dbms_ijob.broken(’||job||’,false);’ from dba_jobs;”

SQL> select ‘exec dbms_ijob.broken(’||job||’,false);’ from dba_jobs;

‘EXECDBMS_IJOB.BROKEN(’||JOB||’,FALSE);’
———————————————————————-
exec dbms_ijob.broken(1,false);
exec dbms_ijob.broken(2,false);
exec dbms_ijob.broken(4,false);
exec dbms_ijob.broken(5,false);
exec dbms_ijob.broken(41,false);
exec dbms_ijob.broken(42,false);
exec dbms_ijob.broken(370,false);
exec dbms_ijob.broken(371,false);
exec dbms_ijob.broken(1605,false);
exec dbms_ijob.broken(2204,false);
exec dbms_ijob.broken(2224,false);
exec dbms_ijob.broken(2225,false);
exec dbms_ijob.broken(2245,false);

5. Execute the SQL generated by the step above to enable all jobs.

exec dbms_ijob.broken(370,false);
exec dbms_ijob.broken(371,false);
exec dbms_ijob.broken(1605,false);
exec dbms_ijob.broken(2204,false);
SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

6. This completes the disablement and enablement of jobs with DBMS_JOB package.

Manual upgrade from 9i to 10g Release 2

This procedure describes the steps necessary to manually upgrade a database from 9.2.x to 10.2.x. It assumes that you have already installed 10g onto the server. Of course, it is essential that you take a backup of your database before attempting any upgrade.


1. Compile any invalid objects
@?/rdbms/admin/utlrp.sql


2. Create a sysaux tablespace
Having a sysaux tablespace is a requirement in 10g. So, if you haven't already got one, create one now.
create tablespace sysaux
datafile '<file_name>' size 512M
extent management local
segment space management auto
/


3. Run utlu102i.sql
This utility script checks that the database is ready to be upgraded to 10g. It also identifies any actions that need to be taken. The script is located in the 10g oracle home, so you will need to specify the full path to it.
@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i.sql
Review the output and make any necessary alterations. Make a note of how many invalid objects there are.


4. Shut the database down with either normal or immediate
shutdown immediate


5. Copy the spfile (or pfile) and the password file from the existing home to the 10g one.
cp ${ORACLE_HOME}/dbs/*${ORACLE_SID}* <new_home>/dbs/


6. Edit oratab
Alter /etc/oratab (or /var/opt/oracle/oratab) to point to the10g home. Once done, rerun oraenv to bring the alteration into effect.


7. Upgrade the database
sqlplus "/ as sysdba"


startup upgrade
This next bit is the upgrade itself. It takes roughly half an hour to complete. Spool the output to a file so that you can review it afterward.
@?/rdbms/admin/catupgrd.sql


8. Recompile any invalid objects
@?/rdbms/admin/utlrp.sql
Compare the number of invalid objects with the number noted in step 3. It should hopefully be the same or less.


9. Then check the status of the upgrade
@?/rdbms/admin/utlu102s.sql


10. Alter or remove initialization parameters
Temporarily creating a pfile is the easiest way.
create pfile from spfile;


shutdown immediate


vi ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
Alter/remove parameters identified in step 9. Set compatible to 10.2.0.0.0
startup


create spfile from pfile;


shutdown immediate


startup

Getting DDL

SQL> set long 2000
SQL> set lines 20000
SQL> set pages 10000
SQL> select dbms_metadata.get_ddl('<OBJECT_TYPE>','<OBJECT_NAME>') from dual;

Oracle Demantra Related Stuff

For any Demantra related information you want, please drop an mail to rakesh4dba@gmail.com.


Supported Version: Demantra 7.2.x to 7.3.x

Temp: Blocked Temp Space usage by session

/**************************************************************/
-- To see how much actual total tempspace is used
-- Even the sql ended, if the user session remained login, the tempspace will not be released
/**************************************************************/
SELECT 
F.TABLESPACE_NAME, 
G.TOTAL_TEMP_SPACE_USED, 
F.TOTAL_TEMP_MB, 
G.TOTAL_TEMP_SPACE_USED/F.TOTAL_TEMP_MB*100 PCT_TEMP_USED,
100 - (G.TOTAL_TEMP_SPACE_USED/F.TOTAL_TEMP_MB*100) PCT_TEMP_FREE
FROM 
(SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 TOTAL_TEMP_MB FROM   DBA_TEMP_FILES  GROUP BY TABLESPACE_NAME) F ,
(SELECT 
B.TABLESPACE_NAME, 
SUM(A.BLOCKS * B.BLOCK_SIZE/1024/1024 ) TOTAL_TEMP_SPACE_USED 
FROM 
V$SORT_USAGE A,
DBA_TABLESPACES B
WHERE
A.TABLESPACE = B.TABLESPACE_NAME
GROUP BY  B.TABLESPACE_NAME) G
WHERE F.TABLESPACE_NAME = G.TABLESPACE_NAME
/

Temp: Temp Space usage by session

/**************************************************************/
-- To see how much actual tempspace is used by each user session(if a user has multiple sessions, 
-- it will get total for each session)
/**************************************************************/


SELECT
G.USERNAME, 
g.sid,
g.serial#, 
F.TABLESPACE_NAME, 
G.TOTAL_TEMP_USED_MB, 
F.TOTAL_TEMP_MB, 
G.TOTAL_TEMP_USED_MB/F.TOTAL_TEMP_MB*100 PCT_TEMP_USED,
100 - (G.TOTAL_TEMP_USED_MB/F.TOTAL_TEMP_MB*100) PCT_TEMP_FREE
FROM 
(SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 TOTAL_TEMP_MB FROM   DBA_TEMP_FILES  GROUP BY TABLESPACE_NAME) F ,
(SELECT 
C.USERNAME ,
c.sid,
c.serial#, 
A.TABLESPACE TABLESPACE_NAME,
sum(A.BLOCKS * E.BLOCK_SIZE/1024/1024) TOTAL_TEMP_USED_MB
FROM
V$SORT_USAGE A,
V$SESSION C,
DBA_TABLESPACES E
WHERE C.SADDR=A.SESSION_ADDR
AND A.TABLESPACE = E.TABLESPACE_NAME
group by c.username, c.sid, c.serial#, tablespace
) G
WHERE F.TABLESPACE_NAME = G.TABLESPACE_NAME
/

Temp Space Usage Check

/**************************************************************/
-- To see who is using the tempspace
/**************************************************************/
SELECT DISTINCT
C.USERNAME "USER",
C.OSUSER ,
C.SID,
C.SERIAL#,
B.SPID "UNIX_PID",
C.MACHINE,
C.PROGRAM "PROGRAM",
A.BLOCKS * E.BLOCK_SIZE/1024/1024 MB_TEMP_USED  ,
A.TABLESPACE,
D.SQL_TEXT
FROM
V$SORT_USAGE A,
V$PROCESS B,
V$SESSION C,
V$SQLAREA D,
DBA_TABLESPACES E
WHERE C.SADDR=A.SESSION_ADDR
AND B.ADDR=C.PADDR
AND C.SQL_ADDRESS=D.ADDRESS(+)
AND A.TABLESPACE = E.TABLESPACE_NAME
/

Temp Space Check

select * from v$temp_space_header;


SELECT (USED_BLOCKS * 8192 ) "used" , (FREE_BLOCKS * 8192) "free" ,(MAX_USED_BLOCKS * 8192 ) "maxused"
FROM v$sort_segment;


SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)
FROM V$temp_space_header
GROUP BY tablespace_name;


SQL> set lines 152
col FreeSpaceGB format 999.999
col UsedSpaceGB format 999.999
col TotalSpaceGB format 999.999
col host_name format a30
col tablespace_name format a30
select tablespace_name,
(free_blocks*8)/1024/1024 FreeSpaceGB,
(used_blocks*8)/1024/1024 UsedSpaceGB, 
(total_blocks*8)/1024/1024 TotalSpaceGB,
i.instance_name,i.host_name
from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from 
dba_tablespaces where contents='TEMPORARY') and
i.inst_id=ss.inst_id;


col tablespace_name for a20
col FILE_NAME for a35
select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 from dba_temp_files;

Deleting the Database Objects Manually

-- |----------------------------------------------------------------------------|

-- | NOTE     : Make sure we connect to the specific schema of which we are     |

-- |            going to drop the objects |

-- |               environment before attempting to run it in production.       |

-- +----------------------------------------------------------------------------+

BEGIN

FOR V_DATA IN (  SELECT  V.VIEW_NAME

,       V.VIEW_TYPE

FROM    USER_VIEWS V ) LOOP

EXECUTE IMMEDIATE ('DROP VIEW '||V_DATA.VIEW_NAME);

END LOOP;

FOR I IN (  SELECT  OBJECT_NAME ,

OBJECT_TYPE

FROM    USER_OBJECTS

WHERE   OBJECT_TYPE = 'JAVA RESOURCE'

OR      OBJECT_TYPE= 'JAVA CLASS' ) LOOP

EXECUTE IMMEDIATE ('DROP '|| I.OBJECT_TYPE || ' "' || I.OBJECT_NAME || '"');

END LOOP;

FOR I IN (  SELECT  OBJECT_NAME

,       OBJECT_TYPE

FROM    USER_OBJECTS

WHERE   OBJECT_TYPE = 'PACKAGE BODY') LOOP

EXECUTE IMMEDIATE ('DROP '|| I.OBJECT_TYPE || ' "' || I.OBJECT_NAME || '"');

END LOOP;

FOR I IN (  SELECT  OBJECT_NAME

,       OBJECT_TYPE

FROM    USER_OBJECTS

WHERE   OBJECT_TYPE = 'PACKAGE') LOOP

EXECUTE IMMEDIATE ('DROP '|| I.OBJECT_TYPE || ' "' || I.OBJECT_NAME || '"');

END LOOP;

FOR I IN (  SELECT  OBJECT_NAME

,       OBJECT_TYPE

FROM    USER_OBJECTS

WHERE   OBJECT_TYPE = 'PROCEDURE') LOOP

EXECUTE IMMEDIATE ('DROP '|| I.OBJECT_TYPE || ' "' || I.OBJECT_NAME || '"');

END LOOP;

FOR I IN (  SELECT  OBJECT_NAME

,       OBJECT_TYPE

FROM    USER_OBJECTS

WHERE   OBJECT_TYPE = 'FUNCTION') LOOP

EXECUTE IMMEDIATE ('DROP '|| I.OBJECT_TYPE || ' "' || I.OBJECT_NAME || '"');

END LOOP;

FOR I IN (  SELECT  OBJECT_NAME

,       OBJECT_TYPE

FROM    USER_OBJECTS

WHERE   OBJECT_TYPE = 'SYNONYM') LOOP

EXECUTE IMMEDIATE ('DROP '|| I.OBJECT_TYPE || ' "' || I.OBJECT_NAME || '"');

END LOOP;

FOR I IN (  SELECT  OBJECT_NAME

,       OBJECT_TYPE

FROM    USER_OBJECTS

WHERE   OBJECT_TYPE = 'MATERIALIZED VIEW') LOOP

EXECUTE IMMEDIATE ('DROP '|| I.OBJECT_TYPE || ' "' || I.OBJECT_NAME || '"');

END LOOP;

FOR I IN (  SELECT  OBJECT_NAME

,       OBJECT_TYPE

FROM    USER_OBJECTS

WHERE   OBJECT_TYPE = 'SEQUENCE') LOOP

EXECUTE IMMEDIATE ('DROP '|| I.OBJECT_TYPE || ' "' || I.OBJECT_NAME || '"');

END LOOP;

FOR I IN (  SELECT  OBJECT_NAME

,       OBJECT_TYPE

FROM    USER_OBJECTS

WHERE   OBJECT_TYPE = 'TABLE') LOOP

EXECUTE IMMEDIATE ('DROP '|| I.OBJECT_TYPE || ' "' || I.OBJECT_NAME || '" CASCADE CONSTRAINTS');

END LOOP;

FOR I IN (  SELECT  object_name

FROM    user_objects

WHERE   object_type = 'TYPE'

ORDER BY object_id DESC) LOOP

EXECUTE IMMEDIATE ('DROP TYPE  "' || I.OBJECT_NAME ||'"');

dbms_output.put_line(I.OBJECT_NAME);

END LOOP;

END;

Tablespace % free check

set feed off
set linesize 200


column "tablespace_name" heading "Tablespace | Name" format a20
column "FileCount" heading "File | Count" format 999999
column "Size(MB)" heading "Size | (MB)" format 999,999,999.99
column "Free(MB)" heading "Free | (MB)" format 999,999,999.99
column "Used(MB)" heading "Used | (MB)" format 999,999,999.99
column "Max Ext(MB)" heading "Max Ext | (MB)" format 999,999,999
column "%Free" heading "% | Free" format 999.99
column "%Free Ext" heading "% | Free Ext" format 999.99
column "Graph" heading "Graph" format a11
column tablespace_name heading "Tablespace | Name" format a20




SELECT ts.tablespace_name, "File Count",
TRUNC("SIZE(MB)", 2) "Size(MB)",
TRUNC(fr."FREE(MB)", 2) "Free(MB)",
TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",
df."MAX_EXT" "Max Ext(MB)",
(fr."FREE(MB)" / df."SIZE(MB)") * 100 "% Free",
RPAD('*', TRUNC(CEIL((fr."FREE(MB)" / df."SIZE(MB)") * 100)/10), '*') "Graph"
FROM (SELECT tablespace_name,
SUM (bytes) / (1024 * 1024) "FREE(MB)"
FROM dba_free_space
GROUP BY tablespace_name) fr,
(SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "SIZE(MB)", COUNT(*)
"File Count", SUM(maxbytes) / (1024 * 1024) "MAX_EXT"
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name
FROM dba_tablespaces) ts
WHERE fr.tablespace_name = df.tablespace_name (+)
AND fr.tablespace_name = ts.tablespace_name (+)
ORDER BY "% Free" desc
/


prompt
prompt "The last line shows the Tablespace wich is growing too large..."
prompt


***************************************************************

rem
rem Name:     free_space.sql
rem
rem FUNCTION: Provide data on tablespace extent status
rem FUNCTION: this report uses the free_space2 view
rem FUNCTION: includes fsfi from DBA Handbook
rem
SET FEED OFF
SET FLUSH OFF
SET VERIFY OFF
set pages 58 LINES 132
COLUMN tablespace      HEADING Name             FORMAT a30
COLUMN files           HEADING '#Files'         FORMAT 9,999
COLUMN pieces          HEADING 'Frag'           FORMAT 9,999
COLUMN free_bytes      HEADING 'Free|Byte'      FORMAT 9,999,999,999
COLUMN free_blocks     HEADING 'Free|Blk'       FORMAT 999,999
COLUMN largest_bytes   HEADING 'Biggest|Bytes'  FORMAT 9,999,999,999
COLUMN largest_blks    HEADING 'Biggest|Blks'   FORMAT 999,999
COLUMN ratio           HEADING 'Percent'        FORMAT 999.999
COLUMN average_fsfi    HEADING 'Average|FSFI'   FORMAT 999.999
START title132 "FREE SPACE REPORT"
DEFINE 1 = report_output/&&db/free_spc
SPOOL &1
SELECT
       tablespace,
       COUNT(*) files,
       SUM(pieces) pieces,
       SUM(free_bytes) free_bytes,
       SUM(free_blocks) free_blocks,
       SUM(largest_bytes) largest_bytes,
       SUM(largest_blks) largest_blks,
       SUM(largest_bytes)/sum(free_bytes)*100 ratio,
       SUM(fsfi)/COUNT(*) average_fsfi
FROM
       free_space
GROUP BY
       tablespace;
SPOOL OFF
CLEAR COLUMNS
TTITLE OFF
SET FEED ON
SET FLUSH ON
SET VERIFY ON
SET PAGES 22 LINES 80
PAUSE Press Enter to continue


**************************************************************



spool tbs_freespace.txt
    undefine Tablespace
    set pages 200
    set lines 120
    select a.tablespace_name,
    SUM((BYTES/1024)/1024) "Used Space(MB)",
    SUM(total)  "allocated size(MB)",
    SUM(max) "maximum allowable (MB)",
    SUM(max-(BYTES/1024)/1024) "effective free(MB)",
    SUM(round(((max-(BYTES/1024)/1024)/max)*100,2)) "% FREE"
      from SM$TS_USED a,
      (
       select tablespace_name,sum((BYTES/1024)/1024) total,
       sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024) max
       from dba_data_files
       group by tablespace_name
      ) b where
      a.tablespace_name = b.tablespace_name
      group by
       a.tablespace_name
    order by 6 asc;
    spool off


************************************************************

COLUMN dummy noprint
COLUMN  pct_used format 999.9       heading "Used%"
COLUMN  name    format a25      heading "Tablespace Name"
COLUMN  Kbytes   format 999,999,999    heading "MBytes"
COLUMN  used    format 999,999,999   heading "MB Used"
COLUMN  free    format 999,999,999  heading "MB Free"
COLUMN  largest    format 999,999,999  heading "MB Largest"
BREAK   on report
COMPUTE sum LABEL "Total (in MB)" of kbytes on report
COMPUTE sum of free on report
COMPUTE sum of used on report


SELECT   NVL (b.tablespace_name, NVL (a.tablespace_name, 'UNKOWN')) name,
kbytes_alloc / 1024 kbytes,
(  kbytes_alloc - NVL (kbytes_free, 0)) / 1024 used,
NVL (kbytes_free, 0) / 1024 free,
(  ((  kbytes_alloc - NVL (kbytes_free, 0)) / kbytes_alloc)* 100) pct_used,
NVL (largest, 0) / 1024 largest,chunks "Total Chunks"
FROM     (SELECT   SUM (bytes) / 1024 kbytes_free,
MAX (bytes) / 1024 largest,
count(file_id) chunks,
tablespace_name
FROM     sys.dba_free_space
GROUP BY tablespace_name) a,
(SELECT   SUM (bytes) / 1024 kbytes_alloc,
tablespace_name
FROM     sys.dba_data_files
GROUP BY tablespace_name) b
WHERE    a.tablespace_name(+) = b.tablespace_name
union all
SELECT   NVL (b.tablespace_name, NVL (a.tablespace_name, 'UNKOWN')) name,
kbytes_alloc / 1024 kbytes,
(  kbytes_alloc - NVL (kbytes_free, 0)) / 1024 used,
NVL (kbytes_free, 0) / 1024 free,
(  ((  kbytes_alloc - NVL (kbytes_free, 0)) / kbytes_alloc)* 100) pct_used,
NVL (largest, 0) / 1024 largest,chunks "Total Chunks"
FROM     (SELECT   SUM (bytes) / 1024 kbytes_free,
MAX (bytes) / 1024 largest,
count(file_id) chunks,
tablespace_name
FROM     sys.dba_free_space
GROUP BY tablespace_name) a,
(SELECT   SUM (bytes) / 1024 kbytes_alloc,
tablespace_name
FROM     sys.dba_temp_files
GROUP BY tablespace_name) b
WHERE    a.tablespace_name(+) = b.tablespace_name;
ORDER BY &orderby DESC;

Report on all Oracle installed options.

-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle                                                          |
-- | FILE     : dba_options.sql                                                 |
-- | CLASS    : Database Administration                                         |
-- | PURPOSE  : Report on all Oracle installed options.                         |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- +----------------------------------------------------------------------------+


SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY   OFF


COLUMN parameter  FORMAT a45   HEADING 'Option Name'
COLUMN value      FORMAT a10   HEADING 'Installed?'


SELECT
    parameter
  , value
FROM
    v$option
ORDER BY
    parameter;

User for a query string

-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle                                                          |
-- | FILE     : dba_object_search.sql                                           |
-- | CLASS    : Database Administration                                         |
-- | PURPOSE  : Prompt the user for a query string and look for any object that |
-- |            contains that string.                                           |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- +----------------------------------------------------------------------------+


SET LINESIZE 135
SET PAGESIZE 9999


COLUMN owner           FORMAT A15    HEADING "Owner"
COLUMN object_name     FORMAT A35    HEADING "Object Name"
COLUMN object_type     FORMAT A18    HEADING "Object Type"
COLUMN created                       HEADING "Created"
COLUMN status                        HEADING "Status"




ACCEPT sch prompt 'Enter Search String (i.e. CONTROL) : '


SELECT
    owner
  , object_name
  , object_type
  , TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') created
  , LPAD(status, 7) status
FROM all_objects
WHERE object_name like UPPER('%&sch%')
ORDER BY owner, object_name, object_type
/

Summary of objects in the shared pool cache.

-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle                                                          |
-- | FILE     : dba_object_cache.sql                                            |
-- | CLASS    : Database Administration                                         |
-- | PURPOSE  : Summary of objects in the shared pool cache.                    |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- +----------------------------------------------------------------------------+


SET LINESIZE 155
SET PAGESIZE 9999


COLUMN inst_id                              HEAD "Inst.|ID"
COLUMN owner         FORMAT a10             HEAD "Owner"
COLUMN name          FORMAT a30             HEAD "Name"
COLUMN db_link       FORMAT a7              HEAD "DB Link"
COLUMN namespace     FORMAT a25             HEAD "Namespace"
COLUMN type          FORMAT a18             HEAD "Type"
COLUMN sharable_mem  FORMAT 99,999,999,999  HEAD "Sharable|Memory"
COLUMN loads                                HEAD "Loads"
COLUMN executions    FORMAT 99,999,999      HEAD "Executions"
COLUMN locks                                HEAD "Locks"
COLUMN pins                                 HEAD "Pins"
COLUMN kept          FORMAT a5              HEAD "Kept?"
COLUMN child_latch                          HEAD "Child Latch"
COLUMN hash_value                           HEAD "Hash Value"
COLUMN address                              HEAD "Address"
COLUMN paddress                             HEAD "Paddress"
COLUMN crsr_plan_hash_value                 HEAD "Cursor Plan|Hash Value"
COLUMN kglobt02                             HEAD "kglobt02"


BREAK ON report
COMPUTE sum OF sharable_mem ON report


SELECT
    inst_id                                 inst_id
  , kglnaown                                owner
  , kglnaobj                                name
--  , kglnadlk                                db_link
  , DECODE(   kglhdnsp
            , 0 , 'CURSOR'
            , 1 , 'TABLE/PROCEDURE'
            , 2 , 'BODY'
            , 3 , 'TRIGGER'
            , 4 , 'INDEX'
            , 5 , 'CLUSTER'
            , 6 , 'OBJECT'
            , 13, 'JAVA SOURCE'
            , 14, 'JAVA RESOURCE'
            , 15, 'REPLICATED TABLE OBJECT'
            , 16, 'REPLICATION INTERNAL PACKAGE'
            , 17, 'CONTEXT POLICY'
            , 18, 'PUB_SUB'
            , 19, 'SUMMARY'
            , 20, 'DIMENSION'
            , 21, 'APP CONTEXT'
            , 22, 'STORED OUTLINE'
            , 23, 'RULESET'
            , 24, 'RSRC PLAN'
            , 25, 'RSRC CONSUMER GROUP'
            , 26, 'PENDING RSRC PLAN'
            , 27, 'PENDING RSRC CONSUMER GROUP'
            , 28, 'SUBSCRIPTION'
            , 29, 'LOCATION'
            , 30, 'REMOTE OBJECT'
            , 31, 'SNAPSHOT METADATA'
            , 32, 'JAVA SHARED DATA'
            , 33, 'SECURITY PROFILE'
            , 'INVALID NAMESPACE'
    )                                       namespace
  , DECODE (   BITAND(kglobflg, 3)
             , 0, 'NOT LOADED'
             , 2, 'NON-EXISTENT'
             , 3, 'INVALID STATUS'
             , DECODE (   kglobtyp
                        , 0 , 'CURSOR'
                        , 1 , 'INDEX'
                        , 2 , 'TABLE'
                        , 3 , 'CLUSTER'
                        , 4 , 'VIEW'
                        , 5 , 'SYNONYM'
                        , 6 , 'SEQUENCE'
                        , 7 , 'PROCEDURE'
                        , 8 , 'FUNCTION'
                        , 9 , 'PACKAGE'
                        , 10, 'NON-EXISTENT'
                        , 11, 'PACKAGE BODY'
                        , 12, 'TRIGGER'
                        , 13, 'TYPE'
                        , 14, 'TYPE BODY'
                        , 15, 'OBJECT'
                        , 16, 'USER'
                        , 17, 'DBLINK'
                        , 18, 'PIPE'
                        , 19, 'TABLE PARTITION'
                        , 20, 'INDEX PARTITION'
                        , 21, 'LOB'
                        , 22, 'LIBRARY'
                        , 23, 'DIRECTORY'
                        , 24, 'QUEUE'
                        , 25, 'INDEX-ORGANIZED TABLE'
                        , 26, 'REPLICATION OBJECT GROUP'
                        , 27, 'REPLICATION PROPAGATOR'
                        , 28, 'JAVA SOURCE'
                        , 29, 'JAVA CLASS'
                        , 30, 'JAVA RESOURCE'
                        , 31, 'JAVA JAR'
                        , 32, 'INDEX TYPE'
                        , 33, 'OPERATOR'
                        , 34, 'TABLE SUBPARTITION'
                        , 35, 'INDEX SUBPARTITION'
                        , 36, 'REPLICATED TABLE OBJECT'
                        , 37, 'REPLICATION INTERNAL PACKAGE'
                        , 38, 'CONTEXT POLICY'
                        , 39, 'PUB_SUB'
                        , 40, 'LOB PARTITION'
                        , 41, 'LOB SUBPARTITION'
                        , 42, 'SUMMARY'
                        , 43, 'DIMENSION'
                        , 44, 'APP CONTEXT'
                        , 45, 'STORED OUTLINE'
                        , 46, 'RULESET'
                        , 47, 'RSRC PLAN'
                        , 48, 'RSRC CONSUMER GROUP'
                        , 49, 'PENDING RSRC PLAN'
                        , 50, 'PENDING RSRC CONSUMER GROUP'
                        , 51, 'SUBSCRIPTION'
                        , 52, 'LOCATION'
                        , 53, 'REMOTE OBJECT'
                        , 54, 'SNAPSHOT METADATA'
                        , 55, 'IFS'
                        , 56, 'JAVA SHARED DATA'
                        , 57, 'SECURITY PROFILE'
                        , 'INVALID TYPE'
               )
    )                                       type
  , kglobhs0 + 
    kglobhs1 + 
    kglobhs2 + 
    kglobhs3 + 
    kglobhs4 + 
    kglobhs5 + 
    kglobhs6                                sharable_mem
  , kglhdldc                                loads
  , kglhdexc                                executions
  , kglhdlkc                                locks
  , kglobpc0                                pins
  , DECODE(   kglhdkmk
            , 0 ,'NO'
            , 'YES'
    )                                       kept
--  , kglhdclt                                child_latch
--  , kglnahsh                                hash_value
--  , kglhdadr                                address
--  , kglhdpar                                paddress
--  , kglobt30                                crsr_plan_hash_value
--  , kglobt02                                kglobt02
FROM x$kglob
/ 

DBA Jobs Report

-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle                                                          |
-- | FILE     : dba_jobs.sql                                                    |
-- | CLASS    : Database Administration                                         |
-- | PURPOSE  : Provides summary report on all registered and scheduled jobs.   |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- +----------------------------------------------------------------------------+


SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY   OFF


COLUMN job        FORMAT 999   HEADING 'Job ID'
COLUMN username   FORMAT a15   HEADING 'User'
COLUMN what       FORMAT a30   HEADING 'What'
COLUMN next_date               HEADING 'Next Run Date'
COLUMN interval   FORMAT a30   HEADING 'Interval'
COLUMN last_date               HEADING 'Last Run Date'
COLUMN failures                HEADING 'Failures'
COLUMN broken     FORMAT a7    HEADING 'Broken?'


SELECT
    job
  , log_user username
  , what
  , TO_CHAR(next_date, 'DD-MON-YYYY HH24:MI:SS') next_date
  , interval
  , TO_CHAR(last_date, 'DD-MON-YYYY HH24:MI:SS') last_date
  , failures
  , broken
FROM
    dba_jobs;

Detailed Report of Invalid Obejcts

-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle                                                          |
-- | FILE     : dba_invalid_objects.sql                                         |
-- | CLASS    : Database Administration                                         |
-- | PURPOSE  : Provide a detailed report of all invalid objects in the         |
-- |            database.                                                       |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- +----------------------------------------------------------------------------+


SET LINESIZE  145
SET PAGESIZE  9999


clear columns
clear breaks
clear computes


column owner           format a25         heading 'Owner'
column object_name     format a30         heading 'Object Name'
column object_type     format a20         heading 'Object Type'
column status          format a10         heading 'Status'


break on owner skip 2 on report
compute count label ""               of object_name on owner
compute count label "Grand Total: "  of object_name on report


SELECT
    owner
  , object_name
  , object_type
  , status
FROM dba_objects
WHERE status <> 'VALID'
ORDER BY owner, object_name
/

HIGH WATER MARK Concept

HIGH WATER MARK
**********************
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle                                                          |
-- | FILE     : dba_highwater_mark.sql                                          |
-- | CLASS    : Database Administration                                         |
-- | PURPOSE  : Determine the highwater mark of a given table.                  |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- +----------------------------------------------------------------------------+


analyze table &owner.&table_name compute statistics
/


SELECT
 blocks
FROM
  dba_segments
WHERE
      owner = '&&owner'
  AND segment_name = '&&table_name'
/


SELECT
  empty_blocks
FROM
  dba_tables
WHERE
      owner = '&&owner'
  AND table_name = '&&table_name'
/


HIGHWATER_MARK = dba_segments.blocks - dba_tables.empty_blocks - 1




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


What is the High Water Mark?
----------------------------
All Oracle segments have an upper boundary containing the data within
the segment. This upper boundary is called the "high water mark" or HWM.
The high water mark is an indicator that marks blocks that are allocated 
to a segment, but are not used yet. This high water mark typically bumps 
up at 5 data blocks at a time. It is reset to "zero" (position to the start
of the segment) when a TRUNCATE command is issued.  So you can have empty 
blocks below the high water mark, but that means that the block has been 
used (and is probably empty caused by deletes). Oracle does not move the 
HWM, nor does it *shrink* tables, as a result of deletes.  This is also 
true of Oracle8.  Full table scans typically read up to the high water mark.


Data files do not have a high water mark; only segments do have them. 


How to determine the high water mark
------------------------------------
To view the high water mark of a particular table::


  ANALYZE TABLE <tablename> ESTIMATE/COMPUTE STATISTICS;


This will update the table statistics. After generating the statistics,
to determine the high water mark:


SELECT blocks, empty_blocks, num_rows
FROM   user_tables
WHERE table_name = <tablename>;


BLOCKS represents the number of blocks 'ever' used by the segment. 
EMPTY_BLOCKS represents only the number of blocks above the 'HIGH WATER MARK' 
.
Deleting records doesn't lower the high water mark. Therefore, deleting 
records doesn't raise the EMPTY_BLOCKS figure.


Let us take the following example based on table BIG_EMP1 which
has 28672 rows (Oracle 8.0.6):


SQL> connect system/manager
Connected.


SQL> SELECT segment_name,segment_type,blocks
  2> FROM dba_segments
  3> WHERE segment_name='BIG_EMP1';
SEGMENT_NAME                  SEGMENT_TYPE      BLOCKS      EXTENTS
----------------------------- ----------------- ----------  -------
BIG_EMP1                      TABLE                   1024      2
1 row selected.


SQL> connect scott/tiger


SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.


SQL> SELECT table_name,num_rows,blocks,empty_blocks
  2> FROM user_tables
  3> WHERE table_name='BIG_EMP1';
TABLE_NAME                     NUM_ROWS   BLOCKS     EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
BIG_EMP1                            28672        700        323
1 row selected.


Note: BLOCKS + EMPTY_BLOCKS (700+323=1023) is one block less than 
DBA_SEGMENTS.BLOCKS. This is because one block is reserved for the 
segment header. DBA_SEGMENTS.BLOCKS holds the total number of blocks 
allocated to the table. USER_TABLES.BLOCKS holds the total number of
blocks allocated for data.


SQL> SELECT COUNT (DISTINCT 
  2>          DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
  3>          DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
  4> FROM big_emp1;
Used      
----------
       700
1 row selected.


SQL> DELETE from big_emp1;
28672 rows processed.


SQL> commit;
Statement processed.


SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.


SQL> SELECT table_name,num_rows,blocks,empty_blocks
  2> FROM user_tables
  3> WHERE table_name='BIG_EMP1';
TABLE_NAME                     NUM_ROWS   BLOCKS     EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
BIG_EMP1                                0        700        323
1 row selected.


SQL> SELECT COUNT (DISTINCT 
  2>          DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
  3>          DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
  4> FROM big_emp1;
Used      
----------
         0
1 row selected.


SQL> TRUNCATE TABLE big_emp1;
Statement processed.


SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.


SQL> SELECT table_name,num_rows,blocks,empty_blocks
  2> FROM user_tables
  3> WHERE table_name='BIG_EMP1';
TABLE_NAME                     NUM_ROWS   BLOCKS     EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
BIG_EMP1                                0          0        511
1 row selected.


SQL> connect system/manager
Connected.


SQL> SELECT segment_name,segment_type,blocks
  2> FROM dba_segments
  3> WHERE segment_name='BIG_EMP1';
SEGMENT_NAME                  SEGMENT_TYPE      BLOCKS      EXTENTS
----------------------------- ----------------- ----------  -------
BIG_EMP1                      TABLE                   512      1
1 row selected.


NOTE:
----
  TRUNCATE has also deallocated the space from the deleted rows.
  To retain the space from the deleted rows allocated to the table use:


  SQL> TRUNCATE TABLE big_emp1 REUSE STORAGE

Report free space fragmentation.

-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle                                                          |
-- | FILE     : dba_free_space_frag.sql                                         |
-- | CLASS    : Database Administration                                         |
-- | PURPOSE  : Report free space fragmentation.                                |
-- |            THIS SCRIPT MUST BE RUN AS THE SYS USER!!!                      |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- +----------------------------------------------------------------------------+


connect / as sysdba


CREATE OR REPLACE VIEW free_space (
    tablespace
  , pieces
  , free_bytes
  , free_blocks
  , largest_bytes
  , largest_blks
  , fsfi
  , data_file
  , file_id
  , total_blocks
)
AS
SELECT
    a.tablespace_name
  , COUNT(*)
  , SUM(a.bytes)
  , SUM(a.blocks)
  , MAX(a.bytes)
  , MAX(a.blocks)
  , SQRT(MAX(a.blocks)/SUM(a.blocks))*(100/SQRT(SQRT(count(a.blocks))))
  , UPPER(b.file_name)
  , MAX(a.file_id)
  , MAX(b.blocks)
FROM
    sys.dba_free_space  a
  , sys.dba_data_files  b
WHERE
    a.file_id = b.file_id
GROUP BY
    a.tablespace_name,  b.file_name
/


CLEAR COLUMNS
SET LINESIZE  120
SET PAGESIZE  9999
SET FEEDBACK  off
SET VERIFY    off


BREAK ON tablespace SKIP 2 ON REPORT


COMPUTE SUM OF  total_blocks  ON tablespace
COMPUTE SUM OF  free_blocks   ON tablespace
COMPUTE SUM OF  free_blocks   ON report
COMPUTE SUM OF  total_blocks  ON report


COLUMN tablespace     HEADING "Tablespace"    FORMAT a15
COLUMN file_id        HEADING File#           FORMAT 99999
COLUMN pieces         HEADING Frag            FORMAT 9999
COLUMN free_bytes     HEADING 'Free Byte'
COLUMN free_blocks    HEADING 'Free Blk'      FORMAT 999,999,999
COLUMN largest_bytes  HEADING 'Biggest Bytes'
COLUMN largest_blks   HEADING 'Biggest Blks'  FORMAT 999,999,999
COLUMN data_file      HEADING 'File Name'     FORMAT a45
COLUMN total_blocks   HEADING 'Total Blocks'  FORMAT 999,999,999




SELECT
    tablespace
  , data_file
  , pieces
  , free_blocks
  , largest_blks
  , file_id
  , total_blocks
FROM
    free_space
/




DROP VIEW free_space
/

User sessions ordered by current PGA Size

-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle                                                          |
-- | FILE     : sess_users_by_memory.sql                                        |
-- | CLASS    : Session Management                                              |
-- | PURPOSE  : List all currently connected user sessions ordered by current   |
-- |            PGA size.                                                       |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- +----------------------------------------------------------------------------+


SET LINESIZE 145
SET PAGESIZE 9999


COLUMN sid                     FORMAT 99999          HEADING 'SID'
COLUMN serial_id               FORMAT 999999         HEADING 'Serial#'
COLUMN session_status          FORMAT a9             HEADING 'Status'          JUSTIFY right
COLUMN oracle_username         FORMAT a12            HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username             FORMAT a9             HEADING 'O/S User'        JUSTIFY right
COLUMN os_pid                  FORMAT 9999999        HEADING 'O/S PID'         JUSTIFY right
COLUMN session_program         FORMAT a18            HEADING 'Session Program' TRUNC
COLUMN session_machine         FORMAT a8             HEADING 'Machine'         JUSTIFY right TRUNC
COLUMN session_pga_memory      FORMAT 9,999,999,999  HEADING 'PGA Memory'
COLUMN session_pga_memory_max  FORMAT 9,999,999,999  HEADING 'PGA Memory Max'
COLUMN session_uga_memory      FORMAT 9,999,999,999  HEADING 'UGA Memory'
COLUMN session_uga_memory_max  FORMAT 9,999,999,999  HEADING 'UGA Memory MAX'


prompt 
prompt +----------------------------------------------------+
prompt | User Sessions Ordered by Current PGA Size          |
prompt +----------------------------------------------------+


SELECT
    s.sid                sid
  , s.serial#            serial_id
  , lpad(s.status,9)     session_status
  , lpad(s.username,12)  oracle_username
  , lpad(s.osuser,9)     os_username
  , lpad(p.spid,7)       os_pid
  , s.program            session_program
  , lpad(s.machine,8)    session_machine
  , sstat1.value         session_pga_memory
  , sstat2.value         session_pga_memory_max
  , sstat3.value         session_uga_memory
  , sstat4.value         session_uga_memory_max
FROM 
    v$process  p
  , v$session  s
  , v$sesstat  sstat1
  , v$sesstat  sstat2
  , v$sesstat  sstat3
  , v$sesstat  sstat4
  , v$statname statname1
  , v$statname statname2
  , v$statname statname3
  , v$statname statname4
WHERE
      p.addr (+)            = s.paddr
  AND s.sid                 = sstat1.sid
  AND s.sid                 = sstat2.sid
  AND s.sid                 = sstat3.sid
  AND s.sid                 = sstat4.sid
  AND statname1.statistic#  = sstat1.statistic#
  AND statname2.statistic#  = sstat2.statistic#
  AND statname3.statistic#  = sstat3.statistic#
  AND statname4.statistic#  = sstat4.statistic#
  AND statname1.name        = 'session pga memory'
  AND statname2.name        = 'session pga memory max'
  AND statname3.name        = 'session uga memory'
  AND statname4.name        = 'session uga memory max'
ORDER BY session_pga_memory DESC
/

User sessions ordered by Logical I/O

-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle                                                          |
-- | FILE     : sess_users_by_io.sql                                            |
-- | CLASS    : Session Management                                              |
-- | PURPOSE  : List all currently connected user sessions ordered by Logical   |
-- |            I/O.                                                            |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- +----------------------------------------------------------------------------+


SET LINESIZE 145
SET PAGESIZE 9999


COLUMN sid               FORMAT 99999            HEADING 'SID'
COLUMN serial_id         FORMAT 999999           HEADING 'Serial#'
COLUMN session_status    FORMAT a9               HEADING 'Status'          JUSTIFY right
COLUMN oracle_username   FORMAT a12              HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username       FORMAT a9               HEADING 'O/S User'        JUSTIFY right
COLUMN os_pid            FORMAT 9999999          HEADING 'O/S PID'         JUSTIFY right
COLUMN session_program   FORMAT a18              HEADING 'Session Program' TRUNC
COLUMN session_machine   FORMAT a8               HEADING 'Machine'         JUSTIFY right TRUNC
COLUMN logical_io        FORMAT 999,999,999,999  HEADING 'Logical I/O'
COLUMN physical_reads    FORMAT 999,999,999,999  HEADING 'Physical Reads'
COLUMN physical_writes   FORMAT 999,999,999,999  HEADING 'Physical Writes'


prompt 
prompt +----------------------------------------------------+
prompt | User Sessions Ordered by Logical I/O               |
prompt +----------------------------------------------------+


SELECT
    s.sid                sid
  , s.serial#            serial_id
  , lpad(s.status,9)     session_status
  , lpad(s.username,12)  oracle_username
  , lpad(s.osuser,9)     os_username
  , lpad(p.spid,7)       os_pid
  , s.program            session_program
  , lpad(s.machine,8)    session_machine
  , sstat1.value
    + sstat2.value         logical_io
  , sstat3.value         physical_reads
  , sstat4.value         physical_writes
FROM 
    v$process  p
  , v$session  s
  , v$sesstat  sstat1
  , v$sesstat  sstat2
  , v$sesstat  sstat3
  , v$sesstat  sstat4
  , v$statname statname1
  , v$statname statname2
  , v$statname statname3
  , v$statname statname4
WHERE
      p.addr (+)            = s.paddr
  AND s.sid                 = sstat1.sid
  AND s.sid                 = sstat2.sid
  AND s.sid                 = sstat3.sid
  AND s.sid                 = sstat4.sid
  AND statname1.statistic#  = sstat1.statistic#
  AND statname2.statistic#  = sstat2.statistic#
  AND statname3.statistic#  = sstat3.statistic#
  AND statname4.statistic#  = sstat4.statistic#
  AND statname1.name        = 'db block gets'
  AND statname2.name        = 'consistent gets'
  AND statname3.name        = 'physical reads'
  AND statname4.name        = 'physical writes'
ORDER BY logical_io DESC
/

User sessions ordered by the number of current open cursors

-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle                                                          |
-- | FILE     : sess_users_by_cursors.sql                                       |
-- | CLASS    : Session Management                                              |
-- | PURPOSE  : List all currently connected user sessions ordered by the       |
-- |            number of current open cursors within their session.            |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- +----------------------------------------------------------------------------+


SET LINESIZE 145
SET PAGESIZE 9999


COLUMN sid               FORMAT 99999            HEADING 'SID'
COLUMN serial_id         FORMAT 999999           HEADING 'Serial#'
COLUMN session_status    FORMAT a9               HEADING 'Status'          JUSTIFY right
COLUMN oracle_username   FORMAT a12              HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username       FORMAT a9               HEADING 'O/S User'        JUSTIFY right
COLUMN os_pid            FORMAT 9999999          HEADING 'O/S PID'         JUSTIFY right
COLUMN session_program   FORMAT a20              HEADING 'Session Program' TRUNC
COLUMN session_machine   FORMAT a14              HEADING 'Machine'         JUSTIFY right TRUNC
COLUMN open_cursors      FORMAT 99,999           HEADING 'Open Cursors'
COLUMN open_pct          FORMAT 999              HEADING 'Open %'


prompt 
prompt +----------------------------------------------------+
prompt | User Sessions Ordered by Number of Open Cursors    |
prompt +----------------------------------------------------+


SELECT
    s.sid                sid
  , s.serial#            serial_id
  , lpad(s.status,9)     session_status
  , lpad(s.username,12)  oracle_username
  , lpad(s.osuser,9)     os_username
  , lpad(p.spid,7)       os_pid
  , s.program            session_program
  , lpad(s.machine,14)   session_machine
  , sstat.value          open_cursors
  , ROUND((sstat.value/u.value)*100) open_pct
FROM 
    v$process  p
  , v$session  s
  , v$sesstat  sstat
  , v$statname statname
  , (select name, value
     from v$parameter) u
WHERE
      p.addr (+)          = s.paddr
  AND s.sid               = sstat.sid
  AND statname.statistic# = sstat.statistic#
  AND statname.name       = 'opened cursors current'
  AND u.name              = 'open_cursors'
ORDER BY open_cursors DESC
/

List all currently connected user sessions ordered by CPU time.

-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle                                                          |
-- | FILE     : sess_users_by_cpu.sql                                           |
-- | CLASS    : Session Management                                              |
-- | PURPOSE  : List all currently connected user sessions ordered by CPU time. |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- +----------------------------------------------------------------------------+


SET LINESIZE 145
SET PAGESIZE 9999


COLUMN sid               FORMAT 99999            HEADING 'SID'
COLUMN serial_id         FORMAT 999999           HEADING 'Serial#'
COLUMN session_status    FORMAT a9               HEADING 'Status'          JUSTIFY right
COLUMN oracle_username   FORMAT a12              HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username       FORMAT a9               HEADING 'O/S User'        JUSTIFY right
COLUMN os_pid            FORMAT 9999999          HEADING 'O/S PID'         JUSTIFY right
COLUMN session_program   FORMAT a20              HEADING 'Session Program' TRUNC
COLUMN session_machine   FORMAT a14              HEADING 'Machine'         JUSTIFY right TRUNC
COLUMN cpu_value         FORMAT 999,999,999,999  HEADING 'CPU'


prompt 
prompt +----------------------------------------------------+
prompt | User Sessions Ordered by CPU                       |
prompt +----------------------------------------------------+


SELECT
    s.sid                sid
  , s.serial#            serial_id
  , lpad(s.status,9)     session_status
  , lpad(s.username,12)  oracle_username
  , lpad(s.osuser,9)     os_username
  , lpad(p.spid,7)       os_pid
  , s.program            session_program
  , lpad(s.machine,14)   session_machine
  , sstat.value          cpu_value
FROM 
    v$process  p
  , v$session  s
  , v$sesstat  sstat
  , v$statname statname
WHERE
      p.addr (+)          = s.paddr
  AND s.sid               = sstat.sid
  AND statname.statistic# = sstat.statistic#
  AND statname.name       = 'CPU used by this session'
ORDER BY cpu_value DESC
/