Database Reference
In-Depth Information
END OF STMT
PARSE #5:c=0,e=711,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=63968189057
The trace file shows that the statement
ALTER
SESSION
SET
CURRENT_SCHEMA="HR"
was parsed
with parsing user identity 5 (
lid=5
; SYSTEM) and parsing schema identity 5 (
uid=5
). This
ALTER
SESSION
statement sets the parsing schema name to HR, as evidenced by the subsequent state-
ment
CREATE
SEQUENCE
, which was parsed with a parsing schema identity of 38 (
uid=38
), which
corresponds to the schema HR.
Creating Database Objects in a Foreign Schema
Let's assume that a software developer working with the account HR needs a large quantity of
new tables for testing purposes. The database user HR does not have the privilege
CREATE
TABLE
,
since the developer is only allowed to work with existing tables in the schema HR. Thus, the
developer is unable to create the tables in schema HR himself. The developer sent a script for
creating the tables, but the database object names in the script are not prefixed by a schema name.
This scenario is an example for putting the
ALTER
SESSION
SET
CURRENT_SCHEMA
statement to
use. Without the statement, one of the following solutions must be chosen:
The DBA has to temporarily grant
CREATE
TABLE
to the database user HR, such that the
developer can create the tables himself.
The DBA has to ask the developer to prefix each and every object name in the script with
the schema name HR, such that the DBA can run the script.
The DBA may run the script unchanged by leveraging
CURRENT_SCHEMA
. Below is a single
CREATE
TABLE
statement executed after
ALTER
SESSION
SET
CURRENT_SCHEMA
. The example illus-
trates that database objects are created under the parsing schema identifier, not the privilege
schema identifier.
SQL> SHOW USER
USER is "SYSTEM"
SQL> ALTER SESSION SET current_schema=hr;
Session altered.
SQL> CREATE TABLE country (
country_id char(2),
country_name varchar2(40),
region_id number,
CONSTRAINT pk_country PRIMARY KEY (country_id)
);
Table created.
SQL> SELECT owner, object_type
FROM dba_objects
WHERE object_name IN ('COUNTRY', 'PK_COUNTRY');
OWNER OBJECT_TYPE
----- -----------
HR TABLE
HR INDEX
