Oracle 的基本操作（2）

skwxmnopmq

--创建用户及授权
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);