Categotry Archives: oracle 11g特性

oracle 11g securefiles

0

Posted on by

    oracle 11g推出了新一代LOB:SecureFiles ,这个全新的数据类型同时还提供了高级的下一代功能与优点,可以存储非结构化数据,智能压缩,取消重复和透明加密等功能。

    使用 SecureFiles 时,块大小介于 Oracle 数据块大小到 64 MB 之间。Oracle DB 尝试使数据集中在磁盘的相邻物理位置,从而将内部碎片降到最低。通过使用可变的块大小,SecureFiles 避免对不必要的大型 LOB 数据块进行版本化。 SecureFiles 还提供了新的客户机/服务器网络层,从而允许在支持更高读写性能的服务器和客户机之间进行高速数据传输。SecureFiles 自动确定生成重做和还原的最有效方法,因而不需要用户定义参数。SecureFiles 自动确定是仅为更改生成重做和还原,还是通过生成完整的重做记录创建新版本。 由于 SecureFiles 需要维护各种内存中统计信息以帮助有效地分配内存和空间,所以将其设计为可自适应的智能工具。这样,由于减少了很难使用不可预测的负荷进行优化的可优化参数数量,可管理性有所提高。

    使用SecureFiles的条件:

     1.COMPATIBLE初始化参数比较要设置为11.1或者更高才可以使用SecureFiles

     2.通过设置初始化参数DB_SECUREFILE来确定是否启用SecureFiles

     3.创建 securefile 的表空间应启用自动段空间管理 (ASSM)

 

DB_SECUREFILE参数的说明:

     使用 DB_SECUREFILE 初始化参数,其中有效值为:

       •ALWAYS:尝试将所有 LOB 创建为 SecureFile LOB,但是仅可将自动段空间管理 (ASSM) 表空间外的任何 LOB 创建为 BasicFile LOB

       •FORCE:强制将所有 LOB 创建为 SecureFile LOB

       •PERMITTED:允许创建 SecureFiles(默认值)

       •NEVER:禁止创建 SecureFiles

      •IGNORE:禁止创建 SecureFiles,并忽略使用 SecureFiles 选项强制创建 BasicFiles 而导致的任何错误

      如果指定了NEVER,则任何指定为SecureFiles 的LOB 均被创建为BasicFiles。如果对BasicFiles 使用任何SecureFiles 特定的存储选项和功能(如压缩、加密和取消重复),则会导致异常错误。将对任何未指定的存储选项使用BasicFiles 默认值。
    如果指定了ALWAYS,则系统中创建的所有LOB 均会被创建为SecureFiles。必须在ASSM 表空间中创建LOB,否则会发生错误。将忽略所有指定的BasicFiles 存储选项。

    可以使用ALTER SYSTEM 命令更改所有存储的SecureFiles 默认值。

       SQL>Altersystem set db_securefile=’ALWAYS’;(可以在线修改,不需要重启)

 

 

Securefiles属性如下:

  DEDUPLICATE/KEEP_DUPLICATES: DEDUPLICATE选项允许指定在一个LOB列中有两行或者更多行的时候,所有行都共享同样的数据块。相反的就是KEEP_DUPLICATES选项。11g数据库使用一个的哈希索引来检测重复性并且联合LOBs的内容到一个单一的拷贝里面,减少存储空间并且简化存储管理。默认是KEEP_DUPLICATES

  VALIDATE:对SecureFiles执行一个字节到字节的比较,确定SecureFiles都是同样的安全哈希值。

  COMPRESS/NOCOMPRESS:决定是否启用LOB压缩。还有COMPRESS HIGH,COMPRESS MEDIUM ,COMPRESS LOW的压缩。默认是NOCOMPRESS 。

  ENCRYPT/DECRYPT:决定是否启用LOB加密。如果一旦设置好了加密,就只能用ALTER TABLE REKEY命令来更新加密算法或者是加密值。默认是DECRYPT

  RETENTION:只能影响使用ALTER TABLE语句之后所创建的空间。
                     (1)  MAX 在达到段MAXSIZE 后重新使用旧版本。
                     (2)  MIN 在指定的最短时间内保留旧版本。
                     (3)  AUTO 是默认设置,主要对空间和时间进行折中使之达到平衡。这是自动确定的。
                    (4)  NONE 尽可能重用旧版本。

  对于Securefiles的访问,Oracle11g数据库是通过使用DBMS包来实现的。主要是通过DBMS_LOB包和DBMS_SPACE包来访问。

  DBMS_LOB包:LOBs继承LOB列的设定的属性,当然也可以通过LOB locator API配置成每个LOB级别上。但是LONG API不能用于配置这些LOB设定,必须使用DBMS_LOB包来设定这些属性:

  DBMS_LOB.GETOPTIONS:可以用来获取设定,每个选项类型的预定义的一个常量对应于一个整数将会返回。

  DBMS_LOB.SETOPTIONS:用于设定特性并且允许在每一个LOB基础上设定特性,会覆盖所有默认的LOB设定。

  DBMS_LOB.GET_DEDUPLICATE_REGIONS:这个存储过程用于输出在一个LOB中不重复区域的记录收集。LOB级别上只包含一个不重复区域。

  DBMS_SPACE.SPACE_USAGE:这个存储过程用于返回LOB空间使用的负载信息。返回在LOB段中所有LOB使用的磁盘空间的blocks的数量。这个存储过程目前只能用于ASSM创建的表空间,不支持属于BasicFiles那部分LOB使用的空间。

 

