** Can not create view on a table whose select privilege was granted through roles. SQL> conn scott/tiger@athena Connected. SQL> select * from dba_data_files; select * from dba_data_files * ERROR at line 1: ORA-00942: table or view does not exist SQL> conn jyang/jyang@athena Connected. SQL> grant select_catalog_role to scott; Grant succeeded. SQL> conn scott/tiger@athena Connected. SQL> select * from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS ---------- ------------------------------ ---------- ---------- --------- RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ------------ --- ---------- ---------- ------------ ---------- ----------- D:\ORACLE\ORADATA\ATHENA\SYSTEM01.DBF 1 SYSTEM 450887680 55040 AVAILABLE 1 YES 3.4360E+10 4194302 1280 450822144 55032 D:\ORACLE\ORADATA\ATHENA\UNDOTBS01.DBF 2 UNDOTBS1 209715200 25600 AVAILABLE 2 YES 3.4360E+10 4194302 640 209649664 25592 FILE_NAME -------------------------------------------------------------------------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS ---------- ------------------------------ ---------- ---------- --------- RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ------------ --- ---------- ---------- ------------ ---------- ----------- D:\ORACLE\ORADATA\ATHENA\CWMLITE01.DBF 3 CWMLITE 20971520 2560 AVAILABLE 3 YES 3.4360E+10 4194302 80 20905984 2552 D:\ORACLE\ORADATA\ATHENA\DRSYS01.DBF 4 DRSYS 20971520 2560 AVAILABLE FILE_NAME -------------------------------------------------------------------------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS ---------- ------------------------------ ---------- ---------- --------- RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ------------ --- ---------- ---------- ------------ ---------- ----------- 4 YES 3.4360E+10 4194302 80 20905984 2552 D:\ORACLE\ORADATA\ATHENA\EXAMPLE01.DBF 5 EXAMPLE 156631040 19120 AVAILABLE 5 YES 3.4360E+10 4194302 80 156565504 19112 D:\ORACLE\ORADATA\ATHENA\INDX01.DBF FILE_NAME -------------------------------------------------------------------------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS ---------- ------------------------------ ---------- ---------- --------- RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ------------ --- ---------- ---------- ------------ ---------- ----------- 6 INDX 27525120 3360 AVAILABLE 6 YES 3.4360E+10 4194302 160 27459584 3352 D:\ORACLE\ORADATA\ATHENA\ODM01.DBF 7 ODM 20971520 2560 AVAILABLE 7 YES 3.4360E+10 4194302 80 20905984 2552 FILE_NAME -------------------------------------------------------------------------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS ---------- ------------------------------ ---------- ---------- --------- RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ------------ --- ---------- ---------- ------------ ---------- ----------- D:\ORACLE\ORADATA\ATHENA\TOOLS01.DBF 8 TOOLS 10485760 1280 AVAILABLE 8 YES 3.4360E+10 4194302 40 10420224 1272 D:\ORACLE\ORADATA\ATHENA\USERS01.DBF 9 USERS 201850880 24640 AVAILABLE 9 YES 3.4360E+10 4194302 160 201785344 24632 FILE_NAME -------------------------------------------------------------------------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS ---------- ------------------------------ ---------- ---------- --------- RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ------------ --- ---------- ---------- ------------ ---------- ----------- D:\ORACLE\ORADATA\ATHENA\XDB01.DBF 10 XDB 39976960 4880 AVAILABLE 10 YES 3.4360E+10 4194302 80 39911424 4872 D:\ORACLE\ORADATA\ATHENA\OEM_REPOSITORY.DBF 11 OEM_REPOSITORY 36708352 4481 AVAILABLE FILE_NAME -------------------------------------------------------------------------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS ---------- ------------------------------ ---------- ---------- --------- RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ------------ --- ---------- ---------- ------------ ---------- ----------- 11 YES 2097152000 256000 640 36634624 4472 D:\ORACLE\ORADATA\ATHENA\LOGMINER.ORA 12 LOGMINER 26214400 3200 AVAILABLE 12 NO 0 0 0 26148864 3192 12 rows selected. SQL> create view t_vw as select * from dba_data_files; create view t_vw as select * from dba_data_files * ERROR at line 1: ORA-01031: insufficient privileges SQL> conn jyang/jyang@athena Connected. SQL> grant select on dba_data_files to scott; grant select on dba_data_files to scott * ERROR at line 1: ORA-01031: insufficient privileges SQL> conn sys/athena@athena as sysdba Connected. SQL> grant select on dba_data_files to scott; Grant succeeded. SQL> conn scott/tiger@athena Connected. SQL> create view t_vw as select * from dba_data_files; View created. SQL> drop view t_vw; View dropped. SQL> conn sys/athena@athena as sysdba Connected. SQL> revoke select on dba_data_files from scott; Revoke succeeded. SQL> revoke select_catalog_role from scott; Revoke succeeded. SQL>