Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- In subpartition templates, subpartition name is required, but in interval partitioning, the subpartition names are not used, and system names are assigned.
- SQL> @ interval.subpart.template.sql
- Table dropped.
- SQL> create table t
- 2 (
- 3 d date,
- 4 s number,
- 5 n number
- 6 )
- 7 partition by range ( d )
- 8 interval ( numtoyminterval(1, 'month'))
- 9 subpartition by list ( s )
- 10 (
- 11 partition p2018 values less than ( date '2019-01-01' )
- 12 (
- 13 subpartition s01 values ( 1 )
- 14 )
- 15 );
- Table created.
- SQL>
- SQL>
- SQL> alter table t set subpartition template
- 2 (
- 3 subpartition values ( 1 )
- 4 );
- alter table t set subpartition template
- *
- ERROR at line 1:
- ORA-14605: Name missing for subpartition / lob segment in template
- SQL>
- SQL> alter table t set subpartition template
- 2 (
- 3 subpartition s01 values ( 1 )
- 4 );
- Table altered.
- SQL>
- SQL> insert into t ( d, s, n ) values ( date '2019-01-01', 1, 0 );
- 1 row created.
- SQL>
- SQL> select partition_name, high_value
- 2 from user_tab_partitions
- 3 where table_name = 'T';
- PARTITION_NAME HIGH_VALUE
- ------------------------------ ------------------------------------------------------------------------------------------
- P2018 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
- SYS_P379 TO_DATE(' 2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
- SQL>
- SQL> select subpartition_name, high_value
- 2 from user_tab_subpartitions
- 3 where table_name = 'T';
- SUBPARTITION_NAME HIGH_VALUE
- ------------------------------ ------------------------------------------------------------------------------------------
- S01 1
- SYS_SUBP378 1
Add Comment
Please, Sign In to add comment