Oracle PL/SQL Tutorial/SQL PLUS Session Environment/Session Variables — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 10:04, 26 мая 2010
Содержание
- 1 alter session set hash_area_size
- 2 alter session set NLS_DATE_FORMAT="DD-MM-YYYY"
- 3 ALTER SESSION SET optimizer_dynamic_sampling
- 4 alter session set sort_area_size = 102400000
- 5 ALTER SESSION SET SQL_TRACE = TRUE
- 6 alter session set workarea_size_policy=manual
- 7 alter system flush shared_pool
- 8 alter system quiesce restricted
- 9 alter system resume
- 10 alter system suspend
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 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 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 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;