Previous | Table of Contents | Next

Page 138

NOTE
Disabling the SQL*Plus SET command will also disable the SQL SET ROLE and SET TRANSACTION commands.

NOTE
Disabling the SQL*Plus START command will also disable the SQL*Plus @ and @@ commands.

The following SQL commands can be disabled:

You can also disable the following PL/SQL commands:

Page 139

NOTE
Disabling BEGIN and DECLARE does not prevent the use of the SQL*Plus EXECUTE command. EXECUTE must be disabled separately.

Disabling SET ROLE

The SQL command SET ROLE enables an application user to access privileges granted through an application role, which can cause security problems. To prevent application users from accessing application roles in SQL*Plus, a DBA can use the PRODUCT_USER_PROFILE table to disable the SET ROLE command. This restricts a SQL*Plus user to only those privileges associated with the roles enabled when he or she started SQL*Plus.

Disabling Roles

To disable a role for a specific user, insert a row in PRODUCT_USER_PROFILE containing the user's username in the Userid column, ROLES in the Attribute column, and the role name in the Char_Value column.

NOTE
If you enter PUBLIC or % in the Userid column when disabling a role, you disable that role for all the users. % or PUBLIC should only be used for roles that are granted to PUBLIC.

The Oracle user SYSTEM owns the PRODUCT_USER_PROFILE table. When SYSTEM logs on, SQL*Plus does not read the PRODUCT_USER_PROFILE table. Thus, no SQL*Plus restrictions apply to user SYSTEM.

CAUTION
The product-level security provided by PRODUCT_USER_PROFILE is enforced only by SQL*Plus, not the Oracle RDBMS.

Tracing SQL Statements

Before SQL*Plus 3.3, obtaining statistics about the execution of SQL statements in SQL*Plus was very limited. The only commands available for this purpose are SET TIME and SET TIMING.

The SET TIME command controls how SQL*Plus displays the current time. The ON parameter displays the current time before each SQL*Plus prompt and the OFF parameter suppresses the display of the current time. The syntax of the SET TIME command is

SET TI[ME] [OFF | ON]     (OFF is the default).

Listing 8.14 shows an example of using the SET TIME command.

Page 140

Listing 8.14 Estimating Runtime

SQL> SHOW TIME
time OFF
SQL> SET TIME ON
01:17:57 SQL> SELECT t1.dname, t2.ename, t2.sal, t2.job
01:18:11   2  FROM dept t1, emp t2
01:18:23   3  WHERE t1.deptno = t2.deptno;

DNAME            ENAME              SAL   JOB
--------------   ----------   --------   --------
RESEARCH         SMITH              800   CLERK
SALES            ALLEN             1600   SALESMAN
SALES            WARD              1250   SALESMAN
RESEARCH         JONES             2975   MANAGER
SALES            MARTIN            1250   SALESMAN
SALES            BLAKE             2850   MANAGER
ACCOUNTING       CLARK             2450   MANAGER
RESEARCH         SCOTT             3000   ANALYST
ACCOUNTING       KING              5000   PRESIDENT
SALES            TURNER            1500   SALESMAN
RESEARCH         ADAMS             1100   CLERK
SALES            JAMES              950   CLERK
RESEARCH         FORD              3000   ANALYST
ACCOUNTING       MILLER            1300   CLERK
14 rows selected.
01:18:43 SQL>

By subtracting the time displayed at the last line entered (01:18:23) from the time displayed at the first prompt after the query execution (01:18:43), the user can get an estimate of the time it took to execute the query (includes the time required to enter the WHERE clause).

The runtime calculated using this method does not reflect the true speed with which Oracle executes a query, because this elapsed time includes the overhead of displaying the query to the SQL*Plus interface. The actual time to run the query is much shorter.

The SET TIMING command controls the display of execution time statistics. The syntax of the SET TIMING command is

SET TIMI[NG] [OFF | ON]                    (OFF is the default)

The SET TIMING ON command displays the execution time statistics for each SQL or PL/SQL block executed in SQL*Plus. SET TIMING OFF suppresses the display of the timing statistics.

Listing 8.15 shows an example of using the SET TIMING command.

Listing 8.15 Timing Execution

SQL> SHOW TIMING
timing OFF
SQL> SET TIMING ON
SQL>  SELECT t1.dname, t2.ename, t2.sal, t2.job

Page 141

     2  FROM dept t1, emp t2
     3  WHERE t1.deptno = t2.deptno;
DNAME            ENAME              SAL   JOB
--------------   ----------   --------   --------
RESEARCH         SMITH              800   CLERK
SALES            ALLEN             1600   SALESMAN
SALES            WARD              1250   SALESMAN
RESEARCH         JONES             2975   MANAGER
SALES            MARTIN            1250   SALESMAN
SALES            BLAKE             2850   MANAGER
ACCOUNTING       CLARK             2450   MANAGER
RESEARCH         SCOTT             3000   ANALYST
ACCOUNTING       KING              5000   PRESIDENT
SALES            TURNER            1500   SALESMAN
RESEARCH         ADAMS             1100   CLERK
SALES            JAMES              950   CLERK
RESEARCH         FORD              3000   ANALYST
ACCOUNTING       MILLER            1300   CLERK
14 rows selected.
real: 2690

As shown in Listings 8.14 and 8.15, the SET TIME and SET TIMING commands provide very basic, and not very precise, timing statistics for SQL statements and/or PL/SQL blocks executed in SQL*Plus. No information about the execution/access path used by the optimizer was available directly in SQL*Plus prior to SQL*Plus 3.3.

A new feature, called AUTOTRACE, is available starting with SQL*Plus 3.3 and higher. It enables a SQL*Plus user to see the execution plan of DML statements (SELECT, INSERT, UPDATE, and DELETE) while in SQL*Plus, without having to use the regular facilities (SQL_TRACE, EXPLAIN PLAN, and the TKPROF utility) that Oracle provides to trace the execution of SQL statements.

To enable this feature, the DBA must run, as user SYS, an SQL script called PLUSTRCE.SQL. The location of the PLUSTRCE.SQL file is operating system dependent. The PLUSTRCE script does the following:

To enable the AUTOTRACE capability for a user, three conditions must be met:

Previous | Table of Contents | Next