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

The Geek Diary

HowTos | Basics | Concepts

  • Solaris
    • Solaris 11
    • SVM
    • ZFS
    • Zones
    • LDOMs
    • Hardware
  • Linux
    • CentOS/RHEL 7
    • RHCSA notes
    • SuSE Linux Enterprise
    • Linux Services
  • VCS
    • VxVM
  • Interview Questions
  • oracle
    • ASM
    • mysql
    • RAC
    • oracle 12c
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Hadoop
    • Hortonworks HDP
      • HDPCA
    • Cloudera
      • CCA 131

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 Interview Questions – Flash Recovery Area
  2. Oracle 12.2 : RMAN Cross-Platform Transport of PDB into Destination CDB
  3. How to set custom device names using udev in CentOS/RHEL 7
  4. Understanding Oracle Database Automatic SGA Memory Tuning
  5. How to startup an Oracle Database Instance using spfile or pfile(init.ora) parameter file
  6. Oracle Database : Understanding Dead Connection Detection, Resource Limits, V$SESSION, V$PROCESS and OS processes
  7. RMAN Pluggable Database Backup and Recovery in a Multitenant Environment
  8. Oracle database : Basics about pfile and spfile
  9. How to Roll Forward a standby database using RMAN incremental backup in 11g
  10. Oracle Interview Questions : Grid Infrastructure Single Client Access Name (SCAN)

You May Also Like

Primary Sidebar

Recent Posts

  • How to Configure Network Namespaces in Docker Containers
  • How to change the default IP address of docker bridge
  • “su: Authentication failure” – in Docker
  • How to Pause and Resume Docker Containers
  • How to find docker storage device and its size (device mapper storage driver)
  • Archives
  • Contact Us
  • Copyright

© 2019 · The Geek Diary