Database Reference
In-Depth Information
You might be looking at this output and asking why everything is in the
USERS
tablespace. We clearly asked for
the data to be spread out over the
USERS
tablespace and the
EXAMPLE
tablespace, so why is everything in a single
tablespace? It has to do with the fact that when the database is figuring out what partition the data goes into, it is
also computing which tablespace it would go into. Since each of our partitions is an even number of months away
from each other and we are using just two tablespaces, we end up using the same tablespace over and over. If we
only loaded “every other month” into this table, we would end up using only a single tablespace. We can see that the
EXAMPLE
tablespace can be used by adding some row that is an 'odd' number of months away from our existing data:
EODA@ORA12CR1> insert into audit_trail (ts,data) values
2 ( to_date('15-mar-2014','dd-mon-yyyy'), 'xx' );
1 row created.
EODA@ORA12CR1> select a.partition_name, a.tablespace_name, a.high_value,
2 decode( a.interval, 'YES', b.interval ) interval
3 from user_tab_partitions a, user_part_tables b
4 where a.table_name = 'AUDIT_TRAIL'
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')
Now we have used the
EXAMPLE
tablespace. This new partition was slid in between the two existing partitions and
will contain all of our March 2014 data.
You might be asking, “What happens if I rollback at this point?” If we were to rollback, it should be obvious that
the
AUDIT_TRAIL
rows we just inserted would go away:
EODA@ORA12CR1> select * from audit_trail;
TS DATA
----------------------------------- ------------------------------
27-FEB-14 12.00.00.000000 AM xx
15-MAR-14 12.00.00.000000 AM xx
25-JUN-14 12.00.00.000000 AM xx
EODA@ORA12CR1> rollback;
Rollback complete.
EODA@ORA12CR1> select * from audit_trail;
no rows selected
But what isn't clear immediately is what would happen to the partitions we added: do they stay or will they go
away as well? A quick query will verify that they will stay:
EODA@ORA12CR1> select a.partition_name, a.tablespace_name, a.high_value,
2 decode( a.interval, 'YES', b.interval ) interval
3 from user_tab_partitions a, user_part_tables b
4 where a.table_name = 'AUDIT_TRAIL'
