BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

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.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

   
 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter