Thinking Out Loud

March 13, 2021

Best To Rename Both Table And Index Partitions

Filed under: partitioning — mdinh @ 8:21 pm

I have been working on compressing table and index partitions.

Currently, system generated partition names are the same for tables and indexes.

When table and index partitions have different names, it’s not straight forward to associate them.

So why did I not renamed index partitions?

There are 3 years of table partitions and for each table partitions there were 18 index partitions for a total of 648 index partitions.

The script I was using to rename table and index partitions failed at renaming some indexes; hence, I thought I would skip renaming indexes.

Find partition size for TABLE (SYS_P2321):

SQL> @ partition_size_tab.sql
SQL> select s.owner, s.segment_name, s.segment_type,
  2  s.partition_name, s.tablespace_name tbs,
  3  -- t.partitioning_type type, t.interval,
  4  s.bytes/1024/1024/1024 size_gb
  5  from dba_segments s, dba_part_tables t
  6  where s.segment_type in ('TABLE PARTITION')
  7  and s.owner=UPPER('&&owner')
  8  and t.table_name=UPPER('&&table')
  9  and s.partition_name=UPPER('&&partition')
 10  and s.owner=t.owner
 11  and s.segment_name=t.table_name
 12  order by s.partition_name asc
 13  ;
Enter value for owner: app
Enter value for table: interval
Enter value for partition: SYS_P2321

OWNER           SEGMENT_NAME    SEGMENT_TYPE       PARTITION TBS                 SIZE_GB
--------------- --------------- ------------------ --------- --------------- -----------
APP             INTERVAL        TABLE PARTITION    SYS_P2321 USERS                   .01
SQL> set echo off

Find partition size for INDEX (SYS_P2321):

-- There is no need to enter variables.
SQL> @ partition_size_idx.sql
SQL> select s.owner, s.segment_name, s.segment_type,
  2  s.partition_name, s.tablespace_name tbs,
  3  -- i.partitioning_type type, i.interval,
  4  s.bytes/1024/1024/1024 size_gb
  5  from dba_segments s, dba_part_indexes i
  6  where s.segment_type in ('INDEX PARTITION')
  7  and s.owner=UPPER('&&owner')
  8  and i.table_name=UPPER('&&table')
  9  and s.partition_name=UPPER('&&partition')
 10  and s.owner=i.owner
 11  and s.segment_name=i.index_name
 12  order by s.partition_name asc
 13  ;

OWNER           SEGMENT_NAME    SEGMENT_TYPE       PARTITION TBS                 SIZE_GB
--------------- --------------- ------------------ --------- --------------- -----------
APP             INTERVAL_IDX    INDEX PARTITION    SYS_P2321 USERS                   .00
SQL> set echo off

When renaming system generated partition name, it is best to rename for both table and index with the same partition name in order to simplify finding partition size for tables and indexes.

Here is an example where table partitions were renamed while index partitions were not renamed.

TABLE partitions were renamed.

SQL> @ partition_interval_delta_tab.sql
SQL> set echo off
SQL> declare
  2    l_date date;
  3  begin
  4    for x in (
  5    select p.partition_name, p.high_value
  6    from dba_tab_partitions p
  7    where table_owner=UPPER('&&owner') and table_name=UPPER('&&table')
  8    and p.compression='DISABLED' and p.interval='YES' order by 1
  9    )
 10    loop
 11      execute immediate
 12      'begin :h := ' || x.high_value || '; end;' using OUT l_date;
 13      if months_between(sysdate, l_date) > 36 then
 14        dbms_output.put_line(x.partition_name||' : '||to_char(l_date, 'DD-MON-YYYY'));
 15      end if;
 16    end loop;
 17  end;
 18  /
P201612 : 01-JAN-2017
P201701 : 01-FEB-2017
P201702 : 01-MAR-2017
P201703 : 01-APR-2017
P201704 : 01-MAY-2017
P201705 : 01-JUN-2017
P201706 : 01-JUL-2017
P201707 : 01-AUG-2017
P201708 : 01-SEP-2017
P201709 : 01-OCT-2017
P201710 : 01-NOV-2017
P201711 : 01-DEC-2017
P201712 : 01-JAN-2018
P201801 : 01-FEB-2018
P201802 : 01-MAR-2018
SQL> set echo off

INDEX partition were NOT renamed.

