ORACLE创建表空间、用户、授权和Navicat创建序列和触发器及解决ORA-00942、ORA-01219错误

删除

--- 删除用户
DROP user TEST cascade;
--- 删除表空间
drop tablespace TEST including contents and datafiles;
drop tablespace TEST_TEMP including contents and datafiles;

创建

确保路径里的文件夹都是存在的，最后一个是文件名称

---临时表空间
create temporary tablespace TEST_TEMP tempfile 'D:\softwareWork\Oracle\oradata\test\TEST_TEMP.DBF' size 50M autoextend on next 50m maxsize 4096M extent management local;
---表空间
CREATE TABLESPACE TEST DATAFILE 'D:\softwareWork\Oracle\oradata\test\TEST.DBF' size 50M  autoextend on next 50m maxsize 4096M extent management local;
--- 创建用户TEST密码TEST授予表空间TEST
create user TEST identified by TEST default tablespace TEST temporary tablespace TEST_TEMP;
---授权：不授权dba，可能会出现ORA-00942: 表或视图不存在的错误
grant connect,resource,dba to TEST;

Navicat创建序列和触发器

序列

新建序列

序列命名建议SEQ_加表名，如：SEQ_USER

触发器

新建触发器

点击定义

SEQ_USER是你创建的序列的名称，NEW.ID其中ID是你表里面的主键列名

BEGIN
  select SEQ_USER.nextval into:NEW.ID from dual;
END;

触发器命名建议TRG_加表名，如：TRG_USER

问题描述：因为每次Oracle删除数据库的时候磁盘文件还没删除，然后自己手动停止Oracle，删除磁盘里的.DBF文件导致数据库重启后无法连接。

cmd

sqlplus
sys as sysdba

执行alter database open;查看你报错的数据文件（就是你停止Oracle删除的数据库文件路径）

alter database open;

错误提示如下

alter database open
*
第 1 行出现错误:
ORA-01110: 数据文件 6: ‘D:\SOFTWAREWORK\ORACLE\ORADATA\TEST’ #这个就是你停止数据库后删除的文件路径
ORA-01115: 从文件 6 读取块时出现 IO 错误 (块 # 1)
ORA-27069: 试图超出文件范围执行 I/O
OSD-04026: 传递的参数无效。
O/S-Error: (OS 1) 函数不正确。

删除Oracle里已经删除的数据库

alter database datafile 'D:\SOFTWAREWORK\ORACLE\ORADATA\TEST' offline drop;

-- 数据库已更改。

alter database open;

--数据库已更改。

重新使用sys连接数据库即可

Oracle创建表使用自增主键本质上就是使用序列

下面这种默认使用自增主键，允许用户手动填充主键。【不推荐，因为使用自增了，还允许人为填充字段，容易出现主键冲突】

"ID" NUMBER(18,0) GENERATED BY DEFAULT AS IDENTITY

下面这种强制使用自增主键，不允许用户手动填充主键。【推荐，因为这种方式不会导致性能问题】

"ID" NUMBER(18,0) GENERATED ALWAYS AS IDENTITY

完整SQL

CREATE TABLE "DB"."TABLE_NAME" (
  "ID" NUMBER(18,0) GENERATED BY DEFAULT AS IDENTITY
)
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT;
COMMENT ON COLUMN "DB"."TABLE_NAME"."ID" IS '主键';

ALTER TABLE "DB"."TABLE_NAME" ADD CONSTRAINT "SYS_C0043295" PRIMARY KEY ("ID");

直接删除由GENERATED BY DEFAULT AS IDENTITY或GENERATED ALWAYS AS IDENTITY创建的表导致的问题

需要注意的点，如下所示：直接删除表是不会删除系统生成的序列的。因为drop table删除表以后会放到回收站，相当于逻辑删除，要清空回收站中的表才会删除序列

DROP TABLE "DB"."TABLE_NAME";

清除回收站中的所有对象（如果表被放入了回收站）

PURGE RECYCLEBIN;

要使用如下所示的SQL语句删除表及相关依赖才会把系统生成的序列一并删除，相当于是物理删除了。

DROP TABLE "DB"."TABLE_NAME" CASCADE CONSTRAINT PURGE;