SQL PLUS Session Environment Oracle PLSQL Tutorial

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>