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. |
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.
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. |
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: