Search This Blog

Total Pageviews

Monday 9 August 2010

Oracle invalid objects


Oracle Invalid objects .... 


object info 


define OBJECT_NAME='xxxxxxx'
set linesize 300 
col OWNER for a15
col OBJECT_NAME for a25

select  *   from   (select 1 con_id,OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where OBJECT_NAME='&OBJECT_NAME'
                    union 
                    select con_id, OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from cdb_objects where OBJECT_NAME='&&OBJECT_NAME'
                  )
;

======


@$ORACLE_HOME/rdbms/admin/utlrp.sql

utlprp.sql =

DECLARE
   threads pls_integer := &&1;
BEGIN
   utl_recomp.recomp_parallel(threads);
END;
/




declare
threads pls_iteger :=&&1;
begin 
utl_recomp.recomp_parallel(threds, flags =>utl_recomp.oracle_maintained);
end;


or 



define threds=4
exec utl_recomp.recomp_parallel(&threds, flags =>utl_recomp.oracle_maintained);  --- <<<<< Only for Oracle manage objects 

======================


set recsep off
column invalid_object format A30
column likely_reason format A35 word_wrapped
break on type on invalid_object
select owner || '.' || object_name invalid_object,'--- ' || object_type || ' ---' likely_reason from dba_objects
where status = 'INVALID'
union
select d.owner || '.' || d.name,'Non-existent referenced db link ' || d.referenced_link_name from dba_dependencies d
where not exists (select 'x'from dba_db_links
                  where owner in ('PUBLIC', d.owner) 
                  and db_link = d.referenced_link_name
                 )
and d.referenced_link_name is not null
and (d.owner, d.name, d.type) in (select owner, object_name, object_type from dba_objects where status = 'INVALID')
union
select d.owner || '.' || d.name,'Depends on invalid ' || d.referenced_type || ' '|| d.referenced_owner || '.' || d.referenced_name from dba_objects ro,dba_dependencies d
where ro.status = 'INVALID'
and ro.owner    = d.referenced_owner
and ro.object_name = d.referenced_name
and ro.object_type = d.referenced_type
and d.referenced_link_name is null
and (d.owner, d.name, d.type) in (select owner, object_name, object_type from  dba_objects where status = 'INVALID')
union
select d.owner || '.' || d.name,'Depends on newer ' || d.referenced_type || ' '|| d.referenced_owner || '.' || d.referenced_name from dba_objects ro,dba_dependencies d,dba_objects o
where nvl(ro.last_ddl_time, ro.created) > nvl(o.last_ddl_time, o.created)
and ro.owner = d.referenced_owner
and ro.object_name = d.referenced_name
and ro.object_type = d.referenced_type
and d.referenced_link_name is null
and d.owner  = o.owner
and d.name   = o.object_name
and d.type   = o.object_type
and o.status = 'INVALID'
union
select d.owner || '.' || d.name,'Depends on ' || d.referenced_type || ' '|| d.referenced_owner || '.' || d.referenced_name|| decode(d.referenced_link_name,NULL, '','@' || d.referenced_link_name) from dba_dependencies d
where d.referenced_owner != 'PUBLIC' -- Public synonyms generate noise
and d.referenced_type = 'NON-EXISTENT'
and (d.owner, d.name, d.type) in (select owner, object_name, object_type from dba_objects where status = 'INVALID')
union
select d.owner || '.' || d.name invalid_object,'No privilege on referenced ' || d.referenced_type || ' '|| d.referenced_owner || '.' || d.referenced_name from dba_objects ro,dba_dependencies d 
where not exists (select 'x' from dba_tab_privs p
                  where p.owner = d.referenced_owner
                  and p.table_name = d.referenced_name
                  and p.grantee in ('PUBLIC', d.owner)
                  )
and ro.status = 'VALID'
and ro.owner  = d.referenced_owner
and ro.object_name = d.referenced_name
and d.referenced_link_name is not null
and (d.owner, d.name, d.type) in (select owner, object_name, object_type from dba_objects where status = 'INVALID')
union
select o.owner || '.' || o.object_name,e.text from dba_errors e,dba_objects o
where e.text like 'PLS-%'
and e.owner  = o.owner
and e.name   = o.object_name
and e.type   = o.object_type
and o.status = 'INVALID';


===============
Components valid / invalid 

set lines 90
col version  for a12
col comp_id  for a8
col comp_name  for a35
col status for  a12
col schema like version
select comp_id,schema,status,version,comp_name from dba_registry 
order by 1;

============

set lines 120
col status   for a9
col object_type  for a20;
col owner.object for a50
select status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
from dba_objects
where 0=0 
and status != 'VALID' 
and object_name not like 'BIN$%' 
order by 4,2;

=====
set serveroutput on
BEGIN
   FOR r
   IN (  SELECT      'alter '
                  || REPLACE (object_type, 'BODY', '')
                  || ' '
                  || owner
                  || '.'
                  || object_name
                  || ' compile '
                  || DECODE (object_type, 'PACKAGE BODY', 'BODY')
                     stmt
           FROM   dba_objects
          WHERE   status = 'INVALID'
       ORDER BY   owner,
               DECODE (object_type,
                          'SYNONYM', 1,
                          'VIEW', 2,
                          'FUNCTION', 3,
                          'PROCEDURE', 4,
                          'PACKAGE', 7,
                          'PACKAGE BODY', 8,
                          9))
   LOOP
      BEGIN
        -- EXECUTE IMMEDIATE (r.stmt);
dbms_output.put_line(r.stmt||' ;');
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;
   END LOOP;
END;
/


===


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 count format 999,999 heading 'Count'
break on owner skip 2 on report
compute sum label "Count: " of count on owner
compute sum label "Grand Total: " of count on report
SELECT
owner
, object_type
, count(*) Count
FROM dba_objects
WHERE status <> 'VALID'
GROUP BY owner, object_type
/


=====


set pagesize 1000  LINESIZE 300
col COMP_ID format A9
col COMP_NAME format A40
col STATUS format A15
col VERSION format A12
col owner format a30
col object_name format a30
col object_type format a30
col COMMENTS for a60
col ACTION_TIME for a30
col VERSION for a25
--SPOOL DB_DD_CHECK.TXT
prompt /* Database identification */
select NAME, PLATFORM_ID, DATABASE_ROLE from v$database;
select * from V$version where banner like 'Oracle Database%';
select INSTANCE_NAME, to_char(STARTUP_TIME,'DD/MM/YYYY HH24:MI:SS') "STARTUP_TIME" from v$instance;

