Remote DBA Plans
Remote DBA Service

Partition Views

Oracle Tips by Burleson Consulting

Under Oracle7 version 7.3, a new type of view, called the partition view, was allowed. This view joins several tables that have identical structure into a single entity that can be queried as if all of the component parts were actually in one table. The purpose for a partition view is to allow physical partitioning of data into several table partitions (this is pre-Oracle8 and shouldn’t be confused with actual partition tables, which aren’t available until Oracle8). These table partitions must be hand-built by the Remote DBA to spread data across several disk volumes and to separate data by a preset algorithm that is application-controlled (instead of as in Oracle8, where the partitioning is specified at the table’s creation). An example would be an application that breaks down sales data by month and stores it in independent monthly sales tables. A partitioned view could be created to join all of the monthly sales tables in quarterly, yearly, or other views of all sales for that period. All that said, because these views were not widely used, and have been replaced by true partitions, I do not discus them in detail.

Object Views

In order to take advantage of the benefits of the new object paradigm in Oracle8, 8i, and 9i, a common relational table can be made into a pseudo-object table by creating what is known as an object view that is directly based on the relational table. The object ID is not system-generated but is based on columns that you specify.

An example using the emp table would be:

CREATE TYPE emp_t AS OBJECT (
empno     NUMBER(5),
ename     VARCHAR2(20),
salary    NUMBER(9,2),
job       VARCHAR2(20));
/
CREATE TABLE emp(
empno     NUMBER(5) CONSTRAINT pk_emp PRIMARY KEY,
ename     VARCHAR2(20),
salary    NUMBER(9,2),
job       VARCHAR2(20));

CREATE VIEW emp_man OF emp_t
WITH OBJECT IDENTIFIER (empno) AS
SELECT empno, ename, salary, job
FROM emp
WHERE job=''MANAGER'';

This creates an object view of emp_t (type) objects that correspond to the employees from the emp table who are managers, with empno, the primary key of EMP, as the object identifier.

Example Views

An example view that uses aliases and expressions to modify columns is shown in Source 7.2.

SOURCE 7.2 Example of a view with expressions.

CREATE VIEW free_space
(tablespace, file_id, pieces, free_bytes, free_blocks,
largest_bytes,largest_blks) AS
SELECT tablespace_name, file_id, COUNT(*),
SUM(bytes), SUM(blocks),
MAX(bytes), MAX(blocks) FROM sys.Remote DBA_free_space
GROUP BY tablespace_name, file_id;

In Source 7.2, the SUM, MAX, and COUNT expressions (functions) are used to provide summary data on space usage. This view could not be updated. Further reading will show it is also based upon a view, Remote DBA_FREE_SPACE, that is based on several data dictionary tables owned by the SYS user. An example of a view that performs calculations and filters the data provided is shown in Source 7.3.

SOURCE 7.3 View using expressions and filtering.

REM Title   : DD_VIEW.SQL
REM Purpose : View of the Data Dictionary caches
REM     showing only parameters that have usage
REM     and the percent of GETMISSES/GETS
REM USE     : Use as a selectable table only
REM Limitations   : User must have access to V$ views.
REM Revisions:
REM   Date Modified By Reason For change
REM   4/28/93     Mike Ault   Initial Creation
REM
CREATE VIEW dd_cache
AS SELECT parameter,gets,getmisses,
getmisses/gets*100 percent
,count,usage
FROM v$rowcache
WHERE gets > 100 AND getmisses > 0;

To create a script to document and allow rebuild of existing views, the script in Source 7.4 can be used.

SOURCE 7.4 Script to rebuild views.

REM
REM NAME        :view_rct.sql
REM FUNCTION:re-create database views by owner
REM USE              :Generate a report on database views
REM Limitations :If your view definitions are greater than 5000
REM           characters then increase the set long. This can be
REM           determined by querying the Remote DBA_VIEWS table's
REM           text_length column for the max value: select
REM           max(text_length) from Remote DBA_views;
REM
SET PAGES 59 LINES 79 feedback OFF ECHO OFF VERIFY OFF
DEFINE cr='chr(10)'
COLUMN text      FORMAT a80 word_wrapped
COLUMN view_name      FORMAT a20
COLUMN dbname NEW_VALUE db NOPRINT
UNDEF owner_name
UNDEF view_name
SELECT name dbname from v$database;
SET LONG 5000 HEADING OFF
SPOOL rep_out\&db\cre_view.sql
SELECT
'rem Code for view: '||v.view_name||'instance: '||&&db||&&cr||
'CREATE OR REPLACE VIEW '||v.owner||'.'||v.view_name||' AS '
||&&cr,
v.text
FROM
Remote DBA_views v
WHERE
v.owner LIKE UPPER('&&owner_name%')
AND view_name LIKE UPPER('%&&view_name%')
ORDER BY
v.view_name;
SPOOL OFF
SET HEADING ON PAGES 22 LINES 80 feedback ON
CLEAR COLUMNS
TTITLE OFF
PAUSE Press enter to continue

Alteration of Views

Under Oracle7, Oracle8, and Oracle8i, there is only a single option for the ALTER VIEW command: the COMPILE option. In Oracle9i, you can ADD, MODIFY, or DROP view constraints using ALTER VIEW. If a view’s underlying views or tables are marked as invalid or changed, the view is marked as invalid and must be recompiled. This can be done automatically when the view is next called, or it can be done explicitly with the ALTER VIEW command. It is best to do this explicitly so that any problems are found before users attempt to use the view. The format for the ALTER VIEW command follows:

ALTER VIEW [schema.]view_name COMPILE
[ADD|MODIFY|DROP constraint_clause] (Oracle9i only.)

Dropping Views

Views are dropped with the DROP VIEW command. Its format follows:

DROP VIEW [schema.]view_name;

Tip: Altering or dropping a view will result in invalidation of any dependent packages, functions, or procedures. Be sure to check dependencies before performing these operations.

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.