oracle基本笔记整理及案例分析2

/*
===================================================================
===================================================================
*/
/*
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);