Search My Oracle Blog

Custom Search

02 May, 2016

FBDA -- 7 : Maintaining Partitioned Source Table

Taking up the TEST_FBDA_PARTITIONED table,  let's look at a couple of Partition Maintenance operations.

SQL> select partition_name, high_value, num_rows
  2  from user_tab_partitions
  3  where table_name = 'TEST_FBDA_PARTITIONED'
  4  order by partition_position
  5  /

PARTITION_NAME   HIGH_VALUE                  NUM_ROWS
---------------- ------------------------- ----------
P_100            101                              100
P_200            201                              100
P_300            301                              100
P_400            401                              100
P_MAX            MAXVALUE                         301

SQL> 


Let's try a TRUNCATE PARTITION

SQL> alter table test_fbda_partitioned truncate partition p_100;

Table truncated.

SQL> 


So, that's supported.

Let's try a SPLIT PARTTIION

SQL> alter table test_fbda_partitioned       
  2  split partition p_max at (501)
  3  into (partition p_500, partition p_max)
  4  /
alter table test_fbda_partitioned
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table


SQL> 


So, a SPLIT PARTITION fails.  We need to DISASSOCIATE the Flashback Archive.

SQL> execute dbms_flashback_archive.disassociate_fba('HEMANT','TEST_FBDA_PARTITIONED');

PL/SQL procedure successfully completed.

SQL> select table_name, flashback_archive_name, archive_table_name, status
  2  from user_flashback_archive_tables
  3  where table_name = 'TEST_FBDA_PARTITIONED'
  4  /

TABLE_NAME
--------------------------------------------------------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
ARCHIVE_TABLE_NAME          STATUS
----------------------------------------------------- -------------
TEST_FBDA_PARTITIONED
FBDA
SYS_FBA_HIST_93342          DISASSOCIATED


SQL> 
SQL> alter table test_fbda_partitioned
  2  split partition p_max at (501)
  3  into (partition p_500, partition p_max)
  4  /

Table altered.

SQL> execute dbms_flashback_archive.reassociate_fba('HEMANT','TEST_FBDA_PARTITIONED');

PL/SQL procedure successfully completed.

SQL> 
SQL> select table_name, flashback_archive_name, archive_table_name, status
  2  from user_flashback_archive_tables
  3  where table_name = 'TEST_FBDA_PARTITIONED'
  4  /

TABLE_NAME
--------------------------------------------------------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
ARCHIVE_TABLE_NAME          STATUS
----------------------------------------------------- -------------
TEST_FBDA_PARTITIONED
FBDA
SYS_FBA_HIST_93342          ENABLED


SQL> 


While a Table is disassociated with it's Flashback Archive, DDL that would not normally be permitted may be done under strict control to ensure that there is no data divergence.

.
.
.

No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016