 |
|
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. |
|
