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

--删除用户drop user sz cascade;--创建表空间create tablespace worktablspdatafile '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 RHIDENTIFIED BY HRDEFAULT  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');