2 Replies Latest reply on Nov 16, 2012 3:25 PM by j_DBA_sourav

    create table statement goes to hang state..in ODI.. in BI database

    j_DBA_sourav
      Our development team is trying to create an external tabe to another database's schema. ODI is creating the connection bridge inside. I don't know how. But whenever they fire this query it is going to hang state. where as same code is running fine from another database.

      The users by which they are connceting to the source database to fire this query are having same privileges.
      For one database it is working fine as the table is created at the target database and in proper schema but for another it is not working and hanging...(not giving any result)

      Databases are used for BI work and having almost same configurations.
      I have checked the Target database v$session view. It is giving me <enq: PS - contention> event
      and three rows are returning with <PX Deq: Table Q Close> event for other two following rows....

      can anybody throw some light on this? waht should I do now. as killing the blocking session is not solving the problems


      Here is an example of the query executed :

      CREATE TABLE SCHEMA_NAME.C$_0ODS_JDE_F0004
      ( C1_DTSY, C2_DTUCD1, C3_DTRT, C4_DTJOBN, C5_DTUPMJ_DT, C6_DTMRTY, C7_DTDL01, C8_DTPID,
      C9_DTMRCT, C10_DTLN2, C11_DTCNUM, C12_DTUSEQ, C13_DTUSER, C14_DTCDL, JRN_SUBSCRIBER, JRN_FLAG, JRN_DATE )
      ORGANIZATION EXTERNAL
      ( TYPE oracle_datapump
      DEFAULT DIRECTORY DIR_SCHEMA_NAME
      LOCATION ('X58030417C$_0ODS_JDE_F0004_1.EXP' , 'X58030417C$_0ODS_JDE_F0004_2.EXP')
      )
      PARALLEL 2 AS
      SELECT
      NVL(TRIM(F0004.DTSY), ' ') C1_DTSY, NVL(TRIM(F0004.DTUCD1), ' ') C2_DTUCD1, F0004.DTRT C3_DTRT,
      TRIM(F0004.DTJOBN) C4_DTJOBN, J2DT(DTUPMJ, DTUPMT) C5_DTUPMJ_DT, NVL(TRIM(F0004.DTMRTY), ' ') C6_DTMRTY,
      TRIM(F0004.DTDL01) C7_DTDL01, TRIM(F0004.DTPID) C8_DTPID, F0004.DTMRCT C9_DTMRCT, F0004.DTLN2 C10_DTLN2,
      F0004.DTCNUM C11_DTCNUM, F0004.DTUSEQ/10 C12_DTUSEQ, TRIM(F0004.DTUSER) C13_DTUSER,
      F0004.DTCDL C14_DTCDL, ' ' AS JRN_SUBSCRIBER, 'I' AS JRN_FLAG, SYSDATE AS JRN_DATE
      FROM SCHEMA_NAME.F0004 F0004 WHERE (1=1)

      -Regards,
      Saha