This conclusion to a three-part article series on using views in PHP and Oracle picks up where part two left off, discussing different kinds of views. You'll also learn how to use views to restrict access, and in web applications. This article is excerpted from chapter 35 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
Dynamic Performance Views
Dynamic performance views are similar to data dictionary views, with one important difference: dynamic performance views are continuously updated while the database is open; they are repopulated when the database is shut down and restarted. In other words, the contents of these views are not based on any physical table and instead reside in memory only. The contents of dynamic performance views primarily relate to the performance of the database. Dynamic performance views begin with the prefix V$to help distinguish them from data dictionary views.
One common dynamic performance view isV$INSTANCE. This view returns one row of statistics for each Oracle instance running against the database; since Oracle Database XE only supports one instance per database,V$INSTANCEwill always have one row.
Using the following query, you can retrieve some basic information about the instance, including how long the database has been up since the last restart:
select instance_name, host_name, version, edition, startup_time, round(sysdate-startup_time,2) UPTIME from v$instance;
The results of this query look like this:
INSTANCE_NAME HOST_NAME VERSION EDITION STARTUP_T UPTIME
XE phpxe 10.2.0.1.0 XE 21-JAN-07 4.09
In other words, the database has been up for slightly more than four days.
Using Views to Restrict Data Access
Views also help to facilitate data security: for example, you might want to provide access to some columns in selected HRtables but not others, such as SSNs, birth dates, and other sensitive information. To solve this problem without data duplication or raising privacy concerns, you can use views such asEMPLOYEE_DEPARTMENT_VIEWto allow other departments to access selected columns from specific tables without allowing access to columns in the view’s base tables.
By default, the userFC(or any other user) cannot access any ofHR’s tables; this query returns the message “Table or View Does Not Exist”:
select * from hr.employees;
The userFCdoes not have access to any ofHR’s views either; but theHRuser can give access to the view like this:
grant select on employee_department_view to fc;
Now the userFCcan see the rows in the view but not in the underlying tables:
