Oracle数据库基本概念理解（2）

--删除用户
drop user sz cascade;
--创建表空间
create tablespace worktablsp
datafile 'C:\app\lenovo\oradata\orcl\EMPTB.dbf' SIZE 5M
AUTOEXTEND ON;
--修改表空间
ALTER tablespace worktablsp
ADD datafile 'C:\app\lenovo\oradata\orcl\EMPTB.dbf' SIZE 5M;
--扩展数据文件大小
ALTER database
datafile 'C:\app\lenovo\oradata\orcl\EMPTB.dbf' RESIZE 6M;
--删除
DROP TABLESPACE worktablsp INCLUDING CONTENTS and datafiles
--创建用户:
CREATE USER RH
IDENTIFIED BY HR
DEFAULT TABLESPACE tablespace ;

--给用户授权:
GRANT CONNECT TO RH; --连接数据库角色
GRANT SELECT ON SCOTT.EMP TO RH;--查看SCOTT.EMP表的权限
--分区表
--范围分区
create table saletb
(
saleid number ,
productid number,
saleDate date not null
)
partition by range(saleDate)
(
partition p1 values less than(to_date('2013-01-01','yyyy-mm-dd')),
partition p2 values less than(to_date('2014-01-01','yyyy-mm-dd')),
partition p3 values less than(to_date('2015-01-01','yyyy-mm-dd')),
partition p4 values less than(maxvalue)
);
select * from saletb;
insert into saletb values(1,1,to_date('2012-01-01','yyyy-mm-dd'));

insert into saletb values(2,2,to_date('2013-06-01','yyyy-mm-dd'));
insert into saletb values(3,3,to_date('2014-10-01','yyyy-mm-dd'));
insert into saletb values(4,4,to_date('2016-01-01','yyyy-mm-dd'));

select * from saletb partition(p3);
--间隔分区 一个季度（三个月）一个分区
CREATE TABLE SALES2
(
SALES_ID NUMBER,
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE NOT NULL
)

    PARTITION BY RANGE(SALES_DATE)
    INTERVAL(NUMTOYMINTERVAL(3,'MONTH'))
    (PARTITION P1 VALUES LESS THAN (to_date('2013-04-1','yyyy/mm/dd')));
--
select * from sales2;

--插入数据

INSERT INTO sales2 VALUES (1,'a',to_date('2013-08-1','yyyy/mm/dd'));

INSERT INTO sales2 VALUES (3,'c',to_date('2013-02-1','yyyy/mm/dd'));
--查看分区数据
select * from sales2 partition(sys_p41);
--获得分区情况

SELECT table_name,partition_name 
     FROM user_tab_partitions 
 WHERE table_name=UPPER('sales2');