在select运行的过程中删除表会怎样

When I was reading a oracle document, it said that the select action will not put any locks on the table you are reading. That is different from what I thought before. I understand that if a table were selecting, the session will not put any locks to stop othere sesssion from writing the table. Or I should say I understand that read will not block write in Oracle.  But I was also thinging that there should be a table lock protecting the structure of the table. This lock is not to protect the data of the table only the table structure. If there is not this kind of lock that means we can drop a table when we reading it. And I think that will make the reading session be aborted because the drop action will not generate UNDO information. So I did a test.

1. first i create a table below. Then I run a sqlstatement which is very time consuming

SQL> create table scott.emp1 as select * from scott.emp;

Table created.

Elapsed: 00:00:00.09
SQL>
SQL> select * from scott.emp1 where (select count(*) from dba_objects,dba_tables) = (select count(*) from dba_objects,dba_tables);


     EMPNO ENAME                          JOB                                MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH                          CLERK                             7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN                          SALESMAN                          7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD                           SALESMAN                          7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES                          MANAGER                           7839 1981-04-02 00:00:00          1                    20
      7654 MARTIN                         SALESMAN                          7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE                          MANAGER                           7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK                          MANAGER                           7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT                          ANALYST                           7566 1987-04-19 00:00:00       3000                    20
      7839 KING                           PRESIDENT                              1981-11-17 00:00:00       5000                    10
      7844 TURNER                         SALESMAN                          7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS                          CLERK                             7788 1987-05-23 00:00:00       1100                    20

     EMPNO ENAME                          JOB                                MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
      7900 JAMES                          CLERK                             7698 1981-12-03 00:00:00        950                    30
      7902 FORD                           ANALYST                           7566 1981-12-03 00:00:00      99999                    20
      7934 MILLER                         CLERK                             7782 1982-01-23 00:00:00       9999                    10

14 rows selected.


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值