Wednesday, December 25, 2013

enq: FB - contention

Was working on splitting a partition from max (about 300 Million Records) to month wise and happened to notice this wait event.

What is this event means?
This is associated with block formatting and it ensures that only one process format the blocks in an ASSM tablespace.
As I mentioned earlier, I was splitting a partition with 8 parallels and each sessions doing insert and could be that each session tried to format the block which caused the contention.
Below query will give you the details
select session_id, sql_id, event, p1, p1text, p2 "TS#", p3 "Block", wait_time, session_state, time_waited
  from gv$active_session_history
 where event like '%FB%'
   and sql_id = 'dg8ksy7wvv1nd'; --replace sqlid here

Query v$tablespace for the returned TS# to find out which tablespace.
Use below query to find the block and database file associated with it.

select dbms_utility.data_block_address_block(p3 value from above query) "BLOCK",
         dbms_utility.data_block_address_file(p3 value from above query) "FILE"
  from dual;

Tuesday, December 10, 2013

ORA-39168: Object path TABLE was not found. ORA-31655: no data or metadata objects selected for job

ORA-39168: Object path TABLE was not found.
ORA-31655: no data or metadata objects selected for job

Was getting the above error while trying to export using expdp (not my cup of coffee with this beast).
Basically, I was trying to export selected tables from a schema using INCLUDE=TABLE:"IN (select query

Problem was I removed SCHEMAS param in the parameter and thus it was throwing this error.

Correct Syntax (Unix):
SCHEMAS=schema_name_here
INCLUDE=TABLE:"IN (select table_name from other_schema.final_export_tabs where schema_name = 'schema_name_here' and notes is null)"

I am still trying to find a way to pass schema name along with the table so that I can try getting one export dump with multiple schemas and selected tables only... if some can find it please reply to me with the solution. Thanks in advance :)

Never mind, I figured that out.
Here is the syntax:
SCHEMAS=schema_name1,schema_name2,schema_name3
INCLUDE=TABLE:"IN (select table_name from other_schema.final_export_tabs where notes is null)"

I was worried that it might fail as there are tables which have same name in multiple schemas but no issues as expdp got them from both schemas without any issues.