prompt  /* Database Component Registry status */

select comp_id, comp_name, status, version from dba_registry;

prompt  /* Level of patch in the database */
select * from dba_registry_history;

prompt /* INVALID objects count, by type -in detail */

select count(*) "INVALID Objects Count" from dba_objects where status !='VALID';
select owner, object_type, count(*) from dba_objects where status !='VALID' group by owner, object_type order by owner, object_type;
select owner, object_type, object_name, status from dba_objects where status !='VALID' order by owner, object_type, object_name;


Oracle Number of Objects in Tablespaces



Oracle  Users and Number of Objects in Tablespaces



set pagesize 60  echo off
column tablespace_name         format a20
column owner format a20
column objects format a20
break on owner on tablespace_name 
spool users_ts
select substr(owner,1,20) owner,substr(tablespace_name,1,32) tablespace_name,count(*)||decode(count(*),1,' table',' tables') objects
from sys.dba_tables
group by substr(owner,1,20),substr(tablespace_name,1,32) 
union
select substr(owner,1,20) owner,substr(tablespace_name,1,32) tablespace_name,count(*)||decode(count(*),1,' index',' indexes') objects
from sys.dba_indexes
group by substr(owner,1,20),substr(tablespace_name,1,32) 
/

spool off


OWNER                TABLESPACE_NAME      OBJECTS
-------------------- -------------------- --------------------
ANUJ                 USERS                2 tables
APEX_040200          SYSAUX               1518 indexes
                                          450 tables
                                          2 tables

AUDSYS                                    1 table
                                          4 indexes

Oracle Instance up time

select
trunc(sysdate - startup_time) days,
trunc(mod(24 * (sysdate - startup_time), 24)) hours,
round(mod(60 * 24 * (sysdate - startup_time), 60)) minutes
from
sys.v_$instance
/

Oracle Col name search

col structure for a10
col "Data type" for a12

accept x_owner prompt "[= allowed ] Owner : ";
accept x_column prompt "[= allowed ] Column Name : ";

select owner,
table_name "Table name",
column_name "Column name",
data_type "Data type",
decode( upper( data_type ), 'NUMBER', to_char( data_precision ) || ',' || to_char( data_scale ),
'DATE', ' ',
'LONG', ' ',
to_char( data_length ) ) "Structure",
nullable "Nullable"
from dba_tab_columns
where ( ( substr( upper( nvl( '&&x_owner', 'x' ) ), 1, 1 ) <> '=' and owner like upper( '%&&x_owner%' ) )
or
( substr( upper( '&&x_owner' ), 1, 1 ) = '=' and owner = upper( substr( '&&x_owner', 2 ) ) )
)
and ( ( substr( upper( nvl( '&&x_column', 'x' ) ), 1, 1 ) <> '=' and column_name like upper( '%&&x_column%' ) )
or
( substr( upper( '&&x_column' ), 1, 1 ) = '=' and column_name = upper( substr( '&&x_column', 2 ) ) )
)
order by owner, table_name, column_name
/

Oracle user details

accept xs_username prompt '[= allowed ] User : ';

declare
ls_exact varchar2(1) := 'N';
ls_username varchar2( 30 ) := upper( '&xs_username' );

cursor csr_user_dets is
select username, default_tablespace, temporary_tablespace, created, profile
from dba_users
where ( ( ls_exact <> 'Y'
and username like upper( '%&xs_username%' ) )
or
( ls_exact = 'Y'
and username = ls_username )
)
and username not in ( 'SYS', 'SYSTEM' )
order by username;

cursor csr_sys_privs( xs_username in varchar2 ) is
select *
from dba_sys_privs
where grantee = xs_username
order by privilege;

cursor c_dba_role_privs is
select *
from dba_role_privs
where ( ( ls_exact <> 'Y'
and grantee like upper( '%&xs_username%' ) )
or
( ls_exact = 'Y'
and grantee = ls_username )
)
order by grantee;

cursor c_ts_quotas is
select *
from dba_ts_quotas
where ( ( ls_exact <> 'Y'
and username like upper( '%&xs_username%' ) )
or
( ls_exact = 'Y'
and username = ls_username )
)
order by username;

i_loop_counter integer;
s_sql_text varchar2( 200 );

begin

if substr( '&xs_username', 1, 1) = '=' then
ls_exact := 'Y';
ls_username := substr( upper( '&xs_username' ), 2 );
else
ls_exact := 'N';
ls_username := upper( '&xs_username' );
end if;
dbms_output.put_line( ls_exact || ' ' || ls_username );

