Database Reference
In-Depth Information
EODA@ORA11GR2> insert into partitioned partition(fy_2015)
2 select to_date('31-dec-2015','dd-mon-yyyy')-mod(rownum,364), rownum
3 from dual connect by level < 100000;
99999 rows created.
EODA@ORA11GR2> create index partitioned_idx_global
2 on partitioned(timestamp)
3 GLOBAL;
Index created.
Next we'll run a query to retrieve the current values of
redo size
and
db block gets
statistics for the current
session:
EODA@ORA11GR2> col r1 new_value r2
EODA@ORA11GR2> col b1 new_value b2
EODA@ORA11GR2> select * from
2 (select b.value r1
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 and a.name = 'redo size'),
6 (select b.value b1
7 from v$statname a, v$mystat b
8 where a.statistic# = b.statistic#
9 and a.name = 'db block gets');
R1 B1
---------- ----------
4816712 4512
Next a partition is dropped with the
UPDATE GLOBAL INDEXES
clause specified:
EODA@ORA11GR2> alter table partitioned drop partition fy_2014 update global indexes;
Table altered.
Now we'll calculate the amount of redo generated and the number of current blocks accessed:
EODA@ORA11GR2> select * from
2 (select b.value - &r2 redo_gen
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 and a.name = 'redo size'),
6 (select b.value - &b2 db_block_gets
7 from v$statname a, v$mystat b
8 where a.statistic# = b.statistic#
9 and a.name = 'db block gets');
old 2: (select b.value - &r2 redo_gen
new 2: (select b.value - 4816712 redo_gen
old 6: (select b.value - &b2 db_block_gets
new 6: (select b.value - 4512 db_block_gets
