• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Oracle Tablespace Transport for a Single Partition

By admin

Prior to 11g, there is a restriction on transporting tablespaces for a single partition. Starting in RDBMS version 11g, transport of a tablespace for a single partition and plugging it into another database is now supported. This note shows the process to follow in order to achieve this.

Steps

1. Create the partitioned table:

SQL> CREATE TABLE rptg_pd 
2 ( 
3 a varchar2(5), 
4 period_dt date, 
5 b number) 
6 PARTITION BY RANGE (period_dt) 
7 ( 
8 PARTITION mv_period_dt_1994 VALUES LESS THAN (TO_DATE('01-JAN-1995')) tablespace part1, 
9 PARTITION mv_period_dt_1995 VALUES LESS THAN (TO_DATE('01-JAN-1996')) tablespace part2 
10 ); 

Table created. 
SQL> select partition_name, tablespace_name 
2 from user_tab_partitions 
3 where table_name = 'RPTG_PD'; 

PARTITION_NAME TABLESPACE_NAME 
------------------------------ ------------------------------ 
MV_PERIOD_DT_1994 PART1 
MV_PERIOD_DT_1995 PART2

2. Change the tablespace that contains the partition to read only:

SQL> alter tablespace PART1 read only; 

Tablespace altered.

3. Use the following command to export the metadata.

$ expdp samir/samir tables=samir.rptg_pd:mv_period_dt_1994 transportable=always directory=TEST_COMPRESS dumpfile=p_ct.dmp

4. Copy the datafiles to new location.

$ cp -p /data2/oracle/ora11g/PART1.dbf /data2/oracle/ora11g/TEST_DIR/

5. For this example, we will transport the partition back into the same database, but into a different schema by doing the following:

alter tablespace part1 rename to part3; Tablespace altered.
SQL> create user geek identified by geek; 

User created. 
SQL> grant dba to geek; 

Grant succeeded.

6. Now perform the import:

$ impdp system PARTITION_OPTIONS=departition TRANSPORT_DATAFILES='/data2/oracle/ora11g/TEST_DIR/PART1.dbf' DIRECTORY=TEST_COMPRESS DUMPFILE=p_ct.dmp REMAP_SCHEMA=samir:geek

You can now see a new table with name RPTG_PD_MV_PERIOD_DT_1994 in schema geek.

SQL> select table_name,tablespace_name from user_tables; 

TABLE_NAME                      TABLESPACE_NAME 
--------------------------      ------------------------------ 
RPTG_PD_MV_PERIOD_DT_1994       PART1
Note: The table name is always a combination of the original table and the partition name.
How To Convert A Partitioned Table To A Non-Partitioned Table Using DataPump In Oracle 11g and 12c

Filed Under: oracle, Oracle 11g, oracle 12c

Some more articles you might also be interested in …

  1. Oracle Database 18c new feature – Scalable Sequences
  2. Oracle 11G RMAN – Understanding UNDO backup optimisation
  3. How to Change Timezone Settings on Exadata
  4. Oracle sql script to report the list of files stored in ASM and CURRENTLY NOT OPENED
  5. Where to find ASMLib / oracleasm RPMs for CentOS/RHEL, SUSE, OEL
  6. What Happens and What to Do when the SPFILE has been Manually Modified
  7. Oracle Database: Profile Limits (Resource Parameter(s)) Are Not Enforced / Do Not Work
  8. New Background Processes In Oracle 11g
  9. How to duplicate a Oracle Database to a previous Incarnation
  10. Oracle – How To Check if Autoextensible Datafiles Set To Maxsize Unlimited

You May Also Like

Primary Sidebar

Recent Posts

  • What are different Oracle Database Vault Roles
  • Unable to export realm protected table using data pump
  • Beginners Guide to Oracle Database Vault
  • How to Disable IPv6 on Ubuntu 18.04 Bionic Beaver Linux
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary