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

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • 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 RAC Interview Questions – Coherence and Split-Brain
  2. How an SQL query is executed in Oracle Database
  3. How long does Oracle retain the statistics for
  4. How to move spfile from ASM to filesystem
  5. Steps to relink Oracle Forms 12c in Linux/UNIX
  6. How to Use real-time query to access data on a physical standby database
  7. How to Move OCR, Vote Disk File, ASM SPILE to new Diskgroup
  8. Difference between using srvctl vs using sqlplus for start/stop one or more Oracle Database Instances
  9. How to Modify spfile in Oracle Database
  10. Beginners Guide to Oracle Temporary Tablespace Groups

You May Also Like

Primary Sidebar

Recent Posts

  • powertop Command Examples in Linux
  • powertop: command not found
  • powerstat: command not found
  • powerstat Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright