标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,670)
- DB2 (22)
- MySQL (73)
- Oracle (1,532)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (21)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (65)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (560)
- Oracle安装升级 (91)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (78)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- ORA-600 krse_arc_complete.4
- Oracle 19c 202410补丁(RUs+OJVM)
- ntfs MFT损坏(ntfs文件系统故障)导致oracle异常恢复
- .mkp扩展名oracle数据文件加密恢复
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-01092 ORA-00604 ORA-01558故障处理
- ORA-65088: database open should be retried
- Oracle 19c异常恢复—ORA-01209/ORA-65088
- ORA-600 16703故障再现
- 数据库启动报ORA-27102 OSD-00026 O/S-Error: (OS 1455)
- .[metro777@cock.li].Elbie勒索病毒加密数据库恢复
- 应用连接错误,初始化mysql数据库恢复
- RAC默认服务配置优先节点
- Oracle 19c RAC 替换私网操作
- 监听报TNS-12541 TNS-12560 TNS-00511错误
- drop tablespace xxx including contents恢复
- Linux 8 修改网卡名称
标签归档:Identity Columns
ORACLE 12C 新特性Identity Columns—实现ORACLE自增长列功能
在ORACLE 12C以前的版本中,如果要实现列自增长,需要通过序列+触发器实现,到了12C ORACLE 引进了Identity Columns新特性,从而实现了列自增长功能,和mysql,sql server类似功能.
使用语法
GENERATED ALWAYS AS IDENTITY方式测试
C:\Users\ffcheng>sqlplus chf/xifenfei@pdb SQL*Plus: Release 12.1.0.2.0 Production on 星期二 3月 10 14:34:46 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. 上次成功登录时间: 星期五 12月 19 2014 21:00:26 +08:00 连接到: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt ions XFF_PDB@CHF> select * from v$version; BANNER CON_ID -------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12. 0 1.0.2.0 - 64bit Production PL/SQL Release 12.1.0.2.0 - Production 0 CORE 12.1.0.2.0 Production 0 TNS for 64-bit Windows: Version 12.1.0.2.0 - Produ 0 ction NLSRTL Version 12.1.0.2.0 - Production 0 XFF_PDB@CHF> create table t_xifenfei (id number GENERATED ALWAYS AS IDENTITY,na me varchar2(100)); 表已创建。 XFF_PDB@CHF> select object_name,object_type from user_objects; OBJECT_NAME OBJECT_TYPE --------------- ----------------------- ISEQ$$_91982 SEQUENCE T_XIFENFEI TABLE XFF_PDB@CHF> set long 10000 XFF_PDB@CHF> select dbms_metadata.get_ddl('TABLE','T_XIFENFEI') FROM DUAL; DBMS_METADATA.GET_DDL('TABLE','T_XIFENFEI') -------------------------------------------------------------------------------- CREATE TABLE "CHF"."T_XIFENFEI" ( "ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOT NULL ENABLE, "NAME" VARCHAR2(100) ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "INMEMORY" XFF_PDB@CHF> INSERT INTO T_XIFENFEI VALUES(1,'WWW.XIFNEFEI.COM'); INSERT INTO T_XIFENFEI VALUES(1,'WWW.XIFNEFEI.COM') * 第 1 行出现错误: ORA-32795: 无法插入到“始终生成”身份列 XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.XIFNEFEI.COM'); 已创建 1 行。 XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.orasos.COM'); 已创建 1 行。 XFF_PDB@CHF> commit; 提交完成。 XFF_PDB@CHF> col name for a30 XFF_PDB@CHF> select * from t_xifenfei; ID NAME ---------- ------------------------------ 1 WWW.XIFNEFEI.COM 2 WWW.orasos.COM XFF_PDB@CHF> update t_xifenfei set id=3 where id=2; update t_xifenfei set id=3 where id=2 * 第 1 行出现错误: ORA-32796: 无法更新“始终生成”身份列 XFF_PDB@CHF> delete from t_xifenfei where id=1; 已删除 1 行。 XFF_PDB@CHF> commit; 提交完成。 XFF_PDB@CHF> select ISEQ$$_91982.nextval from dual; NEXTVAL ---------- 3 XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.ORASOS.COM'); 已创建 1 行。 XFF_PDB@CHF> commit; 提交完成。 XFF_PDB@CHF> select * from t_xifenfei; ID NAME ---------- ------------------------------ 2 WWW.orasos.COM 4 WWW.ORASOS.COM XFF_PDB@CHF> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 1 2'; 会话已更改。 XFF_PDB@CHF> select value from v$diag_info where name='Default Trace File'; VALUE -------------------------------------------------------------------------------- D:\APP\FFCHENG\diag\rdbms\xff\xff\trace\xff_ora_10628.trc XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.XIFENFEI.COM'); 已创建 1 行。 XFF_PDB@CHF> COMMIT; 提交完成。 --跟踪trace文件 PARSING IN CURSOR #688719640 len=55 dep=0 uid=103 oct=2 lid=103 tim=15129490112 hv=961646460 ad='7ff05d11a18' sqlid='21uzyjhwp33vw' INSERT INTO T_XIFENFEI(name) VALUES('WWW.XIFENFEI.COM') END OF STMT PARSE #688719640:c=15600,e=18909,p=0,cr=44,cu=0,mis=1,r=0,dep=0,og=1,plh=2541165129,tim=15129490112 EXEC #688719640:c=0,e=347,p=0,cr=1,cu=5,mis=0,r=1,dep=0,og=1,plh=2541165129,tim=15129490731 STAT #688719640 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL T_XIFENFEI (cr=1 pr=0 pw=0 time=296 us)' STAT #688719640 id=2 cnt=1 pid=1 pos=1 obj=91983 op='SEQUENCE ISEQ$$_91982 (cr=0 pr=0 pw=0 time=89 us)' WAIT #688719640: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=15129490971 XFF_PDB@CHF> alter table t_xifenfei2 modify(id number GENERATED ALWAYS AS IDENTI TY MINVALUE 1 MAXVALUE 99999999999999 INCREMENT BY 1 START WITH 1 CACHE 100); 表已更改。 XFF_PDB@CHF> drop sequence ISEQ$$_91982; drop sequence ISEQ$$_91982 * 第 1 行出现错误: ORA-32794: 无法删除系统生成的序列 XFF_PDB@CHF> drop table t_xifenfei; 表已删除。 XFF_PDB@CHF> select object_name,object_type from user_objects where object_name= 'ISEQ$$_91982'; OBJECT_NAME OBJECT_TYPE -------------------- ----------------------- ISEQ$$_91982 SEQUENCE XFF_PDB@CHF> select object_name,object_type from user_objects where object_name= 'ISEQ$$_91982'; OBJECT_NAME OBJECT_TYPE -------------------- ----------------------- ISEQ$$_91982 SEQUENCE XFF_PDB@CHF> purge table t_xifenfei; 表已清除。 XFF_PDB@CHF> select object_name,object_type from user_objects where object_name= 'ISEQ$$_91982'; 未选定行
这里的出来几个结论:
1. GENERATED ALWAYS AS IDENTITY 列无法人工指定值和修改该值
2. GENERATED IDENTITY 本质也是通过sequence实现
3. GENERATED IDENTITY 中sequence不能单独被删除
4. GENERATED IDENTITY 中的表删除,如果存在回收站中,该sequence依然存储,如果表被彻底删除,则sequence也被删除
5. GENERATED IDENTITY 中的sequence可以通过select 语句查询
6. 通过alert table 语句来修改GENERATED IDENTITY 的sequence相关值
GENERATED BY DEFAULT AS IDENTITY方式测试
XFF_PDB@CHF> create table t_xifenfei2 (id number GENERATED BY DEFAULT AS IDENTI TY,name varchar2(100)) tablespace users; 表已创建。 XFF_PDB@CHF> insert into t_xifenfei2 values (1,'www.xifenfei.com'); 已创建 1 行。 XFF_PDB@CHF> insert into t_xifenfei2(name) values ('www.orasos.com'); 已创建 1 行。 XFF_PDB@CHF> col name for a20 XFF_PDB@CHF> select * from t_xifenfei2; ID NAME ---------- -------------------- 1 www.xifenfei.com 1 www.orasos.com XFF_PDB@CHF> insert into t_xifenfei2 values (null,'www.xifenfei.com'); insert into t_xifenfei2 values (null,'www.xifenfei.com') * 第 1 行出现错误: ORA-01400: 无法将 NULL 插入 ("CHF"."T_XIFENFEI2"."ID") XFF_PDB@CHF> desc t_xifenfei2 名称 是否为空? 类型 ----------------------------------------- -------- --------------------------- ID NOT NULL NUMBER NAME VARCHAR2(100)
得出结论:
1. GENERATED BY DEFAULT AS IDENTITY方式不能在该列中插入null值
2. GENERATED BY DEFAULT AS IDENTITY方式可以指定具体值插入
GENERATED BY DEFAULT ON NULL AS IDENTITY方式测试
XFF_PDB@CHF> create table t_xifenfei3 (id number GENERATED BY DEFAULT on null A S IDENTITY,name varchar2(100)) tablespace users; 表已创建。 XFF_PDB@CHF> desc t_xifenfei3 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER NAME VARCHAR2(100) XFF_PDB@CHF> insert into t_xifenfei3 values (null,'www.xifenfei.com'); 已创建 1 行。 XFF_PDB@CHF> select * from t_xifenfei3; ID NAME ---------- -------------------- 1 www.xifenfei.com
测试结论:GENERATED BY DEFAULT ON NULL AS IDENTITY的列上可以查询null值,只是默认转换为对应的sequence值
传统自增长列实现方法
XFF_PDB@CHF> create table t_xifenfei4 (id number,name varchar2(100)) tablespace users; 表已创建。 XFF_PDB@CHF> create sequence xff_sequence 2 increment by 1 3 minvalue 1 4 nomaxvalue 5 start with 1 6 cache 20 7 order; 序列已创建。 XFF_PDB@CHF> create or replace trigger xifenfei_id 2 before insert on t_xifenfei4 3 for each row 4 begin 5 select xff_sequence.nextval into :new.id from dual; 6 end; 7 / 触发器已创建 XFF_PDB@CHF> insert into t_xifenfei4(name) values('www.xifenfei.com'); 已创建 1 行。 XFF_PDB@CHF> select * from t_xifenfei4; ID NAME ---------- -------------------- 1 www.xifenfei.com