• 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 – How to recover from a lost datafile with no backup
  2. Oracle – How To Check if Autoextensible Datafiles Set To Maxsize Unlimited
  3. How To Size UNDO Tablespace For Automatic Undo Management
  4. How to recreate an ASM disk group
  5. How To Catalog Backups / Archivelogs / Datafile Copies / Controlfile Copies in Oracle Database
  6. Oracle RMAN – Restore and Recovery of a Noarchivelog Database
  7. How to Modify an Existing ASM Spfile in a RAC Environment
  8. How to list all the named events set for a database
  9. Oracle RMAN Pluggable Database Point in Time Recovery
  10. How to Roll Forward a standby database using RMAN incremental backup in 11g

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary