Script to Find HOT BLOCKS

SET LINESIZE 200


SET VERIFY OFF



SELECT *

FROM (SELECT name,

addr,

gets,

misses,

sleeps

FROM v$latch_children

WHERE name = 'cache buffers chains'

AND misses > 0

ORDER BY misses DESC)

WHERE rownum < 11;



ACCEPT address PROMPT "Enter ADDR: "



COLUMN owner FORMAT A15

COLUMN object_name FORMAT A30

COLUMN subobject_name FORMAT A20



SELECT *

FROM (SELECT o.owner,

o.object_name,

o.subobject_name,

o.object_type,

bh.tch,

bh.obj,

bh.file#,

bh.dbablk,

DECODE(bh.class,1,'data block',

2,'sort block',

3,'save undo block',

4,'segment header',

5,'save undo header',

6,'free list',

7,'extent map',

8,'1st level bmb',

9,'2nd level bmb',

10,'3rd level bmb',

11,'bitmap block',

12,'bitmap index block',

13,'file header block',

14,'unused',

15,'system undo header',

16,'system undo block',

17,'undo header',

18,'undo block') AS class,

DECODE(bh.state, 0,'free',

1,'xcur',

2,'scur',

3,'cr',

4,'read',

5,'mrec',

6,'irec',

7,'write',

8,'pi',

9,'memory',

10,'mwrite',

11,'donated') AS state

FROM x$bh bh,

dba_objects o

WHERE o.data_object_id = bh.obj

AND hladdr = '&address'

ORDER BY tch DESC)

WHERE rownum < 11;

Script to find Session_log_on_time

set line 200


col osuser format a10 trunc heading "OSUSER AS"

col orauser format a10 trunc

col machine format a10 trunc

col sprogram format a15 trunc

col process format a20 trunc

col server format a3 trunc

col sess_id format 9999

col proc_id format a10

col logon_time format a20

SELECT s.osuser osuser,

s.username orauser,

s.machine machine,

s.program sprogram,

p.program process,

s.sid sess_id,

s.serial# sess_num,

p.spid proc_id,

to_char(s.logon_time, 'hh24:mi dd/mm/yy') login_time,

s.server server,status

---s.client_info

FROM v$session s,

v$process p

WHERE s.paddr = p.addr

AND type != 'BACKGROUND'

AND p.username is not null

ORDER BY 9 desc

/

col osuser clear

col machine clear

col orauser clear

ttitle off

Finding Clustering Factor for Oracle Index

-  A good CF is equal (or near) to the values of number of blocks of table.

-  A bad CF is equal (or near) to the number of rows of table.
 
It not 100% true that Rebuilding of index can improve the CF.To improve the CF, it’s the table that must be rebuilt (and reordered)
 
====== Script to find the same ======
 
select a.index_name,b.num_rows,b.blocks,a.clustering_factor


from dba_indexes a, dba_tables b

where

index_name in('INDEX_1','INDEX_2','INDEX_3')

and a.table_name=b.table_name;
 

Taking export in Compressed form + Running Export in nohup mode

It is one of the regular DBA activity to take the Backup of database of the schema via exp utility..if we are facing any issue related to the space we can alway is this command to compress the backups and ...if export schema is big and going to take time no need to waste your sleep just run that in backgroung using nohup

 and here it goes

=== Generating Export dump in compressed form ====


iinerva(ammsc001):/m002/oradata/ak>vi exp_4schm.sh

"exp_4schm.sh" 1 line, 1 character

rm exp_pipe

mknod exp_pipe p

compress < exp_pipe > 4Schema_SRF100303.dmp.Z &

exp userid=vijay/spring*****@AMMSC001 file=exp_pipe owner=SALSY,SALEX,SALPF,SALVO statistics=none
buffer=100000
~
~
====== Running in nohup mode =======

iinerva(ammsc001):/m002/oradata/ak>nohup sh exp_4schm.sh &

[1] 74842

iinerva(ammsc001):/m002/oradata/ak>Sending nohup output to nohup.out.

iinerva(ammsc001):/m002/oradata/ak>tail -f nohup.out

About to export SALSY's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export SALSY's tables via Conventional Path ...

. . exporting table AFDELINGEN 7510 rows exported

. . exporting table AFDELINGENGROEPEN 58 rows exported

. . exporting table AFDELINGENGROEPKODES 14 rows exported

. . exporting table AHOLD_LOONKODES 734 rows exported

. . exporting table AHOLD_MUTATIES 5748 rows exported

. . exporting table ARBEIDSRELATIES 450424 rows exported

. . exporting table AVW_CAO_KODES 81 rows exported

. . exporting table AVW_GROEPEN 3215 rows exported

. . exporting table AVW_GROEPKODES 58 rows exported

. . exporting table

........

shell INVALID command to compile database objects

======== INVALID COMMAND =======

Though there are many utilities and packages are there to complie the invalid database objects..But an easy approach to all these are to use invalid command at OS level. I am giving some example here for which i ran in my environment to compile the objects

====

Infrastructure Services Europe - Computer Services Midrange

If you experience technical difficulties regarding this system
please contact the Infrastructure Services Europe - IT Services Center:

24 hours 7 days a week, phone +31 (0)75 659 21 00

______________________________________________________________________________



!!! HP DISK CONVERSION IN PROGRESS. PLEASE DO NO ADD NEW DISK

