Oracle高级--事务，锁，分区

Oracle高级

--复制一个与dept表一样的表 mydept
create table mydept as select * from dept;

--mydept 表插入一条55部门的数据
insert into mydept(deptno,dname,loc)values(55,'销售','河南');
--mydept 表插入一条52部门的数据
insert into mydept(deptno,dname,loc)values(52,'销售','河南');
rollback;--回滚
commit;--提交

--同时向mydept表中插入两条数据，修改部门编号为51 LOC 为广州
insert into mydept(deptno,dname,loc)values(52,'销售','河南');
insert into mydept(deptno,dname,loc)values(53,'人事','河南');
update mydept set loc='广州' where deptno=51;

rollback;

--保存点
savepoint sp_1;
insert into mydept(deptno,dname,loc)values(52,'销售','河南');
insert into mydept(deptno,dname,loc)values(53,'人事','河南');
savepoint sp_2;
update mydept set loc='广州' where deptno=51;
savepoint sp_3;
delete from mydept;

rollback to sp_1;
rollback to sp_2;
rollback to sp_3;

select * from  mydept;

--复制表emp表  到myemp
create table myemp as select * from emp;

select * from mydept;

--事务自动提交
--set autocommit on;--开始事务自动提交   
--set autocommit on;--关闭事务自动提交  

select * from myemp;

--修改myemp表 empno 1112的员工姓名为 李球球
update myemp set ename='李球球' where empno=1112;

--for update  修改部门编号为20的数据
select * from myemp where deptno=20 for update;

--需求：给myemp表上加上共享锁
lock table myemp in share mode;
update myemp set ename='李球球' where empno=1112;

--需求L给 myemp加上排他锁
lock table myemp in exclusive mode;
lock table myemp in share mode;


--需求  把80以上的算作优秀   60-80算作及格   60以下的算作不及格 表和分区要一起建
create table grade(
       sno number,
       sname varchar2(30),
       grade number
)

partition by range(grade)(
          partition p1 values less than (60),
          partition p2 values less than (80),
          partition p3 values less than (maxvalue)
)

select * from grade;
insert into grade(sno,sname,grade)values(1,'wenwen',100);
insert into grade(sno,sname,grade)values(2,'huahua',90);
insert into grade(sno,sname,grade)values(3,'baibai',80);
insert into grade(sno,sname,grade)values(4,'hehe',59);
insert into grade(sno,sname,grade)values(5,'wenwen',100);
insert into grade(sno,sname,grade)values(6,'wenwen',100);
insert into grade(sno,sname,grade)values(7,'wenwen',100);

--查分区表的数据
select * from grade partition(p1);
select * from grade partition(p2);
select * from grade partition(p3);


--创建表空间
create tablespace yc1 datafile 'F:yc1.dbf' size 10m;
create tablespace yc2 datafile 'F:yc2.dbf' size 10m;
create tablespace yc3 datafile 'F:yc3.dbf' size 10m;
create tablespace yc4 datafile 'F:yc4.dbf' size 10m;

--创建hash分区
create table users(
       userid number,
       uname varchar2(20)
)    --;不能加  因为要和分区一起创建
partition by hash(uname)(
          partition p1 tablespace yc1,
          partition p2 tablespace yc2,
          partition p3 tablespace yc3,
          partition p4 tablespace yc4
)
create sequence seq_userid;

--插入数据
insert into users select seq_userid.nextval,dbms_random.string('1',10)from dual connect by level<=1000000;

--查看p1/p2/p3/p4分区数据
select count(1) from users partition(p1);--250699
select count(1) from users partition(p2);--249459
select count(1) from users partition(p3);--250042
select count(1) from users partition(p4);--250220

select count(1) from users;