Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 215 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
OBIEE 12C Usage Tracking Dashboard Error
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
Please have your System Administrator look at the log for more details on this error. (HY000)
Could you please help me to fix the issue.
Thanks,
Sudheer
Answers
-
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?
0 -
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:
]]
0 -
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
0 -
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
0 -
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
0 -
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>
0
