理解Oracle中的系统命名约定

使用分区扩展名称和数据字典,而不是依赖于系统名称。

语法:

例子:

--Create and populate sample schema.
--drop table test1;

create table test1(a date)
partition by range(a)
interval (numToDSInterval(1, 'DAY'))
(
    partition p1 values less than (date '2000-01-01')
);

insert into test1
select date '2000-01-01' + level
from dual
connect by level <= 100;

--Select the partition using a value.
select * from test1 partition for (date '2000-01-05');

--Must create as a table to convert LONG to CLOB in SQL.
--(This would be easier in PL/SQL, you can just "TO_CHAR" it.)
create table temp_convert_long_to_lob as
select table_name, partition_name, to_lob(high_value) high_value
from user_tab_partitions
where table_name = 'TEST1';

--Show some data about the partitions.
select table_name, partition_name, to_char(high_value) high_value
from temp_convert_long_to_lob
order by partition_name;

TABLE_NAME   PARTITION_NAME   HIGH_VALUE
----------   --------------   ----------
TEST1        P1               TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TEST1        SYS_P3094        TO_DATE(' 2000-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TEST1        SYS_P3095        TO_DATE(' 2000-01-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TEST1        SYS_P3096        TO_DATE(' 2000-01-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')