Two custom views [DDFS_TOTAL on DBA_DATA_FILES,
and FSH_TOTAL on DBA_FREE_SPACE]
which I create in the DBSNMP schema are now failing with
ORA-00942 after having set O7_DICTIONARY_ACCESSIBILITY=FALSE.
The database is 8.1.7.4 32-bit on Solaris8.
These views used to work with O7_DICTIONARY_ACCESSIBILITY=TRUE
For example, the query on DBA_DATA_FILES works. I only get the ORA-942
when I use the
query inside a CREATE VIEW. [see below]
Also, a Trace file for
ORA-00600: internal error code, arguments: [17067], [0], [], [], [], [],
[], []
Current SQL statement for this session:
SELECT * FROM "DBSNMP"."DDFS_TOTAL"
is generated in the user_dump_dest for each of such databases the first
time the automated scripts which create the view and then generate
reports are run after
setting O7_DICTIONARY_ACCESSIBILITY=FALSE
{The reason why I use the views DDFS_TOTAL and FSH_TOTAL is that I have a
generic set of
scripts working from 7.3.4 to 9.2.0 so I am not using some of the
advanced In-Line views}.
Other 8.0 to 8.1.7 instances with
O7_DICTIONARY_ACCESSIBILITY=TRUE.
9.2 instances work with O7_DICTIONARY_ACCESSIBILITY=FALSE.
However, the difference in 9.2 instances is that DBSNMP has the SELECT
ANY DICTIONARY
Privilege while in the 8.1.7 and below instances, DBSNMP has the
SELECT_CATALOG_ROLE Role.
{The SELECT_CATALOG_ROLE and SELECT ANY DICTIONARY grants to DBSNMP
are my own "extensions" run manually after
catsnmp.sql}
These are my View Definitions :
REM These views are created in the
remote (monitored) database
create or replace view fsh_total (tablespace_name, free_size, date_stamp)
as
select tablespace_name, sum(bytes)/1024/1024 free_size, trunc(sysdate)
from dba_free_space
group by tablespace_name, trunc(sysdate);
create or replace view ddfs_total (tablespace_name, total_size)
as
select tablespace_name, sum(bytes/1024/1024)
from dba_data_files
where status = 'AVAILABLE'
group by tablespace_name;
rem spool off
rem Do NOT put an EXIT as this script is called by FREE_SPACE_WARN !!
rem
See below [8.1.7.4 32-bit on Solaris 8] for the Errors I get
:
SQL> show user
USER is "DBSNMP"
SQL> select granted_role from dba_role_privs where grantee =
'DBSNMP';
GRANTED_ROLE
------------------------------
CONNECT
RESOURCE
SELECT_CATALOG_ROLE
SNMPAGENT
SQL> select privilege from dba_sys_privs where grantee =
'DBSNMP';
PRIVILEGE
----------------------------------------
CREATE ANY DIRECTORY
CREATE DATABASE LINK
CREATE PUBLIC SYNONYM
CREATE SESSION
DROP ANY DIRECTORY
SELECT ANY TABLE
UNLIMITED TABLESPACE
7 rows selected.
SQL> show user
USER is "DBSNMP"
SQL> l
1 select tablespace_name, sum(bytes)/1024/1024 free_size,
trunc(sysdate)
2 from dba_free_space
3* group by tablespace_name, trunc(sysdate)
SQL> /
TABLESPACE_NAME FREE_SIZE TRUNC(SYSDATE)
------------------------------ ---------- --------------------------
PLUMINDEX 717.554688 January 12 2004 00:00:00
PLUMTABLE 421.128906 January 12 2004 00:00:00
RBS 219.527344 January 12 2004 00:00:00
SYSDEFLT 149.019531 January 12 2004 00:00:00
SYSTEM 116.25 January 12 2004 00:00:00
TEMP 199.996094 January 12 2004 00:00:00
6 rows selected.
SQL> l
1 create or replace view fsh_total (tablespace_name, free_size,
date_stamp)
2 as
3 select tablespace_name, sum(bytes)/1024/1024 free_size, trunc(sysdate)
4 from dba_free_space
5* group by tablespace_name, trunc(sysdate)
SQL> /
from dba_free_space
*
ERROR at line 4:
ORA-00942: table or view does not exist
SQL> l4
4* from dba_free_space
SQL> c/dba/sys.dba
4* from sys.dba_free_space
SQL> /
from sys.dba_free_space
*
ERROR at line 4:
ORA-00942: table or view does not exist
SQL> l
1 create or replace view fsh_total (tablespace_name, free_size,
date_stamp)
2 as
3 select tablespace_name, sum(bytes)/1024/1024 free_size, trunc(sysdate)
4 from sys.dba_free_space
5* group by tablespace_name, trunc(sysdate)
SQL> del 1
SQL> l
1 as
2 select tablespace_name, sum(bytes)/1024/1024 free_size,
trunc(sysdate)
3 from sys.dba_free_space
4* group by tablespace_name, trunc(sysdate)
SQL> del 1
SQL> l
1 select tablespace_name, sum(bytes)/1024/1024 free_size,
trunc(sysdate)
2 from sys.dba_free_space
3* group by tablespace_name, trunc(sysdate)
SQL> /
TABLESPACE_NAME FREE_SIZE TRUNC(SYSDATE)
------------------------------ ---------- --------------------------
PLUMINDEX 717.554688 January 12 2004 00:00:00
PLUMTABLE 421.128906 January 12 2004 00:00:00
RBS 219.527344 January 12 2004 00:00:00
SYSDEFLT 149.019531 January 12 2004 00:00:00
SYSTEM 116.25 January 12 2004 00:00:00
TEMP 199.996094 January 12 2004 00:00:00
6 rows selected.
SQL>
Of course, I resolved the issue with GRANT SELECT ON
DBA_FREE_SPACE to DBSNMP.
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
http://hkchital.tripod.com
{last updated 05-Jan-04}
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
INET: hkchital@singnet.com.sg
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Mon Jan 12 2004 - 09:14:26 CST
