Database Reference
In-Depth Information
CREATE OR REPLACE PROCEDURE ndebes.create_db_link
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE DATABASE LINK lnk
CONNECT TO remote_user IDENTIFIED BY pwd
USING ''dbserver1.oradbpro.com''';
END;
/
Procedure created.
SQL> EXEC ndebes.create_db_link
PL/SQL procedure successfully completed.
SQL> SELECT owner, db_link, username, host
FROM dba_db_links
WHERE db_link LIKE 'LNK%';
OWNER DB_LINK USERNAME HOST
------ ---------------- ----------- ----------------------
NDEBES LNK.ORADBPRO.COM REMOTE_USER dbserver1.oradbpro.com
Since stored procedures are run with owner's rights by default, the
CREATE
DATABASE
LINK
statement is executed as privilege user NDEBES and parsing user NDEBES, such that the data-
base link is created in the same schema as the procedure. Analogous to database links, a directory
name in a
CREATE
DIRECTORY
statement cannot be prefixed with a schema name. However, all
directories are owned by SYS, so it's irrelevant which user creates a directory.
Stored Outlines
Stored outlines may be used to fix an execution plan for a SQL statement, such that the optimizer
always uses the plan from the stored outline instead of optimizing the statement in the current
environment. It is possible to create a stored outline in a foreign schema, although there is no
privilege
CREATE
ANY
OUTLINE
.
SQL> CONNECT system/secret
Connected.
SQL> ALTER SESSION SET CURRENT_SCHEMA=ndebes;
Session altered.
SQL> CREATE OUTLINE some_outline ON
SELECT emp.last_name, emp.first_name, d.department_name
FROM hr.employees emp, hr.departments d
WHERE emp.department_id=d.department_id;
Outline created.
SQL> SELECT node, stage, join_pos, hint FROM dba_outline_hints
WHERE
owner='NDEBES'
AND name='SOME_OUTLINE'
ORDER by node, join_pos;
NODE STAGE JOIN_POS HINT
