Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Administration of Database Links

Oracle Tips by Burleson
 

Database links allow users to access tables in other databases, even other databases on other computers running different operating systems. To use database links, the systems involved must have the SQL*NET product installed. All systems need to have network links as well.

Creation of Database Links

Database links are created with the CREATE DATABASE LINK command. The format of this command follows:

CREATE [SHARED] [PUBLIC] DATABASE LINK dblink
CONNECT TO
CURRENT_USER|username IDENTIFIED BY password [authenticated_clause]|
Authenticated_clause
USING 'connect string';
Authenticated_clause:
AUTHENTICATED BY username IDENTIFIED BY password

where:

database link. Under Oracle7 and Oracle8, the GLOBAL db_name and the DB_DOMAIN.

DBLINKS. These are schema-independent.

SHARED. Uses a single network connection, which is shared between different users of the link. This clause can only be specified if the server is using the multithreaded server (MTS) configuration.

PUBLIC. Specified for database links that are to be used by all users. The user must have DBA privilege to specify a PUBLIC link.

CURRENT_USER. Specifies that the invoking user’s user name and password be invoked to connect to the remote instance.

CONNECT TO. Used to force connection to a specific database user at the database being connected to. This allows the DBA to restrict access to confidential or sensitive information for one user instead of all users. If this clause isn’t specified, the user’s user name and password will be used in its place.

‘connect string’. The protocol-specific connection command. For a version 2 SQL*NET or NET8 connection, the string would be:

   sid|alias.domain

       where:

sid|alias is either the actual SID for the database or the alias entered in the tnsnames.ora file for the platform or names server.

domain is the domain to which the instance belongs.

Authenticated_clause.  New in Oracle8i, allows the specification of a user name and password that must exist on the remote instance. This clause must be used if the database link is created SHARED.

An example of a database link creation would be:

CREATE PUBLIC SHARED DATABASE LINK ortest1.world
CONNECT TO tele_dba IDENTIFIED BY not_the_password
AUTHENTICATED BY security_dba IDENTIFIED BY secure_password
USING 'ortest1';

The database link would be used in the following manner:

SELECT * FROM emp@ortest1

The combination of table name and link can be placed into a single synonym for ease of use:

CREATE PUBLIC SYNONYM BOS_EMP FOR EMP@ORPERDB.BOSTON

(assuming ORPERDB.BOSTON is a defined alias in the tnsnames.ora file).

To document existing database links, the script DBLINK_RCT.SQL, available from the Wiley Web site can be run to create a database link rebuild script.

Alteration of Database Links

Database links cannot be altered. To modify a database link, it must be dropped and re-created.

Dropping Database Links

Database links are dropped via the DROP DATABASE LINK command. For public database links, the word PUBLIC must be inserted after DROP. Only DBAs can drop public database links. The format of the DROP command follows:

DROP [PUBLIC] DATABASE LINK dblink;

Administration of Views

Views (with the exception of materialized views) offer virtual looks at tables. They don’t exist until queried except as a specification statement stored in the database. A single view can be very efficient, but the “stacking” of views--that is, views that reference views that reference views--will cause a performance problem.

Views allow the DBA to restrict access to certain columns within a table or tables. Views can also act as preprocessing for reports, and can be used to perform calculations and display the results alongside of the data as if the results were stored in a table. Views can also be used to “filter” data. A view can be constructed from virtually any SELECT statement. Depending upon how a view is constructed, updates and inserts can be done through them.

Creation of Views

The creation of views is accomplished with the CREATE VIEW command. Let’s look at this command:

CREATE [OR REPLACE] [[NO] FORCE] VIEW [schema.]viewname
[[(alias list)]|
OF [schema.]type_name
WITH OBJECT IDENTIFIER DEFAULT|(attribute_list)]| UNDER super_view
AS subquery [with clause];
With clause:
WITH READ ONLY|CHECK OPTION [CONSTRAINT constraint]

where:

[NO] FORCE. The force option allows a view to be created even if the creator doesn’t have proper access to the tables, types, or views specified. However, before the view can be used, the appropriate grants must be issued.

viewname. The name for the view.

alias list. A valid column name or list of column names; the name isn’t required to be the same as the column it is based on. If aliases aren’t used, the names of the columns are used. If a column is modified by an expression, it must be aliased. If four columns are in the query, there must be four aliases. Each alias may also include a constraint. In addition, the alias list may also contain table or view constraints.

OF... Used for an object view. The specified type must exist and must contain an attribute for each column in the subquery.