可以通过视图dba_lobs查看相关信息

select * from dba_lobs where owner='ORADBCA'

 

示例:

1.智能LOB压缩

--创建表 
SQL> CREATE TABLE oradbca.clob_test1 
  2  (id         NUMBER, clob_data  CLOB)
  3   LOB(clob_data) STORE AS BASICFILE;

Table created.

 SQL> CREATE TABLE oradbca.clob_test2 
  2  ( id         NUMBER,  clob_data  CLOB) 
  3  LOB(clob_data) STORE AS SECUREFILE;

Table created.

SQL> 
SQL> 
SQL> CREATE TABLE oradbca.clob_test3 
  2  (id         NUMBER, clob_data  CLOB) LOB(clob_data)
  3   STORE AS SECUREFILE (COMPRESS HIGH);

Table created.

SQL> 
SQL> 
SQL> CREATE TABLE oradbca.clob_test4
  2   (  id         NUMBER,  clob_data  CLOB) LOB(clob_data) 
  3  STORE AS SECUREFILE (COMPRESS MEDIUM);

Table created.
SQL> 
SQL> CREATE TABLE oradbca.clob_test5
  2   (  id         NUMBER,  clob_data  CLOB) LOB(clob_data) 
  3  STORE AS SECUREFILE (COMPRESS low);
Table created.
--批量插入数据
12:44:00 SQL> DECLARE
12:44:01   2    l_clob CLOB := RPAD('X', 5000, 'X');
12:44:01   3  BEGIN
12:44:01   4    FOR i IN 1 .. 1000000 LOOP
12:44:01   5      INSERT INTO oradbca.clob_test1 VALUES (i, l_clob);
12:44:01   6    END LOOP;
12:44:01   7    COMMIT;
12:44:01   8  END;
12:44:01   9  /

PL/SQL procedure successfully completed.

12:46:51 SQL> 


12:47:23 SQL> 
12:47:23 SQL> 
12:47:23 SQL> 
12:47:24 SQL> 
12:47:24 SQL> 
12:47:24 SQL> 
12:47:25 SQL> DECLARE
12:47:25   2    l_clob CLOB := RPAD('X', 5000, 'X');
12:47:25   3  BEGIN
12:47:25   4    FOR i IN 1 .. 1000000 LOOP
12:47:25   5      INSERT INTO oradbca.clob_test2 VALUES (i, l_clob);
12:47:25   6    END LOOP;
12:47:25   7    COMMIT;
12:47:25   8  END;
12:47:25   9  /

PL/SQL procedure successfully completed.

12:54:21 SQL> 



12:54:48 SQL> 
12:56:18 SQL> 
12:56:19 SQL> DECLARE
12:56:20   2    l_clob CLOB := RPAD('X', 5000, 'X');
12:56:20   3  BEGIN
12:56:20   4    FOR i IN 1 .. 1000000 LOOP
12:56:20   5      INSERT INTO oradbca.clob_test3 VALUES (i, l_clob);
12:56:20   6    END LOOP;
12:56:20   7    COMMIT;
12:56:20   8  END;
12:56:20   9  /

PL/SQL procedure successfully completed.

12:59:26 SQL> 


13:00:37 SQL> 
13:00:51 SQL> 
13:00:51 SQL> DECLARE
13:00:53   2    l_clob CLOB := RPAD('X', 5000, 'X');
13:00:53   3  BEGIN
13:00:53   4    FOR i IN 1 .. 1000000 LOOP
13:00:53   5      INSERT INTO oradbca.clob_test4 VALUES (i, l_clob);
13:00:53   6    END LOOP;
13:00:53   7    COMMIT;
13:00:53   8  END;
13:00:53   9  /

PL/SQL procedure successfully completed.

13:03:11 SQL> 

13:07:34 SQL> DECLARE l_clob CLOB := RPAD('X', 5000, 'X');
13:07:36   2  BEGIN
13:07:36   3    FOR i IN 1 .. 1000000 LOOP
13:07:36   4      INSERT INTO oradbca.clob_test5 VALUES (i, l_clob);
13:07:36   5    END LOOP;
13:07:36   6    COMMIT;
13:07:36   7  END;
13:07:36   8  /

PL/SQL procedure successfully completed.

13:09:45 SQL> 


--查询表占用空间
SELECT 'CLOB_TEST1  ' || sum(bytes / 1024 / 1024) || 'MB' mb
  FROM dba_segments
 where owner = 'ORADBCA'
   and (segment_name in ('CLOB_TEST1') or
       segment_name in (select segment_name
                           from dba_lobs
                          where owner = 'ORADBCA'
                            and table_name = 'CLOB_TEST1'))
union all
SELECT 'CLOB_TEST2  ' || sum(bytes / 1024 / 1024) || 'MB' mb
  FROM dba_segments
 where owner = 'ORADBCA'
   and (segment_name in ('CLOB_TEST2') or
       segment_name in (select segment_name
                           from dba_lobs
                          where owner = 'ORADBCA'
                            and table_name = 'CLOB_TEST2'))
union all
SELECT 'CLOB_TEST3  ' || sum(bytes / 1024 / 1024) || 'MB' mb
  FROM dba_segments
 where owner = 'ORADBCA'
   and (segment_name in ('CLOB_TEST3') or
       segment_name in (select segment_name
                           from dba_lobs
                          where owner = 'ORADBCA'
                            and table_name = 'CLOB_TEST3'))
union all
SELECT 'CLOB_TEST4  ' || sum(bytes / 1024 / 1024) || 'MB' mb
  FROM dba_segments
 where owner = 'ORADBCA'
   and (segment_name in ('CLOB_TEST4') or
       segment_name in (select segment_name
                           from dba_lobs
                          where owner = 'ORADBCA'
                            and table_name = 'CLOB_TEST4'))
union all
SELECT 'CLOB_TEST5  ' || sum(bytes / 1024 / 1024) || 'MB' mb
  FROM dba_segments
 where owner = 'ORADBCA'
   and (segment_name in ('CLOB_TEST5') or
       segment_name in (select segment_name
                           from dba_lobs
                          where owner = 'ORADBCA'
                            and table_name = 'CLOB_TEST5'));

MB
------------------------------------------------------
CLOB_TEST1  15741MB
CLOB_TEST2  16075.125MB
CLOB_TEST3  96.125MB
CLOB_TEST4  136.125MB
CLOB_TEST5  120.125MB

 

查询结果的压缩率很明显,没开压缩与普通的大字段在空间上没啥区别,

是开了压缩后,他的压缩率特别的明显。

 

 

2.取消重复

--创建带DEDUPLICATE参数的表
SQL> CREATE TABLE oradbca.clob_test6
  2  ( id         NUMBER,  clob_data  CLOB) 
  3  LOB(clob_data) STORE AS SECUREFILE  (DEDUPLICATE);

Table created.
	
--插入数据
13:34:55 SQL> DECLARE l_clob CLOB := RPAD('X', 5000, 'X');
13:34:56   2  BEGIN
  FOR i IN 1 .. 1000000 LOOP
13:34:56   3  13:34:56   4      INSERT INTO oradbca.clob_test6 VALUES (i, l_clob);
13:34:56   5    END LOOP;
13:34:56   6    COMMIT;
13:34:56   7  END;
13:34:56   8  /

PL/SQL procedure successfully completed.

13:39:23 SQL> 


--查看一下占用大小
MB
------------------------------------------------------
CLOB_TEST2  16075.125MB
CLOB_TEST6  89.125MB

可以看到在blob字段中如果出现相同的内容时,在空间占用上有很大的优化。

 

管理:

  从BasicFiles移植SecureFiles

      有两种推荐的方法来移植。这两种方法分别是交换分区和在线重定义。

  1.交换分区:

  需要和表中最大的分区相等的额外空间

  在交换的时候可以维护索引

  能够在几个小的维护窗口延展工作负载

  执行交换分区的时候需要将表或者分区离线

 

  2.在线重定义:

  不要求表或者分区离线

  可以被并行的执行

  要求额外的等于整个表或者所有LOB段大小的可用空间

  要求必须重建所有的全局索引

 

  3.如果你想升级你的BasicFiles到SecureFiles,你可以使用传统的方法来升级数据,例如CTAS/ITAS、export/import、column-to-column拷贝等等。大部分这些解决方案都要求使用两倍的磁盘空间来移植。然而,使用分区解决方案来移植可以按照每个分区来移植,从而有助于减少磁盘空间的需求。

 

 

附:官方语法:

create:

CREATE [ GLOBAL TEMPORARY ] TABLE
   [ schema.]table OF
   [ schema.]object_type
   [ ( relational_properties ) ]
   [ ON COMMIT { DELETE | PRESERVE } ROWS ]
   [ OID_clause ]
   [ OID_index_clause ]
   [ physical_properties ]
   [ table_properties ] ;
 
<relational_properties> ::= 
{ column_definition
| { out_of_line_constraint
  | out_of_line_ref_constraint
  | supplemental_logging_props
  }
}
  [, { column_definition
     | { out_of_line_constraint
       | out_of_line_ref_constraint
       | supplemental_logging_props
       }
  ]...
 
<column_definition> ::= 
column data_type [ SORT ]
      [ DEFAULT expr ]
      [ ENCRYPT encryption_spec ]
      [ ( inline_constraint [ inline_constraint ] ... )
      | inline_ref_constraint 
      ]
 
<data_type> ::=
{ Oracle_built_in_datatypes
| ANSI_supported_datatypes
| user_defined_types
| Oracle_supplied_types
}
 
<Oracle_built_in_datatypes> ::=
{ character_datatypes
| number_datatypes
| long_and_raw_datatypes
| datetime_datatypes
| large_object_datatypes
| rowid_datatypes
}
 
<large_object_datatypes> ::= 
{ BLOB | CLOB | NCLOB| BFILE }
 
 <table_properties> ::=
  [ column_properties ]
  [ table_partitioning_clauses ]
  [ CACHE | NOCACHE ]
  [ parallel_clause ]
  [ ROWDEPENDENCIES | NOROWDEPENDENCIES ]
  [ enable_disable_clause ]
  [ enable_disable_clause ]...
  [ row_movement_clause ]
  [ AS subquery ]
 
<column_properties> ::=
  { object_type_col_properties
  | nested_table_col_properties
  | { varray_col_properties | LOB_storage_clause }
    [ (LOB_partition_storage
        [, LOB_partition_storage ]...
      )
    ]
  | XMLType_column_properties
  }
  [ { object_type_col_properties
    | nested_table_col_properties
    | { varray_col_properties | LOB_storage_clause }
      [ ( LOB_partition_storage
          [, LOB_partition_storage ]...
        )
      ]
    | XMLType_column_properties
    }
  ]...
 
<LOB_partition_storage> ::=
  PARTITION partition
  { LOB_storage_clause | varray_col_properties }
    [ LOB_storage_clause | varray_col_properties ]...
  [ ( SUBPARTITION subpartition
     { LOB_storage_clause | varray_col_properties }
       [ LOB_storage_clause
       | varray_col_properties
       ]...
    )
  ]
 
<LOB_storage_clause> ::=
  LOB
  { (LOB_item [, LOB_item ]...)
      STORE AS [ SECUREFILE | BASICFILE ] (LOB_storage_parameters)
  | (LOB_item)
      STORE AS [ SECUREFILE | BASICFILE ]
        { LOB_segname (LOB_storage_parameters)
        | LOB_segname
        | (LOB_storage_parameters)
        }
  }
 
<LOB_storage_parameters> ::=
  { TABLESPACE tablespace
  | { LOB_parameters [ storage_clause ]
    }
  | storage_clause
  }
    [ TABLESPACE tablespace
    | { LOB_parameters [ storage_clause ]
      }
    ]...
 
<LOB_parameters> ::=
  [ { ENABLE | DISABLE } STORAGE IN ROW
  | CHUNK integer
  | PCTVERSION integer
  | RETENTION [ { MAX | MIN integer | AUTO | NONE } ]
  | FREEPOOLS integer
  | LOB_deduplicate_clause
  | LOB_compression_clause
  | LOB_encryption_clause
  | { CACHE | { { NOCACHE | CACHE READS } [ logging_clause ] } }
  ]
 
<logging_clause> ::=
  { LOGGING | NOLOGGING | FILESYSTEM_LIKE_LOGGING }
 
<storage_clause> ::=
  STORAGE
  ({ INITIAL integer [ K | M ]
   | NEXT integer [ K | M ]
   | MINEXTENTS integer
   | MAXEXTENTS { integer | UNLIMITED }
   | PCTINCREASE integer
   | FREELISTS integer
   | FREELIST GROUPS integer
   | OPTIMAL [ integer [ K | M ]
             | NULL
             ]
   | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
   }
     [ INITIAL integer [ K | M ]
     | NEXT integer [ K | M ]
     | MINEXTENTS integer
     | MAXEXTENTS { integer | UNLIMITED }
     | MAXSIZE { { integer { K | M | G | T | P } } | UNLIMITED }
     | PCTINCREASE integer
     | FREELISTS integer
     | FREELIST GROUPS integer
     | OPTIMAL [ integer [ K | M ]
               | NULL
               ]
     | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
     ]...
  )
 
<LOB_deduplicate_clause> ::=
  { DEDUPLICATE 
  | KEEP_DUPLICATES
  }
 
<LOB_compression_clause> ::=
  { COMPRESS [ HIGH | MEDIUM ]
  | NOCOMPRESS }
 
<LOB_encryption_clause> ::=
  { ENCRYPT [ USING 'encrypt_algorithm' ] 
    [ IDENTIFIED BY password ]
  | DECRYPT 
  }
 
<XMLType_column_properties> ::= 
XMLTYPE [ COLUMN ] column
   [ XMLType_storage ]
   [ XMLSchema_spec ]
 
<XMLType_storage> ::=
STORE AS
   { OBJECT RELATIONAL
   | [ SECUREFILE | BASICFILE ] { CLOB | BINARY XML }
       [ { LOB_segname [ (LOB_parameters) ]
         | LOB_parameters
         }
         ]
 
<varray_col_properties> ::=
VARRAY varray_item 
   { [ substitutable_column_clause ]
     STORE AS [ SECUREFILE | BASICFILE ] LOB
        { [ LOB_segname ] (LOB_parameters)
        | LOB_segname 
        }
   | substitutable_column_clause
   }

 

alter:

ALTER TABLE [ schema.]table
  [ alter_table_properties
  | column_clauses
  | constraint_clauses
  | alter_table_partitioning
  | alter_external_table_clauses
  | move_table_clause
  ]
    [ enable_disable_clause
    | { ENABLE | DISABLE }
      { TABLE LOCK | ALL TRIGGERS }
    [ enable_disable_clause
    | { ENABLE | DISABLE }
      { TABLE LOCK | ALL TRIGGERS }
    ]...
  ] ;
 
 
<column_clauses> ::=
  { { add_column_clause
    | modify_column_clause
    | drop_column_clause
    }
    [ add_column_clause
    | modify_column_clause
    | drop_column_clause
    ]...
  | rename_column_clause
  | modify_collection_retrieval
    [ modify_collection_retrieval ]...
  | modify_LOB_storage_clause
    [ modify_LOB_storage_clause ] ...
  | alter_varray_col_properties
    [ alter_varray_col_properties ]
  }
 
<modify_LOB_storage_clause> ::=
MODIFY LOB (LOB_item) (LOB_deduplicate_clause [LOB_deduplicate_clause ] )
 
<LOB_deduplicate_clause> ::=
  { storage_clause
  | PCTVERSION integer
  | RETENTION [ { MAX | MIN integer | AUTO | NONE } ]
  | FREEPOOLS integer
  | REBUILD FREEPOOLS
  | LOB_deduplicate_clause
  | LOB_compression_clause
  | LOB_encryption_clause
  | { CACHE | { { NOCACHE | CACHE READS } [ logging_clause ] } }
  | allocate_extent_clause
  | shrink_clause
  | deallocate_unused_clause
  }
    [ storage_clause
    | PCTVERSION integer
    | RETENTION
    | FREEPOOLS integer
    | REBUILD FREEPOOLS
    | modify_LOB_sharing_clause
    | LOB_compression_clause
    | LOB_encryption_clause
  | { CACHE | { NOCACHE | CACHE READS } [ logging_clause ] }
    | shrink_clause
    | deallocate_unused_clause
    ]...

oracle 11g interval partitioning

0

Posted on by

 11g在分区上有了很大的改进,其中有一个特性是Interval-Partition,他是range分区的派生,自动创建指定间隔的分区。

  在 11g 之前,如果数据中出现未能匹配分区条件的情况,系统会拒绝进行数据操作。

对时间类型和数字类型的支持:

1.对于采用date类型或TIMESTAMP类型的Interval Partitioning可以支持按year、month、day、hour、minute、second六种类型的定长宽度分区,分别通过如下函数转换

numtoyminterval ( n, { 'YEAR'|'MONTH'})
numtodsinterval ( n, { 'DAY'|'HOUR'|'MINUTE'|'SECOND'})

2. Interval (number) 即多少值一个分区

 

创建示例

1.时间类型

--创建表
SQL> CREATE TABLE oradbca.intervalpart1 (c1 NUMBER, c2 DATE)
  2  PARTITION BY RANGE (c2)
  3     INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
  4     (PARTITION part1
  5         VALUES LESS THAN (TO_DATE ('02/01/2013', 'MM/DD/YYYY'))
  6    );

Table created.

--查询只有一个分区
SQL> select table_name, partition_name, HIGH_VALUE
  2    from dba_tab_partitions t
  3   where table_name = 'INTERVALPART1';

TABLE_NAME       PARTITION_NAME           HIGH_VALUE
---------------- -----------------         -------------------------------------------------------
INTERVALPART1       PART1                 TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

--增加2行记录一行是2月份的,一行是1月份的
SQL>   insert into oradbca.intervalpart1 values (1,TO_DATE ('02/02/2013', 'MM/DD/YYYY'));

1 row created.

SQL> commit;

Commit complete.

SQL> insert into oradbca.intervalpart1 values (1,TO_DATE ('01/01/2013', 'MM/DD/YYYY')); 

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL>   Select Count(1) From oradbca.intervalpart1;

  COUNT(1)
----------
         2

--增加了2月份的分区
SQL> 
SQL> select table_name, partition_name, HIGH_VALUE
  2    from dba_tab_partitions t
  3   where table_name = 'INTERVALPART1';

TABLE_NAME      PARTITION_NAME    HIGH_VALUE
-------------- ----------------- -------------------------------------------------------------
INTERVALPART1        PART1        TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVALPART1        SYS_P105     TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

--插入当前时间的分区
SQL>  insert into oradbca.intervalpart1 values (1,sysdate);

1 row created.

SQL> commit;

Commit complete.

--又有一个分区新建了,如果插入的时间在两个月以后或者更久,则Oracle只生成必须的分区,并不会生成连续分区。
SQL> select table_name, partition_name, HIGH_VALUE
  2    from dba_tab_partitions t
  3   where table_name = 'INTERVALPART1';

TABLE_NAME         PARTITION_NAME    HIGH_VALUE
----------------- ---------------    --------------------------------------------------------------------------------
INTERVALPART1      PART1             TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVALPART1      SYS_P105          TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVALPART1      SYS_P106          TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> 

 

2.数字类型

----创建表
SQL> CREATE TABLE oradbca.intervalpart2 (c1 NUMBER, c2 DATE)
  2   PARTITION BY RANGE (c1)
  3     INTERVAL (100)
  4      (PARTITION part1
  5          VALUES LESS THAN (100)
  6    );

Table created.

--查询只有一个分区
SQL> select table_name, partition_name, HIGH_VALUE
  2     from dba_tab_partitions t
  3    where table_name = 'INTERVALPART2';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ -------------------------
INTERVALPART2                  PART1                          100

SQL> 
--增加2行记录一行是1,一行是101
SQL> insert into  oradbca.intervalpart2 values (1,sysdate);

1 row created.

SQL> insert into  oradbca.intervalpart2 values (101,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL>  Select Count(1) From  oradbca.intervalpart2;

  COUNT(1)
----------
         2

--增加了101的分区
SQL> select table_name, partition_name, HIGH_VALUE
  2  from dba_tab_partitions t
  3  where table_name = 'INTERVALPART2';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ --------------------------
INTERVALPART2                  PART1                          100
INTERVALPART2                  SYS_P107                       200

--插入1101数值的分区
SQL>  insert into  oradbca.intervalpart2 values (1101,sysdate);

1 row created.

SQL> commit;

Commit complete.

--又有一个分区新建了,如果插入的数值是差别很大或者更久,则Oracle只生成必须的分区,并不会生成连续分区。
SQL> select table_name, partition_name, HIGH_VALUE
  2  from dba_tab_partitions t
  3  where table_name = 'INTERVALPART2';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ---------------------------------------
INTERVALPART2                  PART1                          100
INTERVALPART2                  SYS_P107                       200
INTERVALPART2                  SYS_P108                       1200

 

管理:

1.自动分区与自动分区的转换

--创建普通分区表
SQL> CREATE TABLE oradbca.intervalpart3 (c1 NUMBER, c2 DATE)
  2         PARTITION BY RANGE (c1)
  3         (PARTITION part1 VALUES LESS THAN (100)
  4  );

Table created.

SQL>  insert into  oradbca.intervalpart3 values (1,sysdate);

1 row created.

SQL> commit;

Commit complete.
--当插入超出值时会报错
SQL>  insert into  oradbca.intervalpart3 values (101,sysdate);
 insert into  oradbca.intervalpart3 values (101,sysdate)
                   *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

--设置100数值为分区
SQL> alter table  oradbca.intervalpart3 set INTERVAL(100);

Table altered.

--插入成功
SQL> insert into  oradbca.intervalpart3 values (101,sysdate);

1 row created.

SQL> commit;

Commit complete.

--新生成分区
SQL> select table_name, partition_name, HIGH_VALUE
  2    from dba_tab_partitions t
  3   where table_name = 'INTERVALPART3';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------------------------------
INTERVALPART3                  PART1                          100
INTERVALPART3                  SYS_P109                       200

SQL> 
SQL> 
--也可以设置个更高的值
SQL> alter table  oradbca.intervalpart3 set INTERVAL(300);

Table altered.
--插入数据
SQL> insert into  oradbca.intervalpart3 values (201,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL>  insert into  oradbca.intervalpart3 values (401,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into  oradbca.intervalpart3 values (501,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into  oradbca.intervalpart3 values (601,sysdate);

1 row created.

SQL> commit;

Commit complete.

--更改的值生效,之前的分区不影响。
SQL> select table_name, partition_name, HIGH_VALUE
  2    from dba_tab_partitions t
  3   where table_name = 'INTERVALPART3';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------------------
INTERVALPART3                  PART1                          100
INTERVALPART3                  SYS_P109                       200
INTERVALPART3                  SYS_P110                       500
INTERVALPART3                  SYS_P111                       800

SQL> 
--关闭自动分区
SQL> alter table  oradbca.intervalpart3 set INTERVAL();

Table altered.

--插入数据不成功
SQL>  insert into  oradbca.intervalpart3 values (801,sysdate);
 insert into  oradbca.intervalpart3 values (801,sysdate)
                   *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

2.配置多个表空间的分区存储

alter table oradbca.intervalpart3 set STORE IN (tablespace1, tablespace2, tablespace3);

这3个表空间,分区会循环分配到这3个表空间。

3. rename 分区名

分区:
alter table ORADBCA.INTERVALPART3  rename partition SYS_P109 to part2;

子分区:
alter table ORADBCA.INTERVALPART3  rename partition SYS_P109 to part2;