Posts

enq:HW contention

-
If the event is constantly occurring in the database, just run the following query to identify the sessions encountering the HW contention 1.select * from v$session_wait where event='enq: HW - contention'; Determine the correct file and block number 2.select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(51236787) FILE#, DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(51236787) BLOCK# from dual; FILE#  BLOCK# 12 905139 here you will get file and block no  in the above query   " 51236787" is P3 value from V$session query 1. Optionally can check file name  by query - 3.select * from dba_data_files where file_id='12'; now get  segment name and owner from query:  4. select owner, segment_type, segment_name    from dba_extents    where file_id = 12    and 905139 between block_id and block_id + blocks - 1; Next, isolate the table name that references this LOB segment  using the following query: 5.  select * from all_lobs where segment_name=
Post a Comment
Read more

Overcome from large trace file issue

-
When you are facing large size trace file generation in oracle database then further space lack possible to occur. To limit size of trace file generation you need to modify this oracle parameter - Set max_dump_file_size parameter to a lower value as - SQL> show parameter dump_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_dump_file_size string unlimited SQL> alter system set max_dump_file_size='500M' scope=both sid='*'; System altered. SQL> show parameter dump_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_dump_file_size string 500M
Post a Comment
Read more

Reclaim space of deleted lob datatype

-
When we simply delete  some rows from a table having multiple lob datatype columns  the no space released from the data file. To reclaim space of delete lob datatype columns we need to use shrink  as follows- select 'alter table TABLE_NAME modify lob ('||column_name||') (SHRINK SPACE);' from user_tab_columns where table_name='TABLE_NAME' and data_type='BLOB'; Put your table name on above query and it will generate a script to shrink space occupied by lob segment.
Post a Comment
Read more

Start Enterprise Manager After Hostname Change

-
Some time we need to change hostname or ip address  of machine , the result is no listner and unable to start Enterprise Manager. To resolve this issue  thre are some simple steps we need to follow- 1. first change your host name in listner.ora file 2. execute following command to reconfigure enterprise manager-     emca -deconfig dbcontrol db emca -config dbcontrol db -repos recreate   or   emca -deconfig dbcontrol db -repos drop emca -config dbcontrol db -repos create  
Post a Comment
Read more