Yesterday, someone asked in whatsapp group about how to take expdp backup of a table with specific columns or Export A Table excluding few Columns. Hence today I am going to cover how to export ( expdp) a table with specific columns only That is you will be excluding the columns of table which you don’t want.
Table of Contents
You might be thinking how to achieve it
There are 3 ways to achieve it
- CTAS,Export and Import
- Export, Import and then drop the columns you don’t want at destination
- If your database version is above 12c, then you can use VIEW_AS_TABLES
Lets Discuss each one of them one by one
CTAS, Export and Import
This will be traditional way of doing it. It’s easy and straight forward.
create a backup table as select <specific columns here> from source_table; For example – I have created a table from dba_objects as follows – dbsnmp.oracledbworld will be considered as original table for this instance
SQL> create table dbsnmp.oracledbworld as select * from dba_objects; Table created. SQL>
Now application team only looking for OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED Columns only from the table.
SQL> desc dbsnmp.oracledbworld Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(128) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(128) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(23) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NUMBER EDITION_NAME VARCHAR2(128) SHARING VARCHAR2(18) EDITIONABLE VARCHAR2(1) ORACLE_MAINTAINED VARCHAR2(1) APPLICATION VARCHAR2(1) DEFAULT_COLLATION VARCHAR2(100) DUPLICATED VARCHAR2(1) SHARDED VARCHAR2(1) CREATED_APPID NUMBER CREATED_VSNID NUMBER MODIFIED_APPID NUMBER MODIFIED_VSNID NUMBER
CTAS backup table with specific columns you were looking for, from original table.
CTAS stands for “create table as”
create table dbsnmp.oracledbworld_bkp as select OWNER,OBJECT_NAME,OBJECT_TYPE,Created from dbsnmp.oracledbworld;
Expdp Backup of backup table
export ORACLE_SID=<SOURCE_DATABASE_SID> expdp directory=oracledbworld dumpfile=expdp_oracledbworld_bkp.dmp logfile=expdp_oracledbworld_bkp.log tables=dbsnmp.oracledbworld_bkp
Impdp the table in Destination database with remap_table
export ORACLE_SID=<DESTINATION_DATABASE_SID> impdp directory=oracledbworld dumpfile=expdp_oracledbworld_bkp.dmp logfile=impdp_oracledbworld_bkp.log tables=dbsnmp.oracledbworld_bkp remap_table=dbsnmp.oracledbworld_bkp:oracledbworld
Export-Import, Mark it as Unused and then Drop unused columns
Export Backup of Original Table –
export ORACLE_SID=<SOURCE_DATABASE_SID> expdp directory=oracledbworld dumpfile=expdp_oracledbworld.dmp logfile=expdp_oracledbworld.log tables=dbsnmp.oracledbworld
Import the Table –
export ORACLE_SID=<DESTINATION_DATABASE_SID> impdp directory=oracledbworld dumpfile=expdp_oracledbworld.dmp logfile=impdp_oracledbworld.log tables=dbsnmp.oracledbworld
Mark columns as unused –
You have to mark the columns unused which you don’t want on destination database server.
ALTER TABLE ORACLEDBWORLD SET UNUSED (COL1,COL2,COL3,COL4......);
Drop unused columns –
SELECT * FROM DBA_UNUSED_COL_TABS WHERE TABLE_NAME=’ORACLEDBWORLD’;
You can use this view to understand how many columns are marked as UNUSED.
ALTER TABLE ORACLEDBWORLD DROP UNUSED COLUMNS;
12c and Above, VIEW_AS_TABLES in Expdp
Step 1 Create a view with required columns
For instance , Lets create a view with the columns we are looking for –
SQL> create view dbsnmp.oracledbworld_vw as select OWNER,OBJECT_NAME,OBJECT_TYPE,Created from dbsnmp.oracledbworld; View created. SQL>
Step 2 Expdp with VIEWS_AS_TABLES parameter
$expdp directory=oracledbworld dumpfile=expdp_table_from_view_test.dmp logfile=expdp_table_from_view_test.log VIEWS_AS_TABLES=dbsnmp.oracleworld_vw Export: Release 19.0.0.0.0 - Production on Tue Dec 14 20:13:59 2021 Version 19.11.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production FLASHBACK automatically enabled to preserve database integrity. Starting "SYS"."SYS_EXPORT_TABLE_02": /******** AS SYSDBA directory=oracleworld dumpfile=expdp_table_from_view_test.dmp logfile=expdp_table_from_view_test.log VIEWS_AS_TABLES=dbsnmp.oracleworld_vw Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE . . exported "DBSNMP"."ORACLEWORLD_VW" 4.402 MB 97649 rows Master table "SYS"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_02 is: /DBHOME/ora19300/expdp_table_from_view_test.dmp Job "SYS"."SYS_EXPORT_TABLE_02" successfully completed at Tue Dec 14 20:15:14 2021 elapsed 0 00:00:58
Step 3 Import the table on destination database –
I am using remap_table just to rename the table as original at destination database server.
impdp directory=oracledbworld dumpfile=expdp_table_from_view_test.dmp remap_table=dbsnmp.oracleworld_vw:oracleworld
Activity is completed.
VIEW_AS_TABLES comes with a restriction that you can’t use it on a table which has LONG data type as column
Just for Fun
I created a sqlfile to understand what command it execute during impdp, This way you will come to know what’s happening in the database.
$impdp directory=oracledbworld dumpfile=expdp_table_from_view_test.dmp VIEWS_AS_TABLES=dbsnmp.oracledbworld_vw sqlfile=test2.sql Import: Release 19.0.0.0.0 - Production on Tue Dec 14 20:21:58 2021 Version 19.11.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Master table "SYS"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_SQL_FILE_TABLE_01": /******** AS SYSDBA directory=oracledbworld dumpfile=expdp_table_from_view_test.dmp VIEWS_AS_TABLES=dbsnmp.oracledbworld_vw sqlfile=test2.sql Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE Job "SYS"."SYS_SQL_FILE_TABLE_01" successfully completed at Tue Dec 14 20:22:15 2021 elapsed 0 00:00:13
Without Remap_table parameter –
$cat test2.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
CREATE TABLE "DBSNMP"."ORACLEDBWORLD_VW"
( "OWNER" VARCHAR2(128 BYTE),
"OBJECT_NAME" VARCHAR2(128 BYTE),
"OBJECT_TYPE" VARCHAR2(23 BYTE),
"CREATED" DATE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "SYSAAS" ;
With Remap_table
cat test3.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
CREATE TABLE "DBSNMP"."ORACLEDBWORLD"
( "OWNER" VARCHAR2(128 BYTE),
"OBJECT_NAME" VARCHAR2(128 BYTE),
"OBJECT_TYPE" VARCHAR2(23 BYTE),
"CREATED" DATE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "SYSAAS" ;
Conclusion
If you ask me, VIEW_AS_TABLES is actually useful and save your time.