How to configure AUTOTRACE in SQLPLUS for database user?

1. Change directory path and connect with SYSTEM user to database
C:\>cd c:\oracle\product\10.1.0\db_1\rdbms\admin

C:\Oracle\product\10.1.0\Db_1\RDBMS\ADMIN>sqlplus /nolog

SQL*Plus: Release 9.0.1.0.1 - Production on Tue Oct 16 17:08:20 2007

(c) Copyright 2001 Oracle Corporation. All rights reserved.

SQL> conn system/manager
Connected.

2. run UTLXPLAN.SQL script for plan table.
SQL> @utlxplan.sql

3. Create public synonym for plan table or grant all privilege.
SQL> create public synonym plan_table for plan_table;

SQL> grant all on plan_table to public;

Grant succeeded.

4. Exit and again change directory path and connect with SYS user to database
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

C:\Oracle\product\10.1.0\Db_1\RDBMS\ADMIN>cd\

C:\>cd c:\oracle\product\10.1.0\db_1\sqlplus\admin

C:\Oracle\product\10.1.0\Db_1\sqlplus\admin>sqlplus /nolog

SQL*Plus: Release 9.0.1.0.1 - Production on Tue Oct 16 17:12:07 2007

(c) Copyright 2001 Oracle Corporation. All rights reserved.

SQL> conn sys as sysdba
Enter password:
Connected.

5. run plustrce script and script must be run with SYS user.
SQL> @plustrce
SQL>
SQL> drop role plustrace;

Role dropped.

SQL> create role plustrace;

Role created.

SQL>
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL> set echo off

6. Grant plustrace role to public.
SQL> grant plustrace to public;

Grant succeeded.

No comments:

Post a Comment

Please Provide your feedback here