12 February, 2013

Moving a Partition to an Archival Schema and Tablespace

Here is a demo of moving a partition of the BALANCES table in the BALANCES schema to a separate Schema (ARCH), Tablespace (ARCHIVAL) and Table (BALANCES_ARCH).


SQL> connect hemant/hemant
Connected.
SQL> -- drop users
SQL> drop user BALANCES cascade;

User dropped.

SQL> drop user ARCH cascade;

User dropped.

SQL> -- set target file dest
SQL> alter system set db_create_file_dest='/tmp/';

System altered.

SQL> 
SQL> -- create tablespaces
SQL> drop tablespace balances including contents and datafiles;

Tablespace dropped.

SQL> create tablespace balances ;

Tablespace created.

SQL> 
SQL> drop tablespace archival including contents and datafiles;

Tablespace dropped.

SQL> create tablespace archival ;

Tablespace created.

SQL> 
SQL> -- create users
SQL> drop user BALANCES cascade;
drop user BALANCES cascade
          *
ERROR at line 1:
ORA-01918: user 'BALANCES' does not exist


SQL> create user BALANCES identified by BALANCES default tablespace balances;

User created.

SQL> grant create session to BALANCES;

Grant succeeded.

SQL> alter user BALANCES quota unlimited on balances;

User altered.

SQL> alter user BALANCES quota unlimited on archival;

User altered.

SQL> grant create table to BALANCES;

Grant succeeded.

SQL> 
SQL> drop user ARCH cascade;
drop user ARCH cascade
          *
ERROR at line 1:
ORA-01918: user 'ARCH' does not exist


SQL> create user ARCH identified by ARCH default tablespace archival;

User created.

SQL> grant create session to ARCH;

Grant succeeded.

SQL> alter user ARCH quota unlimited on archival;

User altered.

SQL> alter user ARCH quota unlimited on balances;

User altered.

SQL> grant create table to ARCH;

Grant succeeded.

SQL> 
SQL> -- rem create source table
SQL> connect BALANCES/BALANCES
Connected.
SQL> create table BALANCES
  2  (branch_code  varchar2(5),
  3   account_number number,
  4   fiscal_year  number,
  5   accounting_period number,
  6   balance number)
  7  partition by range (branch_code, fiscal_year, accounting_period)
  8  (partition ABC_2012_1 values less than ('ABC',2012,2),
  9   partition ABC_2012_2 values less than ('ABC',2012,3),
 10   partition ABC_2012_3 values less than ('ABC',2012,4),
 11   partition ABC_2012_4 values less than ('ABC',2012,5),
 12   partition ABC_2012_5 values less than ('ABC',2012,6),
 13   partition ABC_2012_6 values less than ('ABC',2012,7),
 14   partition XYZ_2012_1 values less than ('XYZ',2012,2),
 15   partition XYZ_2012_2 values less than ('XYZ',2012,3),
 16   partition XYZ_2012_3 values less than ('XYZ',2012,4),
 17   partition XYZ_2012_4 values less than ('XYZ',2012,5),
 18   partition XYZ_2012_5 values less than ('XYZ',2012,6),
 19   partition XYZ_2012_6 values less than ('XYZ',2012,7)
 20  )
 21  tablespace balances;

Table created.

SQL> create index BALANCES_NDX on
  2  BALANCES(BRANCH_CODE, ACCOUNT_NUMBER, FISCAL_YEAR, ACCOUNTING_PERIOD)
  3  LOCAL;

Index created.

SQL> 
SQL> 
SQL> 
SQL> --- rem create target archival table
SQL> connect ARCH/ARCH
Connected.
SQL> create table BALANCES_ARCH
  2  (branch_code  varchar2(5),
  3   account_number number,
  4   fiscal_year  number,
  5   accounting_period number,
  6   balance number)
  7  partition by range (branch_code, fiscal_year, accounting_period)
  8  (partition ABC_2012_1 values less than ('ABC',2012,2),
  9   partition ABC_2012_2 values less than ('ABC',2012,3),
 10   partition ABC_2012_3 values less than ('ABC',2012,4),
 11   partition ABC_2012_4 values less than ('ABC',2012,5),
 12   partition ABC_2012_5 values less than ('ABC',2012,6),
 13   partition ABC_2012_6 values less than ('ABC',2012,7),
 14   partition XYZ_2012_1 values less than ('XYZ',2012,2),
 15   partition XYZ_2012_2 values less than ('XYZ',2012,3),
 16   partition XYZ_2012_3 values less than ('XYZ',2012,4),
 17   partition XYZ_2012_4 values less than ('XYZ',2012,5),
 18   partition XYZ_2012_5 values less than ('XYZ',2012,6),
 19   partition XYZ_2012_6 values less than ('XYZ',2012,7)
 20  )
 21  tablespace archival;

Table created.

SQL> create index BALANCES_ARCH_NDX on
  2  BALANCES_ARCH(BRANCH_CODE, ACCOUNT_NUMBER, FISCAL_YEAR, ACCOUNTING_PERIOD)
  3  LOCAL;

Index created.

SQL> 
SQL> 
SQL> -- insert data into source
SQL> connect BALANCES/BALANCES
Connected.
SQL> insert into balances
  2  select decode(mod(rownum,2),0,'ABC',1,'XYZ'),rownum,2012,1,1000
  3  from dual
  4  connect by level < 10
  5  /

9 rows created.

SQL> insert into balances
  2  select decode(mod(rownum,2),0,'ABC',1,'XYZ'),rownum,2012,2,2000
  3  from dual
  4  connect by level < 10
  5  /

9 rows created.

SQL> insert into balances
  2  select decode(mod(rownum,2),0,'ABC',1,'XYZ'),rownum,2012,3,3000
  3  from dual
  4  connect by level < 10
  5  /

9 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> REM REM REM
SQL> -- select branch_code, account_number, fiscal_year, accounting_period, count(*)
SQL> -- from balances
SQL> -- group by
SQL> -- branch_code, account_number, fiscal_year, accounting_period
SQL> -- order by 1,2,3,4
SQL> -- /
SQL> REM REM REM
SQL> 
SQL> -- create intermediate table with index
SQL> connect BALANCES/BALANCES
Connected.
SQL> create table balances_exch_tbl tablespace balances as select * from balances where 1=2;

Table created.

SQL> create index balances_exch_tbl_ndx on balances_exch_tbl(branch_code, account_number, fiscal_year, accounting_period)
  2  tablespace balances;

Index created.

SQL> grant select, alter on balances_exch_tbl to ARCH;

Grant succeeded.

SQL> 
SQL> -- verify row counts in source and archival partitions
SQL> connect BALANCES/BALANCES
Connected.
SQL> select count(*) from BALANCES partition (ABC_2012_1);

  COUNT(*)
----------
         4

SQL> connect ARCH/ARCH
Connected.
SQL> select count(*) from BALANCES_ARCH partition (ABC_2012_1);

  COUNT(*)
----------
         0

SQL> 
SQL> -- exchange for account_period 1 for ABC
SQL> connect BALANCES/BALANCES
Connected.
SQL> alter table BALANCES exchange partition ABC_2012_1 with table balances_exch_tbl including indexes without validation;

Table altered.

SQL> connect ARCH/ARCH
Connected.
SQL> alter table BALANCES_ARCH exchange partition ABC_2012_1 with table balances.balances_exch_tbl including indexes without validation;

Table altered.

SQL> alter table BALANCES_ARCH move partition ABC_2012_1 tablespace archival;

Table altered.

SQL> connect BALANCES/BALANCES
Connected.
SQL> alter table balances_exch_tbl move tablespace balances;

Table altered.

SQL> alter index balances_exch_tbl_ndx rebuild tablespace balances;

Index altered.

SQL> 
SQL> 
SQL> -- verify row counts in source and archival partitions
SQL> connect BALANCES/BALANCES
Connected.
SQL> select count(*) from BALANCES partition (ABC_2012_1);

  COUNT(*)
----------
         0

SQL> connect ARCH/ARCH
Connected.
SQL> select count(*) from BALANCES_ARCH partition (ABC_2012_1);

  COUNT(*)
----------
         4

SQL> 
SQL> -- query for segment tablespaces
SQL> connect hemant/hemant
Connected.
SQL> select owner, segment_name, segment_type, partition_name, tablespace_name
  2  from dba_segments
  3  where segment_name like 'BALANC%'
  4  order by 1,2,3,4
  5  /

OWNER      SEGMENT_NAME          SEGMENT_TYPE     PARTITION_NAME        TABLESPACE_NAME
---------- --------------------- ---------------- --------------------- ---------------------
ARCH       BALANCES_ARCH         TABLE PARTITION  ABC_2012_1            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  ABC_2012_2            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  ABC_2012_3            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  ABC_2012_4            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  ABC_2012_5            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  ABC_2012_6            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  XYZ_2012_1            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  XYZ_2012_2            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  XYZ_2012_3            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  XYZ_2012_4            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  XYZ_2012_5            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  XYZ_2012_6            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  ABC_2012_2            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  ABC_2012_3            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  ABC_2012_4            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  ABC_2012_5            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  ABC_2012_6            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  XYZ_2012_1            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  XYZ_2012_2            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  XYZ_2012_3            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  XYZ_2012_4            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  XYZ_2012_5            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  XYZ_2012_6            ARCHIVAL
BALANCES   BALANCES              TABLE PARTITION  ABC_2012_1            BALANCES
BALANCES   BALANCES              TABLE PARTITION  ABC_2012_2            BALANCES
BALANCES   BALANCES              TABLE PARTITION  ABC_2012_3            BALANCES
BALANCES   BALANCES              TABLE PARTITION  ABC_2012_4            BALANCES
BALANCES   BALANCES              TABLE PARTITION  ABC_2012_5            BALANCES
BALANCES   BALANCES              TABLE PARTITION  ABC_2012_6            BALANCES
BALANCES   BALANCES              TABLE PARTITION  XYZ_2012_1            BALANCES
BALANCES   BALANCES              TABLE PARTITION  XYZ_2012_2            BALANCES
BALANCES   BALANCES              TABLE PARTITION  XYZ_2012_3            BALANCES
BALANCES   BALANCES              TABLE PARTITION  XYZ_2012_4            BALANCES
BALANCES   BALANCES              TABLE PARTITION  XYZ_2012_5            BALANCES
BALANCES   BALANCES              TABLE PARTITION  XYZ_2012_6            BALANCES
BALANCES   BALANCES_EXCH_TBL     TABLE                                  BALANCES
BALANCES   BALANCES_EXCH_TBL_NDX INDEX                                  BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  ABC_2012_1            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  ABC_2012_2            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  ABC_2012_3            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  ABC_2012_4            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  ABC_2012_5            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  ABC_2012_6            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  XYZ_2012_1            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  XYZ_2012_2            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  XYZ_2012_3            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  XYZ_2012_4            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  XYZ_2012_5            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  XYZ_2012_6            BALANCES

49 rows selected.

SQL> 
The account HEMANT is the DBA account. BALANCES owns the Source BALANCES table in the BALANCES Tablespace. ARCH owns the the Archival BALANCES_ARCH table in the ARCHIVAL Tablespace.

No comments: