Oracle PL/SQL/SQL Plus/Session variable — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Текущая версия на 09:58, 26 мая 2010
Содержание
- 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.
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> --
alter session set current_schema=Smart
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>
alter session set cursor_sharing = force
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>
alter session set db_file_multiblock_read_count
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>
alter session set hash_area_size
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;
alter session set NLS_DATE_FORMAT="DD-MM-YYYY"
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>
alter session set nls_date_format="dd-mm-yyyy", nls_language=Dutch, nls_currency="Eur"
SQL> alter session
2 set nls_date_format="dd-mm-yyyy"
3 nls_language=Dutch
4 nls_currency="Eur";
Session altered.
SQL>
SQL>
alter session set nls_date_format = "DD-MON-YYYY HH24:MI:SS"
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> --
ALTER SESSION SET optimizer_dynamic_sampling
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>
alter session set optimizer_index_cost_adj, alter session set optimizer_index_caching
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>
alter session set optimizer_max_permutations=80000
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;
alter session set optimizer_mode=all_rows
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>
alter session set optimizer_mode=first_rows
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;
alter session set OPTIMIZER_MODE = RULE
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>
ALTER SESSION SET QUERY_REWRITE_ENABLED
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.
alter session set query_rewrite_integrity=enforced
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;
alter session set sort_area_size = 102400000
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;
ALTER SESSION SET SQL_TRACE = TRUE
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.
alter session set use_stored_outlines
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>
alter session set workarea_size_policy=manual
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>
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 /
alter system quiesce restricted
SQL> alter system quiesce restricted;
SQL>
SQL>
alter system resume
SQL> alter system resume;
SQL>
SQL>
alter system suspend
SQL> alter system suspend;
alter the session with the ALTER SESSION statement and set the session"s time zone forward
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>
Alter time_zone
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> --
demonstrates the use of DBMS_SQL to execute an ALTER SESSION statement.
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>
event-based call and exception tracing.
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>
If your session time zone is not US/Central (-06:00), alter your session to Central time:
SQL>
SQL>
SQL> alter session set time_zone = "-06:00";
Session altered.
SQL>
SQL>
SQL> --
If your session time zone is not US/Central Standard Time (-06:00), alter your session to Central Standard time:
SQL>
SQL>
SQL> alter session set time_zone = "-06:00";
Session altered.
SQL>
interaction between ALTER SESSION and autonomous transactions.
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>
sessiontimezone, current_timestamp
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> --
set optimizer_features_enable = "8.1.5" scope = spfile
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>
Set the following session parameters to enable query rewrite:
SQL>
SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;
Session altered.
SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY=ENFORCED;
Session altered.
SQL>
SQL> --
show filestat
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
show parameter optimizer_features
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>
show verify
SQL>
SQL>
SQL> set verify on
SQL> set verify off
SQL> show verify
verify OFF
SQL>
SQL>
SQL trace
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>
sql_trace a stored procedure
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>
uses dynamic SQL to issue an ALTER SESSION statement.
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>