--删除用户 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');