ORA-1555 Snapshot Too old while a running job or query .

If we are getting this error frequently we need to think about the size of  undo tablespace and undo retention  time. Below query helped me to take optimum size of
undo tablespace and undo retention time.

                                                                        ACTUAL UNDO SIZE
OPTIMAL UNDO RETENTION =  ____________________________________
                                                       DB_BLOCK_SIZE*UNDO_BLOCK_PER_SEC


SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       ROUND((d.undo_size / (to_number(f.value) *
       g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
  FROM (
       SELECT SUM(a.bytes) undo_size
          FROM v$datafile a,
               v$tablespace b,
               dba_tablespaces c
         WHERE c.contents = 'UNDO'
           AND c.status = 'ONLINE'
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#
       ) d,
       v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
              undo_block_per_sec
         FROM v$undostat
       ) g
WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size';



                     
OPTIMAL UNDO SIZE      = UNDO RETENTION*DB_BLOCK_SIZE*UNDO_BLOCK_PER_SEC

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",

       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",

       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *

       g.undo_block_per_sec) / (1024*1024) 

      "NEEDED UNDO SIZE [MByte]"

  FROM (

       SELECT SUM(a.bytes) undo_size

         FROM v$datafile a,

              v$tablespace b,

              dba_tablespaces c

        WHERE c.contents = 'UNDO'

          AND c.status = 'ONLINE'

          AND b.name = c.tablespace_name

          AND a.ts# = b.ts#

       ) d,

      v$parameter e,

       v$parameter f,

       (

       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))

         undo_block_per_sec

         FROM v$undostat

       ) g

 WHERE e.name = 'undo_retention'

  AND f.name = 'db_block_size';

 
 
 
  Note:This above estimation undo size and retention are depends upon the calculations from v$undostat during the previous run. Please impliment this estimation carefully
  as per your requirment and resource avaialability.


Thanks,
Jyothish

Execution Time of an SQL using SQL_ID with respect to SNAP_ID

Here we can calculate Elapsed of time of an sql statement using sql id. Below query will figure out the Elapsed time in seconds.



col begin_time for a25
col end_time for a11
col inst for 99999
col snapid for 999999
set lines 200
set pages 20000
select snap_id snapid,
(select substr(BEGIN_INTERVAL_TIME,1,18)||' '||substr(BEGIN_INTERVAL_TIME,24,2) from dba_hist_snapshot b where b.snap_id=a.snap_id and

a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) begin_time
,(select substr(end_INTERVAL_TIME,11,8)||' '||substr(end_INTERVAL_TIME,24,2) from dba_hist_snapshot b where b.snap_id=a.snap_id and

a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) end_time
,INSTANCE_NUMBER inst , PLAN_HASH_VALUE,
EXECUTIONS_DELTA Executions,
ROWS_PROCESSED_DELTA rows1,
round( CPU_TIME_DELTA /1000000,0) cpu_time,round(IOWAIT_DELTA /1000000,0) io_wait,
round( ELAPSED_TIME_DELTA /1000000,0) elapsed
from wrh$_sqlstat a where sql_id in('&SQL_ID')
order by snap_id, INSTANCE_NUMBER;

Thanks,

Jyothish Balakrishnan

Archive Generation Details

Archive Generation per Hour

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';
select trunc(COMPLETION_TIME,'HH') Hour,round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,count(*) Archives from v$archived_log group by trunc(COMPLETION_TIME,'HH') order by 1 ;

Archive Generation per Day

select trunc(COMPLETION_TIME,'DD') Day, round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,count(*) Archives_Generated from v$archived_log
group by trunc(COMPLETION_TIME,'DD') order by 1;

How to find out ASM file locations for a Database

column full_alias_path format a70
column file_type format a15


select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
       system_created, alias_directory, file_type
from ( select b.name gname, a.parent_index pindex, a.name aname,
              a.reference_index rindex , a.system_created, a.alias_directory,
              c.type file_type
       from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
       where a.group_number = b.group_number
             and a.group_number = c.group_number(+)
             and a.file_number = c.file_number(+)
             and a.file_incarnation = c.incarnation(+)
     )
start with (mod(pindex, power(2, 24))) = 0
            and rindex in
                ( select a.reference_index
                  from v$asm_alias a, v$asm_diskgroup b
                  where a.group_number = b.group_number
                        and (mod(a.parent_index, power(2, 24))) = 0
                        and a.name = '&DATABASENAME'
                )
connect by prior rindex = pindex;


You can use this query with any Database running on the same server for a Database.

Drop Database Link in another schema .

We can drop Database link in another schema by a simple procedure without knowing password of the user

SQL> conn / as sysdba
SQL> create or replace procedure schema.procedurename as
begin
execute immediate  ‘drop database link  db_link_name’;
end procedurename;


SQL>exec Procedurename;

Oracle Multimedia (ORDIM) status is "LOADING" in DBA_REGISTRY

Oracle Version - 11g

Cause : Oracle Multimedia Registry Components are not properly configured .

sqlplus / as sysdab

SQL> COL COMP_NAME FOR A20
SQL> COL COMP_ID FOR A20
SQL> select comp_id,comp_name,status from dba_registry where comp_name='Oracle Multimedia';

SQL> select comp_id,comp_name,status from dba_registry where comp_name='Oracle Multimedia';

COMP_ID              COMP_NAME            STATUS
-------------------- -------------------- --------
ORDIM                Oracle Multimedia    LOADING



Action

1) Reload Registry component


SQL>conn / as sysdba

SQL> execute sys.dbms_registry.loaded('ORDIM');

2) Validate Registry Status .


SQL> execute sys.dbms_registry.valid('ORDIM');


Now we can see the status is "VALID"


SQL> select comp_id,comp_name,status from dba_registry where comp_name='Oracle Multimedia';

COMP_ID              COMP_NAME            STATUS
-------------------- -------------------- --------
ORDIM                Oracle Multimedia    VALID

Drop user failed with ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist

Cause

Table system.aq$_internet_agent_privs is missing in "SYSTEM" Schema.



Action:

1. Check if system.aq$_internet_agent_privs exist.

       SQL> conn / as sysdba

       SQL> desc system.aq$_internet_agent_privs

2. Make sure that the default tablespace of SYSTEM user is properly set to SYSTEM tablespace.

        SQL> select default_tablespace from dba_users where   username=’SYSTEM’;

      DEFAULT_TABLESPACE
      ——————————
      SYSTEM

Run the below Script as sysdba user.

      SQL> conn / as sysdba
      SQL> @$ORACLE_HOME/rdbms/admin/catqueue.sql 
      SQL> exit


3. Then execute the DROP USER command again.

4.   SQL> Drop user Username;

      User Dropped