alter session set workarea_size_policy=manual : Session variable « SQL Plus « Oracle PL / SQL






  1. Oracle PL / SQL
  2. SQL Plus
  3. Session variable

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>








Related examples in the same category

1.alter the session with the ALTER SESSION statement and set the session's time zone forward
2.alter session set nls_date_format = 'dd-MON-yyyy hh24:mi:ss';
3.alter session set OPTIMIZER_MODE = RULE
4.Adjust your session time zone to -08:00, display the contents of your time table.
5.alter session set cursor_sharing = force
6.alter session set use_stored_outlines
7.Event-based call and exception tracing
8.event-based call and exception tracing.
9.SQL trace
10.uses dynamic SQL to issue an ALTER SESSION statement.
11.ALTER SESSION SET QUERY_REWRITE_ENABLED
12.alter session set sql_trace=true
13.sql_trace a stored procedure
14.Set the following session parameters to enable query rewrite:
15.If your session time zone is not US/Central (-06:00), alter your session to Central time:
16.alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'
17.Alter time_zone
18.sessiontimezone, current_timestamp
19.demonstrates the use of DBMS_SQL to execute an ALTER SESSION statement.
20.interaction between ALTER SESSION and autonomous transactions.
21.ALTER SESSION SET SQL_TRACE = TRUE
22.ALTER SESSION SET optimizer_dynamic_sampling
23.alter session set NLS_DATE_FORMAT='DD-MM-YYYY'
24.alter session set current_schema=Smart
25.alter session set db_file_multiblock_read_count
26.alter session set hash_area_size
27.alter session set nls_date_format='dd-mm-yyyy', nls_language=Dutch, nls_currency='Eur'
28.alter session set optimizer_index_cost_adj, alter session set optimizer_index_caching
29.alter session set optimizer_max_permutations=80000
30.alter session set optimizer_mode=all_rows
31.alter session set optimizer_mode=first_rows
32.alter session set query_rewrite_integrity=enforced
33.alter session set sort_area_size = 102400000
34.alter system flush shared_pool
35.alter system quiesce restricted
36.alter system resume
37.alter system suspend
38.set optimizer_features_enable = '8.1.5' scope = spfile
39.show filestat
40.show parameter optimizer_features
41.show verify
42.If your session time zone is not US/Central Standard Time (-06:00), alter your session to Central Standard time: