Oracle使用记录

1、连接数据库的方式

sqlplus system/system@127.0.0.1:1521/orcl    #远程用户名密码连接
sqlplus dbsnmp/oracle;                       #本地用户名密码连接
sqlplus /nolog								 #本地无登录连接
sqlplus / as sysdba							 #本地以超管权限连接
sqlplus "sys/oracle as sysdba"

2、数据库字符集的设置与查看（Oracle数据库自带自动转码功能）

若数据库的字符集为gbk，则为了数据能正确的存储与查看，需设置windows客户端的字符编码为936，设置客户端环境变量的字符集为gbk（这相当于告诉数据库，我传给你的数据的字符编码都是gbk，然后数据库会检查自己的字符集是否是gbk，若是，则原封不动地将数据拿过来存储；若不是，则先将gbk的数据转码成与自身字符集一致的数据，然后再以自身字符编码的方式存储传过来的数据。客户端从数据库查询显示的过程也是同理）；

#windows
chcp 936
set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
echo %NLS_LANG%
--gbk
sqlplus system/root@192.168.1.7:1521/orcl
--查看当前数据库的字符集
SELECT * FROM v$nls_parameters where parameter='NLS_CHARACTERSET';
--查看某个字段（如name字段）在数据库中的存储编码
select dump(name,1016) from TEST2.tb_clob15G;

we8iso8859p1字符集的数据库和US7ASCII字符集的数据库，既可以以gbk的编码方式存储数据，也可以以utf8编码的方式存储数据；可以设置环境变量的字符集与数据库的字符集一样，这样客户端传来utf8编码的数据时，则以utf8方式存储；客户端传来gbk的数据时，则以gbk的方式存储。
数据库的字符集是we8iso8859p1，客户端的字符集是gbk或者是utf8时，则可以进行如下设置：

--we8iso8859p1字符集-gbk-windows
chcp 936
set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
echo %NLS_LANG%
sqlplus integrated/test@192.168.1.7:1521/we8iso8859p1
SELECT * FROM v$nls_parameters where parameter='NLS_CHARACTERSET';
--we8iso8859p1字符集-utf8-Linux
设置ssh会话端字符集为utf8
--Linux端设置环境变量字符集
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
env|grep NLS_LANG 
sqlplus integrated/test@192.168.1.7:1521/we8iso8859p1

数据库的字符集是US7ASCII，客户端的字符集是gbk或者是utf8时，则可以进行如下设置：

--US7ASCII字符集-gbk-windows
chcp 936
set NLS_LANG=AMERICAN_AMERICA.US7ASCII
echo %NLS_LANG%
sqlplus integrated/test@192.168.1.7:1521/us7ascii
SELECT * FROM v$nls_parameters where parameter='NLS_CHARACTERSET';
--US7ASCII字符集-utf8-Linux
设置ssh会话端字符集为utf8
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
env|grep NLS_LANG 
sqlplus integrated/test@192.168.1.7:1521/us7ascii

输出Linux utf8字符集（Linux的vim编辑环境中）
```
  set encoding=utf-8
```

3、Oracle常用操作

切换用户（本地连接远程数据库时，切换需先在本地配好监听）配监听需安装本地数据库（貌似），装客户端只能本地远程连接
```
  conn mytest/mytest;
```
查当前数据库的实例
```
  select name from v$database;
```

修改用户密码

  --修改当前用户密码无限期
  alter profile default limit password_life_time unlimited;
  --更改用户（integrated）密码
  alter user integrated identified by test;

查看字段编码

  --查clob字段（xmlfield）编码
  select dump(to_char(xmlfield),1016) from t_clobxml_gbk where rownum < 2;
  --查普通字段（c1）的编码
  select dump(c1,1016) from t_6;

my schema 创建用户、授权、更改用户所属（默认）的表空间

  create user mytest identified by mytest;
  grant dba to mytest;
  alter user mytest default tablespace USERS;
  
  sqlplus mytest/mytest@192.168.32.130:1521/orcl

查看某个字段字节数

  select sum(length(XMLRECORD)) from FLCN_CUSTOMER;
  
  select sum(length(ID))+sum(length(XMLRECORD)) from FLCN_CUSTOMER;

字符转数字

  select * from MYTEST.FLCN_CUSTOMER order by to_number(RECID) desc;

查看所有表空间

  select tablespace_name 表空间名 from dba_tablespaces;

