OBIEE 12C Usage Tracking Dashboard Error

2cc95da3-ac23-4bc7-9673-0f20f0d79379

Hi All,

when i am trying to run the top usage report in OBIEE 12c ,i am getting below error

OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.

State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS.

State: HY000. Code: 43119. [nQSError: 43119] Query Failed: 

State: HY000. Code: 17001. 
Please have your System Administrator look at the log for more details on this error. (HY000)

State: HY000. Code: 17010. 

SQL Issued: SELECT 0 s_0, "Usage Tracking"."Personal Data"."Name" s_1, "Usage Tracking"."Users"."User Name" s_2, "Usage Tracking"."Measures"."Query Count" s_3, MAX("Usage Tracking"."Calendar"."Date") s_4 FROM "Usage Tracking" FETCH FIRST 10000000 ROWS ONLY

Could you please help me to fix the issue.

Thanks,

Sudheer

Joel

You'll need to be a bit more specific. Have you looked at the query logs to see the error. Does the query run when run directly on the underlying RCU database?

2cc95da3-ac23-4bc7-9673-0f20f0d79379

Query has ran but the data is not displaying in the database.

for more details ,below the query log session

[2018-10-16T10:29:17.583+05:30] [OBIS] [TRACE:7] [] [] [ecid: c22acb8d-6565-400e-a38e-7f9b747e1b80-00045b4b,0:1:49:3] [sik: ssi] [tid: 6a] [messageid: USER-0] [requestid: 1116001d] [sessionid: 11160000] [username: weblogic] ############################################## [[

-------------------- SQL Request, logical request hash:

96a3f9e

SET VARIABLE QUERY_SRC_CD='Report',SAW_DASHBOARD='/shared/Usage Tracking Dashboard/_portal/Usage Tracking Dashboard',SAW_DASHBOARD_PG='Top Usage',SAW_SRC_PATH='/shared/Usage Tracking Dashboard/Usage Monitoring Reports/Top Users';SELECT

   0 s_0,

   "Usage Tracking"."Personal Data"."Name" s_1,

   "Usage Tracking"."Users"."User Name" s_2,

   "Usage Tracking"."Measures"."Query Count" s_3,

   MAX("Usage Tracking"."Calendar"."Date") s_4

FROM "Usage Tracking"

ORDER BY 5 DESC NULLS FIRST, 3 ASC NULLS LAST, 2 ASC NULLS LAST

FETCH FIRST 10000000 ROWS ONLY

]]

[2018-10-16T10:29:17.584+05:30] [OBIS] [TRACE:7] [] [] [ecid: c22acb8d-6565-400e-a38e-7f9b747e1b80-00045b4b,0:1:49:3] [sik: ssi] [tid: 6a] [messageid: USER-74] [requestid: 1116001d] [sessionid: 11160000] [username: weblogic] -------------------- Logical query limits: max query time(user, system, session) = (0, 0, 0); max rows(system, session) = (0, 0)

[2018-10-16T10:29:17.584+05:30] [OBIS] [TRACE:7] [] [] [ecid: c22acb8d-6565-400e-a38e-7f9b747e1b80-00045b4b,0:1:49:3] [sik: ssi] [tid: 6a] [messageid: USER-23] [requestid: 1116001d] [sessionid: 11160000] [username: weblogic] -------------------- General Query Info: [[

Repository: ssi, Subject Area: Usage Tracking, Presentation: Usage Tracking

]]

[2018-10-16T10:29:17.587+05:30] [OBIS] [TRACE:7] [] [] [ecid: c22acb8d-6565-400e-a38e-7f9b747e1b80-00045b4b,0:1:49:3] [sik: ssi] [tid: 6a] [messageid: USER-2] [requestid: 1116001d] [sessionid: 11160000] [username: weblogic] -------------------- Logical Request (before navigation): [[

RqList [1,2]

    0 as c1 GB,

    Personal Data.Name as c2 GB,

    Users.User Name as c3 GB,

    Query Count:[DAggr(Measures.Query Count by [ Users.User Name, Personal Data.Name] )] as c4 GB,

    max(Calendar.Date by [ Users.User Name, Personal Data.Name] ) as c5 GB

OrderBy: c5 desc NULLS FIRST, c3 asc NULLS LAST, c2 asc NULLS LAST

]]

[2018-10-16T10:29:17.591+05:30] [OBIS] [TRACE:7] [] [] [ecid: c22acb8d-6565-400e-a38e-7f9b747e1b80-00045b4b,0:1:49:5] [sik: ssi] [tid: 6a] [messageid: USER-53] [requestid: 1116001d] [sessionid: 11160000] [username: weblogic] -------------------- List of attributes and their LTS sources: [[

Column Query Count:[DAggr(Measures.Query Count by [ Users.User Name, Personal Data.Name] )] rendered via fact LTS [Logical table sources (Priority=0, SystemGenerated=False): Measures.NQSACCT]

]]

[2018-10-16T10:29:17.592+05:30] [OBIS] [TRACE:7] [] [] [ecid: c22acb8d-6565-400e-a38e-7f9b747e1b80-00045b4b,0:1:49:5] [sik: ssi] [tid: 6a] [messageid: USER-48] [requestid: 1116001d] [sessionid: 11160000] [username: weblogic] -------------------- The logical query block failed to hit or seed the cache in subrequest level due to   [[

only one subrequest

]]

[2018-10-16T10:29:17.592+05:30] [OBIS] [TRACE:7] [] [] [ecid: c22acb8d-6565-400e-a38e-7f9b747e1b80-00045b4b,0:1:49:5] [sik: ssi] [tid: 6a] [messageid: USER-53] [requestid: 1116001d] [sessionid: 11160000] [username: weblogic] -------------------- List of attributes and their LTS sources: [[

Select: Users.User Name: Logical table sources (Priority=0, SystemGenerated=False): Users.NQ_USERGROUP

Select: Calendar.Date: Logical table sources (Priority=0, SystemGenerated=False): Calendar.NQ_CALENDAR

Select: Personal Data.Name: Logical table sources (Priority=0, SystemGenerated=False): Personal Data.Personal Data

]]

[2018-10-16T10:29:17.595+05:30] [OBIS] [TRACE:7] [] [] [ecid: c22acb8d-6565-400e-a38e-7f9b747e1b80-00045b4b,0:1:49:5] [sik: ssi] [tid: 6a] [messageid: USER-51] [requestid: 1116001d] [sessionid: 11160000] [username: weblogic] -------------------- The logical plan contains non-cacheable table or expression: S_ETL_DAY T176599 [[

plan

RqList <<9791025>>

    D1.c1 as c1,

    D1.c2 as c2,

    D1.c3 as c3,

    D1.c4 as c4,

    D1.c5 as c5

Child Nodes (RqJoinSpec): <<9791040>>

    RqJoinNode <<9791039>> []

(

RqBreakFilter <<9791024>>[2,3]

RqList <<9790910>>

0 as c1 GB,

D1.c3 as c2 GB,

D1.c4 as c3 GB,

D1.c1 as c4 GB,

D1.c2 as c5 GB

Child Nodes (RqJoinSpec): <<9791018>>

RqJoinNode <<9791017>> []

(

RqList <<9790917>>

count(S_NQ_ACCT.QUERY_TEXT by [ NQ_LOGIN_GROUP.LOGIN] ) as c1 GB,

max(S_ETL_DAY.DAY_DT by [ NQ_LOGIN_GROUP.LOGIN, Personal Data.Name] ) as c2 GB,

Personal Data.Name as c3 GB,

NQ_LOGIN_GROUP.LOGIN as c4 GB

Child Nodes (RqJoinSpec): <<9790999>>

RqJoinNode <<9790998>> []

                                        S_ETL_DAY T176599

InnerJoin <<9790991>> On S_NQ_ACCT.START_DT = S_ETL_DAY.DAY_DT

                                            (SELECT DISTINCT USER_NAME AS LOGIN, NULL AS RESP FROM S_NQ_ACCT) as T176573

InnerJoin <<9790990>> On NQ_LOGIN_GROUP.LOGIN = S_NQ_ACCT.USER_NAME

S_NQ_ACCT T176576 LeftOuterJoin <<9790996>> On NQ_LOGIN_GROUP.LOGIN = Personal Data.Emp_ID

                                        (select distinct 'u'||emplid as emp_id,name from wc_emp_personal_data_d

) as T376998

) as D1

) as D1

OrderBy: c5 desc NULLS FIRST, c3 asc NULLS LAST, c2 asc NULLS LAST

]]

[2018-10-16T10:29:17.703+05:30] [OBIS] [TRACE:7] [] [] [ecid: c22acb8d-6565-400e-a38e-7f9b747e1b80-00045b4b,0:1:49:5] [sik: ssi] [tid: 6a] [messageid: USER-50] [requestid: 1116001d] [sessionid: 11160000] [username: weblogic] -------------------- The logical query seed the plan cache [[

plan

RqList <<9791161>> [for database 0:0,0] /* FETCH FIRST 10000000 ROWS ONLY */

    D1.c1 as c1 [for database 3023:66173:Oracle Analytics Usage,57],

    D1.c2 as c2 [for database 3023:66173:Oracle Analytics Usage,57],

    D1.c3 as c3 [for database 3023:66173:Oracle Analytics Usage,57],

    D1.c4 as c4 [for database 3023:66173:Oracle Analytics Usage,57],

    D1.c5 as c5 [for database 3023:66173:Oracle Analytics Usage,57]

Child Nodes (RqJoinSpec): <<9791173>> [for database 0:0,0]

    RqJoinNode <<9791174>> []

(

RqList <<9791177>> [for database 3023:66173:Oracle Analytics Usage,57]

0 as c1 GB [for database 3023:66173:Oracle Analytics Usage,57],

D1.c3 as c2 GB [for database 3023:66173:Oracle Analytics Usage,57],

D1.c4 as c3 GB [for database 3023:66173:Oracle Analytics Usage,57],

D1.c1 as c4 GB [for database 3023:66173:Oracle Analytics Usage,57],

D1.c2 as c5 GB [for database 3023:66173:Oracle Analytics Usage,57]

Child Nodes (RqJoinSpec): <<9791189>> [for database 3023:66173:Oracle Analytics Usage,57]

RqJoinNode <<9791190>> []

(

RqList <<9791193>> [for database 3023:66173:Oracle Analytics Usage,57]

sum_SQL99(D1.c1 by [ D1.c4] ) as c1 [for database 3023:66173:Oracle Analytics Usage,57],

D1.c2 as c2 [for database 3023:66173:Oracle Analytics Usage,57],

D1.c3 as c3 [for database 3023:66173:Oracle Analytics Usage,57],

D1.c4 as c4 [for database 3023:66173:Oracle Analytics Usage,57]

Child Nodes (RqJoinSpec): <<9791206>> [for database 3023:66173:Oracle Analytics Usage,57]

RqJoinNode <<9791207>> []

(

RqList <<9791210>> [for database 3023:66173:Oracle Analytics Usage,57]

count(S_NQ_ACCT.QUERY_TEXT by [ NQ_LOGIN_GROUP.LOGIN, Personal Data.Name] ) as c1 GB [for database 3023:66173:Oracle Analytics Usage,57],

max(S_ETL_DAY.DAY_DT by [ NQ_LOGIN_GROUP.LOGIN, Personal Data.Name] ) as c2 GB [for database 3023:66173:Oracle Analytics Usage,57],

Personal Data.Name as c3 GB [for database 3023:66173:Oracle Analytics Usage,57],

NQ_LOGIN_GROUP.LOGIN as c4 GB [for database 3023:66173:Oracle Analytics Usage,57]

Child Nodes (RqJoinSpec): <<9791228>> [for database 3023:66173:Oracle Analytics Usage,57]

RqJoinNode <<9791229>> []

                                                S_ETL_DAY T176599

InnerJoin <<9791231>> On S_NQ_ACCT.START_DT = S_ETL_DAY.DAY_DT

                                                    (SELECT DISTINCT USER_NAME AS LOGIN, NULL AS RESP FROM S_NQ_ACCT) as T176573

InnerJoin <<9791236>> On NQ_LOGIN_GROUP.LOGIN = S_NQ_ACCT.USER_NAME

S_NQ_ACCT T176576 LeftOuterJoin <<9791230>> On NQ_LOGIN_GROUP.LOGIN = Personal Data.Emp_ID

                                                (select distinct 'u'||emplid as emp_id,name from wc_emp_personal_data_d

) as T376998

GroupBy: [ NQ_LOGIN_GROUP.LOGIN, Personal Data.Name]  [for database 3023:66173:Oracle Analytics Usage,57]

) as D1

) as D1

OrderBy: c5 desc NULLS FIRST, c3 asc NULLS LAST, c2 asc NULLS LAST [for database 3023:66173:Oracle Analytics Usage,57]

) as D1

]]

