表与分区

 /*行级锁*/
select * from emp where id=1 for update WAIT 5;--5为等待时间，如果此表被别的用户锁定，则等待5秒，5秒后如果锁定没有解除，则退出执行，如果不指定时间，则无限等待。
update emp set ename='sa' where id=1
select * from emp

/*表级共享锁*/
LOCK TABLE scott.emp IN SHARE MODE;
commit
/*行共享锁*/
LOCK TABLE SCOTT.EMP IN ROW SHARE MODE;
UPDATE SCOTT.EMP SET ENAME='GG' WHERE EMPNO=7369;
SELECT * FROM SCOTT.EMP

/*创建分区*/
create table employee(empid varchar2(5)primary key,ename varchar2(25),job varchar2(20),dateofjoinning date)
partition by range(dateofjoinning)
(
partition p1 values less than(to_date('2001-01-01','yyyy-mm-dd')),
partition p2 values less than(to_date('2002-01-02','yyyy-mm-dd')),
partition p3 values less than(to_date('2003-01-01','yyyy-mm-dd')),
partition p4 values less than(to_date('2004-01-01','yyyy-mm-dd')),
partition p5 values less than(maxvalue)
);

select * from employee;

INSERT INTO EMPLOYEE
VALUES
  ('A001', 'LISA', 'SR.ACCOCUNTANT', TO_DATE('2000-05-04', 'YYYY-MM-DD'));
INSERT INTO EMPLOYEE
VALUES
  ('A002', 'GEORGE', 'SR.ACCOCUNTANT', TO_DATE('2001-05-04', 'YYYY-MM-DD'));
INSERT INTO EMPLOYEE
VALUES
  ('A003', 'WUMI', 'SR.ACCOCUNTANT', TO_DATE('2002-05-04', 'YYYY-MM-DD'));
INSERT INTO EMPLOYEE
VALUES
  ('A004', 'JOHN', 'SR.ACCOCUNTANT', TO_DATE('2003-05-04', 'YYYY-MM-DD'));
INSERT INTO EMPLOYEE
VALUES
  ('B001', 'HOUQING', 'ORA DBA', TO_DATE('2007-02-01', 'yyyy-mm-dd'));
INSERT INTO EMPLOYEE
VALUES
  ('A005', 'ARANCK', 'SR.ACCOCUNTANT', TO_DATE('2004-05-04', 'YYYY-MM-DD'));

SELECT * FROM EMPLOYEE PARTITION (P1);

/* hash  */
create table employees
(
empid varchar2(5) primary key,
ename varchar2(25),
job varchar2(20),
dateofjoinning date
)
partition by hash(dateofjoinning)
(
partition p1,partition p2,partition p3,partition p4
);

INSERT INTO EMPLOYEEs
VALUES
  ('A001', 'LISA', 'SR.ACCOCUNTANT', TO_DATE('2000-05-04', 'YYYY-MM-DD'));
INSERT INTO EMPLOYEEs
VALUES
  ('A002', 'GEORGE', 'SR.ACCOCUNTANT', TO_DATE('2001-05-04', 'YYYY-MM-DD'));
INSERT INTO EMPLOYEEs
VALUES
  ('A003', 'WUMI', 'SR.ACCOCUNTANT', TO_DATE('2002-05-04', 'YYYY-MM-DD'));
INSERT INTO EMPLOYEEs
VALUES
  ('A004', 'JOHN', 'SR.ACCOCUNTANT', TO_DATE('2003-05-04', 'YYYY-MM-DD'));
INSERT INTO EMPLOYEEs
VALUES
  ('B001', 'HOUQING', 'ORA DBA', TO_DATE('2007-02-01', 'yyyy-mm-dd'));
INSERT INTO EMPLOYEEs
VALUES
  ('A005', 'ARANCK', 'SR.ACCOCUNTANT', TO_DATE('2004-05-04', 'YYYY-MM-DD'));
INSERT INTO EMPLOYEEs
VALUES
  ('A006', 'ARANCK', 'SR.ACCOCUNTANT', TO_DATE('2008-05-04', 'YYYY-MM-DD'));

INSERT INTO EMPLOYEEs
VALUES
  ('A007', 'ARANCK', 'ORA DBA', TO_DATE('2004-08-04', 'YYYY-MM-DD'));

