Index Range Scan and db file scattered read as session wait event

I was asked to check a session which was running for more than expected time on 11.2.0.3 single instance database.The session was running a simple select statement 05:03:09 SYS@xx4040 > @sqlid 9kpjtkdz1mzus old 9: sql_id = ('&1') new 9: sql_id = ('9kpjtkdz1mzus') SQL_ID HASH_VALUE SQL_TEXT ------------- ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------ 9kpgmndz1mzus 2115632984 SELECT SC.ID FROM user.s_seq_cd SC … Continue reading Index Range Scan and db file scattered read as session wait event

Advertisement

Index Sample Size

Recently i was asked a question, what would be the sample size percentage for indexes when tables stats are gathered with estimate percent of 5 and cascade set to true.My answer to it was 5 within a second.I had this believe that the sample size percentage for an index is same to the estimate_percent given … Continue reading Index Sample Size

Change in Database link – 11gR2

A little change in dblink from 11gR2 - SQL> conn anand/anand123 Connected. SQL> SQL> @db_link OWNER DB_LINK USERNAME HOST ------------------------------ ------------------------------ ------------------------------ ------------------------------ ANAND SCOTT_DBLINK SCOTT MATSTDBY SQL> select sysdate from dual@SCOTT_DBLINK; SYSDATE --------- 11-MAY-12 I login as sys and change scott schema's password - SQL> conn / as sysdba Connected. SQL> SQL> SQL> SQL> … Continue reading Change in Database link – 11gR2

ORA-20003: You are not allowed to alter password user

Today while working on UAT database refresh activity, had a step to change all schemas password to a common one. begin for x in (select username from dba_users ) loop execute immediate 'alter user '||x.username||' identified by xxxxxx profile DEFAULT'; end loop; end; / The above was executed as SYS user and it errored out … Continue reading ORA-20003: You are not allowed to alter password user

ORA-00600:[kcbsor_2], [3], [2]

Recently we had a node crash of one node in 3-node 11.2.0.3 RAC database configuration.The alert log showed - Thu Apr 26 00:15:16 2012 Hex dump of (file 488, block 345039) in trace file /u01/oradiag/diag/rdbms/matrix_adc/matrix3/trace/matrix3_dbw1_16218.trc Corrupt block relative dba: 0x7a0543cf (file 488, block 345039) Bad header found during preparing block for write Data in bad … Continue reading ORA-00600:[kcbsor_2], [3], [2]