WITH OBJECT IDENTIFIER. Replaces the version 8.0 WITH OBJECT OID clause and specifies that the attribute list specified will be used to generate an object identifier. The specified attribute or attribute list must uniquely identify the row in the view. This clause can be omitted or the DEFAULT clause used if the view is based on an existing object table or another object view.

UNDER super_view. New in Oracle, allows a view to be created as a subview under a master super_view. To see if a view is a sub- or super_view, use the SUPERVIEW_NAME column in the appropriate VIEW data dictionary view (DBA_VIEWS, USER_VIEWS or ALL_VIEWS).

The restrictions on the UNDER clause are:

* You must create a subview in the same schema as the superview.

* The object type type_name must be the immediate subtype of superview.

* You can create only one subview of a particular type under the same superview.

subquery. Any valid SELECT statement that doesn’t include an ORDER BY or FOR UPDATE clause. Other restrictions on a view’s query are:

* The view query cannot select the CURRVAL or NEXTVAL pseudocolumns.

* If the view query selects the ROWID, ROWNUM, or LEVEL pseudocolumns, those columns must have aliases in the view query.

* If the view query uses an asterisk (*) to select all columns of a table, and you later add new columns to the table, the view will not contain those columns until you re-create the view by issuing a CREATE OR REPLACE VIEW statement. 

* For object views, the number of elements in the view subquery select list must be the same as the number of top-level attributes for the object type. The datatype of each of the selecting elements must be the same as the corresponding top-level attribute.

* You cannot specify the SAMPLE clause.

* The preceding restrictions apply to materialized views as well.

If you want the view to be inherently updatable, it must not contain any of the following constructs:

* A set operator

* A DISTINCT operator

* An aggregate or analytic function

* An ORDER BY, GROUP BY, CONNECT BY, or START WITH clause

* A collection expression in a SELECT list

* A subquery in a SELECT list

* Joins (with some exceptions)

If an inherently updatable view contains pseudocolumns or expressions, the UPDATE statement must not refer to any of these pseudocolumns or expressions.

* If you want a join view to be updatable, all of the following conditions must be true:

* The DML statement must affect only one table underlying the join.

* For an UPDATE statement, all columns updated must be extracted from a key-preserved table. If the view has the CHECK OPTION, join columns and columns taken from tables that are referenced more than once in the view must be shielded from UPDATE.

* For an INSERT statement, all columns into which values are inserted must come from a key-preserved table, and the view must not have the CHECK_OPTION.

With clause. Specifies that inserts and updates through the view must be selectable from the view. This can be used in a view based on a view.

READ ONLY. Specifies that the view is READ ONLY and cannot be updated.

CONSTRAINT. Specifies the name associated with the WITH CHECK constraint.

A view can usually be used in the following commands:

COMMAND
DELETE
INSERT
LOCK TABLE
UPDATE
SELECT

A view’s SELECT statement in the subquery cannot select a CURRVAL or NEXTVAL from a sequence, or directly access ROWID, ROWNUM, or LEVEL pseudocolumns. To use the pseudocolumns for a table, a view select must alias them.

A normal view is just a window to data; it can’t store data itself. Views can be used in an SQL statement, just as a table can, with the following exceptions. You can’t update a view if:

* It contains a join.

* It contains a GROUP BY, CONNECT BY, or START WITH clause.

* It contains a DISTINCT clause or expressions like “AMOUNT+10” in the column list.

* It doesn’t reference all NOT NULL columns in the table (all NOT NULLs must be in the view and assigned a value by the update).

You can update a view that contains pseudocolumns or columns modified by expressions if the update doesn’t affect these columns. A new trigger, called an INSTEAD OF trigger, can be used to update the underlying tables in a view that can’t be directly updated. If you base a view on a view with INSTEAD OF triggers, the new view must also contain INSTEAD OF triggers.

You can query the view USER_UPDATABLE_COLUMNS to find out if the columns in a join view are updatable. Generally speaking, as long as all of the NOT NULLs and key columns are included in a join view for a table, then that table may be updated through the view.

A join view can have the commands INSERT, UPDATE, and DELETE used against it under the circumstances described below.

* The DML affects only one of the tables in the join.

* If the command is UPDATE, then all of the columns updated are extracted from a key-preserved table. In addition, if the view has a CHECK OPTION constraint, join columns and columns taken from tables that are referenced more than once in the view are shielded from update.

* If the statement is DELETE, then there is one and only one key-preserved table

in the join, and that table can be present more than once if there is no CHECK OPTION constraint on the view.

* If the statement is INSERT, then all of the columns are from a key-preserved table, and the view doesn’t have a CHECK OPTION constraint.


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.
   
  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

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

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter