Oracle 的基本操作（2）

--创建用户及授权
create user A_hr identified by 123456;
grant dba to A_hr;
create user A_oe identified by 123456;
grant dba to A_oe;

drop table Stock_Received --删除该表

--创建 Stock_Received 表，有 Stock_ID 、Stock_Date 和 Cost 列。
--创建 myseq 序列，该序列的起始值为 1000，每次查询增加10，直到该序列达到 1100，然后重新从 1000 开始
create table Stock_Received(
       Stock_ID number not null,
       Stock_Date date not null,
       Cost number not null
)

create sequence MYSEQ
       minvalue 1000
       maxvalue 1100
       start with 1000
       increment by 10
       cache 10
cycle;

select MYSEQ.nextval from dual;

--创建公有同义词 p_Stock_Received ,并通过 A_oe 访问

CREATE PUBLIC SYNONYM p_Stock_Received FOR A_hr.Stock_Received; 

GRANT SELECT ON p_Stock_Received TO A_oe;

select * from p_Stock_Received;--可以通过A_oe访问

select * from A_hr.Stock_Received;--可以通过A_oe访问


--将 Stock_Date 创建 3 个范围分区
CREATE TABLE Stock_Received2  
partition by range(Stock_Date)  
(partition part2 values less than (to_date('2012/01/01','yyyy/mm/dd')),    
 partition part3 values less than (to_date('2013/01/01','yyyy/mm/dd')),    
 partition part4 values less than (to_date('2014/01/01','yyyy/mm/dd'))  
)  
as select * from Stock_Received;  


--在 Stock_ID 列上创建唯一索引
CREATE unique INDEX idx_Stock_ID ON Stock_Received(Stock_ID);