if ls_exact = 'Y' then
dbms_output.put_line( 'User Details : Search for exact match on ' || '''' || ls_username || '''' || chr(10) );
else
dbms_output.put_line( 'User Details : Search for all like ' || '''' || ls_username || '''' || chr(10) );
end if;

dbms_output.put_line( rpad( 'User Name', 20 ) || ' ' ||
rpad( 'Default T Space', 25 ) || ' ' ||
rpad( 'Temp T Space', 25 ) || ' ' ||
'Created ' || ' ' ||
rpad( 'Profile', 25 ) );
dbms_output.put_line( rpad( '---------', 20 ) || ' ' ||
rpad( '---------------', 25 ) || ' ' ||
rpad( '------------', 25 ) || ' ' ||
'------- ' || ' ' ||
rpad( '-------', 25 ) );

for i_csr_user_dets in csr_user_dets loop
dbms_output.put_line( rpad( i_csr_user_dets.username, 20 ) || ' ' ||
rpad( i_csr_user_dets.default_tablespace, 25 ) || ' ' ||
rpad( i_csr_user_dets.temporary_tablespace, 25 ) || ' ' ||
to_char( i_csr_user_dets.created, 'dd/mm/yyyy hh24:mi' ) || ' ' ||
rpad( i_csr_user_dets.profile, 25 ) );
end loop;

dbms_output.put_line( chr(9) );
dbms_output.put_line( 'System privileges' );
for i_csr_user_dets in csr_user_dets loop
i_loop_counter := 0;
for i_csr_sys_privs in csr_sys_privs( i_csr_user_dets.username ) loop
if i_loop_counter = 0 then
s_sql_text := rpad( i_csr_user_dets.username, 14 ) || chr(9);
else
s_sql_text := chr(9) || chr(9);
end if;
if i_csr_sys_privs.admin_option = 'YES' then
s_sql_text := s_sql_text || i_csr_sys_privs.privilege || ' with admin';
else
s_sql_text := s_sql_text || i_csr_sys_privs.privilege || ' (no admin)';
end if;
dbms_output.put_line( s_sql_text );
i_loop_counter := i_loop_counter + 1 ;
end loop;
if i_loop_counter > 0 then
dbms_output.put_line( chr(9) );
end if;
end loop;

dbms_output.put_line( chr(9) );
dbms_output.put_line( 'Roles' );

dbms_output.put_line( rpad( 'Grantee', 30 ) || ' ' ||
rpad( 'Role', 30 ) || ' ' ||
rpad( 'Admin', 4 ) || ' ' ||
rpad( 'Default', 4 ) );

dbms_output.put_line( rpad( '-------', 30 ) || ' ' ||
rpad( '----', 30 ) || ' ' ||
rpad( '-----', 4 ) || ' ' ||
rpad( '-------', 4 ) );

for i_c_dba_role_privs in c_dba_role_privs loop
dbms_output.put_line( rpad( i_c_dba_role_privs.grantee, 30 ) || ' ' ||
rpad( i_c_dba_role_privs.granted_role, 30 ) || ' ' ||
rpad( i_c_dba_role_privs.admin_option, 4 ) || ' ' ||
rpad( i_c_dba_role_privs.default_role, 4 ) );

end loop;

dbms_output.put_line( chr(10) );

dbms_output.put_line( 'Tablespace quotas' || chr(10) );

dbms_output.put_line( rpad( 'Tablespace', 30 ) || ' ' ||
rpad( 'User', 30 ) || ' ' ||
lpad( 'Bytes', 14 ) || ' ' ||
lpad( 'Max Bytes', 14 ) || ' ' ||
lpad( 'Blocks', 12 ) || ' ' ||
lpad('Max Blocks', 12 ) );

dbms_output.put_line( rpad( '-------', 30 ) || ' ' ||
rpad( '----', 30 ) || ' ' ||
lpad( '--------', 14 ) || ' ' ||
lpad( '------------', 14 ) || ' ' ||
lpad( '------', 12 ) || ' ' ||
lpad( '----------', 12 ) );

for i_c_ts_quotas in c_ts_quotas loop
dbms_output.put_line( rpad( i_c_ts_quotas.tablespace_name, 30 ) || ' ' ||
rpad( i_c_ts_quotas.username, 30 ) || ' ' ||
to_char( ( i_c_ts_quotas.bytes ), '99999,999,999' ) || ' ' ||
to_char( ( i_c_ts_quotas.max_bytes ), '99999,999,999' ) || ' ' ||
to_char( ( i_c_ts_quotas.blocks ), '999,999,999' ) || ' ' ||
to_char( ( i_c_ts_quotas.max_blocks ), '999,999,999' ) );


end loop;


end;
/

user detail

col external_name for a20

select username, account_status, default_tablespace, temporary_tablespace, created, profile,
password, lock_date, expiry_date, user_id, initial_rsrc_consumer_group, external_name
from dba_users
where username like upper( '%&LikeUser%' )
order by username
/

Oracle User Object Summary

prompt SYNONYM, CLUSTER, CONSUMER GROUP, INDEX PARTITION, TABLE PARTITION, LIBRARY, QUEUE
prompt
accept x_exclude prompt "Exclude above types ? : "
accept x_sys_tem prompt "Include SYS/SYSTEM ? : "
accept x_all_users prompt "Include users without objects ? : "

declare
ls_exclude varchar2( 1 ) := upper( substr( nvl( '&x_exclude', 'N' ), 1, 1 ) );
ls_all_users varchar2( 1 ) := upper( substr( nvl( '&x_all_users', 'N' ), 1, 1 ) );
ls_sys_tem varchar2( 1 ) := upper( substr( nvl( '&x_sys_tem', 'N' ), 1, 1 ) );

cursor c_users is
select *
from dba_users
where ( username not in ( 'SYS', 'SYSTEM' ) or ls_sys_tem <> 'N' )
order by username;

cursor c_object_types is
select unique object_type
from dba_objects
where ( ls_exclude <> 'Y'
or object_type not in ( 'SYNONYM', 'CLUSTER', 'CONSUMER GROUP', 'INDEX PARTITION', 'TABLE PARTITION',
'LIBRARY', 'QUEUE' )
)
and ( owner not in ( 'SYS', 'SYSTEM' ) or ls_sys_tem <> 'N' )
order by 1;


cursor c_u_objects ( x_user in varchar2, x_obj_type in varchar2 ) is
select count(*)
from dba_objects
where owner = x_user
and object_type = x_obj_type;
li_num_objects integer;

type table_obj_type is table of dba_objects.object_type%type index by binary_integer;
t_obj_type table_obj_type;
li_counter integer := 1;
li_no_users integer := 0;

li_column_width integer := 8;
ls_header1 varchar2( 255 );
ls_header2 varchar2( 255 );
ls_header3 varchar2( 255 );
ls_out_string varchar2( 255 );

begin

for i_c_object_types in c_object_types loop
t_obj_type( t_obj_type.count+1 ) := i_c_object_types.object_type;
end loop;

ls_header1 := ' ';
ls_header2 := ' ';
ls_header3 := ' ';

for x in 1..t_obj_type.count loop
ls_header1 := ls_header1 || lpad( t_obj_type( x ), li_column_width ) || ' ';
ls_header2 := ls_header2 || lpad( nvl( substr( t_obj_type( x ), li_column_width + 1 ), ' ' ), li_column_width ) || ' ';
ls_header3 := ls_header3 || lpad( rpad( '-', length( t_obj_type( x ) ), '-' ), li_column_width ) || ' ';
end loop;

dbms_output.put_line( ls_header1 );
dbms_output.put_line( ls_header2 );
dbms_output.put_line( ls_header3 );

for i_c_users in c_users loop

ls_out_string := rpad( i_c_users.username, 15 );
li_counter := 0;

for x in 1..t_obj_type.count loop
li_num_objects := 0;
open c_u_objects ( i_c_users.username, t_obj_type( x ) );
fetch c_u_objects into li_num_objects;
close c_u_objects;
li_counter := li_counter + li_num_objects;
if li_num_objects > 0 then
ls_out_string := ls_out_string || lpad( to_char( li_num_objects, '999,999' ), li_column_width ) || ' ';
else
ls_out_string := ls_out_string || lpad( ' .', li_column_width ) || ' ';
end if;
end loop;

if li_counter > 0 or ls_all_users <> 'N' then
li_no_users := li_no_users + 1;
dbms_output.put_line( ls_out_string || lpad( to_char( li_counter, '999,999' ), li_column_width ) );
end if;

end loop;

dbms_output.put_line( chr(10) );
dbms_output.put_line( 'No of users displayed : ' || li_no_users );



end;
/

Oracle Transaction Monitor

accept x_revolutions prompt "No of cycles: "
accept x_sleep_secs prompt "Seconds for sleep interval: "
prompt

declare

cursor c_trans_sess is
select a.addr,
a.status "TStatus",
a.start_time,
a.name,
a.used_ublk,
a.used_urec,
a.log_io,
a.phy_io,
b.sid,
b.serial#,
b.username,
b.taddr,
b.status "SStatus",
b.osuser,
b.program,
b.client_info,
b.logon_time
from v$transaction a,
v$session b
where b.taddr = a.addr
order by b.sid;

type tr_trans_sess is record
( t_addr v$transaction.addr%type,
t_status v$transaction.status%type,
t_start_time v$transaction.start_time%type,
t_name v$transaction.name%type,
t_used_ublk v$transaction.used_ublk%type,
t_used_urec v$transaction.used_urec%type,
t_log_io v$transaction.log_io%type,
t_phy_io v$transaction.phy_io%type,
s_sid v$session.sid%type,
s_serial# v$session.serial#%type,
s_username v$session.username%type,
s_taddr v$session.taddr%type,
s_status v$session.status%type,
s_osuser v$session.osuser%type,
s_program v$session.program%type,
s_client_info v$session.client_info%type,
s_logon_time v$session.logon_time%type
);
type tt_trans_sess is table of tr_trans_sess index by binary_integer;

lt_trans_sess tt_trans_sess;

ln_counter number := 0;

ln_rec_loc number := -1;
ls_sql varchar2( 2000 );
ls_head1 varchar2( 2000 );
ls_head2 varchar2( 2000 );

ln_revolutions number := to_number( nvl( '&x_revolutions', '1' ) );
ln_sleep_secs number := to_number( nvl( '&x_sleep_secs', '5' ) );

begin
for i in c_trans_sess loop
ln_counter := ln_counter + 1;

lt_trans_sess( ln_counter ).t_addr := i.addr;
lt_trans_sess( ln_counter ).t_status := i."TStatus";
lt_trans_sess( ln_counter ).t_start_time := i.start_time;
lt_trans_sess( ln_counter ).t_name := i.name;
lt_trans_sess( ln_counter ).t_used_ublk := i.used_ublk;
lt_trans_sess( ln_counter ).t_used_urec := i.used_urec;
lt_trans_sess( ln_counter ).t_log_io := i.log_io;
lt_trans_sess( ln_counter ).t_phy_io := i.phy_io;
lt_trans_sess( ln_counter ).s_sid := i.sid;
lt_trans_sess( ln_counter ).s_serial# := i.serial#;
lt_trans_sess( ln_counter ).s_username := i.username;
lt_trans_sess( ln_counter ).s_taddr := i.taddr;
lt_trans_sess( ln_counter ).s_status := i."SStatus";
lt_trans_sess( ln_counter ).s_osuser := i.osuser;
lt_trans_sess( ln_counter ).s_program := i.program;
lt_trans_sess( ln_counter ).s_client_info := i.client_info;
lt_trans_sess( ln_counter ).s_logon_time := i.logon_time;

end loop;

for cycles in 1..ln_revolutions loop
dbms_lock.sleep( ln_sleep_secs );

dbms_output.put_line( '' );
dbms_output.put_line( 'Cycle: ' || cycles );
dbms_output.put_line( '' );

ls_head1 := rpad( 'Username', 30 ) || ' ' ||
lpad( 'SID', 5 ) || ' ' ||
rpad( 'Usr Status', 10 ) || ' ' ||
rpad( 'Tran Stat', 10 ) || ' ' ||
'Used Blocks1' || ' ' ||
'Used Blocks2' || ' ' ||
' Used Recs1' || ' ' ||
' Used Recs2' || ' ' ||
' Rec Change' || ' ' ||
rpad( 'Tran Start', 20 );
ls_head2 := rpad( '--------', 30 ) || ' ' ||
lpad( '---', 5 ) || ' ' ||
rpad( '----------', 10 ) || ' ' ||
rpad( '---------', 10 ) || ' ' ||
'------------' || ' ' ||
'------------' || ' ' ||
' ----------' || ' ' ||
' ----------' || ' ' ||
' ----------' || ' ' ||
rpad( '----------', 20 );

dbms_output.put_line( ls_head1 );
dbms_output.put_line( ls_head2 );

for i in c_trans_sess loop
ln_rec_loc := -1;
-- locate the transaction
for j in 1..ln_counter loop
if lt_trans_sess( j ).t_addr = i.addr then
ln_rec_loc := j;
exit;
end if;
end loop;
if ln_rec_loc > 0 then
ls_sql := rpad( nvl( lt_trans_sess( ln_rec_loc ).s_username, ' '), 30 ) || ' ' ||
to_char( lt_trans_sess( ln_rec_loc ).s_sid, '9999' ) || ' ' ||
rpad( lt_trans_sess( ln_rec_loc ).s_status, 10 ) || ' ' ||
rpad( lt_trans_sess( ln_rec_loc ).t_status, 10 ) || ' ' ||
to_char( lt_trans_sess( ln_rec_loc ).t_used_ublk, '999,999,999' ) || ' ' ||
to_char( i.used_ublk, '999,999,999' ) || ' ' ||
to_char( lt_trans_sess( ln_rec_loc ).t_used_urec, '999,999,999' ) || ' ' ||
to_char( i.used_urec, '999,999,999' ) || ' ' ||
to_char( i.used_urec - lt_trans_sess( ln_rec_loc ).t_used_urec, '999,999,999' ) || ' ' ||
lt_trans_sess( ln_rec_loc ).t_start_time;
if lt_trans_sess( ln_rec_loc ).t_used_urec > i.used_urec then
ls_sql := ls_sql || ' ' || 'Rolling back?';
end if;
else
ls_sql := rpad( nvl( i.username, ' ' ), 30 ) || ' ' ||
to_char( i.sid, '9999' ) || ' ' ||
rpad( i."SStatus", 10 ) || ' ' ||
rpad( i."TStatus", 10 ) || ' ' ||
rpad( ' ', 12 ) || ' ' ||
to_char( i.used_ublk, '999,999,999' ) || ' ' ||
rpad( ' ', 12 ) || ' ' ||
to_char( i.used_urec, '999,999,999' ) || ' ' ||
' ' || ' ' ||
i.start_time;

end if;
if ln_rec_loc > 0 then
dbms_output.put_line( ls_sql );
end if;
end loop;

end loop; -- end revolution cycles

end;
/

Oracle Resource Intensive SQL

accept x_ignore_sys prompt "Ignore SYS? [Y/N]: "
accept x_owner prompt "Owner: "
accept x_num_rows prompt "Number of top records to return: "

prompt Choose sort order:
prompt action
prompt buffer_gets
prompt cpu_time
prompt disk_reads
prompt elapsed_time
prompt executions
prompt fetches
prompt invalidations
prompt kept_versions
prompt loaded_versions
prompt loads
prompt module
prompt open_versions
prompt parse_calls
prompt parsing_schema_id
prompt parsing_user_id
prompt persistent_mem
prompt rows_processed
prompt runtime_mem
prompt serializable_aborts
prompt sharable_mem
prompt sorts
prompt users_executing
prompt users_opening
prompt version_count

accept x_order prompt "Enter sort order of inner select: "

declare

cursor c_top_records is
select *
from ( select *
from v$sqlarea a
where exists ( select 1
from v$sql_plan
where address = a.address
and hash_value = a.hash_value
and ( object_owner <> 'SYS' or upper( nvl( '&x_ignore_sys', 'N' ) ) <> 'Y' )
and nvl( upper( '&x_owner' ), object_owner ) = object_owner
)
order by &x_order desc, cpu_time desc
)
where rownum < ( to_number( nvl( '&x_num_rows', '10' ) ) + 1 )
order by &x_order desc, cpu_time desc;

cursor c_vsql_plan ( x_address in varchar2, x_hash in number ) is
select *
from v$sql_plan
where address = x_address
and hash_value = x_hash
order by address, hash_value, depth;

cursor c_sql ( x_address in varchar2, x_hash in number ) is
select *
from v$sqltext
where address = x_address
and hash_value = x_hash
order by piece;

cursor c_sqlarea ( x_address in varchar2, x_hash in number ) is
select *
from v$sqlarea
where address = x_address
and hash_value = x_hash;
lt_sqlarea v$sqlarea%rowtype;

ls_sql_text varchar2( 10000 );

begin

dbms_output.put_line( '' );

for i in c_top_records loop
dbms_output.put_line( rpad( '-', 150, '-' ) );
open c_sqlarea( i.address, i.hash_value );
fetch c_sqlarea into lt_sqlarea;
close c_sqlarea;

case lower( '&x_order' )
when lower( 'action' ) then
dbms_output.put_line( 'Action :' || lt_sqlarea.action );
when lower( 'buffer_gets' ) then
dbms_output.put_line( 'Buffer gets :' || lt_sqlarea.buffer_gets );
when lower( 'cpu_time' ) then
dbms_output.put_line( 'CPU time:' || lt_sqlarea.cpu_time );
when lower( 'disk_reads' ) then
dbms_output.put_line( 'Disk reads :' || lt_sqlarea.disk_reads );
when lower( 'elapsed_time' ) then
dbms_output.put_line( 'Elapsed time :' || lt_sqlarea.elapsed_time );
when lower( 'executions' ) then
dbms_output.put_line( 'Executions :' || lt_sqlarea.executions );
when lower( 'fetches' ) then
dbms_output.put_line( 'Fetches :' || lt_sqlarea.fetches );
when lower( 'invalidations' ) then
dbms_output.put_line( 'Invalidations :' || lt_sqlarea.invalidations );
when lower( 'kept_versions' ) then
dbms_output.put_line( 'Kept versions:' || lt_sqlarea.kept_versions );
when lower( 'loaded_versions' ) then
dbms_output.put_line( 'Loaded versions:' || lt_sqlarea.loaded_versions );
when lower( 'loads' ) then
dbms_output.put_line( 'Loads :' || lt_sqlarea.loads );
when lower( 'module' ) then
dbms_output.put_line( 'Module :' || lt_sqlarea.module );
when lower( 'open_versions' ) then
dbms_output.put_line( 'Open versions :' || lt_sqlarea.open_versions );
when lower( 'parse_calls' ) then
dbms_output.put_line( 'Parse calls:' || lt_sqlarea.parse_calls );
when lower( 'parsing_schema_id' ) then
dbms_output.put_line( 'Parsing Schema Id:' || lt_sqlarea.parsing_schema_id );
when lower( 'parsing_user_id' ) then
dbms_output.put_line( 'Parsing User Id:' || lt_sqlarea.parsing_user_id );
when lower( 'persistent_mem' ) then
dbms_output.put_line( 'Persistent mem :' || lt_sqlarea.persistent_mem );
when lower( 'rows_processed' ) then
dbms_output.put_line( 'Rows Processed :' || lt_sqlarea.rows_processed );
when lower( 'runtime_mem' ) then
dbms_output.put_line( 'Runtime mem:' || lt_sqlarea.runtime_mem );
when lower( 'serializable_aborts' ) then
dbms_output.put_line( 'Serialisable aborts :' || lt_sqlarea.serializable_aborts );
when lower( 'sharable_mem' ) then
dbms_output.put_line( 'Sharable mem:' || lt_sqlarea.sharable_mem );
when lower( 'sorts' ) then
dbms_output.put_line( 'Sorts :' || lt_sqlarea.sorts );
when lower( 'users_executing' ) then
dbms_output.put_line( 'Users executing:' || lt_sqlarea.users_executing );
when lower( 'users_opening' ) then
dbms_output.put_line( 'Users opening :' || lt_sqlarea.users_opening );
when lower( 'version_count' ) then
dbms_output.put_line( 'Version count :' || lt_sqlarea.version_count );
else
dbms_output.put_line( 'Sort order not recognised.' );
end case;

dbms_output.put_line( '' );

ls_sql_text := '';
for j in c_sql( i.address, i.hash_value ) loop
-- if procedure bigt is not available or not required, comment the line
-- out and uncomment the following line:
-- dbms_output.put_line( j.sql_text );
ls_sql_text := ls_sql_text || j.sql_text;
end loop;
bigt( ls_sql_text );

dbms_output.put_line( rpad( 'Operation', 30 ) || ' ' ||
rpad( 'Options', 25 ) || ' ' ||
rpad( 'Owner', 25 ) || ' ' ||
rpad( 'Obj Name', 30 ) || ' ' ||
' Bytes' || ' ' ||
' Cost' || ' ' ||
'Part Start' || ' ' ||
'Part Stop ' || ' ' ||
'Card.' );
dbms_output.put_line( rpad( '---------', 30 ) || ' ' ||
rpad( '-------', 25 ) || ' ' ||
rpad( '-----', 25 ) || ' ' ||
rpad( '-----', 30 ) || ' ' ||
' -----' || ' ' ||
' ----' || ' ' ||
'----------' || ' ' ||
'--------- ' || ' ' ||
'-----' );

for k in c_vsql_plan( i.address, i.hash_value ) loop
dbms_output.put_line( rpad( lpad( ' ', 2 * ( k.depth-1 ), '. ' ) || k.operation, 30 ) || ' ' ||
rpad( nvl( k.options, ' ' ), 25 ) || ' ' ||
rpad( nvl( k.object_owner, ' ' ), 25 ) || ' ' ||
rpad( nvl( k.object_name, ' ' ), 30 ) || ' ' ||
nvl( to_char( k.bytes, '99,999,999' ), ' ' ) || ' ' ||
nvl( to_char( k.cost, '99,999,999' ), ' ' ) || ' ' ||
rpad( nvl( k.partition_start, ' ' ), 10 ) || ' ' ||
rpad( nvl( k.partition_stop, ' ' ), 10 ) || ' ' ||
nvl( to_char( k.cardinality, '9999' ), ' ' ) );
end loop;

end loop;

end;
/

Partition Table Info

accept x_owner prompt "Owner : "
accept x_name prompt "Name : "

declare
ln_no_of_parts number := 0;
ls_owner varchar2( 100 ) := upper( '&x_owner' );
ls_name varchar2( 100 ) := upper( '&x_name' );

cursor c_parts is
select unique owner, name, object_type
from dba_part_key_columns
where owner like '%' || ls_owner || '%'
and name like '%' || ls_name || '%'
order by owner, object_type;

cursor c_part_key_columns( x_owner in varchar2, x_table_name in varchar2 ) is
select *
from dba_part_key_columns
where owner = x_owner
and name = x_table_name
order by column_position;

ls_high_val varchar2( 10000 );

cursor c_seg_bytes( x_owner in varchar2, x_segment in varchar2, x_partition in varchar2 ) is
select *
from dba_segments
where owner = x_owner
and segment_name = x_segment
and partition_name = x_partition;
lt_seg_bytes dba_segments%rowtype;

-- ---------------------------------------------------------------------------------
procedure p_show_table( x_owner in varchar2, x_table_name in varchar2 ) is

ls_warning varchar2( 100 ) := '';
ls_seg_bytes varchar2( 100 ) := '';

cursor c_part_tables( x_owner in varchar2, x_table_name in varchar2 ) is
select *
from dba_part_tables
where owner = x_owner
and table_name = x_table_name;

cursor c_tab_partitions( x_owner in varchar2, x_table_name in varchar2 ) is
select *
from dba_tab_partitions
where table_owner = x_owner
and table_name = x_table_name
order by partition_position;

begin
for c_pt in c_part_tables( x_owner, x_table_name ) loop

dbms_output.put_line( 'Partition Type : ' || c_pt.partitioning_type );
dbms_output.put_line( 'Partition Count : ' || c_pt.partition_count );

dbms_output.put_line( ' ' );
dbms_output.put_line( 'Columns : ' );
dbms_output.put_line( ' Pos' || ' ' || rpad( 'Obj Type', 11 ) || ' ' || 'Column Name' );
dbms_output.put_line( ' ---' || ' ' || rpad( '--------', 11 ) || ' ' || '-----------' );
for c_pkc in c_part_key_columns( c_pt.owner, c_pt.table_name ) loop
dbms_output.put_line( to_char( c_pkc.column_position, '999' ) || ' ' ||
rpad( c_pkc.object_type, 11 ) || ' ' ||
c_pkc.column_name );

end loop;

if c_pt.partitioning_type <> 'HASH' then
for c_p in c_tab_partitions( c_pt.owner, c_pt.table_name ) loop
ls_high_val := c_p.high_value;
-- if the high_value exceeds 255 characters procedure bigt will be required
-- e.g. bigt( chr(10) || 'High Value : ' || c_p.partition_position || chr(10) || trim( ls_high_val ) );
dbms_output.put_line( 'High Value : ' || c_p.partition_position || chr(9) || trim( ls_high_val ) );
end loop;
end if;

dbms_output.put_line( ' ' );
dbms_output.put_line( 'Partitions : ' );
dbms_output.put_line( ' Pos' || ' ' || rpad( 'Partition Name', 30 ) || ' ' || rpad( 'Tablespace', 30 ) || ' Bytes (Mb)' );
dbms_output.put_line( ' ---' || ' ' || rpad( '--------------', 30 ) || ' ' || rpad( '----------', 30 ) || ' ----------' );

for c_p in c_tab_partitions( c_pt.owner, c_pt.table_name ) loop
if c_p.subpartition_count > 0 then
ls_warning := ' Warning : sub-partitions attached';
ls_seg_bytes := '';
else
ls_warning := '';
open c_seg_bytes( c_pt.owner, c_pt.table_name, c_p.partition_name );
fetch c_seg_bytes into lt_seg_bytes;
close c_seg_bytes;
ls_seg_bytes := to_char( lt_seg_bytes.bytes/( 1024*1024), '999,999,999' ) || 'Mb';
end if;
dbms_output.put_line( to_char( c_p.partition_position, '999' ) || ' ' ||
rpad( c_p.partition_name, 30 ) || ' ' ||
rpad( c_p.tablespace_name, 30 ) ||
ls_warning ||
ls_seg_bytes );
end loop;

end loop;
end;
-- ---------------------------------------------------------------------------------

procedure p_show_index( x_owner in varchar2, x_index_name in varchar2 ) is

ls_warning varchar2( 100 ) := '';
ls_seg_bytes varchar2( 100 ) := '';

cursor c_part_indexes( x_owner in varchar2, x_index_name in varchar2 ) is
select *
from dba_part_indexes
where owner = x_owner
and index_name = x_index_name;

cursor c_ind_partitions( x_owner in varchar2, x_index_name in varchar2 ) is
select *
from dba_ind_partitions
where index_owner = x_owner
and index_name = x_index_name
order by partition_position;

begin
for c_pt in c_part_indexes( x_owner, x_index_name ) loop
dbms_output.put_line( 'Partition Type : ' || c_pt.partitioning_type );
dbms_output.put_line( 'Partition Count : ' || c_pt.partition_count );

dbms_output.put_line( ' ' );
dbms_output.put_line( 'Columns : ' );
dbms_output.put_line( ' Pos' || ' ' || rpad( 'Obj Type', 11 ) || ' ' || 'Column Name' );
dbms_output.put_line( ' ---' || ' ' || rpad( '--------', 11 ) || ' ' || '-----------' );
for c_pkc in c_part_key_columns( c_pt.owner, c_pt.index_name ) loop
dbms_output.put_line( to_char( c_pkc.column_position, '999' ) || ' ' ||
rpad( c_pkc.object_type, 11 ) || ' ' ||
c_pkc.column_name );

end loop;

if c_pt.partitioning_type <> 'HASH' then
for c_p in c_ind_partitions( c_pt.owner, c_pt.index_name ) loop
ls_high_val := c_p.high_value;
-- if the high_value exceeds 255 characters procedure bigt will be required
-- e.g. bigt( chr(10) || 'High Value : ' || c_p.partition_position || chr(10) || ls_high_val );
dbms_output.put_line( chr(10) || 'High Value : ' || c_p.partition_position || chr(10) || ls_high_val );
end loop;
end if;

dbms_output.put_line( ' ' );
dbms_output.put_line( 'Partitions : ' );
dbms_output.put_line( ' Pos' || ' ' || rpad( 'Partition Name', 30 ) || ' ' || rpad( 'Tablespace', 30 ) || ' Bytes (Mb)' );
dbms_output.put_line( ' ---' || ' ' || rpad( '--------------', 30 ) || ' ' || rpad( '----------', 30 ) || ' ----------' );

for c_p in c_ind_partitions( c_pt.owner, c_pt.index_name ) loop
if c_p.subpartition_count > 0 then
ls_warning := ' Warning : sub-partitions attached';
ls_seg_bytes := '';
else
ls_warning := '';
open c_seg_bytes( c_pt.owner, c_pt.table_name, c_p.partition_name );
fetch c_seg_bytes into lt_seg_bytes;
close c_seg_bytes;
ls_seg_bytes := to_char( lt_seg_bytes.bytes/( 1024*1024), '999,999,999' ) || 'Mb';
end if;
dbms_output.put_line( to_char( c_p.partition_position, '999' ) || ' ' ||
rpad( c_p.partition_name, 30 ) || ' ' ||
rpad( c_p.tablespace_name, 30 ) ||
ls_warning ||
ls_seg_bytes );
end loop;

end loop;
end;
-- ---------------------------------------------------------------------------------

begin

for i_c_parts in c_parts loop
ln_no_of_parts := ln_no_of_parts + 1;
dbms_output.put_line( rpad( '-', 100, '-' ) );
dbms_output.put_line( 'Owner : ' || i_c_parts.owner );
dbms_output.put_line( 'Name : ' || i_c_parts.name );
dbms_output.put_line( 'Type : ' || i_c_parts.object_type );

if trim( i_c_parts.object_type ) = 'TABLE' then
p_show_table( i_c_parts.owner, i_c_parts.name );
elsif trim( i_c_parts.object_type ) = 'INDEX' then
p_show_index( i_c_parts.owner, i_c_parts.name );
else
dbms_output.put_line( 'Type : ' || i_c_parts.object_type || ' not recognised.' );
end if;
dbms_output.put_line( rpad( '-', 100, '-' ) );

end loop;

if ln_no_of_parts = 0 then
dbms_output.put_line( 'No partitioned tables found matching criteria.' );
end if;

end;
/

Oracle Job Schedules detail

declare
cursor csr_jobs is
select * from dba_jobs
where job = nvl( '&jno', job );

ls_env dba_jobs.nls_env%type;
ls_param dba_jobs.nls_env%type;
i_brk_point integer;
i_ctr integer;
cursor csr_runig( xn_jno in number ) is
select count(*)
from dba_jobs_running
where job = xn_jno;
n_runig number;
s_notis varchar2( 200 );
begin
dbms_output.put_line( chr(10) );
for i_csr_jobs in csr_jobs loop
dbms_output.put_line( chr(9) );
open csr_runig( i_csr_jobs.job );
fetch csr_runig into n_runig;
close csr_runig;

s_notis := ' ';
if n_runig > 0 then
s_notis := s_notis || ' \Running/ ';
end if;
if i_csr_jobs.broken = 'Y' then
s_notis := s_notis || ' * * * Broken * * * ';
else
s_notis := s_notis || ' Not broken. ';
end if;
if i_csr_jobs.failures > 0 then
s_notis := s_notis || ' * * * Has failed ' || i_csr_jobs.failures || ' times * * * ';
else
s_notis := s_notis || ' No failures. ';
end if;

dbms_output.put_line( '=======================================================================================' );
dbms_output.put_line( 'Job No : ' || i_csr_jobs.job || ' ' || s_notis );
dbms_output.put_line( '=======================================================================================' );
dbms_output.put_line( 'Log User : ' || i_csr_jobs.log_user );
dbms_output.put_line( 'Priv User : ' || i_csr_jobs.priv_user );
dbms_output.put_line( 'Schema User : ' || i_csr_jobs.schema_user );
dbms_output.put_line( 'Last Date : ' || to_char( i_csr_jobs.last_date, 'hh24:mi dd/mm/yy' ) );
dbms_output.put_line( 'Last Sec : ' || i_csr_jobs.last_sec );
dbms_output.put_line( 'This Date : ' || to_char( i_csr_jobs.this_date, 'hh24:mi dd/mm/yy' ) );
dbms_output.put_line( 'This Sec : ' || i_csr_jobs.this_sec );
dbms_output.put_line( 'Next Date : ' || to_char( i_csr_jobs.next_date, 'hh24:mi dd/mm/yy' ) );
dbms_output.put_line( 'Next Sec : ' || i_csr_jobs.next_sec );
dbms_output.put_line( 'Total Time : ' || i_csr_jobs.total_time );
-- dbms_output.put_line( 'Broken : ' || i_csr_jobs.broken );
dbms_output.put_line( 'Interval : ' || i_csr_jobs.interval );
-- dbms_output.put_line( 'Failures : ' || i_csr_jobs.failures );
dbms_output.put_line( 'What : ' || i_csr_jobs.what );
-- dbms_output.put_line( 'Current Session Label : ' || i_csr_jobs.current_session_label );
-- dbms_output.put_line( 'Clearance HI : ' || i_csr_jobs.clearance_hi );
-- dbms_output.put_line( 'Clearance LO : ' || i_csr_jobs.clearance_lo );
-- dbms_output.put_line( 'NLS Env : ' || i_csr_jobs.nls_env );
dbms_output.put_line( 'Environment : ' );
dbms_output.put_line( '--------------' );
ls_env := i_csr_jobs.nls_env;
i_ctr := 0;
while ls_env is not null loop
i_brk_point := instr( ls_env, chr(32) );
if i_brk_point = 0 then
dbms_output.put_line( chr(9) || ls_env );
exit;
end if;
ls_param := substr( ls_env, 1, i_brk_point );
dbms_output.put_line( chr(9) || ls_param );
ls_env := substr( ls_env, i_brk_point + 1 );

i_ctr := i_ctr + 1;
if i_ctr > 10 then
dbms_output.put_line( 'hath exceeded the tenth....');
dbms_output.put_line( ls_env );
exit;
end if;
end loop;

-- dbms_output.put_line( 'Misc Env : ' || i_csr_jobs.misc_env );
end loop;
end;
/

Review Oracle user privilege

set echo off
set verify off
set lines 1000
set pages 999
set head on
set feed on
set serveroutput on size 1000000 format wrap
set trimspool on
-- clear col
-- clear break
-- clear computes
set arrays 1
set long 10000

declare
ls_sql_text varchar2( 2000 );
ls_link_text varchar2( 2000 );
ls_sel_text varchar2( 2000 );
ls_drop_link varchar2( 2000 );
ls_this_sid varchar2( 20 );
ls_sqlerr number;
ls_sqlcode varchar2( 2000 );
ln_dcount number;
ln_numlinks number;
ls_db_link varchar2( 2000 );
ln_no_nu number := 0;
ln_no_users number := 0;
ls_privs varchar2( 2000 );
ls_out_text varchar2( 2000 );

cursor c_users is
select * from dba_users
order by username;

cursor c_sys_privs( x_user in varchar2 ) is
select *
from dba_sys_privs
where grantee = x_user
order by privilege;

begin
ls_sql_text := 'alter session set global_names=false';
execute immediate ls_sql_text;

select global_name into ls_this_sid from global_name;
ls_db_link := 'TEMPLINK' || substr( ls_this_sid, instr( ls_this_sid, '.' ) );
ls_this_sid := substr( ls_this_sid, 1, instr( ls_this_sid, '.' ) - 1 );

dbms_output.put_line( 'SID : ' || ls_this_sid );
dbms_output.put_line( 'Temporary Link : ' || ls_db_link );
dbms_output.put_line( rpad( '-', 100, '-' ) );
dbms_output.put_line( 'Note : privileges shown are only those directly granted. They do not include any inherited via roles.' || chr( 10 ) );

for i in c_users loop
ln_no_users := ln_no_users + 1;
begin
select count(*) into ln_numlinks from user_db_links where db_link = ls_db_link;
if ln_numlinks > 0 then
ls_drop_link := 'drop database link templink';
execute immediate ls_drop_link;
end if;

if i.account_status = 'LOCKED' then
dbms_output.put_line( 'User ' || rpad( i.username, 31 ) || ' STATUS = LOCKED' );
else
ls_link_text := 'create database link templink connect to ' || i.username || ' identified by ' || i.username || ' using ' || '''' || ls_this_sid || '''';
execute immediate ls_link_text;

ls_sel_text := 'select count(*) from dual@templink';
execute immediate ls_sel_text into ln_dcount;

ls_drop_link := 'drop database link templink';
execute immediate ls_drop_link;

ls_privs := ' ';
for j in c_sys_privs( i.username ) loop
ls_privs := ls_privs || ' * ' || j.privilege;
end loop;
ls_out_text := 'User ' || rpad( i.username, 31 ) || ' is a naughty user.' || ls_privs;
if length( ls_out_text ) > 254 then
dbms_output.put_line( substr( ls_out_text, 1, 240 ) || ' and more ...' );
else
dbms_output.put_line( ls_out_text );
end if;
ln_no_nu := ln_no_nu + 1;
end if;

exception
when others then
ls_sqlerr := sqlcode;
ls_sqlcode := sqlerrm;
if ls_sqlerr = -2024
or ls_sqlerr = -1017
then
null;
else
dbms_output.put_line( rpad( i.username, 31 ) || ls_sqlerr || ' ' || ls_sqlcode );
end if;
end;
end loop;
dbms_output.put_line( rpad( '-', 100, '-' ) );
dbms_output.put_line( 'Number of users : ' || ln_no_users );
dbms_output.put_line( 'Number of naughty users : ' || ln_no_nu );
dbms_output.put_line( 'Percentage : ' || to_char( ( ln_no_nu / ln_no_users ) * 100, '999.99' ) );
dbms_output.put_line( rpad( '-', 100, '-' ) );
end;
/

Oracle DBA

anuj blog Archive