一个微信群中有位朋友问“一张几亿的分区表,能改名么?”。我想他要表达的,不是语法上是否可以改名,而是改名是否有什么影响?
是否有影响,需要看看背后做了什么。
创建测试表,
create table tbl_par ( id number, insert_time date ) partition by range (insert_time) ( partition par_1 values less than (to_date('2017-01-02', 'yyyy-mm-dd')), partition par_2 values less than (to_date('2017-01-03', 'yyyy-mm-dd')), partition par_3 values less than (to_date('2017-01-04', 'yyyy-mm-dd')) );
插入测试数据,
SQL> select insert_time, count(*) from tbl_par group by insert_time; INSERT_TI COUNT(*) --------- ---------- 01-JAN-17 10000 02-JAN-17 10000 03-JAN-17 10000 SQL> select count(*) from tbl_par partition(par_1); COUNT(*) ---------- 10000 SQL> select count(*) from tbl_par partition(par_2); COUNT(*) ---------- 10000 SQL> select count(*) from tbl_par partition(par_3); COUNT(*) ---------- 10000
执行10046事件,
SQL> alter session set events '10046 trace name context forever, level 12'; Session altered. SQL> alter table tbl_par rename to tbl_par_k; Table altered. SQL> alter session set events '10046 trace name context off'; Session altered.
从trace看,首先以对表TBL_PAR以NOWAIT加了EXCLUSIVE表级排他锁,
LOCK TABLE "TBL_PAR" IN EXCLUSIVE MODE NOWAIT
此时禁止对表进行DDL或DML操作,若执行了DDL操作,直接提示“ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired”的错误,若执行了DML操作,则处于hang,但允许执行select(非for update)操作。
接着有一段自治事务,判断table rename的操作,
又做了一系列的CRUD操作,主要是针对数据字典表,总计100次select,7次insert,16次delete,10次update,
整个trace文件一共4107行,大约执行一半的时候,从obj$%E