Hello masters,
Rajatabha, you said you want only the user's orders so what are the differences between user's orders and internal (SYS I presume ) orders? SYS use only the real tables (tab$, user$...) in the FROM clause, not the views like DBA_TABLES, USER_SYNONYMS...
I think you can begin by filtering orders which are using ***$ in the table's name. This is a beginning but it will eliminate many orders.
For example, to be sure, I use trace 10046 for a CREATE TABLE to see internals orders.
CONNECT HR/HR@orcl;
SQL> ALTER SESSION SET timed_statistics = TRUE;
SQL> ALTER SESSION SET max_dump_file_size = unlimited;
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = 'zztest_create_table01.trc';
SQL> ALTER SESSION SET events '10046 trace name context forever, level 4';The CREATE TABLE.
SQL> CREATE TABLE ZZ01 (ID NUMBER primary key, NOM VARCHAR2(50), PRENOM VARCHAR2(50));
SQL> ALTER SESSION SET events '10046 trace name context off';The trace file is here; and I use TKPROF to make him more readable.
SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_4616_zztest_create_table01.trc
Unix$ tkprof orcl12c_ora_4616_zztest_create_table01.trc orcl12c_ora_4616_zztest_create_table01.txt Exemples of orders executed by SYS : like we see, they use ***$ in the table's name.
SQL ID: 0sbbcuruzd66f Plan Hash: 2239883476
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,
sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,
spare1, spare2, avgcln, minimum_enc, maximum_enc
from
hist_head$ where obj#=:1 and intcol#=:2
SQL ID: 2sxqgx5hx76qr Plan Hash: 3312420081
select /*+ rule */ bucket, endpoint, col#, epvalue, epvalue_raw,
ep_repeat_count, endpoint_enc
from
histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
SQL ID: 06gfrprr7w0r2 Plan Hash: 2709293936
select name,password,datats#,tempts#,type#,defrole,resource$, ptime,
decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass), spare1,
spare4,ext_username,spare2,nvl(spare3,16382),spare9,spare10
from
user$ where user#=:1