TO THE EXISTING DISKGROUP. CONTACT RAZIB FOR MORE INFO !!!

SIDS on this machine are: areplm01 areplm02 arepl202 aapex001 awm71002 awm71001 owm71003 pdb2ora1 otrack1 prepl001 prepl002 midtier db10105 db10203 db10204 tonto_listener_10203 tonto_listener_10204 tonto_listener_10204_dg agent agent11g prepl101 prepl102

tonto():/home/oracle>ps -ef
grep smon

oracle 25352 1 0 Jan 17 ? 0:08 ora_smon_owm71003

oracle 9502 1 0 Dec 14 ? 3:33 ora_smon_areplm01

oracle 15795 1 0 Oct 09 ? 10:42 ora_smon_aapex001

oracle 21420 1 0 Jan 17 ? 0:11 ora_smon_awm71001

oracle 3687 3661 0 11:46:15 pts/1 0:00 grep smon

oracle 12109 1 0 Jan 10 ? 0:51 ora_smon_pdb2ora1

oracle 9855 1 0 Dec 14 ? 3:14 ora_smon_areplm02

oracle 3108 1 0 Jan 15 ? 0:18 ora_smon_arepl202

tonto():/home/oracle>. oraenv

ORACLE_SID = [oracle] ? arepl202

tonto(arepl202):/home/oracle>invalid

alter VIEW P1INTF.INTF_OM_ODFD_T1 compile ;



alter public synonym X$KCBFWAIT compile;

alter public synonym X$KSPPSV compile;

alter public synonym X$KSPPI compile;

alter public synonym X$KSQST compile;

alter public synonym X$KSLLT compile;





Warning: View altered with compilation errors.





Synonym altered.





Synonym altered.





Synonym altered.





Synonym altered.





Synonym altered.



tonto(arepl202):/home/oracle>. oraenv

ORACLE_SID = [arepl202] ? areplm01

tonto(areplm01):/home/oracle>invalid

alter VIEW SYS.DBA_HIST_FILESTATXS compile ;

alter VIEW SYS.DBA_HIST_SQLSTAT compile ;

alter VIEW SYS.DBA_HIST_SQLBIND compile ;

alter VIEW SYS.DBA_HIST_SYSTEM_EVENT compile ;

alter VIEW SYS.DBA_HIST_WAITSTAT compile ;

alter VIEW SYS.DBA_HIST_LATCH compile ;

alter VIEW SYS.DBA_HIST_LATCH_MISSES_SUMMARY compile ;

alter VIEW SYS.DBA_HIST_DB_CACHE_ADVICE compile ;

alter VIEW SYS.DBA_HIST_ROWCACHE_SUMMARY compile ;

alter VIEW SYS.DBA_HIST_SGASTAT compile ;

alter VIEW SYS.DBA_HIST_SYSSTAT compile ;

alter VIEW SYS.DBA_HIST_SYS_TIME_MODEL compile ;

alter VIEW SYS.DBA_HIST_OSSTAT compile ;

alter VIEW SYS.DBA_HIST_PARAMETER compile ;

alter VIEW SYS.DBA_HIST_SEG_STAT compile ;

alter VIEW SYS.DBA_HIST_ACTIVE_SESS_HISTORY compile ;

alter VIEW SYS.DBA_HIST_TABLESPACE_STAT compile ;

alter VIEW SYS.DBA_HIST_SERVICE_STAT compile ;

alter VIEW SYS.DBA_HIST_SERVICE_WAIT_CLASS compile ;

alter PACKAGE SYS.DBMS_SWRF_REPORT_INTERNAL compile BODY;

alter PACKAGE SYS.DBMS_SQLTUNE compile BODY;

21 rows selected.



alter public synonym DBA_HIST_FILESTATXS compile;

alter public synonym DBA_HIST_SQLSTAT compile;

alter public synonym DBA_HIST_SQLBIND compile;

alter public synonym DBA_HIST_SYSTEM_EVENT compile;

alter public synonym DBA_HIST_WAITSTAT compile;

alter public synonym DBA_HIST_LATCH compile;

alter public synonym DBA_HIST_LATCH_MISSES_SUMMARY compile;

alter public synonym DBA_HIST_DB_CACHE_ADVICE compile;

alter public synonym DBA_HIST_ROWCACHE_SUMMARY compile;

alter public synonym DBA_HIST_SGASTAT compile;

alter public synonym DBA_HIST_SYSSTAT compile;

alter public synonym DBA_HIST_SYS_TIME_MODEL compile;

alter public synonym DBA_HIST_OSSTAT compile;

alter public synonym DBA_HIST_PARAMETER compile;

alter public synonym DBA_HIST_SEG_STAT compile;

alter public synonym DBA_HIST_ACTIVE_SESS_HISTORY compile;

alter public synonym DBA_HIST_TABLESPACE_STAT compile;

alter public synonym DBA_HIST_SERVICE_STAT compile;

alter public synonym DBA_HIST_SERVICE_WAIT_CLASS compile;

19 rows selected.

View altered.

View altered.

Package body altered.

Synonym altered.

Synonym altered.

Synonym altered.

Synonym altered.

Synonym altered.

Synonym altered.
Synonym altered.
Synonym altered.
tonto(areplm01):/home/oracle>