-
Title
Receiving "ORA-942" errors for collections Ora_IO_Datafile, Ora_IO_Tablespace and Ora_Summary. -
Description
Receiving ORA-942 errors for collections Ora_IO_Datafile, Ora_IO_Tablespace and Ora_Summary.
-
Cause
Apossible cause for the problemmight be with either one of the following tables: sys.ts$, sys.x_$ktfbhc, sys.dba_tablespaces. In order to confirm run the following queries (using the Oracle agent user login):
SELECT count(*) FROM sys.ts$
SELECT count(*) FROM sys.x_$ktfbhc
SELECT count(*) FROM sys.dba_tablespaces
None of these statements should fail. If they do with the message ERROR at line 1:
ORA-00942: table or view does not exist, then the Oracle agent user login should have the correct permissions on these tables. -
Resolution
If the sys.x$ktfbhc table query fails, It seems that the privileges granted to the sys.x$ktfbhc have not been successfully executed. We access this table to get memory information. The table also summarizes free space with one row per datafile.
Because the X$ tables are only visible to the SYS schema and because it would be bad practice to do anything as SYS unnecessarily, this scripts require that one create a set of X_$ views that expose the X$ tables to other DBA schemata. Thedownloadable script ORA-942ErrorScrpt, creates an X_$ view for the x$ktfbhc table. It must be run as SYS from SQL*Plus. There should be no errors running the script.1. Download the script from the following linkhttps://support.quest.com/SUPPORT/index?page=downloadsentitlement&file=2947791
2. log into sqlplus asSYS user and run the following from the command line @ ORA-942ErrorScrpt
Once the script is run, confirm that the Oracle agent user will have the correct permissions by executing the following statement after logging in as FOGLIGHT:
select count(*) from sys.x_$ktfbhc;
Restart the agent, and there should be no more errors for the Ora_IO_Datafile, Ora_IO_Tablespace and Ora_Summary collections in the logs.
----------------------------------------------------Contact support if the following queries fail.
SELECT count(*) FROM sys.ts$
SELECT count(*) FROM sys.dba_tablespaces