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

Comments

Post a Comment

Popular posts from this blog

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=
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.
Read more