Database Reference
In-Depth Information
5 and a.table_name = b.table_name
6 order by a.partition_position;
PARTITION_ TABLESPACE HIGH_VALUE INTERVAL
---------- ---------- ------------------------------- ------------------------------
P0 USERS TIMESTAMP' 1900-01-01 00:00:00'
SYS_P1623 USERS TIMESTAMP' 2014-03-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')
SYS_P1625 EXAMPLE TIMESTAMP' 2014-04-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')
SYS_P1624 USERS TIMESTAMP' 2014-07-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')
As soon as they are created, they are committed and visible. These partitions are created using a
recursive
transaction, a transaction executed separate and distinct from any transaction you might already be performing.
When we went to insert the row and the database discovered that the partition we needed did not exist, the database
immediately started a new transaction, updated the data dictionary to reflect the new partition's existence, and
committed its work. It must do this, or there would be severe contention (serialization) on many inserts as other
transactions would have to wait for us to commit to be able to see this new partition. Therefore, this DDL is done
outside of your existing transaction and the partitions will persist.
You might have noticed that the database names the partition for us;
SYS_P1625
is the name of the newest
partition. The names are not sortable nor very meaningful in the sense most people would be used to. They show
the order in which the partitions were added to the table (although you cannot rely on that always being true; it is
subject to change) but not much else. Normally, in a range partitioned table, the DBA would have named the partition
using some naming scheme and in most cases would have made the partition names sortable. For example, the
February data would be in a partition named
PART_2014_02
(using a format of
PART_yyyy_mm
), March would be in
PART_2014_03
, and so on. With interval partitioning, you have no control over the partition names as they are created,
but you can easily rename them afterward if you like. For example, we could query out the
HIGH_VALUE
string and
using dynamic SQL convert that into nicely formatted, meaningful names. We can do this because we understand
how we'd like the names formatted; the database does not. For example:
EODA@ORA12CR1> declare
2 l_str varchar2(4000);
3 begin
4 for x in ( select a.partition_name, a.tablespace_name, a.high_value
5 from user_tab_partitions a
6 where a.table_name = 'AUDIT_TRAIL'
7 and a.interval = 'YES'
8 and a.partition_name like 'SYS\_P%' escape '\' )
9 loop
10 execute immediate
11 'select to_char( ' || x.high_value ||
12 '-numtodsinterval(1,''second''), ''"PART_"yyyy_mm'' ) from dual'
13 into l_str;
14 execute immediate
15 'alter table audit_trail rename partition "' ||
16 x.partition_name || '" to "' || l_str || '"';
17 end loop;
18 end;
19 /
PL/SQL procedure successfully completed.
