Oracle SQL fetch阶段需要库缓存锁定/pin

嗨，谢谢您在这里的所有作品，我学到了很多!
现在，我有一些问题SQL fetch phase和library cache lock/pin 这让我呆了好几天。

这是我从某个网站上找到的东西，
SELECT statement follows following steps
1. parse : acquires library cache lock(akaDDL lock)和downgrade it to
null mode(akabreakable parse lock)
2. execute : acquires library cache pin和release it
3. fetch: breakable parse lock
4. close: release breakable parse lock

同样来自另一个网站， https://www.oreilly.com/library/view/oracle-internals-an/156592598X/ch04s05.html
Library cache locks can only be broken, however, when the library cache object is not also pinned. A pin is applied to the library cache object for aPL/SQL program unit or SQL statement while it is being compiled, parsed, or executed. (no fetch)

从以上来看，我得到的是获取阶段不REALLY NEED alibrary cache pin, from your site i also got that lets say A simple select query 'select * from abig_table' (1million-rows in big_table),does not read all rows at once, every fetch call return aslice rows ,use it,then next fetch call,next slice. etc..

我的问题是:
1> when an fetch call executing,isn't really need alibrary cache pin,to pin the execution plan in the shared pool?cascade to pin(share) the big_table too? if not,how can it be sure when executing but prevent any DDL on the big_table? will be amess?
2> 如果在第一个fetch调用之后，然后我放下表big_table，第二个fetch调用会知道吗 (发生无效？)？继续获取/返回行或返回一些错误？

多头到空头，
* 从解析，执行步骤中，我可以意识到库缓存锁定/pin的工作原理。
* but,in the FETCH step, will this step need library cache lock/pin or not? did every fetch call need get library cache lock(null)和library cache pin(share)和library cache lock/pin(depended objects) , if so, will that operation(fetch call) bring too much load(costs) to the database?

我们不 * 保证 * 在查询期间获取会起作用，因为您可能会获取，离开一周，然后再回来。我们不能在未知的时间内真正将表 “锁定”。这就是为什么我们可以在你取物的时候让事情发生在表上。你可能会走运... 你可能不会。下面是几个例子:

--
-- Session 1
--

-- tine tablespace with room for just one table

SQL> create tablespace ROOM_FOR_ONE datafile 'X:\ORADATA\DB18\PDB1\ROOM_FOR_ONE.DBF' size 2m;

Tablespace created.

SQL> create table t tablespace room_for_one
  2  as select * from dba_objects
  3  where rownum <= 1000;

Table created.

SQL>
SQL>
SQL> set arraysize 50
SQL> set pagesize 55

--
-- pausing so we will fetch approx 50 rows at a time
--
SQL> set pause on
SQL> select owner, object_name from t;


OWNER                          OBJECT_NAME
------------------------------ -------------------------------------
SYS                            I_FILE#_BLOCK#
SYS                            I_OBJ3
SYS                            I_TS1
SYS                            I_CON1
SYS                            IND$
SYS                            CDEF$
SYS                            C_TS#
SYS                            I_CCOL2
SYS                            I_PROXY_DATA$
SYS                            I_CDEF4
SYS                            I_TAB1
SYS                            CLU$
SYS                            I_PROXY_ROLE_DATA$_1
SYS                            I_OBJ1
SYS                            UNDO$
SYS                            I_UNDO2
SYS                            I_TS#
SYS                            I_FILE1
SYS                            I_COL2
SYS                            I_OBJ#
SYS                            C_OBJ#
SYS                            I_CDEF3
SYS                            C_COBJ#

--
-- Session 2
--
SQL> drop table t purge;

Table dropped.

--
-- Session 1
--

SYS                            I_CON2
SYS                            I_OBJ4
SYS                            CON$
SYS                            I_CDEF2
SYS                            ICOL$
SYS                            I_COL3
SYS                            I_CCOL1
SYS                            COL$
SYS                            I_ICOL1
...
SYS                            STREAMS$_DEST_OBJS_I
SYS                            STREAMS$_DEST_OBJ_COLS
SYS                            STREAMS$_DEST_OBJ_COLS_I
SYS                            STREAMS$_PRIVILEGED_USER

1000 rows selected.

所以你可以，即使我们 * 删除 * 表，我们仍然可以完成我们的查询，因为表数据仍然存在。但是现在让我们重复一遍，你会明白为什么我使用了一个很小的表空间:

SQL> create table t tablespace room_for_one
  2  as select * from dba_objects
  3  where rownum <= 1000;

Table created.

SQL>
SQL>
SQL> set arraysize 50
SQL> set pagesize 55

--
-- pausing so we will fetch approx 50 rows at a time
--
SQL> set pause on
SQL> select owner, object_name from t;


OWNER                          OBJECT_NAME
------------------------------ -------------------------------------
SYS                            I_FILE#_BLOCK#
SYS                            I_OBJ3
SYS                            I_TS1
SYS                            I_CON1
SYS                            IND$
SYS                            CDEF$
SYS                            C_TS#
SYS                            I_CCOL2
SYS                            I_PROXY_DATA$
SYS                            I_CDEF4
SYS                            I_TAB1
SYS                            CLU$
SYS                            I_PROXY_ROLE_DATA$_1
SYS                            I_OBJ1
SYS                            UNDO$
SYS                            I_UNDO2
SYS                            I_TS#
SYS                            I_FILE1
SYS                            I_COL2
SYS                            I_OBJ#
SYS                            C_OBJ#
SYS                            I_CDEF3
SYS                            C_COBJ#

--
-- Session 2
--
SQL> drop table t purge;

Table dropped.

SQL> create table t1 tablespace room_for_one
  2  as select * from dba_objects
  3  where rownum <= 1000;

Table created.

SQL>
SQL> insert into t1 select * from t1;

1000 rows created.

SQL> commit;

Commit complete.

SQL> insert into t1 select * from t1;

2000 rows created.

SQL> commit;

Commit complete.

SQL> insert into t1 select * from t1;
insert into t1 select * from t1
*
ERROR at line 1:
ORA-01653: unable to extend table T1 by 128 in tablespace ROOM_FOR_ONE

--
-- Session 1
--
(hit Enter to get the next set of rows)
ERROR:
ORA-08103: object no longer exists

150 rows selected.

当我创建T1时，因为表空间很小，所以我重用了T最初占用的空间。下一次获取从磁盘中获取 “垃圾”，我们知道我们的T不再存在。

还有其他例子可能会发生这种情况，例如