Oracle PL/SQL/SQL Plus/Session variable
Содержание
- 1 Adjust your session time zone to -08:00, display the contents of your time table.
- 2 alter session set current_schema=Smart
- 3 alter session set cursor_sharing = force
- 4 alter session set db_file_multiblock_read_count
- 5 alter session set hash_area_size
- 6 alter session set NLS_DATE_FORMAT="DD-MM-YYYY"
- 7 alter session set nls_date_format="dd-mm-yyyy", nls_language=Dutch, nls_currency="Eur"
- 8 alter session set nls_date_format = "DD-MON-YYYY HH24:MI:SS"
- 9 ALTER SESSION SET optimizer_dynamic_sampling
- 10 alter session set optimizer_index_cost_adj, alter session set optimizer_index_caching
- 11 alter session set optimizer_max_permutations=80000
- 12 alter session set optimizer_mode=all_rows
- 13 alter session set optimizer_mode=first_rows
- 14 alter session set OPTIMIZER_MODE = RULE
- 15 ALTER SESSION SET QUERY_REWRITE_ENABLED
- 16 alter session set query_rewrite_integrity=enforced
- 17 alter session set sort_area_size = 102400000
- 18 ALTER SESSION SET SQL_TRACE = TRUE
- 19 alter session set use_stored_outlines
- 20 alter session set workarea_size_policy=manual
- 21 alter system flush shared_pool
- 22 alter system quiesce restricted
- 23 alter system resume
- 24 alter system suspend
- 25 alter the session with the ALTER SESSION statement and set the session"s time zone forward
- 26 Alter time_zone
- 27 demonstrates the use of DBMS_SQL to execute an ALTER SESSION statement.
- 28 event-based call and exception tracing.
- 29 If your session time zone is not US/Central (-06:00), alter your session to Central time:
- 30 If your session time zone is not US/Central Standard Time (-06:00), alter your session to Central Standard time:
- 31 interaction between ALTER SESSION and autonomous transactions.
- 32 sessiontimezone, current_timestamp
- 33 set optimizer_features_enable = "8.1.5" scope = spfile
- 34 Set the following session parameters to enable query rewrite:
- 35 show filestat
- 36 show parameter optimizer_features
- 37 show verify
- 38 SQL trace
- 39 sql_trace a stored procedure
- 40 uses dynamic SQL to issue an ALTER SESSION statement.
Adjust your session time zone to -08:00, display the contents of your time table.
<source lang="sql">
SQL> SQL> create table t
2 (c1 timestamp with time zone, 3 c2 timestamp with local time zone) 4 /
Table created. SQL> SQL> SQL> -- Insert a row using the systimestamp function for both columns: SQL> SQL> insert into t (c1,c2) values( systimestamp, systimestamp ); 1 row created. SQL> SQL> select * from t; C1
C2
16-JUN-08 05.42.52.846000 PM -07:00 16-JUN-08 06.42.52.846000 PM
1 row selected. SQL> SQL> alter session set time_zone = "-08:00"; Session altered. SQL> SQL> select * from t; C1
C2
16-JUN-08 05.42.52.846000 PM -07:00 16-JUN-08 04.42.52.846000 PM
1 row selected. SQL> SQL> drop table t; Table dropped. SQL> SQL> SQL> --
</source>
alter session set current_schema=Smart
<source lang="sql">
SQL> SQL> alter session set current_schema=Smart; ERROR: ORA-01435: user does not exist
SQL> show user USER is "sqle" SQL> SQL> select * from dept;
SQL> SQL> alter session set current_schema=book;
SQL>
</source>
alter session set cursor_sharing = force
<source lang="sql">
SQL> SQL> SQL> alter session set cursor_sharing = force; Session altered. SQL> SQL> select substr(object_name,1,2)
2 from all_objects t2 3 where rownum = 1 4 /
SUBSTR(OBJECT_NAME,1,2)
IC SQL> SQL>
</source>
alter session set db_file_multiblock_read_count
<source lang="sql">
SQL> create table myTable
2 as 3 select rownum id, a.* 4 from all_objects a 5 where 1=0 6 /
SQL> SQL> SQL> SQL> set autotrace traceonly explain SQL> select * from myTable; Execution Plan
Plan hash value: 1015944200
| Id | Operation | Name |
| 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| MYTABLE |
Note
- rule based optimizer used (consider using cbo)
SQL> SQL> alter session set db_file_multiblock_read_count = 32; SQL> SQL> select * from myTable; Execution Plan
Plan hash value: 1015944200
| Id | Operation | Name |
| 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| MYTABLE |
Note
- rule based optimizer used (consider using cbo)
SQL> SQL> alter session set db_file_multiblock_read_count = 64; SQL> SQL> select * from myTable; Execution Plan
Plan hash value: 1015944200
| Id | Operation | Name |
| 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| MYTABLE |
Note
- rule based optimizer used (consider using cbo)
SQL> SQL> SQL> set autotrace off SQL> SQL> SQL> drop table myTable; SQL> SQL>
</source>
alter session set hash_area_size
<source lang="sql">
SQL> create table myTable
2 as 3 select rownum id, a.* 4 from all_objects a 5 where 1=0 6 /
SQL> SQL> SQL> SQL> set autotrace traceonly explain SQL> SQL> alter session set sort_area_size = 102400000; SQL> SQL> alter session set hash_area_size = 204800000; SQL> SQL> select a.object_type, b.object_name
2 from myTable a, myTable b 3 where a.last_ddl_time = b.last_ddl_time;
Execution Plan
Plan hash value: 2666611345
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 67 | 3082 | 3 | |* 1 | HASH JOIN | | 67 | 3082 | 3 | | 2 | TABLE ACCESS FULL| MYTABLE | 82 | 1640 | 1 | | 3 | TABLE ACCESS FULL| MYTABLE | 82 | 2132 | 1 |
Predicate Information (identified by operation id):
1 - access("A"."LAST_DDL_TIME"="B"."LAST_DDL_TIME")
Note
- cpu costing is off (consider enabling it)
SQL> SQL> alter session set sort_area_size = 65536; SQL> SQL> alter session set hash_area_size = 131072; SQL> SQL> select a.object_type, b.object_name
2 from myTable a, myTable b 3 where a.last_ddl_time = b.last_ddl_time;
Execution Plan
Plan hash value: 2666611345
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 67 | 3082 | 3 | |* 1 | HASH JOIN | | 67 | 3082 | 3 | | 2 | TABLE ACCESS FULL| MYTABLE | 82 | 1640 | 1 | | 3 | TABLE ACCESS FULL| MYTABLE | 82 | 2132 | 1 |
Predicate Information (identified by operation id):
1 - access("A"."LAST_DDL_TIME"="B"."LAST_DDL_TIME")
Note
- cpu costing is off (consider enabling it)
SQL> SQL> set autotrace off SQL> SQL> SQL> drop table myTable;
</source>
alter session set NLS_DATE_FORMAT="DD-MM-YYYY"
<source lang="sql">
SQL> create table history
2 ( empno NUMBER(4) 3 , beginyear NUMBER(4) 4 , begindate DATE 5 , enddate DATE 6 , deptno NUMBER(2) 7 , sal NUMBER(6,2) 8 , comments VARCHAR2(60) 9 , constraint H_PK primary key 10 (empno,begindate) 11 , constraint H_BEG_END check 12 (begindate < enddate) 13 ) ;
Table created. SQL> SQL> SQL> alter session set NLS_DATE_FORMAT="DD-MM-YYYY"; Session altered. SQL> SQL> insert into history values (1,2000,"01-01-2000","01-02-2000",40, 950,""); 1 row created. SQL> insert into history values (1,2000,"01-02-2000", NULL ,20, 800,"restarted"); 1 row created. SQL> SQL> insert into history values (2,1988,"01-06-1988","01-07-1989",30,1000,""); 1 row created. SQL> insert into history values (2,1989,"01-07-1989","01-12-1993",30,1300,""); 1 row created. SQL> insert into history values (2,1993,"01-12-1993","01-10-1995",30,1500,""); 1 row created. SQL> insert into history values (2,1995,"01-10-1995","01-11-2009",30,1700,""); 1 row created. SQL> insert into history values (2,2009,"01-11-2009", NULL ,30,1600,"just hired"); 1 row created. SQL> SQL> SQL> select empno, begindate, comments
2 from history 3 where comments like "%0\%%" escape "\";
no rows selected SQL> SQL> drop table history; Table dropped. SQL>
</source>
alter session set nls_date_format="dd-mm-yyyy", nls_language=Dutch, nls_currency="Eur"
<source lang="sql">
SQL> alter session
2 set nls_date_format="dd-mm-yyyy" 3 nls_language=Dutch 4 nls_currency="Eur";
Session altered. SQL> SQL>
</source>
alter session set nls_date_format = "DD-MON-YYYY HH24:MI:SS"
<source lang="sql">
SQL> SQL> SQL> alter session set nls_date_format = "DD-MON-YYYY HH24:MI:SS"; Session altered. SQL> SQL> select sysdate from dual; SYSDATE
16-JUN-2008 17:29:01 1 row selected. SQL> SQL> SQL> select sysdate - to_dsinterval( "1 00:00:00" ) "US Marines Birthday" from dual; US Marines Birthday
15-JUN-2008 17:29:01 1 row selected. SQL> SQL> --
</source>
ALTER SESSION SET optimizer_dynamic_sampling
<source lang="sql">
SQL> SQL> ALTER SESSION SET optimizer_dynamic_sampling = 0; Session altered. SQL> SQL> SQL> CREATE TABLE myTable AS SELECT * FROM all_objects; Table created. SQL> SQL> SQL> CREATE TABLE myTable2 AS SELECT object_id, object_name FROM myTable; Table created. SQL> SQL> ALTER TABLE myTable2 ADD object_application VARCHAR2(12); Table altered. SQL> SQL> UPDATE myTable2 SET object_application = "WORKSHOP"; 12597 rows updated. SQL> SQL> COMMIT; Commit complete. SQL> SQL> SET AUTOTRACE TRACEONLY EXPLAIN; SQL> SQL> SQL> ALTER SESSION SET optimizer_dynamic_sampling = 1; Session altered. SQL> SQL> SQL> SET AUTOTRACE OFF; SQL> SQL> DROP TABLE myTable2; Table dropped. SQL> SQL> DROP TABLE myTable; Table dropped. SQL>
</source>
alter session set optimizer_index_cost_adj, alter session set optimizer_index_caching
<source lang="sql">
SQL> create table myTable1
2 as
3 select mod(rownum,1000) id, rpad("x",300,"x") data
4 from all_objects
5 where rownum <= 5000;
SQL> SQL> create table myTable2
2 as
3 select rownum id, rpad("x",300,"x") data
4 from all_objects
5 where rownum <= 1000;
SQL> SQL> create index myTable1_idx on myTable1(id); SQL> SQL> create index myTable2_idx on myTable2(id); SQL> SQL> begin
2 dbms_stats.gather_table_stats( user, "myTable1", method_opt => "for all indexed columns",cascade=>true ); 3 dbms_stats.gather_table_stats( user, "myTable2", method_opt => "for all indexed columns",cascade=>true ); 4 end; 5 /
SQL> set autotrace traceonly explain SQL> SQL> select *
2 from myTable1, myTable2 3 where myTable1.id = myTable2.id 4 and myTable2.id between 5 and 55;
Execution Plan
Plan hash value: 3784280921
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 56 | 20664 | 260 | | 1 | TABLE ACCESS BY INDEX ROWID | MYTABLE1 | 1 | 178 | 5 | | 2 | NESTED LOOPS | | 56 | 20664 | 260 | | 3 | TABLE ACCESS BY INDEX ROWID| MYTABLE2 | 51 | 9741 | 5 | |* 4 | INDEX RANGE SCAN | MYTABLE2_IDX | 51 | | 2 | |* 5 | INDEX RANGE SCAN | MYTABLE1_IDX | 1 | | |
Predicate Information (identified by operation id):
4 - access("MYTABLE2"."ID">=5 AND "MYTABLE2"."ID"<=55)
5 - access("MYTABLE1"."ID"="MYTABLE2"."ID")
filter("MYTABLE1"."ID">=5 AND "MYTABLE1"."ID"<=55)
Note
- cpu costing is off (consider enabling it)
SQL> SQL> alter session set optimizer_index_cost_adj = 50; SQL> SQL> alter session set optimizer_index_caching = 0; SQL> SQL> select *
2 from myTable1, myTable2 3 where myTable1.id = myTable2.id 4 and myTable2.id between 5 and 55;
Execution Plan
Plan hash value: 3784280921
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 56 | 20664 | 156 | | 1 | TABLE ACCESS BY INDEX ROWID | MYTABLE1 | 1 | 178 | 3 | | 2 | NESTED LOOPS | | 56 | 20664 | 156 | | 3 | TABLE ACCESS BY INDEX ROWID| MYTABLE2 | 51 | 9741 | 3 | |* 4 | INDEX RANGE SCAN | MYTABLE2_IDX | 51 | | 1 | |* 5 | INDEX RANGE SCAN | MYTABLE1_IDX | 1 | | 1 |
Predicate Information (identified by operation id):
4 - access("MYTABLE2"."ID">=5 AND "MYTABLE2"."ID"<=55)
5 - access("MYTABLE1"."ID"="MYTABLE2"."ID")
filter("MYTABLE1"."ID">=5 AND "MYTABLE1"."ID"<=55)
Note
- cpu costing is off (consider enabling it)
SQL> SQL> set autotrace off SQL> SQL> SQL> drop table myTable1; SQL> drop table myTable2; SQL>
</source>
alter session set optimizer_max_permutations=80000
<source lang="sql">
SQL> create table t as select mod(object_id,10) id, a.* from all_objects a; SQL> SQL> analyze table t compute statistics
2 for table 3 for columns id;
SQL> SQL> SQL> SQL> alter session set optimizer_max_permutations=80000; SQL> SQL> explain plan for
2 select count(*) 3 from t t1, t t2, t t3, t t4, t t5, t t6 4 where t1.id = t2.id 5 and t1.id = t3.id 6 and t1.id = t4.id 7 and t1.id = t5.id 8 and t1.id = t6.id;
SQL> SQL> set autotrace off SQL> SQL> SQL> drop table t;
</source>
alter session set optimizer_mode=all_rows
<source lang="sql">
SQL> create table myTable
2 as 3 select rownum id, a.* 4 from all_objects a 5 where 1=0 6 /
SQL> SQL> SQL> SQL> set autotrace traceonly explain SQL> SQL> alter session set optimizer_mode=all_rows; SQL> SQL> select t1.object_name, t2.object_name
2 from myTable t1, myTable t2 3 where t1.object_id = t2.object_id 4 and t1.owner = "WMSYS" 5 /
Execution Plan
Plan hash value: 2666611345
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 77 | 3 | |* 1 | HASH JOIN | | 1 | 77 | 3 | |* 2 | TABLE ACCESS FULL| MYTABLE | 1 | 47 | 1 | | 3 | TABLE ACCESS FULL| MYTABLE | 82 | 2460 | 1 |
Predicate Information (identified by operation id):
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OWNER"="WMSYS")
Note
- cpu costing is off (consider enabling it)
SQL> SQL> set autotrace off SQL> SQL> SQL> drop table myTable; SQL> SQL>
</source>
alter session set optimizer_mode=first_rows
<source lang="sql">
SQL> SQL> SQL> SQL> create table myTable
2 as 3 select rownum id, a.* 4 from all_objects a 5 where 1=0 6 /
SQL> SQL> SQL> SQL> alter session set optimizer_mode=first_rows; SQL> SQL> select t1.object_name, t2.object_name
2 from myTable t1, myTable t2 3 where t1.object_id = t2.object_id 4 and t1.owner = "WMSYS" 5 /
SQL> alter session set sql_trace=true; SQL> drop table myTable;
</source>
alter session set OPTIMIZER_MODE = RULE
<source lang="sql">
SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) ); Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> begin
2 dbms_stats.set_table_stats 3 ( user, "EMP", numrows => 10000000, numblks => 1000000 ); 4 dbms_stats.set_table_stats 5 ( user, "DEPT", numrows => 1000000, numblks => 100000 ); 6 end; 7 /
PL/SQL procedure successfully completed. SQL> SQL> select * from emp, dept where emp.deptno = dept.deptno;
EMPNO Employee Name JOB MGR HIREDATE Salary COMM DEPTNO DEPTNO DNAME LOC
------------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
7369 SMITH CLERK 7902 17-DEC-80 $800.00 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-FEB-81 $1,600.00 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 $1,250.00 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-APR-81 $2,975.00 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-SEP-81 $1,250.00 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 $2,850.00 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-JUN-81 $2,450.00 10 10 ACCOUNTING NEW YORK
EMPNO Employee Name JOB MGR HIREDATE Salary COMM DEPTNO DEPTNO DNAME LOC
------------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
7788 SCOTT ANALYST 7566 09-DEC-82 $3,000.00 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-NOV-81 $5,000.00 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-SEP-81 $1,500.00 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 12-JAN-83 $1,100.00 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 $950.00 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-DEC-81 $3,000.00 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-JAN-82 $1,300.00 10 10 ACCOUNTING NEW YORK
14 rows selected. SQL> SQL> alter session set OPTIMIZER_MODE = RULE; Session altered. SQL> SQL> select * from emp, dept where emp.deptno = dept.deptno;
EMPNO Employee Name JOB MGR HIREDATE Salary COMM DEPTNO DEPTNO DNAME LOC
------------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
7782 CLARK MANAGER 7839 09-JUN-81 $2,450.00 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-NOV-81 $5,000.00 10 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 23-JAN-82 $1,300.00 10 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 02-APR-81 $2,975.00 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-DEC-81 $3,000.00 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 12-JAN-83 $1,100.00 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-DEC-80 $800.00 20 20 RESEARCH DALLAS
EMPNO Employee Name JOB MGR HIREDATE Salary COMM DEPTNO DEPTNO DNAME LOC
------------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
7788 SCOTT ANALYST 7566 09-DEC-82 $3,000.00 20 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 22-FEB-81 $1,250.00 500 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-SEP-81 $1,500.00 0 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-FEB-81 $1,600.00 300 30 30 SALES CHICAGO
7900 JAMES CLERK 7698 03-DEC-81 $950.00 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 $2,850.00 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-SEP-81 $1,250.00 1400 30 30 SALES CHICAGO
14 rows selected. SQL> SQL> drop table emp cascade constraints; Table dropped. SQL> SQL> drop table dept cascade constraints; Table dropped. SQL> SQL> SQL>
</source>
ALTER SESSION SET QUERY_REWRITE_ENABLED
<source lang="sql">
SQL> SQL> set echo on SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> SQL> ALTER SESSION SET SQL_TRACE = TRUE; Session altered. SQL> SQL> CREATE OR REPLACE FUNCTION lecturerStatus(
2 p_NumCredits IN NUMBER) 3 RETURN VARCHAR2 AS 4 BEGIN 5 IF p_NumCredits = 0 THEN 6 RETURN "Inactive"; 7 ELSIF p_NumCredits <= 12 THEN 8 RETURN "Part Time"; 9 ELSE 10 RETURN "Full Time"; 11 END IF; 12 END lecturerStatus; 13 /
Function created. SQL> SQL> SELECT id
2 FROM lecturer
3 WHERE SUBSTR(lecturerStatus(current_credits), 1, 20) =
4 "Part Time";
ID
10001
10002
10003
10004
10005
10006
6 rows selected. SQL> SQL> CREATE OR REPLACE FUNCTION lecturerStatus(p_NumCredits IN NUMBER)
2 RETURN VARCHAR2 3 DETERMINISTIC AS 4 BEGIN 5 IF p_NumCredits = 0 THEN 6 RETURN "Inactive"; 7 ELSIF p_NumCredits <= 12 THEN 8 RETURN "Part Time"; 9 ELSE 10 RETURN "Full Time"; 11 END IF; 12 END lecturerStatus; 13 /
Function created. SQL> SQL> CREATE INDEX lecturer_index ON lecturer
2 (SUBSTR(lecturerStatus(current_credits), 1, 20)) 3 COMPUTE STATISTICS;
Index created. SQL> SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE; Session altered. SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; Session altered. SQL> SQL> SELECT /*+ index(lecturer,lecturer_index) */ id
2 FROM lecturer
3 WHERE SUBSTR(lecturerStatus(current_credits), 1, 20) =
4 "Part Time";
ID
10001
10002
10003
10004
10005
10006
6 rows selected. SQL> SQL> DROP INDEX lecturer_index; Index dropped. SQL> SQL> drop table lecturer; Table dropped.
</source>
alter session set query_rewrite_integrity=enforced
<source lang="sql">
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); SQL> SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 );
SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); SQL> SQL> SQL> SQL> alter session set query_rewrite_enabled=true; SQL> SQL> alter session set query_rewrite_integrity=enforced; SQL> SQL> set autotrace traceonly explain SQL> SQL> select dept.deptno, dept.dname, count (*)
2 from emp, dept 3 where emp.deptno = dept.deptno 4 group by dept.deptno, dept.dname 5 /
Execution Plan
Plan hash value: 3219813164
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 67 | 4690 | 5 | | 1 | SORT GROUP BY | | 67 | 4690 | 5 | |* 2 | HASH JOIN | | 67 | 4690 | 3 | | 3 | TABLE ACCESS FULL| EMP | 82 | 1066 | 1 | | 4 | TABLE ACCESS FULL| DEPT | 82 | 4674 | 1 |
Predicate Information (identified by operation id):
2 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
Note
- cpu costing is off (consider enabling it)
SQL> SQL> SQL> select count(*) from emp; Execution Plan
Plan hash value: 2083865914
| Id | Operation | Name | Rows | Cost |
| 0 | SELECT STATEMENT | | 1 | 1 | | 1 | SORT AGGREGATE | | 1 | | | 2 | TABLE ACCESS FULL| EMP | 82 | 1 |
Note
- cpu costing is off (consider enabling it)
SQL> SQL> select * from dept where initcap(dname) = "Sales"; Execution Plan
Plan hash value: 3383998547
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 39 | 1 | |* 1 | TABLE ACCESS FULL| DEPT | 1 | 39 | 1 |
Predicate Information (identified by operation id):
1 - filter(INITCAP("DNAME")="Sales")
Note
- cpu costing is off (consider enabling it)
SQL> SQL> SQL> set autotrace off SQL> SQL> SQL> drop table emp; SQL> SQL> drop table dept;
</source>
alter session set sort_area_size = 102400000
<source lang="sql">
SQL> create table myTable
2 as 3 select rownum id, a.* 4 from all_objects a 5 where 1=0 6 /
SQL> SQL> SQL> SQL> set autotrace traceonly explain SQL> SQL> alter session set sort_area_size = 102400000; SQL> SQL> alter session set hash_area_size = 204800000; SQL> SQL> select a.object_type, b.object_name
2 from myTable a, myTable b 3 where a.last_ddl_time = b.last_ddl_time;
Execution Plan
Plan hash value: 2666611345
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 67 | 3082 | 3 | |* 1 | HASH JOIN | | 67 | 3082 | 3 | | 2 | TABLE ACCESS FULL| MYTABLE | 82 | 1640 | 1 | | 3 | TABLE ACCESS FULL| MYTABLE | 82 | 2132 | 1 |
Predicate Information (identified by operation id):
1 - access("A"."LAST_DDL_TIME"="B"."LAST_DDL_TIME")
Note
- cpu costing is off (consider enabling it)
SQL> SQL> alter session set sort_area_size = 65536; SQL> SQL> alter session set hash_area_size = 131072; SQL> SQL> select a.object_type, b.object_name
2 from myTable a, myTable b 3 where a.last_ddl_time = b.last_ddl_time;
Execution Plan
Plan hash value: 2666611345
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 67 | 3082 | 3 | |* 1 | HASH JOIN | | 67 | 3082 | 3 | | 2 | TABLE ACCESS FULL| MYTABLE | 82 | 1640 | 1 | | 3 | TABLE ACCESS FULL| MYTABLE | 82 | 2132 | 1 |
Predicate Information (identified by operation id):
1 - access("A"."LAST_DDL_TIME"="B"."LAST_DDL_TIME")
Note
- cpu costing is off (consider enabling it)
SQL> SQL> set autotrace off SQL> SQL> SQL> drop table myTable;
</source>
ALTER SESSION SET SQL_TRACE = TRUE
<source lang="sql">
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY, 3 fname VARCHAR2(50), 4 lname VARCHAR2(50) 5 );
Table created. SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "Enn", "F"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z"); 1 row created. SQL> SQL> SQL> ALTER SESSION SET SQL_TRACE = TRUE; Session altered. SQL> SELECT lname
2 FROM emp 3 WHERE fname = "Mike";
no rows selected SQL> ALTER SESSION SET SQL_TRACE = FALSE; Session altered. SQL> SQL> drop table emp; Table dropped.
</source>
alter session set use_stored_outlines
<source lang="sql">
SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> set autotrace traceonly explain SQL> select empno, ename from emp where empno > 0
2 /
Execution Plan
Plan hash value: 3956160932
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 4 | 80 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 4 | 80 | 2 (0)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter("EMPNO">0)
Note
- outline "MYOUTLINE" used for this statement
SQL> set autotrace off SQL> SQL> create or replace outline MyOutline
2 for category mycategory 3 ON 4 select empno, ename from emp where empno > 0 5 /
Outline created. SQL> SQL> alter session set use_stored_outlines = mycategory
2 /
Session altered. SQL> set autotrace traceonly explain SQL> select empno, ename from emp where empno > 0
2 /
Execution Plan
Plan hash value: 3956160932
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 4 | 80 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 4 | 80 | 2 (0)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter("EMPNO">0)
Note
- outline "MYOUTLINE" used for this statement
SQL> set autotrace off SQL> SQL> drop table emp; Table dropped. SQL>
</source>
alter session set workarea_size_policy=manual
<source lang="sql">
SQL> create table t as select * from all_objects where rownum < 20; Table created. SQL> SQL> exec dbms_stats.gather_table_stats( user, "T" ); PL/SQL procedure successfully completed. SQL> SQL> alter session set workarea_size_policy=manual; Session altered. SQL> alter session set sort_area_size = 65536; Session altered. SQL> set termout off SQL> select * from t where rownum < 20 order by 1, 2, 3, 4 ; OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS CON$
28 28 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_COBJ#
29 29 CLUSTER
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS FILE$
17 17 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS ICOL$
20 2 TABLE
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS IND$
19 2 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF2 OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
51 51 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF4
53 53 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS I_CON1
48 48 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE#_BLOCK#
9 9 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS I_FILE1
41 41 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_IND1
39 39 INDEX
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ#
3 3 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ3 OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
38 38 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_PROXY_ROLE_DATA$_1
26 26 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS I_TS#
7 7 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_USER1
44 44 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS PROXY_ROLE_DATA$
25 25 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UET$
13 8 TABLE
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UNDO$
15 15 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N
19 rows selected. SQL> SQL> set termout on SQL> alter session set sort_area_size=1048576; Session altered. SQL> set termout off SQL> select * from t where rownum < 20 order by 1, 2, 3, 4; OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS CON$
28 28 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_COBJ#
29 29 CLUSTER
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS FILE$
17 17 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS ICOL$
20 2 TABLE
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS IND$
19 2 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF2 OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
51 51 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF4
53 53 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS I_CON1
48 48 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE#_BLOCK#
9 9 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS I_FILE1
41 41 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_IND1
39 39 INDEX
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ#
3 3 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ3 OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
38 38 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_PROXY_ROLE_DATA$_1
26 26 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS I_TS#
7 7 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_USER1
44 44 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS PROXY_ROLE_DATA$
25 25 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UET$
13 8 TABLE
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UNDO$
15 15 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N
19 rows selected. SQL> set termout on SQL> alter session set sort_area_size=1073741820; Session altered. SQL> set termout off SQL> select * from t where rownum < 20 order by 1, 2, 3, 4; OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS CON$
28 28 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_COBJ#
29 29 CLUSTER
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS FILE$
17 17 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS ICOL$
20 2 TABLE
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS IND$
19 2 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF2 OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
51 51 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF4
53 53 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS I_CON1
48 48 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE#_BLOCK#
9 9 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS I_FILE1
41 41 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_IND1
39 39 INDEX
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ#
3 3 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ3 OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
38 38 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_PROXY_ROLE_DATA$_1
26 26 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS I_TS#
7 7 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_USER1
44 44 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS PROXY_ROLE_DATA$
25 25 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UET$
13 8 TABLE
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UNDO$
15 15 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N
19 rows selected. SQL> set termout on SQL> SQL> drop table t; Table dropped. SQL> SQL>
</source>
<source lang="sql">
SQL> alter system flush shared_pool; SQL> SQL> alter session set optimizer_index_cost_adj=100; SQL> SQL> alter session set optimizer_index_caching=0; SQL> SQL> begin
2 dbms_stats.import_system_stats( stattab => "SYSTEM_STATS", statid => "OLTP", statown => user ); 3 end; 4 / </source>
alter system quiesce restricted
<source lang="sql">
SQL> alter system quiesce restricted; SQL> SQL>
</source>
alter system resume
<source lang="sql">
SQL> alter system resume; SQL> SQL>
</source>
alter system suspend
<source lang="sql">
SQL> alter system suspend;
</source>
alter the session with the ALTER SESSION statement and set the session"s time zone forward
<source lang="sql">
SQL> SQL> ALTER SESSION SET TIME_ZONE = "+9:00"; Session altered. SQL> Session altered. SQL> SQL> SELECT
2 DBTIMEZONE db_time, 3 SESSIONTIMEZONE session_time 4 FROM dual;
DB_TIM SESSION_TIME
---------------------------------------------------------------------------
+00:00 +09:00 SQL>
</source>
Alter time_zone
<source lang="sql">
SQL> SQL> SQL> column sessiontimezone for a15 SQL> SQL> select sessiontimezone, current_date from dual; SESSIONTIMEZONE CURRENT_D
---------
-07:00 16-JUN-08 1 row selected. SQL> SQL> alter session set time_zone = "-08:00"; Session altered. SQL> SQL> select sessiontimezone, current_date from dual; SESSIONTIMEZONE CURRENT_D
---------
-08:00 16-JUN-08 1 row selected. SQL> SQL> --
</source>
demonstrates the use of DBMS_SQL to execute an ALTER SESSION statement.
<source lang="sql">
SQL> SQL> CREATE OR REPLACE PROCEDURE AlterSession(
2 p_SessionString IN VARCHAR2) AS 3 4 v_CursorID INTEGER; 5 v_Dummy INTEGER; 6 BEGIN 7 v_CursorID := DBMS_SQL.OPEN_CURSOR; 8 9 DBMS_SQL.PARSE(v_CursorID, p_SessionString, DBMS_SQL.NATIVE); 10 v_Dummy := DBMS_SQL.EXECUTE(v_CursorID); 11 END AlterSession; 12 /
Procedure created. SQL> SQL> column sysdate format a30 SQL> SELECT SYSDATE FROM dual; SYSDATE
Jun 19, 2008 20:38:44 1 row selected. SQL> SQL> DECLARE
2 sqlString VARCHAR2(100); 3 BEGIN 4 sqlString := "ALTER SESSION SET NLS_DATE_FORMAT = " || 5 """Mon DD, YYYY HH24:MI:SS"""; 6 AlterSession(sqlString); 7 END; 8 /
PL/SQL procedure successfully completed. SQL> SQL> SELECT SYSDATE FROM dual; SYSDATE
Jun 19, 2008 20:38:44 1 row selected. SQL>
</source>
event-based call and exception tracing.
<source lang="sql">
SQL> SQL> set serveroutput on SQL> SQL> VARIABLE v_String1 VARCHAR2(20); SQL> VARIABLE v_String2 VARCHAR2(20); SQL> SQL> BEGIN
2 :v_String1 := "Hello"; 3 :v_String2 := " World!"; 4 END; 5 /
PL/SQL procedure successfully completed. SQL> SQL> ALTER SESSION SET EVENTS "10938 trace name context level 33"; Session altered. SQL> SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(:v_String1 || :v_String2); 3 END; 4 /
Hello World! PL/SQL procedure successfully completed. SQL> SQL>
</source>
If your session time zone is not US/Central (-06:00), alter your session to Central time:
<source lang="sql">
SQL> SQL> SQL> alter session set time_zone = "-06:00"; Session altered. SQL> SQL> SQL> --
</source>
If your session time zone is not US/Central Standard Time (-06:00), alter your session to Central Standard time:
<source lang="sql">
SQL> SQL> SQL> alter session set time_zone = "-06:00"; Session altered. SQL>
</source>
interaction between ALTER SESSION and autonomous transactions.
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> CREATE OR REPLACE PROCEDURE InsertDate1(p_Msg IN VARCHAR2) AS
2 PRAGMA AUTONOMOUS_TRANSACTION; 3 BEGIN 4 INSERT INTO MyTable(num_col, char_col) 5 VALUES (400, p_Msg || ": " || SYSDATE); 6 COMMIT; 7 END InsertDate1; 8 /
Procedure created. SQL> show errors No errors. SQL> SQL> CREATE OR REPLACE PROCEDURE InsertDate2(p_Msg IN VARCHAR2) AS
2 PRAGMA AUTONOMOUS_TRANSACTION; 3 BEGIN 4 EXECUTE IMMEDIATE 5 "ALTER SESSION SET NLS_DATE_FORMAT = 6 ""MM/DD/YYYY HH24:MI:SS"""; 7 8 INSERT INTO MyTable(num_col, char_col) 9 VALUES (400, p_Msg || ": " || SYSDATE); 10 COMMIT; 11 END InsertDate2; 12 /
Procedure created. SQL> show errors No errors. SQL> SQL> SQL> DELETE FROM MyTable; 0 rows deleted. SQL> SQL> ALTER SESSION SET NLS_DATE_FORMAT = "DD-MON-YYYY HH24:MI:SS"; Session altered. SQL> SQL> BEGIN
2 InsertDate1("First insert");
3 InsertDate2("Second insert");
4 InsertDate1("Third insert");
5 END;
6 /
BEGIN
ERROR at line 1: ORA-00034: cannot COMMIT in current PL/SQL session ORA-06512: at "sqle.INSERTDATE1", line 6 ORA-06512: at line 2
SQL> SQL> SELECT char_col
2 FROM MyTable 3 WHERE num_col = 400;
no rows selected SQL> SQL> ALTER SESSION DISABLE COMMIT IN PROCEDURE; Session altered. SQL> SQL> BEGIN
2 InsertDate1("With COMMIT IN PROCEDURE disabled");
3 COMMIT;
4 END;
5 /
BEGIN
ERROR at line 1: ORA-00034: cannot COMMIT in current PL/SQL session ORA-06512: at "sqle.INSERTDATE1", line 6 ORA-06512: at line 2
SQL> SQL> SQL> drop table MyTable; Table dropped. SQL> SQL>
</source>
sessiontimezone, current_timestamp
<source lang="sql">
SQL> SQL> column sessiontimezone for a15 SQL> col current_timestamp format a36 SQL> SQL> select sessiontimezone, current_timestamp
2 from dual;
SESSIONTIMEZONE CURRENT_TIMESTAMP
------------------------------------
-08:00 16-JUN-08 04.26.31.666000 PM -08:00 1 row selected. SQL> SQL> alter session set time_zone = "-11:00"; Session altered. SQL> SQL> select sessiontimezone, current_timestamp(3) current_timestamp from dual; SESSIONTIMEZONE CURRENT_TIMESTAMP
------------------------------------
-11:00 16-JUN-08 01.26.31.806 PM -11:00 1 row selected. SQL> SQL> --
</source>
set optimizer_features_enable = "8.1.5" scope = spfile
<source lang="sql">
SQL> create table t as select * from all_objects; SQL> SQL> create or replace function get_row_cnt return number
2 as 3 countValue number; 4 begin 5 select count(*) into countValue from t; 6 return countValue; 7 end; 8 /
SQL> SQL> show parameter optimizer_features
NAME TYPE VALUE
----------- ------------------------------
optimizer_features_enable string 8.1.5 SQL> SQL> alter session set sql_trace=true; SQL> SQL> SQL> exec dbms_output.put_line( get_row_cnt ); 12583 SQL> SQL> alter system
2 set optimizer_features_enable = "8.1.5" scope = spfile;
SQL> SQL> drop table t; SQL>
</source>
Set the following session parameters to enable query rewrite:
<source lang="sql">
SQL> SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE; Session altered. SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY=ENFORCED; Session altered. SQL> SQL> --
</source>
show filestat
<source lang="sql">
SQL> select tablespace_name, file_name, PHYRDS, PHYWRTS, PHYBLKRD, PHYBLKWRT
2 from v$filestat, dba_data_files 3 where file_id = file# 4 order by PHYRDS, PHYWRTS desc 5 /
TABLESPACE_NAME FILE_NAME PHYRDS PHYWRTS PHYBLKRD PHYBLKWRT
---------------------------------------------------- ------------ -------- ------------ ---------
DATA_1 C:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABAS 14 8 14 8
E\DATA_1B.DBF
DATA_1 C:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABAS 15 8 15 8
E\DATA_1A.DBF
USERS C:\ORACLEXE\ORADATA\XE\USERS.DBF 24 30 24 57 UNDO C:\ORACLEXE\ORADATA\XE\UNDO.DBF 1,252 8,112 1,252 18,054 SYSAUX C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 2,093 1,450 3,476 1,624 SYSTEM C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 21,550 11,387 58,012 20,835 6 rows selected. SQL> spool off
</source>
show parameter optimizer_features
<source lang="sql">
SQL> create table t as select * from all_objects; SQL> SQL> create or replace function get_row_cnt return number
2 as 3 countValue number; 4 begin 5 select count(*) into countValue from t; 6 return countValue; 7 end; 8 /
SQL> SQL> show parameter optimizer_features
NAME TYPE VALUE
----------- ------------------------------
optimizer_features_enable string 8.1.5 SQL> SQL> alter session set sql_trace=true; SQL> SQL> SQL> exec dbms_output.put_line( get_row_cnt ); 12583 SQL> SQL> alter system
2 set optimizer_features_enable = "8.1.5" scope = spfile;
SQL> SQL> drop table t; SQL>
</source>
show verify
<source lang="sql">
SQL> SQL> SQL> set verify on SQL> set verify off SQL> show verify verify OFF SQL> SQL>
</source>
SQL trace
<source lang="sql">
SQL> SQL> SQL> set serveroutput on SQL> SQL> VARIABLE v_String1 VARCHAR2(20); SQL> VARIABLE v_String2 VARCHAR2(20); SQL> SQL> BEGIN
2 :v_String1 := "Hello"; 3 :v_String2 := " World!"; 4 END; 5 /
PL/SQL procedure successfully completed. SQL> SQL> ALTER SESSION SET EVENTS "10046 trace name context forever, level 4"; Session altered. SQL> SQL> SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(:v_String1 || :v_String2); 3 END; 4 /
Hello World! PL/SQL procedure successfully completed. SQL> SQL>
</source>
sql_trace a stored procedure
<source lang="sql">
SQL> SQL> set echo on SQL> SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> create or replace procedure show_emps
2 as 3 begin 4 for x in ( select ename, empno 5 from emp 6 where empno > 0 ) 7 loop 8 dbms_output.put_line( x.empno || "," || x.ename ); 9 end loop; 10 end; 11 /
Procedure created. SQL> SQL> alter session set sql_trace=true; Session altered. SQL> exec show_emps 7369,SMITH 7499,ALLEN 7521,WARD 7566,JONES 7654,MARTIN 7698,BLAKE 7782,CLARK 7788,SCOTT 7839,KING 7844,TURNER 7876,ADAMS 7900,JAMES 7902,FORD 7934,MILLER PL/SQL procedure successfully completed. SQL> SQL> @gettrace SP2-0310: unable to open file "gettrace.sql" SQL> SQL> set autotrace off SQL> SQL> drop table emp; Table dropped. SQL>
</source>
uses dynamic SQL to issue an ALTER SESSION statement.
<source lang="sql">
SQL> SQL> CREATE TABLE session (
2 department CHAR(3), 3 course NUMBER(3), 4 description VARCHAR2(2000), 5 max_lecturer NUMBER(3), 6 current_lecturer NUMBER(3), 7 num_credits NUMBER(1), 8 room_id NUMBER(5) 9 );
Table created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created. SQL> SQL> SQL> CREATE TABLE place (
2 room_id NUMBER(5) PRIMARY KEY, 3 building VARCHAR2(15), 4 room_number NUMBER(4), 5 number_seats NUMBER(4), 6 description VARCHAR2(50) 7 );
Table created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20001, "Building 7", 201, 1000, "Large Lecture Hall");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20002, "Building 6", 101, 500, "Small Lecture Hall");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20003, "Building 6", 150, 50, "Discussion Room A");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20004, "Building 6", 160, 50, "Discussion Room B");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20005, "Building 6", 170, 50, "Discussion Room C");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20006, "Music Building", 100, 10, "Music Practice Room");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20007, "Music Building", 200, 1000, "Concert Room");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20008, "Building 7", 300, 75, "Discussion Room D");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20009, "Building 7", 310, 50, "Discussion Room E");
1 row created. SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> SQL> DECLARE
2 v_RoomRec place%ROWTYPE; 3 myLecturerRec lecturer%ROWTYPE; 4 v_sessionRec session%ROWTYPE; 5 BEGIN 6 SELECT * 7 INTO v_RoomRec 8 FROM place 9 WHERE room_ID = 20001; 10 11 EXECUTE IMMEDIATE "ALTER SESSION SET SQL_TRACE = TRUE"; 12 13 SELECT * 14 INTO myLecturerRec 15 FROM lecturer 16 WHERE ID = 10007; 17 18 EXECUTE IMMEDIATE "ALTER SESSION SET SQL_TRACE = FALSE"; 19 20 SELECT * 21 INTO v_sessionRec 22 FROM session 23 WHERE department = "NUT" and course = 307; 24 END; 25 /
PL/SQL procedure successfully completed. SQL> SQL> drop table session; Table dropped. SQL> SQL> drop table place; Table dropped. SQL> drop table lecturer; Table dropped. SQL>
</source>
