Database Reference
In-Depth Information
Figure 11-1. Result of a query on the enhanced session wait view CV_$SESSION_WAIT
Users other than SYS may be given access to X_$SESSION_WAIT by granting SELECT privilege
on the view and creating a public synonym. Below are some DDL statements that mimic the
hierarchical approach of the built-in V$ and GV$ views. Additional database objects are created in
schema SITE_SYS, to avoid cluttering the data dictionary with site-specific objects. Privileges
on the views in schema SITE_SYS are then granted to SELECT_CATALOG_ROLE. The letter C
for custom in the view and synonym names is used to distinguish these views from the built-in
dynamic performance views. Use the public synonyms CV$SESSION_WAIT and CGV$SESSION_WAIT
to access enhanced versions of the views V_$SESSION_WAIT and GV_$SESSION_WAIT respectively.
SQL> GRANT SELECT ON x_$session_wait TO site_sys WITH GRANT OPTION;
Grant succeeded.
SQL> CREATE OR REPLACE VIEW site_sys.cgv_$session_wait AS
SELECT * FROM sys.x_$session_wait;
View created.
SQL> CREATE OR REPLACE VIEW site_sys.cv_$session_wait AS
SELECT * FROM sys.x_$session_wait WHERE inst_id=userenv('instance');
View created.
SQL> GRANT SELECT ON site_sys.cgv_$session_wait TO select_catalog_role;
Grant succeeded.
SQL> GRANT SELECT ON site_sys.cv_$session_wait TO select_catalog_role;
Grant succeeded.
SQL> CREATE OR REPLACE PUBLIC SYNONYM cgv$session_wait
FOR site_sys.cgv_$session_wait;
Synonym created.
SQL> CREATE OR REPLACE PUBLIC SYNONYM cv$session_wait FOR site_sys.cv_$session_wait;
Synonym created.
 
Search WWH ::




Custom Search