oracle基本筆記整理及案例分析2____oracle

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