查看所有表空间的总大小

  SELECT TABLESPACE_NAME 表空间名,SUM(BYTES)/1024/1024 表空间大小 from DBA_DATA_FILES GROUP BY TABLESPACE_NAME;

查看某个表空间（如表空间：USERS）的使用情况

  SELECT a.tablespace_name "表空间名", total "表空间大小",  free "表空间剩余大小",  (total - free) "表空间使用大小", 
   total / (1024 * 1024 * 1024) "表空间大小(G)",  free / (1024 * 1024 * 1024) "表空间剩余大小(G)", 
   (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",  round((total - free) / total, 4) * 100 "使用率 %"  
  FROM (SELECT tablespace_name, SUM(bytes) free  FROM dba_free_space  GROUP BY tablespace_name) a, 
   (SELECT tablespace_name, SUM(bytes) total  FROM dba_data_files  GROUP BY tablespace_name) b  
  WHERE a.tablespace_name = b.tablespace_name and a.tablespace_name = 'USERS';

查看所有表空间剩余大小

  SELECT TABLESPACE_NAME 表空间名,SUM(BYTES)/1024/1024 "表空间剩余大小(M)" FROM DBA_FREE_SPACE group by TABLESPACE_NAME;

查看所有的用户和对应的表空间

  select distinct(owner),tablespace_name from dba_segments;

查看当前用户下拥有的所有表

  select table_name 表名 from user_tables;

查看所有用户的表（其中包括系统表）
```
  select table_name from all_tables;
```
查看当前用户下所有的表（不包含系统表）
```
  select * from tab;
```
查看当前用户所拥有的权限角色
```
  select * from user_role_privs;
```

授予执行目录角色

  --查看当前用户所拥有的权限
  select * from session_privs;
  --授予执行目录角色，能够执行所有系统包
  grant execute_catalog_role to mytest;

数据库备份还原

  --数据备份还原时
  grant dba to mytest;
  grant connect,resource to mytest identified by mytest;
  --创建备份还原目录
  create directory mydir as 'C:workspace';
  --查看目录
  select * from dba_directories;
  --对用户授予读写目录的权限
  grant read,write on directory mydir to mytest;
  --还原（样例）
  impdp "'/ as sysdba'" directory=mydir file=customer.0414 REMAP_SCHEMA=XIBINTG_T24:SYSTEM REMAP_TABLESPACE=XIBINTG_T24DISTDATA:USERS,XIBINTG_T24DISTINDEX:users

数据库内部参数调整

  --查看现在已有的SGA设置
  show parameter sga;
  --更改SGA大小：
  alter system set sga_max_size=1536M scope=spfile;
  alter system set sga_target=1024M scope=spfile;
  --切换到sys用户
  conn /as sysdba;
  --重启使更改生效：
  --关闭
  shutdown immediate
  --启动
  startup
  --查看当前pga_aggregate_target大小
  show parameter pga_aggregate_target;
  --更改pga_aggregate_target大小 （大的有4G）
  alter system set pga_aggregate_target=36m;

数据库的启动、监听和关闭

  sqlplus / as sysdba
  startup
  shutdown immediate
  quit
  lsnrctl start/stop
  --查看数据库监听状态
  lsnrctl

创建数据表空间

  create tablespace user_data 
  logging 
  datafile 'H:oradataorcluser_data.dbf' 
  size 50m 
  autoextend on 
  next 50m maxsize 20480m 
  extent management local; 
  
  （DATAFILE 是关键字，用于指定数据文件的路径。'表空间文件名.dbf' 是数据文件名，大小是50M。
  AUTOEXTEND ON 表示数据文件可以自动扩展，即控件用完时，空间可以自动增加。AUTOEXTEND OFF表不自动扩展（此时不需再写后面的语句）
  NEXT 50M 指定数据文件每次扩展或增量的大小是50M。
  MAXSIZE UNLIMITED表示数据文件的大小可以无限扩展。目前是最大可扩展到20480M
  LOGGING 有NOLOGGING 和LOGGING两个选项：
  NOLOGGING:创建表空间时，不创建重做日志。若不写，则默认是nologging
  LOGGING和NOLOGGING正好相反,，就是在创建表空间时生成重做日志。
  用NOLOGGING时，好处在于创建时不用生成日志，这样表空间的创建较快，但是没有日志，数据丢失后，不能恢复，但是一般我们在创建表空间时，是没有数据的。按通常的做法是建完表空间，并导入数据后，是要对数据做备份的，所以通常不需要表空间的创建日志，因此，在创建表空间时，选择 NOLOGGING以加快表空间的创建速度。
  EXTENT MANAGEMENT LOCAL指定本地表空间管理。）

创建临时表空间

  create temporary tablespace user_temp 
  tempfile 'H:oradataorcluser_temp.dbf' 
  size 50m 
  autoextend on 
  next 50m maxsize 20480m 
  extent management local;

删除表空间及表空间所在的物理文件

  DROP TABLESPACE user_data INCLUDING CONTENTS AND DATAFILES;

创建用户并指定表空间、临时表空间，并给用户授予权限

  create user username identified by password 
  default tablespace user_data 
  temporary tablespace user_temp; 
  --给用户授予权限
  grant connect,resource,dba to username;

创建、授权角色，并授予角色给用户

  --创建角色
  create role role1;
  
  --授权给角色
  grant create any table,create procedure to role1;
  
  --授予角色给用户
  grant role1 to username;

存储过程操作

  --创建存储过程
  create or replace procedure insert_student
  (
    user_id Number,user_name varchar2,user_pass varchar2
  )
  as
  begin
    insert into student values(user_id,user_name,user_pass);
  end insert_student;
   /
  --执行存储过程
  begin
    insert_student(1,'aaa','bbb');
    commit;
  end;
  /
  --删除存储过程
  drop procedure insert_student;
  
  --创建带参数的存储过程
  create table stu(id number,name varchar2(10));
  create or replace procedure test_param(u_id number,u_name varchar2)
  as 
  begin
  insert into stu values(u_id,u_name);    
  commit;
  end test_param;
  /
  --调用存储过程法一
  call test_param(1,'nn');
  --调用存储过程法二
  begin
  test_param(1,'nn');
  end;
  /
  
  
  create or replace procedure proc_test1
  (
    invalue in Integer
  )
  as
  testvalue varchar2(40);
  begin
  select user_name into testvalue from user_info where user_id = invalue;
  dbms_output.put_line(testvalue);
  end proc_test1;
  /
  --执行 
  declare   
  parameter Integer := 1;
  begin
    proc_test1(parameter);
  end;
  /
  
  --既有输入，又有输出的存储过程
  create or replace procedure proc_test3
  (
    invalue in Integer,outvalue out varchar2
  )
  as
  begin
  select user_name into outvalue from user_info where user_id = invalue;
  end proc_test3;
  /
  --调用存储过程 
  declare
  parameter Integer := 1;   
  parameter1 varchar2(20);
  begin
    proc_test3(parameter,parameter1);
    dbms_output.put_line(parameter1);
  end;
  /

创建表和索引

  DROP TABLE DEMO.T1_index_constraints;
  CREATE TABLE DEMO.T1_index_constraints
  (
  ID    INT NOT NULL,
  ENAME VARCHAR2(50),
  ADDR  VARCHAR2(200),
  COM   VARCHAR2(200),
  TEL   NUMBER(20),
  SEX   VARCHAR2(200),
  CONSTRAINT PK_ID PRIMARY KEY(ID),
  CONSTRAINT CK_TEL CHECK (SEX IN('男','女'))
  );
  CREATE index T1_IDX ON DEMO.T1_index_constraints(TEL) TABLESPACE TBS_SD;
  CREATE index T1_IDX_nospace ON DEMO.T1_index_constraints(COM);

创建、查看视图

  --创建视图
  CREATE VIEW T_B
  AS
  SELECT * FROM CLASS WHERE ID=2;
  --查看视图
  SELECT * FROM T_B;

创建序列

  create sequense my_seq   //创建序列名：my_seq
  start with 1  //从1开始
  increment by 1  //每次增长1
  maxvalue 999999  //nomaxvalue(不设置最大值) ---最大值  //有限制的序列，无限制的序列设置时的相关参数：maxvalue，minvalue
  minvalue 1  //最小值
  cycle  //nocycle   一直累加，不循环   ；cycle 表示循环
  nocache   ---缓存  //cache 10 表示一次产生10个号，//但是使用缓存产生号，优点是提高效率，缺点是可能产生跳号//上面表示从1开始，每次增长1，最大值为999999，之后又循环开始

创建包头

  create or replace package package_demo is  
   function Getage(birthst varchar,birthend varchar) return integer;  
   function Getsalary(VFpsncode varchar) return number;  
  end package_demo;

创建包体

  create or replace package body package_demo is
    function Getage(birthst varchar,birthend varchar) return integer --得到年龄函数
    is
      V_birth integer;
      ToDateEnd Date;
      Toyear number(4);
      Tomonth number(4);
      Fromyear number(4);
      Frommonth number(4);
    begin
      if (birthend='') or (birthend is null) then 
        select sysdate into ToDateEnd from dual;         --得到系统时间
      end if;
      Toyear := to_number(to_char(ToDateEnd,'YYYY'));    --得到最后年月
      Tomonth := to_number(to_char(ToDateEnd,'MM'));
      Fromyear := to_number(substr(birthst,1,4));        --计算的年月
      Frommonth := to_number(substr(birthst,6,2));
      if Tomonth-Frommonth>0 then
        V_birth:=Toyear-fromyear;
      else
        V_birth:=Toyear-fromyear-1;
      end if;
      return(V_birth);
    end Getage;
  
    function getSalary(VFpsncode varchar) return number  --返回工资情况
    is
      V_psnSalary number(8,2);
    begin
      Select FpsnSalary into V_psnSalary from T_PsnSalary where Fpsncode = VFpsncode;
      return(V_psnSalary);
    end getSalary; 
  end package_demo;
  
  select a.*,package_demo.Getage(Fpsnbirth,'')age from T_psnsalary a;  --调用包得到年龄功能
  select package_demo.getsalary('C001') from dual;                     --代码得到工资

4、万级表数据构造

--表数据构造
create table MYTEST.FBNK_CUSTOMER$HIS
(
RECID 		VARCHAR2(255) NOT NULL,
XMLRECORD   SYS.XMLTYPE,
CONSTRAINT "PK_FBNK_CUSTOMER$HIS" PRIMARY KEY ("RECID") USING INDEX TABLESPACE "TBS_BIGTABLE"  ENABLE 
) 
tablespace tbs_bigtable
XMLTYPE COLUMN "XMLRECORD" STORE AS BASICFILE CLOB
(TABLESPACE "TBS_BIGTABLE" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION CACHE STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
);

create sequence RECID_seq2
minvalue 1
nomaxvalue 
start with 2
increment by 1
nocycle
cache 20;

CREATE OR REPLACE TRIGGER tg_FBNK_CUSTOMER$HIS 
BEFORE INSERT ON MYTEST.FBNK_CUSTOMER$HIS FOR EACH ROW WHEN (new.RECID is null)
begin
select RECID_seq2.nextval into:new.RECID from dual;
end;
/

insert into MYTEST.FBNK_CUSTOMER$HIS values(1,'<row id="999999" xml:space="preserve">
  <c1>身份证</c1>
  <c2>340221198310249859</c2>
  <c3>蔡以枫</c3>
  <c3 m="2">上官巴巴</c3>
  <c4>梁总</c4>
  <c5>510723197709052305</c5>
  <c7>88120013</c7>
  <c23>020-29833651</c23>
  <c24>18927501938</c24>
  <c26>9650@hotmail.com</c26>
  <c27>4</c27>
  <c28>CN</c28>
  <c29>9</c29>
  <c30>CN</c30>
  <c34>解决</c34>
  <c34 m="2">%……</c34>
  <c35>I</c35>
  <c35 m="2">O</c35>
  <c36></c36>
  <c36 m="2"></c36>
  <c37 m="2"/>
  <c38></c38>
  <c38 m="2"></c38>
  <c39></c39>
  <c39 m="2"></c39>
  <c42>198</c42>
</row>');

--以下不需用到触发器，只需序列
declare
begin
  for i in 1..16 loop
    insert /*+appned*/ into MYTEST.FBNK_CUSTOMER$HIS(RECID,XMLRECORD) select RECID_seq2.nextval,XMLRECORD from MYTEST.FBNK_CUSTOMER$HIS;
	commit;
  end loop;
end;
/

--以下同时需要触发器和序列
declare
begin
  for i in 1..22 loop
    insert into DIP.tb_bigclob1U select * from DIP.tb_bigclob1U;
	commit;
  end loop;
end;
/

insert into MYTEST.FBNK_CUSTOMER$HIS select * from MYTEST.FBNK_CUSTOMER where rownum<=10;
create table MYTEST.FBNK_CUSTOMER$HIS as select * from MYTEST.FBNK_CUSTOMER;

5、分区表

schema:test

1、范围分区

2、列表分区

3、Hash分区（散列分区）

4、复合分区

create tablespace dinya_space01 datafile 'H:oradataorcldinya01.dbf' SIZE 5M;   
create tablespace dinya_space02 datafile 'H:oradataorcldinya02.dbf' SIZE 5M;   
create tablespace dinya_space03 datafile 'H:oradataorcldinya03.dbf' SIZE 5M;  
create tablespace PROB_TS01 datafile 'H:oradataorclPROB_TS01.dbf' SIZE 1M;  
create tablespace PROB_TS02 datafile 'H:oradataorclPROB_TS02.dbf' SIZE 1M;  

select * from user_tablespaces;

范围分区

--范围分区
--根据交易记录的序号分区建表：
create table dinya_test  
(  
       transaction_id number primary key,  
       item_id number(8) not null,  
       item_description varchar2(300),  
       transaction_date date not null  
)  
partition by range (transaction_id)  
(  
       partition part_01 values less than(2) tablespace dinya_space01,-----2条以下的交易在此分区上：part_01  
       partition part_02 values less than(3) tablespace dinya_space02,-----等于+大于2而小于3的交易在此分区：part_02  
       partition part_03 values less than(maxvalue) tablespace dinya_space03----大于3的交易在此分区：part_03  
)   

--根据交易日期分区建表
create table dinya_test1  
(  
       transaction_id number primary key,  
       item_id number(8) not null,  
       item_description varchar2(300),  
       transaction_date date not null    
 )  
partition by range (transaction_date)  
(  
       partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd')) tablespace dinya_space01,  
       partition part_02 values less than(to_date('2010-01-01','yyyy-mm-dd')) tablespace dinya_space02,  
       partition part_03 values less than(maxvalue) tablespace dinya_space03  
 )  

insert into dinya_test values(1,12,'BOOKS',sysdate);

insert into dinya_test values(2,12, 'BOOKS',sysdate+30);

insert into dinya_test values(3,12, 'BOOKS',to_date('2006-05-30','yyyy-mm-dd'));

insert into dinya_test values(4,12, 'BOOKS',to_date('2007-06-23','yyyy-mm-dd'));

insert into dinya_test values(5,12, 'BOOKS',to_date('2011-02-26','yyyy-mm-dd'));

insert into dinya_test values(6,12, 'BOOKS',to_date('2011-04-30','yyyy-mm-dd'));

--查询全表数据
select * from dinya_test; 

--
select * from dinya_test partition(part_01);  
select * from dinya_test partition(part_02);  
select * from dinya_test partition(part_03);  

--修改part_01分区的数据
update dinya_test partition(part_01) t set t.item_description='DESK' where t.transaction_id=1;
-- 删除part_03分区的数据
delete from dinya_test partition(part_03) t where t.transaction_id=4;  

--局部本地分区索引的创建：

create index dinya_idx_t on dinya_test(item_id)  
 local  
 (  
     partition idx_1 tablespace dinya_space01,---分区名为：idx_1  
     partition idx_2 tablespace dinya_space02, ---分区名为：idx_2  
     partition idx_3 tablespace dinya_space03---分区名为：idx_3  
 );　  
 
 select *from ALL_TAB_PARTITIONS where table_name ='DINYA_TEST';
select *From dba_ind_partitions where partition_name='IDX_1';

--全局分区索引的创建：全局索引建立时global 子句允许指定索引的范围值，这个范围值为索引字段的范围值： 

create index dinya_idx_t_global on dinya_test1(item_id)  
   global partition by range(item_id)  
 (  
     partition idx_1 values less than (1000) tablespace dinya_space01,  
     partition idx_2 values less than (10000) tablespace dinya_space02,  
     partition idx_3 values less than (maxvalue) tablespace dinya_space03  
 );

Hash分区（散列分区）

--Hash分区（散列分区）
 create table dinya_hash_test  
    (  
       transaction_id number primary key,  
        item_id number(8) not null,  
        item_description varchar2(300),  
        transaction_date date  
   )  
   partition by hash(transaction_id)  
   (  
       partition part_01 tablespace dinya_space01,  
       partition part_02 tablespace dinya_space02,  
       partition part_03 tablespace dinya_space03  
 );

列表分区

 --列表分区

 CREATE TABLE PROBLEM_TICKETS  
(  
   PROBLEM_ID NUMBER(7) NOT NULL PRIMARY KEY,  
   DESCRIPTION VARCHAR2(2000),  
   CUSTOMER_ID NUMBER(7) NOT NULL,  
   DATE_ENTERED DATE NOT NULL,  
   STATUS VARCHAR2(20)  
)  
PARTITION BY LIST (STATUS)  
(  
   PARTITION PROB_ACTIVE VALUES ('ACTIVE') TABLESPACE PROB_TS01,                  
   PARTITION PROB_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02  
)     
insert into PROBLEM_TICKETS values(1,'BOOKS',1,sysdate,'ACTIVE');  
insert into PROBLEM_TICKETS values(2,'son',2,sysdate+30,'INACTIVE');  
insert into PROBLEM_TICKETS values(3,'son',3,to_date('2006-05-30','yyyy-mm-dd'),'INACTIVE');  
insert into PROBLEM_TICKETS values(4,'BOOKS',4,to_date('2007-06-23','yyyy-mm-dd'),'INACTIVE');  
insert into PROBLEM_TICKETS values(5,'old',5,to_date('2011-02-26','yyyy-mm-dd'),'ACTIVE');  
insert into PROBLEM_TICKETS values(6,'test',6,to_date('2011-04-30','yyyy-mm-dd'),'INACTIVE');  
select * from PROBLEM_TICKETS;

复合分区

--复合分区: 范围分区和hash分区
--复合分区是先使用范围分区，然后在每个分区内再使用散列分区的一种分区方法
create tablespace dinya_space07 datafile 'H:oradataorcldinya07.dbf' SIZE 1M;   
create tablespace dinya_space08 datafile 'H:oradataorcldinya08.dbf' SIZE 1M;   
create tablespace dinya_space09 datafile 'H:oradataorcldinya09.dbf' SIZE 1M;   

create table dinya_test_comp
(  
       transaction_id number primary key,  
       item_id number(8) not null,  
   　  item_description varchar2(300),  
 　    transaction_date date  
)  
partition by range(transaction_date)subpartition by hash(transaction_id)  
       subpartitions 3 store in (dinya_space07,dinya_space08,dinya_space09)  
(  
       partition part_07 values less than(to_date('2006-01-01','yyyy-mm-dd')),  
       partition part_08 values less than(to_date('2010-01-01','yyyy-mm-dd')),  
       partition part_09 values less than(maxvalue)  
);  
select *From user_tab_partitions where table_name=upper('dinya_test_comp')  
selec *From user_tab_subpartitions where table_name=upper('dinya_test_comp')  
insert into dinya_test_comp values(1,12,'BOOKS',sysdate);  
insert into dinya_test_comp values(2,12, 'BOOKS',sysdate+30);  
insert into dinya_test_comp values(3,12, 'BOOKS',to_date('2006-05-30','yyyy-mm-dd'));  
insert into dinya_test_comp values(7,12, 'BOOKS',to_date('2005-05-30','yyyy-mm-dd'));  
insert into dinya_test_comp values(4,12, 'BOOKS',to_date('2007-06-23','yyyy-mm-dd'));  
insert into dinya_test_comp values(5,12, 'BOOKS',to_date('2011-02-26','yyyy-mm-dd'));  
insert into dinya_test_comp values(6,12, 'BOOKS',to_date('2011-04-30','yyyy-mm-dd'));  

--复合范围列表分区
create tablespace dinya_space04 datafile 'H:oradataorcldinya04.dbf' SIZE 1M;   
create tablespace dinya_space05 datafile 'H:oradataorcldinya05.dbf' SIZE 1M;   
create tablespace dinya_space06 datafile 'H:oradataorcldinya06.dbf' SIZE 1M;   

Create table sales  
(  
   Product_id varchar2(5),  
   Sales_date date,  
   Sales_cost number(10),  
   Status varchar2(20)  
)  
Partition by range(Sales_cost)  
Subpartition by list(status)  
(  
   Partition p1 values less than (1) tablespace dinya_space01  
   (  
       Subpartition p1sub1 values('ACTIVE') tablespace   dinya_space03,  
       Subpartition p1sub2 values('INACTIVE') tablespace dinya_space04  
   ),  
   Partition p2 values less than (3) tablespace dinya_space02  
   (  
       Subpartition p1sub3 values('ACTIVE') tablespace    dinya_space05,  
       Subpartition p1sub4 values('INACTIVE') tablespace dinya_space06  
   )  
)  
insert into sales values(1,sysdate,0.1,'ACTIVE');  
insert into sales values(2,sysdate+30,1,'INACTIVE');  
insert into sales values(3,to_date('2006-05-30','yyyy-mm-dd'),2,'INACTIVE');  
  
select * From sales;
Select * from sales partition(p2);
SELECT * FROM SALES SUBPARTITION(p1sub4);  
SELECT * FROM SALES SUBPARTITION(p1sub3); 
select * From dba_tab_subpartitions where table_name='SALES';