表加序列3种方法及lock处理

SQL> create sequence seq1;

Sequence created.

SQL> set long 2000;

SQL> select dbms_metadata.get_ddl('SEQUENCE','SEQ1','HR') from dual;  查下默认序列号产生了什么

也证明了默任是nocycle也就是达到大值会出现错误

DBMS_METADATA.GET_DDL('SEQUENCE','SEQ1','HR')

--------------------------------------------------------------------------------

 

   CREATE SEQUENCE  "HR"."SEQ1"

 MINVALUE 1             最小

MAXVALUE 9999999999999999999999999999  最大

INCREMENT BY 1   增加

START WITH 1    开始

CACHE 20    缓存增加性能如异常会产生断号

NOORDER   不集群

NOCYCLE  不循环

NOKEEP   ?

NOSCALE   ?

GLOBAL

 

SQL> alter sequence seq1 cache 30;

Sequence altered.

 

SQL> drop sequence seq1;

Sequence dropped.

 

 

Select sequencename.nextval from dual;

这个是下一个，在不同会话预期结果都是下一个，数据会全局增加

 

 

Select sequencename.currval from dual;

而虽然是当前的值，但是它只代表的是当前会话中的值（实际中用的会非常少了）

 

方法一

SQL> create table orders (order_id number(10),order_date date,customer_id varchar2(5));

Table created.

SQL> create sequence order_seq start with 10;

Sequence created.

insert into orders (order_id,order_date,customer_id) values(order_seq.nextval,sysdate,1000);

SQL> select * from orders;

  ORDER_ID ORDER_DATE        CUSTOMER_I

---------- ------------ ----------

10 07-FEB-21        1000

SQL>  insert into orders (order_id,order_date,customer_id) values(order_seq.nextval,sysdate,1001);

1 row created.

SQL> select * from orders;

  ORDER_ID ORDER_DATE        CUSTOMER_I

---------- ------------ ----------

10 07-FEB-21        1000

11 07-FEB-21        1001

这样就让Order表的Order_id序列递增了

 

方法二

SQL> create  table orders (order_id number(10),order_date date,customer_id varchar2(5));

Table created.

 

insert into orders (order_id,order_date,customer_id) values((select max(order_id)+1 from orders),sysdate,'1000');

Update orders set order_id=1 where order_id is null;

 

 

 

 

方法三

只时候oracle 12及以后版本

SQL> create table orders2(id number generated by default as identity,name varchar2(100));

 

Table created.

 

SQL> insert into orders2(name) values('p1');

SQL> insert into orders2(name) values('p1');  不给值用默认值

SQL> insert into orders2(id,name) values('5','p1'); 给值就用给的值

SQL> insert into orders2(name) values('p1');  不给值用默认值,而且与人工输入的值没关系只与自动产生的值去相加

SQL> select * from orders2;

1 p1

2 p1

5 p1

3 p1

 

SQL> create table orders3(id number generated always,name varchar2(10));

create table orders3(id number generated always,name varchar2(10))

                                               *

ERROR at line 1:

ORA-02000: missing AS keyword

SQL> create table orders3(id number generated always as identity,name varchar2(10));

Table created.

SQL> insert into orders3(id,name) values(1,'a');  给值就报错只能总数系统本身产生序号

insert into orders3(id,name) values(1,'a')

                    *

ERROR at line 1:

ORA-32795: cannot insert into a generated always identity column

SQL> insert into orders3(name) values('a');

1 row created.

SQL> select * from orders3;

ID NAME

---------- --------------------

 1 a

以上方法2，可能会产生资源冲突。方法3只有新版本支持。

 

发现删除表的时候提示忙，正好处理下

SQL> drop table orders;

drop table orders

           *

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

 

SQL> select t2.username,t2.sid,t2.serial#,t2.logon_time

from v$locked_object t1,v$session t2

where t1.session_id=t2.sid order by t2.logon_time;  2    3 

 

USERNAME        SID    SERIAL#        LOGON_TIME

HR                      83        38136           07-FEB-21

 

SQL> select sql_text from v$session a,v$sqltext_with_newlines b

  where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value

  and a.sid=&sid order by piece;     83

 

SQL_TEXT

select * from orders

 

SQL> alter system kill session '83,38136';