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 offINDEX 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 offLet’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
Thinking Out Loud 