[2018-10-16T10:29:17.704+05:30] [OBIS] [TRACE:7] [] [] [ecid: c22acb8d-6565-400e-a38e-7f9b747e1b80-00045b4b,0:1:49:5] [sik: ssi] [tid: 6a] [messageid: USER-16] [requestid: 1116001d] [sessionid: 11160000] [username: weblogic] -------------------- Execution plan: [[

RqList <<9791117>> [for database 0:0,0] /* FETCH FIRST 10000000 ROWS ONLY */

    D1.c1 as c1 [for database 3023:66173:Oracle Analytics Usage,57],

    D1.c2 as c2 [for database 3023:66173:Oracle Analytics Usage,57],

    D1.c3 as c3 [for database 3023:66173:Oracle Analytics Usage,57],

    D1.c4 as c4 [for database 3023:66173:Oracle Analytics Usage,57],

    D1.c5 as c5 [for database 3023:66173:Oracle Analytics Usage,57]

Child Nodes (RqJoinSpec): <<9791138>> [for database 0:0,0]

    RqJoinNode <<9791137>> []

(

RqList <<9790910>> [for database 3023:66173:Oracle Analytics Usage,57]

0 as c1 GB [for database 3023:66173:Oracle Analytics Usage,57],

D1.c3 as c2 GB [for database 3023:66173:Oracle Analytics Usage,57],

D1.c4 as c3 GB [for database 3023:66173:Oracle Analytics Usage,57],

D1.c1 as c4 GB [for database 3023:66173:Oracle Analytics Usage,57],

D1.c2 as c5 GB [for database 3023:66173:Oracle Analytics Usage,57]

Child Nodes (RqJoinSpec): <<9791018>> [for database 3023:66173:Oracle Analytics Usage,57]

RqJoinNode <<9791017>> []

(

RqList <<9791084>> [for database 3023:66173:Oracle Analytics Usage,57]

sum_SQL99(D1.c1 by [ D1.c4] ) as c1 [for database 3023:66173:Oracle Analytics Usage,57],

D1.c2 as c2 [for database 3023:66173:Oracle Analytics Usage,57],

D1.c3 as c3 [for database 3023:66173:Oracle Analytics Usage,57],

D1.c4 as c4 [for database 3023:66173:Oracle Analytics Usage,57]

Child Nodes (RqJoinSpec): <<9791102>> [for database 3023:66173:Oracle Analytics Usage,57]

RqJoinNode <<9791101>> []

(

RqList <<9790917>> [for database 3023:66173:Oracle Analytics Usage,57]

count(S_NQ_ACCT.QUERY_TEXT by [ NQ_LOGIN_GROUP.LOGIN, Personal Data.Name] ) as c1 GB [for database 3023:66173:Oracle Analytics Usage,57],

max(S_ETL_DAY.DAY_DT by [ NQ_LOGIN_GROUP.LOGIN, Personal Data.Name] ) as c2 GB [for database 3023:66173:Oracle Analytics Usage,57],

Personal Data.Name as c3 GB [for database 3023:66173:Oracle Analytics Usage,57],

NQ_LOGIN_GROUP.LOGIN as c4 GB [for database 3023:66173:Oracle Analytics Usage,57]

Child Nodes (RqJoinSpec): <<9790999>> [for database 3023:66173:Oracle Analytics Usage,57]

RqJoinNode <<9790998>> []

                                                S_ETL_DAY T176599

InnerJoin <<9790991>> On S_NQ_ACCT.START_DT = S_ETL_DAY.DAY_DT

                                                    (SELECT DISTINCT USER_NAME AS LOGIN, NULL AS RESP FROM S_NQ_ACCT) as T176573

InnerJoin <<9790990>> On NQ_LOGIN_GROUP.LOGIN = S_NQ_ACCT.USER_NAME

S_NQ_ACCT T176576 LeftOuterJoin <<9790996>> On NQ_LOGIN_GROUP.LOGIN = Personal Data.Emp_ID

                                                (select distinct 'u'||emplid as emp_id,name from wc_emp_personal_data_d

) as T376998

GroupBy: [ NQ_LOGIN_GROUP.LOGIN, Personal Data.Name]  [for database 3023:66173:Oracle Analytics Usage,57]

) as D1

) as D1

OrderBy: c5 desc NULLS FIRST, c3 asc NULLS LAST, c2 asc NULLS LAST [for database 3023:66173:Oracle Analytics Usage,57]

) as D1

]]

[2018-10-16T10:29:17.729+05:30] [OBIS] [TRACE:7] [] [] [ecid: c22acb8d-6565-400e-a38e-7f9b747e1b80-00045b4b,0:1:49:5] [sik: ssi] [tid: 6a] [messageid: USER-18] [requestid: 1116001d] [sessionid: 11160000] [username: weblogic] -------------------- Sending query to database named Oracle Analytics Usage (id: <<9790910>>), connection pool named Usage Tracking Connection Pool, logical request hash 96a3f9e, physical request hash 1233fa0d: [[

select distinct 0 as c1,

     D1.c3 as c2,

     D1.c4 as c3,

     D1.c1 as c4,

     D1.c2 as c5

from

     (select sum(D1.c1) over (partition by D1.c4)  as c1,

D1.c2 as c2,

D1.c3 as c3,

D1.c4 as c4

from

               (select count(T176576.QUERY_TEXT) as c1,

max(T176599.DAY_DT) as c2,

T376998.Name as c3,

T176573.LOGIN as c4

from

                         (

S_ETL_DAY T176599 inner join (

(SELECT DISTINCT USER_NAME AS LOGIN, NULL AS RESP FROM S_NQ_ACCT) T176573 inner join S_NQ_ACCT T176576 On T176573.LOGIN = T176576.USER_NAME) On T176576.START_DT = T176599.DAY_DT) left outer join

                              (select distinct 'u'||emplid as emp_id,name from wc_emp_personal_data_d

) T376998 On T176573.LOGIN = T376998.Emp_ID

group by T176573.LOGIN, T376998.Name

) D1

     ) D1

order by c5 desc, c3, c2

]]

[2018-10-16T10:29:17.731+05:30] [OBIS] [TRACE:7] [] [] [ecid: c22acb8d-6565-400e-a38e-7f9b747e1b80-00045b4b,0:1:49:5:1] [sik: ssi] [tid: 12] [messageid: USER-73] [requestid: 1116001d] [sessionid: 11160000] [username: weblogic] -------------------- Physical query limits: max time = 0; max rows = 0; max data size = 0

[2018-10-16T10:29:17.830+05:30] [OBIS] [TRACE:7] [] [] [ecid: c22acb8d-6565-400e-a38e-7f9b747e1b80-00045b4b,0:1:49:5:1] [sik: ssi] [tid: 12] [messageid: USER-37] [requestid: 1116001d] [sessionid: 11160000] [username: weblogic] -------------------- Query execution terminated: <<9790910>>

[2018-10-16T10:29:18.12+05:30] [OBIS] [TRACE:7] [] [] [ecid: c22acb8d-6565-400e-a38e-7f9b747e1b80-00045b4b,0:1:49:5] [sik: ssi] [tid: 6a] [messageid: USER-20] [requestid: 1116001d] [sessionid: 11160000] [username: weblogic] -------------------- Execution Node for logical request hash 96a3f9e : <<9791117>> Projection, Close Row Count = 0, Row Width = 0 bytes, Temporary file size = 0 bytes

[2018-10-16T10:29:18.12+05:30] [OBIS] [TRACE:7] [] [] [ecid: c22acb8d-6565-400e-a38e-7f9b747e1b80-00045b4b,0:1:49:5] [sik: ssi] [tid: 6a] [messageid: USER-82] [requestid: 1116001d] [sessionid: 11160000] [username: weblogic] -------------------- Execution Node for logical request hash 96a3f9e : <<9791117>> DbGateway Exchange: Oracle Analytics Usage.Usage Tracking Connection Pool Consumer , Close Row Count = 1, Row Width = 0 bytes, Temporary file size = 0 bytes, io size = 0 bytes

[2018-10-16T10:29:18.13+05:30] [OBIS] [TRACE:7] [] [] [ecid: c22acb8d-6565-400e-a38e-7f9b747e1b80-00045b4b,0:1:49:5] [sik: ssi] [tid: 6a] [messageid: USER-34] [requestid: 1116001d] [sessionid: 11160000] [username: weblogic] -------------------- Query Status: [nQSError: 17010] SQL statement preparation failed. [[

[nQSError: 17001] Oracle Error code: 942, message: ORA-00942: table or view does not exist

at OCI call OCIStmtExecute.

[nQSError: 43119] Query Failed:

]]

2cc95da3-ac23-4bc7-9673-0f20f0d79379

I could see the below error in the log file

The logical query block failed to hit or seed the cache in subrequest level due to   [[

only one subrequest

Joel

I can see this:

[nQSError: 17001] Oracle Error code: 942, message: ORA-00942: table or view does not exist

at OCI call OCIStmtExecute.

[nQSError: 43119] Query Failed:

It looks like the DB user used to run this is missing permissions on some tables. What happens when you run this from your log directly in SQL Developer or TOAD?

select distinct 0 as c1,

     D1.c3 as c2,

     D1.c4 as c3,

     D1.c1 as c4,

     D1.c2 as c5

from

     (select sum(D1.c1) over (partition by D1.c4)  as c1,

D1.c2 as c2,

D1.c3 as c3,

D1.c4 as c4

from

               (select count(T176576.QUERY_TEXT) as c1,

max(T176599.DAY_DT) as c2,

T376998.Name as c3,

T176573.LOGIN as c4

from

                         (

S_ETL_DAY T176599 inner join (

(SELECT DISTINCT USER_NAME AS LOGIN, NULL AS RESP FROM S_NQ_ACCT) T176573 inner join S_NQ_ACCT T176576 On T176573.LOGIN = T176576.USER_NAME) On T176576.START_DT = T176599.DAY_DT) left outer join

                              (select distinct 'u'||emplid as emp_id,name from wc_emp_personal_data_d

) T376998 On T176573.LOGIN = T376998.Emp_ID

group by T176573.LOGIN, T376998.Name

) D1

     ) D1

order by c5 desc, c3, c2

2cc95da3-ac23-4bc7-9673-0f20f0d79379

If i ran the above query in all environment SQL Devlopers ,it showing the same error like below

ORA-00942: table or view does not exist

Gianni Ceresa

As Joel pointed out there is your issue.

A standard Oracle database error.

Are you using the BIPLATFORM account to query that table? Because you access the tables directly without a schema, so it can only work if you use the real owner of those tables, by default BIPLATFORM.

If you are using a different user you need to define the schema which contains those tables in the RPD, so the query will be SELECT ... FROM <schema>.<table>