Databases Reference
In-Depth Information
The following query retrieves SQL statements run by user HR from the shared pool by
selecting from the view
V$SQL
. Note that there are no SQL statements where the parsing user
identity differs from the parsing schema identity.
SQL> SELECT s.parsing_user_id, u.username, s.parsing_schema_id,
s.parsing_schema_name, substr(s.sql_text,1,15) sql_text
FROM v$sql s, dba_users u
WHERE s.parsing_user_id=u.user_id
AND s.parsing_schema_name='HR';
PARSING_USER_ID USERNAME PARSING_SCHEMA_ID PARSING_SCHEMA_NAME SQL_TEXT
--------------- -------- ----------------- ------------------- ---------------
38 HR 38 HR SELECT USER FRO
38 HR 38 HR BEGIN DBMS_OUTP
After importing some tables into the schema HR by running the IMPORT utility (
imp
) as
SYSTEM, repeating the previous query on
V$SQL
shows that some statements were executed
with the parsing user identity SYSTEM, while the parsing schema identity was HR.
PARSING_USER_ID USERNAME PARSING_SCHEMA_ID PARSING_SCHEMA_NAME SQL_TEXT
--------------- -------- ----------------- ------------------- ---------------
38 HR 38 HR BEGIN sys.dbm
38 HR 38 HR ALTER SESSION S
38 HR 38 HR ALTER SESSION S
5 SYSTEM 38 HR BEGIN SYS.DBMS
5 SYSTEM 38 HR BEGIN SYS.DBMS
If we trace the SQL statements issued by the IMPORT utility, we will find an
ALTER
SESSION
SET
CURRENT_SCHEMA
statement in the SQL trace file. To set
SQL_TRACE=TRUE
in the import session,
use the undocumented command line option
TRACE=TRUE
.
$ imp trace=true
Import: Release 10.2.0.1.0 - Production on Tue Jun 19 10:09:40 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username:
By the way, Data Pump Export (
expdp
) and Import (
impdp
) have the same undocumented
TRACE
switch. The resulting SQL trace file contains lines such as these:
PARSING IN CURSOR #5 len=38 dep=0 uid=5 oct=42 lid=5 tim=63968187622 hv=886929406 ad
='6786940c'
ALTER SESSION SET CURRENT_SCHEMA= "HR"
END OF STMT
PARSE #5:c=0,e=523,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=63968187614
EXEC #5:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=63968187802
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #5 len=113 dep=0 uid=38 oct=13 lid=5 tim=63968189065 hv=0 ad='8eaf
7d4'
CREATE SEQUENCE "LOCATIONS_SEQ" MINVALUE 1 MAXVALUE 9900 INCREMENT BY 100
START WITH 3300 NOCACHE NOORDER NOCYCLE
