ExaGridDba

subpartition name required but not used in template

Nov 22nd, 2017
134
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.87 KB | None | 0 0
  1. In subpartition templates, subpartition name is required, but in interval partitioning, the subpartition names are not used, and system names are assigned.
  2.  
  3. SQL> @ interval.subpart.template.sql
  4.  
  5. Table dropped.
  6.  
  7. SQL> create table t
  8. 2 (
  9. 3 d date,
  10. 4 s number,
  11. 5 n number
  12. 6 )
  13. 7 partition by range ( d )
  14. 8 interval ( numtoyminterval(1, 'month'))
  15. 9 subpartition by list ( s )
  16. 10 (
  17. 11 partition p2018 values less than ( date '2019-01-01' )
  18. 12 (
  19. 13 subpartition s01 values ( 1 )
  20. 14 )
  21. 15 );
  22.  
  23. Table created.
  24.  
  25. SQL>
  26. SQL>
  27. SQL> alter table t set subpartition template
  28. 2 (
  29. 3 subpartition values ( 1 )
  30. 4 );
  31. alter table t set subpartition template
  32. *
  33. ERROR at line 1:
  34. ORA-14605: Name missing for subpartition / lob segment in template
  35.  
  36.  
  37. SQL>
  38. SQL> alter table t set subpartition template
  39. 2 (
  40. 3 subpartition s01 values ( 1 )
  41. 4 );
  42.  
  43. Table altered.
  44.  
  45. SQL>
  46. SQL> insert into t ( d, s, n ) values ( date '2019-01-01', 1, 0 );
  47.  
  48. 1 row created.
  49.  
  50. SQL>
  51. SQL> select partition_name, high_value
  52. 2 from user_tab_partitions
  53. 3 where table_name = 'T';
  54.  
  55. PARTITION_NAME HIGH_VALUE
  56. ------------------------------ ------------------------------------------------------------------------------------------
  57. P2018 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
  58. SYS_P379 TO_DATE(' 2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
  59.  
  60. SQL>
  61. SQL> select subpartition_name, high_value
  62. 2 from user_tab_subpartitions
  63. 3 where table_name = 'T';
  64.  
  65. SUBPARTITION_NAME HIGH_VALUE
  66. ------------------------------ ------------------------------------------------------------------------------------------
  67. S01 1
  68. SYS_SUBP378 1
Add Comment
Please, Sign In to add comment