Database Reference
In-Depth Information
SQL> CONNECT / AS SYSDBA
Connected.
SQL> EXEC dbms_aqadm.stop_queue('NDEBES.CAUGHT_IN_SLOW_Q_AGAIN')
PL/SQL procedure successfully completed.
RENAME
The names of database tables may be changed with the SQL statement
RENAME
. It is one of the
few SQL statements that does not support the qualification of database objects with schema
names. This limitation cannot be worked around by using
ALTER
SYSTEM
SET
CURRENT_SCHEMA
,
since a subsequent
RENAME
statement causes the error ORA-03001.
SQL> SHOW USER
USER is "NDEBES"
SQL> ALTER SESSION SET CURRENT_SCHEMA=hr;
Session altered.
SQL> RENAME employees TO emp;
RENAME employees TO emp
*
ERROR at line 1:
ORA-03001: unimplemented feature
The solution is to create a stored procedure in the same schema that holds the table requiring
a different name and to use
EXECUTE
IMMEDIATE
along with
RENAME
in the procedure. The
RENAME
through the stored procedure completes successfully, since it executes in the context of the
schema holding the table.
Private Database Links
It is impossible to create a private database link in a foreign schema, since a database link
cannot be prefixed with a schema name. This restriction remains, even when
ALTER
SESSION
SET
CURRENT_SCHEMA
is used.
SQL> CONNECT / AS SYSDBA
Connected.
SQL> ALTER SESSION SET CURRENT_SCHEMA=ndebes;
Session altered.
SQL> CREATE DATABASE LINK lnk CONNECT TO remote_user
IDENTIFIED BY pwd USING 'dbserver1.oradbpro.com';
CREATE DATABASE LINK lnk CONNECT TO remote_user
IDENTIFIED BY pwd USING 'dbserver1.oradbpro.com'
*
ERROR at line 1:
ORA-01031: insufficient privileges
The error message makes sense, since there is no privilege such as
CREATE
ANY
DATABASE
LINK
. If you really do need to create a database link in a foreign schema, then you may use the
trick with the following stored procedure:
