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>