run SQLTXPLAIN as SYS – set configuration parameter “restrict_user”

Oracle provide SQLTXPLAIN.SQL to gathers comprehensive data about a particular query. But by default, it doesn’t allow you run it as SYS user. Otherwise, you would get following error message in logs.

ORA-20101: Invalid user: SYS. If you must execute as SYS, set configuration parameter "restrict_user"

We can make it works by setting up SQLTXPLAIN parameter "restrict_user" in advance. Its value is ‘N’ or ‘Y’, the latter value is by default, which does not allow restrict user like SYS. Then change this behavior by switch value.  

Example:

SQL> EXEC sqltxplain.sqlt$d.set_param(‘restrict_user’,’N’);

PL/SQL procedure successfully completed.

SQL> @run/sqltxtract.sql 5aynax2w2jwyw



Note: SQLTCONFIG.sql is to show off all SQLT configuration parameters, Following list is all default values:

SQL> @utl/sqltconfig.sql


Tool Parameters
——————————————————————————————————————————–
Skip Predicates from Plan       Name:skip_predicates_from_plan  Value:N           Default:N           N, Y
Skip COUNT(*)                   Name:skip_count_star            Value:N           Default:N           N, Y
COUNT(*) Threshold              Name:count_star_th              Value:10000000    Default:10000000    10000000: 0-999999999
Skip Segment Stats              Name:skip_segment_stats         Value:N           Default:N           N, Y
Skip Session Stats              Name:skip_session_stats         Value:N           Default:N           N, Y
Skip Session Wait Events        Name:skip_session_wait_events   Value:N           Default:N           N, Y
Skip Constraints                Name:skip_constraints           Value:N           Default:N           N, Y
Skip Column Stats               Name:skip_column_stats          Value:N           Default:N           N, Y
Skip DBA Statistics Views       Name:skip_dba_statistics_views  Value:N           Default:N           N, Y
Skip Histograms                 Name:skip_histograms            Value:N           Default:N           N, Y
Skip Histograms History         Name:skip_histograms_history    Value:N           Default:N           N, Y
Skip Partitions                 Name:skip_partitions            Value:N           Default:N           N, Y
Skip Subpartitions              Name:skip_subpartitions         Value:Y           Default:Y           Y, N
Skip Partition Column Stats     Name:skip_part_column_stats     Value:Y           Default:Y           Y, N
Skip Subpartition Column Stats  Name:skip_subpart_column_stats  Value:Y           Default:Y           Y, N
Skip Metadata                   Name:skip_metadata              Value:N           Default:N           N, Y
Skip Initialization Parameters  Name:skip_init_ora              Value:N           Default:N           N, Y
Skip Stored Outlines            Name:skip_stored_outlines       Value:N           Default:N           N, Y
Skip Tuning Advisor             Name:skip_tuning_advisor        Value:N           Default:N           N, Y
Skip Join Order                 Name:skip_join_order            Value:N           Default:N           N, Y
Skip 10053 Trace                Name:skip_10053_trace           Value:N           Default:N           N, Y
Skip 10046 Trace                Name:skip_10046_trace           Value:N           Default:N           N, Y
Skip Other Traces               Name:skip_other_traces          Value:N           Default:N           N, Y
Skip Tool Repository Export     Name:skip_repository_export     Value:N           Default:N           N, Y
DBMS_SQLTUNE Scope              Name:dbms_sqltune_scope         Value:COMPREHENS  Default:COMPREHENS  COMPREHENSIVE, LIMITED
DBMS_SQLTUNE Time Limit Secs    Name:dbms_sqltune_time_limit    Value:1800        Default:1800        1800: 30-86400
DBMS_SQLTUNE Report Level       Name:dbms_sqltune_report_level  Value:ALL         Default:ALL         ALL, TYPICAL, BASIC
Skip AWR access                 Name:skip_awr_access            Value:N           Default:N           N, Y
Skip SQL Monitor                Name:skip_sql_monitor           Value:N           Default:N           N, Y
Skip Test Case Builder          Name:skip_test_case_builder     Value:N           Default:N           N, Y
DBMS_SQLDIAG Time Limit Secs    Name:dbms_sqldiag_time_limit    Value:1800        Default:1800        1800: 30-86400
Days to report CBO Stats        Name:days_show_cbo_stats        Value:365         Default:365         365: 0-9999
Restrict User                   Name:restrict_user              Value:Y           Default:Y           Y, N
Gather CBO Stats Staging Objs   Name:gather_cbo_stats           Value:100         Default:100         0-100
Use GV$SQL RAC Views            Name:use_gv$sql_views           Value:Y           Default:Y           Y, N
Statistics Level for Linux      Name:linux_statistics_level     Value:TYPICAL     Default:TYPICAL     TYPICAL, ALL, BASIC
Max Num Child Cursors to List   Name:max_child_cursor_list      Value:100         Default:100         100: 0-999999999
Max Num Tablespaces to List     Name:max_tablespaces_list       Value:100         Default:100         100: 0-999999999
Create File History             Name:create_file_hist           Value:Y           Default:Y           Y, N
Index Compare by Columns        Name:index_compare_by_columns   Value:Y           Default:Y           Y, N
Repeat Rate for Titles          Name:title_repeat_rate          Value:30          Default:30          30: 10-1000
Keep Trace 10046 Open           Name:keep_trace_10046_open      Value:N           Default:N           N, Y
Stage Directory                 Name:stage_dir                  Value:SQLT$STAGE  Default:UDUMP       Set by sqlt/install/sqcdir
s.sql

Advertisements

About Pang

Oracle DBA working in database, fusion middleware and identity management and cloud.
This entry was posted in Oracle database. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s