SQL> @ partition_interval_delta_idx.sql
SQL> declare
  2    l_date date;
  3  begin
  4    for x in (
  5    select p.partition_name, p.high_value
  6    from dba_ind_partitions p, dba_part_indexes i
  7    where i.owner=UPPER('&&owner') and i.table_name=UPPER('&&table')
  8    and p.index_owner=i.owner and p.index_name=i.index_name
  9    and  p.compression='DISABLED' and p.interval='YES' order by 1
 10    )
 11    loop
 12      execute immediate
 13      'begin :h := ' || x.high_value || '; end;' using OUT l_date;
 14      if months_between(sysdate, l_date) > 36 then
 15        dbms_output.put_line(x.partition_name||' : '||to_char(l_date, 'DD-MON-YYYY'));
 16      end if;
 17    end loop;
 18  end;
 19  /
SYS_P2321 : 01-JAN-2017
SYS_P2322 : 01-FEB-2017
SYS_P2323 : 01-MAR-2017
SYS_P2324 : 01-APR-2017
SYS_P2325 : 01-MAY-2017
SYS_P2326 : 01-JUN-2017
SYS_P2327 : 01-JUL-2017
SYS_P2328 : 01-AUG-2017
SYS_P2329 : 01-SEP-2017
SYS_P2330 : 01-OCT-2017
SYS_P2331 : 01-NOV-2017
SYS_P2332 : 01-DEC-2017
SYS_P2333 : 01-JAN-2018
SYS_P2334 : 01-FEB-2018
SYS_P2335 : 01-MAR-2018
SQL> set echo off

Let’s find the partition size for table and index.

Find partition size for TABLE (P201612).

SQL> @ partition_size_tab.sql
SQL> select s.owner, s.segment_name, s.segment_type,
  2  s.partition_name, s.tablespace_name tbs,
  3  -- t.partitioning_type type, t.interval,
  4  s.bytes/1024/1024/1024 size_gb
  5  from dba_segments s, dba_part_tables t
  6  where s.segment_type in ('TABLE PARTITION')
  7  and s.owner=UPPER('&&owner')
  8  and t.table_name=UPPER('&&table')
  9  and s.partition_name=UPPER('&&partition')
 10  and s.owner=t.owner
 11  and s.segment_name=t.table_name
 12  order by s.partition_name asc
 13  ;
Enter value for partition: P201612

OWNER           SEGMENT_NAME    SEGMENT_TYPE       PARTITION TBS                 SIZE_GB
--------------- --------------- ------------------ --------- --------------- -----------
APP             INTERVAL        TABLE PARTITION    P201612   USERS                   .01
SQL> set echo off

Find partition size for INDEX (SYS_P2321).

Need to enter different partition name for INDEX.

SQL> @ partition_size_idx.sql
SQL> select s.owner, s.segment_name, s.segment_type,
  2  s.partition_name, s.tablespace_name tbs,
  3  -- i.partitioning_type type, i.interval,
  4  s.bytes/1024/1024/1024 size_gb
  5  from dba_segments s, dba_part_indexes i
  6  where s.segment_type in ('INDEX PARTITION')
  7  and s.owner=UPPER('&&owner')
  8  and i.table_name=UPPER('&&table')
  9  and s.partition_name=UPPER('&&partition')
 10  and s.owner=i.owner
 11  and s.segment_name=i.index_name
 12  order by s.partition_name asc
 13  ;
SQL> set echo off

SQL> undefine partition

-- How did I know to use SYS_P2321 for index partitions?
-- Both table and index partition have the same date (01-JAN-2017).
TABLE : P201612   : 01-JAN-2017
INDEX : SYS_P2321 : 01-JAN-2017

SQL> @ partition_size_idx.sql
SQL> select s.owner, s.segment_name, s.segment_type,
  2  s.partition_name, s.tablespace_name tbs,
  3  -- i.partitioning_type type, i.interval,
  4  s.bytes/1024/1024/1024 size_gb
  5  from dba_segments s, dba_part_indexes i
  6  where s.segment_type in ('INDEX PARTITION')
  7  and s.owner=UPPER('&&owner')
  8  and i.table_name=UPPER('&&table')
  9  and s.partition_name=UPPER('&&partition')
 10  and s.owner=i.owner
 11  and s.segment_name=i.index_name
 12  order by s.partition_name asc
 13  ;
Enter value for partition: SYS_P2321

OWNER           SEGMENT_NAME    SEGMENT_TYPE       PARTITION TBS                 SIZE_GB
--------------- --------------- ------------------ --------- --------------- -----------
APP             INTERVAL_IDX    INDEX PARTITION    SYS_P2321 USERS                   .00
SQL> set echo off

March 7, 2021

Compress Historical Interval Partitions

Filed under: partitioning — mdinh @ 5:20 am

There is a requirement to compress monthly interval partition older that 36 months.

First, interval partitions were renamed to more intuitive names using Renaming Interval Partitions

In order to use the PL/SQL block below, the interval partition will need to be renamed.

There is a demo to find and compress partitions older that 36 months for 12.2.0.1.0.

SQL> @ partition_interval_delta.sql
SQL> select
  2  partition_name, compression, high_value,
  3  TRUNC(MONTHS_BETWEEN(SYSDATE,(TO_DATE(regexp_substr(partition_name, '[[:digit:]]+'),'YYYYMM')))) months_delta
  4  from dba_tab_partitions
  5  where table_owner = UPPER('&owner')
  6  and table_name = UPPER('&table')
  7  and compression='DISABLED'
  8  and TRUNC(MONTHS_BETWEEN(SYSDATE,(TO_DATE(regexp_substr(partition_name, '[[:digit:]]+'),'YYYYMM')))) >= &months_delta
  9  order by 1
 10  ;
Enter value for owner: app
Enter value for table: interval
Enter value for months_delta: 36

PARTITION_NAME        COMPRESS HIGH_VALUE                                               MONTHS_DELTA
--------------------- -------- -------------------------------------------------------- ------------
P201702               DISABLED TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           49
P201705               DISABLED TO_DATE(' 2017-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           46
P201706               DISABLED TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           45
P201707               DISABLED TO_DATE(' 2017-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           44
P201708               DISABLED TO_DATE(' 2017-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           43
P201709               DISABLED TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           42
P201710               DISABLED TO_DATE(' 2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           41
P201711               DISABLED TO_DATE(' 2017-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           40
P201712               DISABLED TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           39
P201801               DISABLED TO_DATE(' 2018-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           38
P201802               DISABLED TO_DATE(' 2018-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           37
P201803               DISABLED TO_DATE(' 2018-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           36
SQL> 
SQL> set echo off

SQL> set verify on
SQL> define partition_name = P201702
SQL> alter table APP.INTERVAL modify partition &partition_name compress for OLTP;
old   1: alter table APP.INTERVAL modify partition &partition_name compress for OLTP
new   1: alter table APP.INTERVAL modify partition P201702 compress for OLTP

SQL> alter table APP.INTERVAL move partition &partition_name ONLINE UPDATE INDEXES PARALLEL 8;
old   1: alter table APP.INTERVAL move partition &partition_name ONLINE UPDATE INDEXES PARALLEL 8
new   1: alter table APP.INTERVAL move partition P201702 ONLINE UPDATE INDEXES PARALLEL 8

SQL> define partition_name = P201705
SQL> alter table APP.INTERVAL modify partition &partition_name compress for OLTP;
old   1: alter table APP.INTERVAL modify partition &partition_name compress for OLTP
new   1: alter table APP.INTERVAL modify partition P201705 compress for OLTP

SQL> alter table APP.INTERVAL move partition &partition_name ONLINE UPDATE INDEXES PARALLEL 8;
old   1: alter table APP.INTERVAL move partition &partition_name ONLINE UPDATE INDEXES PARALLEL 8
new   1: alter table APP.INTERVAL move partition P201705 ONLINE UPDATE INDEXES PARALLEL 8

SQL> @ partition_interval_delta.sql
SQL> select
  2  partition_name, compression, high_value,
  3  TRUNC(MONTHS_BETWEEN(SYSDATE,(TO_DATE(regexp_substr(partition_name, '[[:digit:]]+'),'YYYYMM')))) months_delta
  4  from dba_tab_partitions
  5  where table_owner = UPPER('&owner')
  6  and table_name = UPPER('&table')
  7  and compression='DISABLED'
  8  and TRUNC(MONTHS_BETWEEN(SYSDATE,(TO_DATE(regexp_substr(partition_name, '[[:digit:]]+'),'YYYYMM')))) >= &months_delta
  9  order by 1
 10  ;
Enter value for owner: app
Enter value for table: interval
Enter value for months_delta: 36

PARTITION_NAME        COMPRESS HIGH_VALUE                                               MONTHS_DELTA
--------------------- -------- -------------------------------------------------------- ------------
P201706               DISABLED TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           45
P201707               DISABLED TO_DATE(' 2017-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           44
P201708               DISABLED TO_DATE(' 2017-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           43
P201709               DISABLED TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           42
P201710               DISABLED TO_DATE(' 2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           41
P201711               DISABLED TO_DATE(' 2017-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           40
P201712               DISABLED TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           39
P201801               DISABLED TO_DATE(' 2018-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           38
P201802               DISABLED TO_DATE(' 2018-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           37
P201803               DISABLED TO_DATE(' 2018-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           36
SQL> set echo off

Check partition info after compression.

SQL> @ partition_info.sql
SQL> select p.table_name, p.partition_name, s.bytes/1024/1024/1024 GB, p.pct_free "%FREE",
  2  p.partition_position pos, p.interval, p.compression, p.compress_for,
  3  p.high_value, t.interval numtoyminterval
  4  from dba_tab_partitions p, dba_part_tables t, dba_segments s
  5  where p.table_owner=t.owner
  6  and p.partition_name=s.partition_name
  7  and p.table_owner = UPPER('&owner')
  8  and p.table_name = UPPER('&table')
  9  and t.table_name=p.table_name
 10  and regexp_like(t.interval,'^[0-9]|DAY|MONTH|YEAR')
 11  order by partition_position asc
 12  ;
Enter value for owner: app
Enter value for table: interval

TABLE_NAME           PARTITION        GB %FREE     POS INT COMPRESS COMPRESS_FOR HIGH_VALUE                                               NUMTOYMINTERVAL
-------------------- --------- --------- ----- ------- --- -------- ------------ -------------------------------------------------------- ---------------------------
INTERVAL             P201611         .01    10       1 NO  ENABLED  ADVANCED     TO_DATE(' 2016-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201612         .01    10       2 YES ENABLED  ADVANCED     TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201701         .01    10       3 YES ENABLED  ADVANCED     TO_DATE(' 2017-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201702         .01    10       4 YES ENABLED  ADVANCED     TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201703         .01    10       5 YES ENABLED  ADVANCED     TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201704         .01    10       6 YES ENABLED  ADVANCED     TO_DATE(' 2017-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201705         .01    10       7 YES ENABLED  ADVANCED     TO_DATE(' 2017-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201706         .01    10       8 YES DISABLED              TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201707         .01    10       9 YES DISABLED              TO_DATE(' 2017-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201708         .01    10      10 YES DISABLED              TO_DATE(' 2017-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201709         .01    10      11 YES DISABLED              TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201710         .01    10      12 YES DISABLED              TO_DATE(' 2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
SQL> set echo off
SQL>

March 14, 2020

ORA-14758: Last partition in the range section cannot be dropped

Filed under: 12.2,partitioning — mdinh @ 7:27 pm

Quick and dirty post.

Works for 12.2.0.1 and failed with ORA-14758 below 12.2.0.1

00:51:45 DINH @ HAWK:HAWK:>@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         D1              NO  TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         D2              NO  TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         D3              NO  TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P661        YES TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P662        YES TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P663        YES TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P664        YES TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P665        YES TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P666        YES TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P667        YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

10 rows selected.

00:51:49 DINH @ HAWK:HAWK:>alter table test drop partition D1;

Table altered.

00:52:00 DINH @ HAWK:HAWK:>alter table test drop partition D2;

Table altered.

00:52:08 DINH @ HAWK:HAWK:>alter table test drop partition D3;

Table altered.

00:52:20 DINH @ HAWK:HAWK:>@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         SYS_P661        NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P662        YES TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P663        YES TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P664        YES TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P665        YES TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P666        YES TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P667        YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

7 rows selected.

00:52:23 DINH @ HAWK:HAWK:>alter table test drop partition SYS_P662;

Table altered.

00:52:46 DINH @ HAWK:HAWK:>alter table test drop partition SYS_P663;

Table altered.

00:52:58 DINH @ HAWK:HAWK:>alter table test drop partition SYS_P664;

Table altered.

00:53:09 DINH @ HAWK:HAWK:>@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         SYS_P661        NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P665        YES TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P666        YES TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P667        YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

00:53:12 DINH @ HAWK:HAWK:>alter table test drop partition SYS_P665;

Table altered.

00:53:43 DINH @ HAWK:HAWK:>alter table test drop partition SYS_P666;

Table altered.

00:53:53 DINH @ HAWK:HAWK:>@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         SYS_P661        NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P667        YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

00:54:04 DINH @ HAWK:HAWK:>alter table test drop partition SYS_P667;

Table altered.

00:54:22 DINH @ HAWK:HAWK:>@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         SYS_P661        NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

00:54:28 DINH @ HAWK:HAWK:>insert into test values(to_date('01.01.2009', 'dd.mm.yyyy'), 1);

1 row created.

00:54:36 DINH @ HAWK:HAWK:>commit;

Commit complete.

00:54:41 DINH @ HAWK:HAWK:>@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         SYS_P661        NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P668        YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

00:54:44 DINH @ HAWK:HAWK:>exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

====================================================================================================

18:59:19 DB-FS-1:(MDINH@hawk):PRIMARY> @p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- -------------------------------------------------------------------------------------
MDINH        TEST         D1              NO  TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         D2              NO  TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         D3              NO  TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P68         YES TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P69         YES TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P70         YES TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P71         YES TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P72         YES TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P73         YES TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P74         YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

10 rows selected.

18:59:40 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test drop partition D1;

Table altered.

18:59:50 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test drop partition D2;

Table altered.

18:59:58 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test drop partition D3;
alter table test drop partition D3
                                *
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped


19:00:06 DB-FS-1:(MDINH@hawk):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@db-fs-1 sql]$

SOLUTION:

I have been discussing this with LDC and agree the better solution is to TRUNCATE the partition.

How To Avoid ORA-14758 in Interval Partitioned Table (Doc ID 1526571.1)
(iv) Temporarily disable the interval partition.

Here is one example of Temporarily disable the interval partition.
This has side effects as shown which has worked until one day it stopped working.
Test cases are simple and real world may not be as such.

12:44:18 DB-FS-1:(MDINH@hawk):PRIMARY> @p

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INTERVAL                       HIGH_VALUE
------------ ------------ --------------- ------------------------------ ----------------------------------------------------------
MDINH        TEST         D1              NO                             TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         D2              NO                             TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         D3              NO                             TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P101        YES                            TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P102        YES                            TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P103        YES                            TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P104        YES                            TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P105        YES                            TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P106        YES                            TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P107        YES                            TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',

10 rows selected.

12:44:20 DB-FS-1:(MDINH@hawk):PRIMARY> drop alter table test drop partition D1;
drop alter table test drop partition D1
     *
ERROR at line 1:
ORA-00950: invalid DROP option


12:44:48 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test drop partition D1;

Table altered.

12:45:45 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test drop partition D2;

Table altered.

12:45:57 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test drop partition D3;
alter table test drop partition D3
                                *
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped


12:46:07 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test truncate partition D3;

Table truncated.

12:46:31 DB-FS-1:(MDINH@hawk):PRIMARY> alter table TEST set interval ();

Table altered.

12:50:23 DB-FS-1:(MDINH@hawk):PRIMARY> alter table TEST drop partition D3;

Table altered.

12:50:44 DB-FS-1:(MDINH@hawk):PRIMARY> alter table TEST set interval (numtoyminterval(1,'YEAR'));

Table altered.

12:50:53 DB-FS-1:(MDINH@hawk):PRIMARY> @p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INTERVAL                       HIGH_VALUE
------------ ------------ --------------- ------------------------------ ----------------------------------------------------------
MDINH        TEST         SYS_P101        NO                             TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P102        NO                             TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P103        NO                             TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P104        NO                             TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P105        NO                             TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P106        NO                             TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P107        NO                             TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',

7 rows selected.

12:50:58 DB-FS-1:(MDINH@hawk):PRIMARY> insert into test values(to_date('01.01.2010', 'dd.mm.yyyy'), 1);

1 row created.

12:52:15 DB-FS-1:(MDINH@hawk):PRIMARY> commit;

Commit complete.

12:54:36 DB-FS-1:(MDINH@hawk):PRIMARY> @p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME           POS INT HIGH_VALUE
------------ ------------ --------------- ------------ --- ----------------------------------------------------------
MDINH        TEST         SYS_P101                   1 NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P102                   2 NO  TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P103                   3 NO  TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P104                   4 NO  TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P105                   5 NO  TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P106                   6 NO  TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P107                   7 NO  TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P108                   8 YES TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',

8 rows selected.

12:54:40 DB-FS-1:(MDINH@hawk):PRIMARY>

February 17, 2016

Converting to Interval Paritions

Filed under: 11g,partitioning — mdinh @ 11:24 pm

Just a quick note to demonstrate how to convert date range partition into interval partition.

Create test case:

ARROW:(MDINH@hawklas):PRIMARY> @test_part.sql
ARROW:(MDINH@hawklas):PRIMARY> set echo on
ARROW:(MDINH@hawklas):PRIMARY> drop table t_part purge;

Table dropped.

ARROW:(MDINH@hawklas):PRIMARY> create table t_part (
  2  col_date DATE
  3  )
  4  PARTITION BY RANGE (col_date)
  5  (
  6  PARTITION P2014 VALUES LESS THAN (TO_DATE('2015-01-01','YYYY-MM-DD')),
  7  PARTITION P2015 VALUES LESS THAN (TO_DATE('2016-01-01','YYYY-MM-DD')),
  8  PARTITION PMAX VALUES LESS THAN (MAXVALUE)
  9  );

Table created.

ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2014-01-01','YYYY-MM-DD'));

1 row created.

ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2015-01-01','YYYY-MM-DD'));

1 row created.

ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2016-01-01','YYYY-MM-DD'));

1 row created.

ARROW:(MDINH@hawklas):PRIMARY> commit;

Commit complete.

ARROW:(MDINH@hawklas):PRIMARY> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'T_PART',cascade=>DBMS_STATS.AUTO_CASCADE,method_opt => 'FOR ALL COLUMNS SIZE AUTO',estimate_percent => NULL);

PL/SQL procedure successfully completed.

Check partitions:

ARROW:(MDINH@hawklas):PRIMARY> @part.sql
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 pages 10000
ARROW:(MDINH@hawklas):PRIMARY> col table_owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col table_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col high_value for a50 wrap
ARROW:(MDINH@hawklas):PRIMARY> SELECT table_owner,table_name,interval,partition_name,num_rows,subpartition_count,high_value
  2  FROM dba_tab_partitions i
  3  WHERE table_name='T_PART'
  4  ;

TABLE_OWNER          TABLE_NAME           INT PARTITION_NAME         NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE
-------------------- -------------------- --- -------------------- ---------- ------------------ --------------------------------------------------
MDINH                T_PART               NO  P2014                         1                  0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2015                         1                  0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  PMAX                          1                  0 MAXVALUE

ARROW:(MDINH@hawklas):PRIMARY> SELECT owner,
  2    table_name,
  3    partitioning_type,
  4    subpartitioning_type,
  5    partition_count,
  6    def_subpartition_count,
  7    partitioning_key_count,
  8    def_tablespace_name
  9  FROM DBA_PART_TABLES
 10  WHERE partitioning_type='RANGE'
 11  AND table_name='T_PART'
 12  ;

OWNER                TABLE_NAME           PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------- --------- --------------- ---------------------- ---------------------- ------------------------------
MDINH                T_PART               RANGE     NONE                    3                      0                      1 USERS

Failed due to PMAX partition as shown above:

ARROW:(MDINH@hawklas):PRIMARY> alter table t_part set interval(numtodsinterval(1,'year'));
alter table t_part set interval(numtodsinterval(1,'year'))
*
ERROR at line 1:
ORA-14759: SET INTERVAL is not legal on this table.

Split partition to remove PMAX:

ARROW:(MDINH@hawklas):PRIMARY> alter table t_part split partition PMAX AT(TO_DATE('2017-01-01', 'YYYY-MM-DD')) into (partition P2017, partition PMAX);

Table altered.

ARROW:(MDINH@hawklas):PRIMARY> alter table t_part drop partition pmax;

Table altered.

Check partition:

ARROW:(MDINH@hawklas):PRIMARY> @part.sql
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 pages 10000
ARROW:(MDINH@hawklas):PRIMARY> col table_owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col table_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col high_value for a50 wrap
ARROW:(MDINH@hawklas):PRIMARY> SELECT table_owner,table_name,interval,partition_name,num_rows,subpartition_count,high_value
  2  FROM dba_tab_partitions i
  3  WHERE table_name='T_PART'
  4  ;

TABLE_OWNER          TABLE_NAME           INT PARTITION_NAME         NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE
-------------------- -------------------- --- -------------------- ---------- ------------------ --------------------------------------------------
MDINH                T_PART               NO  P2014                         1                  0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2015                         1                  0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2017                         1                  0 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')


ARROW:(MDINH@hawklas):PRIMARY> SELECT owner,
  2    table_name,
  3    partitioning_type,
  4    subpartitioning_type,
  5    partition_count,
  6    def_subpartition_count,
  7    partitioning_key_count,
  8    def_tablespace_name
  9  FROM DBA_PART_TABLES
 10  WHERE partitioning_type='RANGE'
 11  AND table_name='T_PART'
 12  ;

OWNER                TABLE_NAME           PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------- --------- --------------- ---------------------- ---------------------- ------------------------------
MDINH                T_PART               RANGE     NONE                    3                      0                      1 USERS



Wrong syntax:

ARROW:(MDINH@hawklas):PRIMARY> alter table t_part set interval(numtodsinterval(1,'year'));
alter table t_part set interval(numtodsinterval(1,'year'))
                                                  *
ERROR at line 1:
ORA-14752: Interval expression is not a constant of the correct type

Correct syntax:

ARROW:(MDINH@hawklas):PRIMARY> alter table t_part set interval(NUMTOYMINTERVAL(1,'year'));

Table altered.

Check partition:

ARROW:(MDINH@hawklas):PRIMARY> @part
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 pages 10000
ARROW:(MDINH@hawklas):PRIMARY> col table_owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col table_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col high_value for a50 wrap
ARROW:(MDINH@hawklas):PRIMARY> SELECT table_owner,table_name,interval,partition_name,num_rows,subpartition_count,high_value
  2  FROM dba_tab_partitions i
  3  WHERE table_name='T_PART'
  4  ;

TABLE_OWNER          TABLE_NAME           INT PARTITION_NAME         NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE
-------------------- -------------------- --- -------------------- ---------- ------------------ --------------------------------------------------
MDINH                T_PART               NO  P2014                         1                  0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2015                         1                  0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2017                         1                  0 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

ARROW:(MDINH@hawklas):PRIMARY> SELECT owner,
  2    table_name,
  3    partitioning_type,
  4    subpartitioning_type,
  5    partition_count,
  6    def_subpartition_count,
  7    partitioning_key_count,
  8    def_tablespace_name
  9  FROM DBA_PART_TABLES
 10  WHERE partitioning_type='RANGE'
 11  AND table_name='T_PART'
 12  ;

OWNER                TABLE_NAME           PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------- --------- --------------- ---------------------- ---------------------- ------------------------------
MDINH                T_PART               RANGE     NONE              1048575                      0                      1 USERS

Add data:

ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2018-01-01','YYYY-MM-DD'));

1 row created.

ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2019-01-01','YYYY-MM-DD'));

1 row created.


ARROW:(MDINH@hawklas):PRIMARY> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'T_PART',cascade=>DBMS_STATS.AUTO_CASCADE,method_opt => 'FOR ALL COLUMNS SIZE AUTO',estimate_percent => NULL);

PL/SQL procedure successfully completed.

Check partition:

ARROW:(MDINH@hawklas):PRIMARY> @part.sql
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 pages 10000
ARROW:(MDINH@hawklas):PRIMARY> col table_owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col table_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col high_value for a50 wrap
ARROW:(MDINH@hawklas):PRIMARY> SELECT table_owner,table_name,interval,partition_name,num_rows,subpartition_count,high_value
2 FROM dba_tab_partitions i
3 WHERE table_name='T_PART'
4 ;

TABLE_OWNER          TABLE_NAME           INT PARTITION_NAME         NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE
-------------------- -------------------- --- -------------------- ---------- ------------------ --------------------------------------------------
MDINH                T_PART               NO  P2014                         1                  0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2015                         1                  0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2017                         1                  0 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               YES SYS_P541                      1                  0 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               YES SYS_P542                      1                  0 TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')
ARROW:(MDINH@hawklas):PRIMARY> SELECT owner,
2 table_name,
3 partitioning_type,
4 subpartitioning_type,
5 partition_count,
6 def_subpartition_count,
7 partitioning_key_count,
8 def_tablespace_name
9 FROM DBA_PART_TABLES
10 WHERE partitioning_type='RANGE'
11 AND table_name='T_PART'
12 ;

OWNER TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------- --------- --------------- ---------------------- ---------------------- ------------------------------
MDINH T_PART RANGE NONE 1048575 0 1 USERS

ARROW:(MDINH@hawklas):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:hawklas:/media/sf_working/sql
$

Create a free website or blog at WordPress.com.