/*======================================================================================================================================*//*tp_orders表空间，大小10M，文件大小可自动增长，允许文件扩展，最大限度为无限制创建A_oe用户的默认表空间为tp_orders 密码 bdqn授予connect,resource权限党文a_hr用户的employee*/--创建表空间和用户，并授予访问数据库的权限create tablespace tp_ordersdatafile 'E:\E盘\tp_orders01.dbf'size 10Mautoextend on;--创建用户create user A_oeidentified by bdqndefault 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_seqstart with 60increment by 10maxvalue 10000--插入数据insert into bumen values(dept_seq.nextval,'学术部');insert into bumen values(dept_seq.nextval,'学术部1');--数据迁移前的工作drop sequence dept_seq;create sequence dept_seqstart with 80increment by 10maxvalue 10000;create table deptBak as select * from bumen;select * from deptBak;--测试插入数据insert into deptBak values(dept_seq.nextval,'人事部');/*创建A_oe模式下dept表的公有同义词，可以允许任何能够连接上数据库的用户访问*/--创建一个测试dept表create table deptas 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),                    --销售代表idpromotion_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 rangeOrderpartition 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_interval1partition 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_partitionswhere 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_partitionswhere table_name=upper('sales_interval1');查看下一个分区是多少2.复制刚刚查询出来的分区，eg:SYS_P213.执行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 rangeOrderdrop partition part3;--移动分区alter table rangeOrdermove partition part1 tablespace works01;    --works01是表空间名称、/*1.创建一个单独的表空间2.把分区的数据移动到这个表空间里面去3.让这个表空间作为只读*/--以system的身份登陆上create tablespace tb_namedatafile 'e:\oracle\tbdb.dbf'size 10M;--授权alter user test quota unlimited on tb_name;--移动alter table rangeOrdermove partition part1 tablespace tb_name;--设置为只读alter tablespace tp_name read only;--设置为读写alter tablespace tp_name read write;/*课后简答题*/--(1)在test用户下创建一个表Stock_Receivedcreate 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 myseqstart with 1000increment by 10maxvalue 1100cycle--(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_Receivedpartition 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);