create sequence user_id_seq
start with 1
increment by 1
maxvalue 99999;
select * from all_sequences where sequence_owner = 'XIYE';
create table tb_user (
id number(5) not null,
name varchar(30)
);
insert into tb_user values(user_id_seq.nextval, 'root');
insert into tb_user values(user_id_seq.nextval, 'system');
insert into tb_user values(user_id_seq.nextval, 'other');
select user_id_seq.currval from dual;
explain plan for
select * from tb_user;
drop table tb_user;
drop sequence user_id_seq;
alter table tb1 modify (id char(18) not null);
alter table tb1 modify (id char(18) null);
alter table tb1 add constraint tb_id_uk unique(id);
alter table tb1 drop constraint tb_id_uk;
alter table tb1 add constraint tb_id_pk primary key(id);
alter table tb1 drop constraint tb_id_pk;
alter table tb1 drop primary key;
create table dept as select *I from scott.dept;
create table emp as select *I from scott.emp;
alter table dept add constraint dept_deptno_pk primary key(deptno);
alter table emp add constraint emp_empno_pk primary key(empno);
alter table emp add constraint emp_deptno_fk foreign key(deptno) references dept(deptno) on delete set null;
select * from emp where deptno = 20;
delete from dept where deptno = 20;
select * from emp where deptno = 20;
alter table emp drop constraint emp_deptno_fk;
alter table tb1 add constraint tb_age_ch check(age > 0 and age < 150);
insert into tb1 values('201912181725003001', 'jack', 200);
alter table tb1 drop constraint tb_age_ch;
create or replace view v1
as
select id, name, age
from tb1;
insert into v1 values('201912181725003001', 'jack', 54);
select * from v1;
create or replace view v2
as
select id, name, age
from tb1
with read only;
insert into v2 values('201912181725003001', 'jack', 54);
select * from v2;
create table emp as select * from scott.emp;
create or replace view v3
as
select empno, ename, deptno, hiredate
from emp
where deptno = 20
with check option;
insert into v3 values(20102, 'jack', 30, sysdate);
select * from v3;
drop view v1;
drop view v2;
drop view v3;
create index i1 on tb1(id);
alter index i1 rebuild;
drop index i1;