select * from employees partition (p1);
select * from employees partition (p2);
select * from employees partition (p3);
select * from employees partition (p4);

/*复合分区*/
create table annualL_sales
(
aid number,
aname varchar2(20),
sal float,sales_date date

)
partition by range(sales_date)
subpartition by hash(aid)
subpartitions   8
(
partition p1 values less than(to_date('2001-01-01','yyyy-mm-dd')),
partition p2 values less than(to_date('2002-01-01','yyyy-mm-dd')),
partition p3 values less than(to_date('2003-01-01','yyyy-mm-dd')),
partition p4 values less than(to_date('2004-01-01','yyyy-mm-dd')),
partition p5 values less than(maxvalue)
)
INSERT INTO annualL_sales VALUES(001,'zhudi',2548.02,TO_DATE('2001-04-19',
          'YYYY-MM-DD'));
INSERT INTO annualL_sales VALUES(002,'WIL',7654.02,TO_DATE('2002-04-19',
          'YYYY-MM-DD'));
INSERT INTO annualL_sales VALUES(003,'BII',25764.02,TO_DATE('2003-04-19',
          'YYYY-MM-DD'));
INSERT INTO annualL_sales VALUES(004,'GIES',8677.02,TO_DATE('2004-04-19',
          'YYYY-MM-DD'));
INSERT INTO annualL_sales VALUES(005,'LISA',5448.02,TO_DATE('2005-04-19',
          'YYYY-MM-DD'));
 

select aid,sales_date from annualL_sales partition (p3)                         

select * from mw_sys.mwt_pd_deps
select * from dba_tablespaces

/*创建列表分区*/
CREATE TABLE mws_sys.EMPLOYEE_DATA
(
EMPLOYEE_ID VARCHAR2(5),
EMPLOYEE_NAME VARCHAR2(30),
DEPARTMENT VARCHAR2(15)
)
PARTITION BY LIST(DEPARTMENT)
(
PARTITION D1 VALUES('ACCOUNTS'),
PARTITION D2 VALUES('MANAGEMENT'),
PARTITION D3 VALUES('HUMAN RESOURCE')
)
tablespace "MWS_SYS";

INSERT INTO mws_sys.EMPLOYEE_DATA VALUES('A01','MARRY','ACCOUNTS');
INSERT INTO mws_sys.EMPLOYEE_DATA VALUES('A02','WULI','MANAGEMENT');
INSERT INTO mws_sys.EMPLOYEE_DATA VALUES('A03','LIULI','HUMAN RESOURCE');
INSERT INTO mws_sys.EMPLOYEE_DATA VALUES('A04','HOUJIANHUA','HUMAN RESOURCE');
INSERT INTO mws_sys.EMPLOYEE_DATA VALUES('A05','LIUSHUZHONG','HUMAN RESOURCE');
INSERT INTO mws_sys.EMPLOYEE_DATA VALUES('A06','PINGLINGLING','MANAGEMENT');
INSERT INTO mws_sys.EMPLOYEE_DATA VALUES('A07','LILI','MANAGEMENT');
INSERT INTO mws_sys.EMPLOYEE_DATA VALUES('A08','CUIYING','MANAGEMENT');

 

select * from mws_sys.employee_data PARTITION (D2)

常见分区操作：
/*添加分区*/
ALTER TABLE Branch_Details ADD PARTITION B5 VALUES('临场部');
SELECT * FROM Branch_Details PARTITION(B5);
/*删除分区*/
ALTER TABLE Branch_Details DROP PARTITION B4;
SELECT * FROM Branch_Details PARTITION(B4);
/*截断分区,分区中数据将被删除*/
ALTER TABLE Branch_Details TRUNCATE PARTITION B3;
SELECT * FROM Branch_Details PARTITION(B3);
/*合并分区*/
ALTER TABLE Branch_Details MERGE PARTITIONS B1,B2 INTO PARTITION B1;
SELECT * FROM Branch_Details PARTITION(B1);
/*拆分分区,AT关键字不能与用列表分区的表一起使用*/
ALTER TABLE Branch_Details SPLIT PARTITION B1 VALUES ('打印部')
INTO (PARTITION B2,PARTITION B6);