Search My Oracle Blog

Custom Search

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:

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