/* =================================================================== =================================================================== */ /* tp_orders表空间,大小10M,文件大小可自动增长,允许文件扩展,最大限度为无限制 创建A_oe用户的默认表空间为tp_orders 密码 bdqn 授予connect,resource权限党文a_hr用户的employee */ --创建表空间和用户,并授予访问数据库的权限 create tablespace tp_orders datafile 'E:\E盘\tp_orders01.dbf' size 10M autoextend on; --创建用户 create user A_oe identified by bdqn default tablespace tp_orders --赋予权限 grant connect,resource to A_oe; grant select on test.employee to A_oe; grant select on test.bumen to A_oe; select * from test.employee; /* 使用序列生成部门编号的值 */ /* 从60开始,间隔是10,最大值是10000的序列的对象dept_seq */ select * from bumen; --创建一个序列 create sequence dept_seq start with 60 increment by 10 maxvalue 10000 --插入数据 insert into bumen values(dept_seq.nextval,'学术部'); insert into bumen values(dept_seq.nextval,'学术部1'); --数据迁移前的工作 drop sequence dept_seq; create sequence dept_seq start with 80 increment by 10 maxvalue 10000; create table deptBak as select * from bumen; select * from deptBak; --测试插入数据 insert into deptBak values(dept_seq.nextval,'人事部'); /* 创建A_oe模式下dept表的公有同义词,可以允许任何能够连接上数据库的用户访问 */ --创建一个测试dept表 create table dept as select * from test.bumen; select * from dept; --创建同义词 create public synonym p_sy_dept for a_oe.dept; --赋予权限 grant select on test.customers to A_oe; grant create public synonym to A_oe; select * from p_sy_dept; /* 切换用户,操作使用test用户 */ --查看并且操作employee表 select * from customers; --为客户编号创建反向建索引 create index index_reverse_customer_id on customers (customer_id) reverse; --为地域列创建位图索引 create bitmap index index_nls_territory on customers (nls_territory); --为名和姓氏列创建组合索引 create index index_cus on customers(cust_fiest_name,cust_last_name); /* 根据订单表创建范围分区表 */ --(1)已完成 --(2)创建分区 create table rangeOrders ( order_id number(12) primary key, --订单编号 order_date date not null, --订货日期 order_mode varchar2(8) not null , --订货模式 customer_id number(6) not null, --客户编号 order_status number(2), --订单状态 order_total number(8,2), --总定价 sales_rep_id number(6), --销售代表id promotion_id number(6) --推广员id ) partition by range (order_date) ( partition part1 values less than (to_date('2013-01-01' , 'yyyy-mm-dd')), partition part2 values less than (to_date('2014-01-01' , 'yyyy-mm-dd')), partition part3 values less than (to_date('2015-01-01' , 'yyyy-mm-dd')), partition part4 values less than (to_date('2016-01-01' , 'yyyy-mm-dd')), partition part5 values less than (to_date('2017-01-01' , 'yyyy-mm-dd')), partition part6 values less than (maxvalue) ) --插入测试数据 insert into rangeOrders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(1,'2017-02-09','网上下单',2,1,323.23,1,2); insert into rangeOrders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(2,'2016-11-09','上门购买',1,2,56.00,2,1); insert into rangeOrders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(3,'2017-12-20','熟人推荐',3,1,6000,1,2); insert into rangeOrders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(4,'2015-12-02','网上下单',5,2,365,2,2); insert into rangeOrders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(5,'2017-12-09','上门购买',3,1,3210,1,2); insert into rangeOrders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(6,'2014-11-11','网上下单',3,1,630,2,2); insert into rangeOrders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(7,'2017-01-01','上门购买',2,1,6300,1,2); --查看表中的数据 select * from rangeOrders --查询每一个分区中的数据 select * from rangeOrders partition (part1); select * from rangeOrders partition (part2); select * from rangeOrders partition (part3); select * from rangeOrders partition (part4); select * from rangeOrders partition (part5); select * from rangeOrders partition (part6); --查看分区情况 select table_name,partition_name from user_tab_partitions; --把已存在的表改为分区表 create table rangeOrder partition by range (order_date) ( partition part1 values less than (to_date('2013-01-01' , 'yyyy-mm-dd')), partition part2 values less than (to_date('2014-01-01' , 'yyyy-mm-dd')), partition part3 values less than (to_date('2015-01-01' , 'yyyy-mm-dd')), partition part4 values less than (to_date('2016-01-01' , 'yyyy-mm-dd')), partition part5 values less than (to_date('2017-01-01' , 'yyyy-mm-dd')), partition part6 values less than (maxvalue) ) as select * from orders; /* 间隔分区(自动化) */ --创建分区表(按照一年分一个表) create table sales_interval1 partition by range (order_date) interval (numtoyminterval(1,'year')) --按照一年分区一个表 (partition part1 values less than (to_date('2017/01/01','yyyy/mm/dd'))) as select * from orders; --查看分区情况 select table_name,partition_name,tablespace_name from user_tab_partitions where table_name=upper('sales_interval1'); --插入一条测试数据 insert into sales_interval1(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(98,'2018/01/03','熟人推荐',3,1,9658,2,2); /*注意:刚刚加的那条记录现在肯定没有,所以 1.先执行 select table_name,partition_name,tablespace_name from user_tab_partitions where table_name=upper('sales_interval1'); 查看下一个分区是多少 2.复制刚刚查询出来的分区,eg:SYS_P21 3.执行select * from sales_interval1 partition (SYS_P21); */ select * from sales_interval1 partition (SYS_P21); --添加分区 alter table rangeOrder add partition part7 values less tahn(to_date('2018-01-01','yyyy-mm-dd')); --删除分区 alter table rangeOrder drop partition part3; --移动分区 alter table rangeOrder move partition part1 tablespace works01; --works01是表空间名称、 /* 1.创建一个单独的表空间 2.把分区的数据移动到这个表空间里面去 3.让这个表空间作为只读 */ --以system的身份登陆上 create tablespace tb_name datafile 'e:\oracle\tbdb.dbf' size 10M; --授权 alter user test quota unlimited on tb_name; --移动 alter table rangeOrder move partition part1 tablespace tb_name; --设置为只读 alter tablespace tp_name read only; --设置为读写 alter tablespace tp_name read write; /* 课后简答题 */ --(1)在test用户下创建一个表Stock_Received create table Stock_Received ( Stock_ID number, Stock_Date date, Cost varchar2(50) ) --插入数据 insert into Stock_Received values (myseq.nextval,'2017/03/05','描述一'); insert into Stock_Received values(myseq.nextval,'2017/01/05','描述二'); insert into Stock_Received values (myseq.nextval,'2017/02/05','描述三'); insert into Stock_Received values(myseq.nextval,'2017/04/05','描述四'); insert into Stock_Received values(myseq.nextval,'2017/05/05','描述五'); insert into Stock_Received values(myseq.nextval,'2017/06/05','描述六'); insert into Stock_Received values(myseq.nextval,'2017/05/05','描述七'); insert into Stock_Received values(myseq.nextval,'2017/04/05','描述八'); insert into Stock_Received values(myseq.nextval,'2017/02/05','描述九'); insert into Stock_Received values(myseq.nextval,'2017/01/05','描述十'); insert into Stock_Received values(myseq.nextval,'2017/08/05','描述十一'); --创建一个名为myseq的序列 create sequence myseq start with 1000 increment by 10 maxvalue 1100 cycle --(2)创建一个公有的同义词 create public synonym p_Stock_received for Stock_Received --给a_oe赋予一个可以查看Stock_Received的权限 grant select on p_Stock_received to a_oe; --用a_oe登陆,测试能不能查看Stock_Received表 select * from p_Stock_received; --↑测试成功 --(3)在Stock_Received中根据Stock_Date列创建3个范围分区 create table range_Stock_Received partition by range(Stock_Date) ( partition p1 values less than(to_date('2017/01/01','yyyy-mm-dd')), partition p2 values less than(to_date('2017/03/01','yyyy-mm-dd')), partition p3 values less than(to_date('2017/05/01','yyyy-mm-dd')), partition p4 values less than(maxvalue) ) as select * from Stock_Received --查看每个分区里面的数据 select * from range_Stock_Received partition (p1); select * from range_Stock_Received partition (p2); select * from range_Stock_Received partition (p3); --(4)在表的id上创建一个逐渐索引列 create index index_Stock_ID on Stock_Received (Stock_ID);