View Definition Viewer (dview.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides comprehensive view analysis by:

• Displaying complete view definitions (SQL text)
• Showing view metadata including text length
• Supporting pattern matching for owner and view names
• Setting appropriate formatting for long view definitions
• Organizing output by owner and view name

Script

rem  dview.sql
rem
rem  linesize = 80
set lines 80
rem
set long 100000
rem
ttitle 'View Definitions'
rem
col owner format a12 heading 'OWNER'
col view_name format a30 heading 'VIEW NAME'
col text_length format 99999 heading 'LENGTH'
col text format a80 heading 'TEXT'
rem
select owner, view_name, text_length, text
  from sys.dba_views
 where owner like upper('&owner')
   and view_name like upper('&view')
 order by owner, view_name;
rem
set long 80

Usage

SQL> @dview.sql
Enter value for owner: HR
Enter value for view: EMP%

Parameters

• owner: Schema owner pattern to filter (use % for all)
• view: View name pattern to filter (use % for all)

Required Privileges

• SELECT on SYS.DBA_VIEWS

Sample Output

View Definitions

OWNER        VIEW NAME                      LENGTH
------------ ------------------------------ ------
TEXT
--------------------------------------------------------------------------------
HR           EMP_DETAILS_VIEW                  245
SELECT e.employee_id, e.first_name, e.last_name, e.email, e.hire_date,
       e.salary, d.department_name, j.job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON e.job_id = j.job_id
WHERE e.status = 'ACTIVE'

HR           EMPLOYEE_SALARY_GRADES           189
SELECT e.employee_id, e.last_name, e.salary,
       CASE
         WHEN e.salary < 5000 THEN 'Grade 1'
         WHEN e.salary < 10000 THEN 'Grade 2'
         WHEN e.salary < 15000 THEN 'Grade 3'
         ELSE 'Grade 4'
       END AS salary_grade
FROM employees e

SCOTT        EMP_DEPT_VIEW                    156
SELECT e.empno, e.ename, e.job, e.sal, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno

Key Output Columns

• OWNER: Schema that owns the view
• VIEW NAME: Name of the view
• LENGTH: Number of characters in view definition
• TEXT: Complete SQL definition of the view

Understanding View Types

Simple Views

• Based on single table
• Direct column mapping
• Often updatable

Complex Views

• Multiple table joins
• Calculated columns
• Aggregate functions
• Usually read-only

Materialized Views

• Physically stored result set
• Periodic refresh
• Performance optimization

Common Use Cases

1. Documentation

   • Document view logic
   • Understand data relationships
   • Create system documentation

2. Impact Analysis

   • Identify view dependencies
   • Plan table structure changes
   • Assess modification impact

3. Performance Tuning

   • Analyze view complexity
   • Identify optimization opportunities
   • Review join strategies

4. Security Analysis

   • Understand data access patterns
   • Validate security filtering
   • Review column exposure

Advanced Analysis

Find complex views:

SELECT owner, view_name, text_length
FROM dba_views
WHERE text_length > 1000
ORDER BY text_length DESC;

Views with specific tables:

SELECT owner, view_name
FROM dba_views
WHERE UPPER(text) LIKE '%EMPLOYEES%'
ORDER BY owner, view_name;

View dependencies:

SELECT owner, name, referenced_owner, referenced_name
FROM dba_dependencies
WHERE type = 'VIEW'
AND referenced_type = 'TABLE'
AND name = 'EMP_DETAILS_VIEW'
ORDER BY referenced_owner, referenced_name;

View Management

Create view with definition visible:

-- Simple view
CREATE VIEW hr.active_employees AS
SELECT employee_id, first_name, last_name, email
FROM employees
WHERE status = 'ACTIVE';

-- Complex view with joins
CREATE VIEW hr.employee_summary AS
SELECT e.employee_id, e.last_name, d.department_name,
       j.job_title, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON e.job_id = j.job_id;

Modify view definition:

CREATE OR REPLACE VIEW hr.active_employees AS
SELECT employee_id, first_name, last_name, email, hire_date
FROM employees
WHERE status = 'ACTIVE'
AND hire_date > DATE '2020-01-01';

Performance Considerations

View Optimization

• Oracle rewrites queries using views
• Complex views may impact performance
• Consider materialized views for heavy aggregations
• Index underlying tables appropriately

Best Practices

• Keep view definitions readable
• Document complex business logic
• Avoid nested view references
• Use meaningful column aliases

Security Implications

Data Access Control

Views can provide:

• Column-level security (hide sensitive columns)
• Row-level security (filter data)
• Computed columns (derived values)
• Data transformation (format changes)

Privilege Management

-- Grant view access without table access
GRANT SELECT ON hr.employee_summary TO app_user;

-- Revoke direct table access
REVOKE SELECT ON hr.employees FROM app_user;

Troubleshooting

Common Issues

• Invalid views: Underlying objects changed
• Performance problems: Complex view logic
• Privilege errors: Missing table permissions
• Data inconsistency: Outdated view logic

Validation Queries

-- Check view validity
SELECT owner, object_name, status
FROM dba_objects
WHERE object_type = 'VIEW'
AND status = 'INVALID';

-- Test view execution
SELECT COUNT(*) FROM hr.employee_summary;

Related Scripts

• Object Dependency Analysis
• Database Objects
• Table Analysis