// // How To export a table with specific columns in Oracle !!
December 21, 2024

A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

How To export a table with specific columns in Oracle !!

5 min read
asrblogger

Hey Folks !!

Today I have received a client requirement regarding exporting few columns from a table in Oracle 19c.

Here are three methods you can use to achieve this:

1. Use Create Table As Select (CTAS) to directly create a table with the specific columns you need.

2. Utilize the Export and Import functionality, and then drop the unnecessary columns from the target table.

3. If you're using Oracle Database 12c or higher, take advantage of the VIEW_AS_TABLES feature.

1. Use Create Table As Select (CTAS) to directly create a table with the specific columns you need.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 9 22:54:52 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> SQL> SQL> 
SQL> 
SQL> def
DEFINE _DATE	       = "09-AUG-24" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "test1" (CHAR)
DEFINE _USER	       = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1903000000" (CHAR)
DEFINE _EDITOR	       = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1903000000" (CHAR)
SQL>

Create a User , Grant Privileges to a User .

SQL> CREATE USER RAM identified by oracle account unlock;

User created.

SQL> GRANT CONNECT,RESOURCE to RAM;

Grant succeeded.

SQL> GRANT DBA to RAM;

Grant succeeded.

Create a Table using CTAS method with all Columns of another DB objects.

SQL> CREATE TABLE ram.tb1_haldiram as SELECT * FROM dba_objects;

Table created.

SQL> DESC ram.tb1_haldiram
 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

SQL>

Create another Table using CTAS Method, but this time we are creating table using specific columns.

SQL> CREATE TABLE ram.tb2_haldiram_backup as SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM ram.tb1_haldiram;

Table created.

SQL> SELECT COUNT(*) FROM ram.tb2_haldiram_backup;

  COUNT(*)
----------
     72476

Now plan for exporting table with specific columns from source and exporting it in target using “REMAP_TABLE” Parameter.

[oracle@localhost ~]$ mkdir -p /u01/export_import
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 9 23:13:10 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> CREATE DIRECTORY EXP_IMP as '/u01/export_import';

Directory created.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


[oracle@localhost ~]$ expdp directory=EXP_IMP dumpfile=expdp_tb1_haldiram_backup.dmp logfile=expdp_tb1_haldiram_backup.log tables=ram.tb1_haldiram_backup

Export: Release 19.0.0.0.0 - Production on Fri Aug 9 23:13:46 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba
Password: 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA directory=EXP_IMP dumpfile=expdp_tb1_haldiram_backup.dmp logfile=expdp_tb1_haldiram_backup.log tables=ram.tb1_haldiram_backup 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "RAM"."TB1_HALDIRAM_BACKUP"                 3.627 MB   72476 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/export_import/expdp_tb1_haldiram_backup.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Fri Aug 9 23:14:34 2024 elapsed 0 00:00:32

[oracle@localhost_dest ~]$ impdp directory=EXP_IMP dumpfile=expdp_tb1_haldiram_backup.dmp logfile=impdp_tb1_haldiram_backup.log tables=ram.tb1_haldiram_backup remap_table=ram.tb1_haldiram_backup:tb2_haldiram_backup

impdp directory=EXP_IMP dumpfile=expdp_tb1_haldiram_backup.dmp logfile=impdp_tb1_haldiram_backup.log tables=ram.tb1_haldiram_backup remap_table=ram.tb1_haldiram_backup:tb2_haldiram_backup


Import: Release 19.0.0.0.0 - Production on Fri Aug 9 23:18:54 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba
Password: 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  /******** AS SYSDBA directory=EXP_IMP dumpfile=expdp_tb1_haldiram_backup.dmp logfile=impdp_tb1_haldiram_backup.log tables=ram.tb1_haldiram_backup remap_table=ram.tb1_haldiram_backup:tb2_haldiram_backup 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "RAM"."TB2_HALDIRAM_BACKUP"                 3.627 MB   72476 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Fri Aug 9 23:19:43 2024 elapsed 0 00:00:41

[oracle@localhost_dest ~]$

2. Utilize the Export and Import functionality, and then drop the unnecessary columns from the target table.

SQL> ALTER TABLE ram.tb1_haldiram SET UNUSED (OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP);

Table altered.

SQL> CONN RAM/oracle
Connected.

SQL> col OWNER for a20
SQL> set lin 200 pages 200
SQL> SELECT * FROM DBA_UNUSED_COL_TABS WHERE TABLE_NAME='TB1_HALDIRAM';

OWNER		     TABLE_NAME        COUNT
-------------------- ------------ ----------
RAM		     TB1_HALDIRAM	   4

SQL> 

SQL> ALTER TABLE TB1_HALDIRAM DROP UNUSED COLUMN;

Table altered.

SQL> SELECT * FROM DBA_UNUSED_COL_TABS WHERE TABLE_NAME='TB1_HALDIRAM';

no rows selected

SQL>

3. If you’re using Oracle Database 12c or higher, take advantage of the VIEW_AS_TABLES feature.

SQL> CREATE TABLE ram.table1 as SELECT * FROM dba_objects;

Table created.

SQL> CREATE VIEW ram.view_haldiram as select OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP from ram.table1 ;

View created.


SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@localhost ~]$ expdp directory=EXP_IMP dumpfile=expdp_view_haldiram.dmp logfile=expdp_view_haldiram.log VIEWS_AS_TABLES=ram.view_haldiram

Export: Release 19.0.0.0.0 - Production on Sat Aug 10 01:12:50 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba
Password: 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA directory=EXP_IMP dumpfile=expdp_view_haldiram.dmp logfile=expdp_view_haldiram.log VIEWS_AS_TABLES=ram.view_haldiram 
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "RAM"."VIEW_HALDIRAM"                       3.417 MB   72584 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/export_import/expdp_view_haldiram.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Aug 10 01:13:17 2024 elapsed 0 00:00:18

[oracle@localhost ~]$

Hope it helped !! 🙂

More Articles

4 min read

Create Pluggable Database (PDB) Manually Using SQL Prompt Oracle 19c !!

asrblogger
text on computer monitor 1 min read

How to check FRA ( Fast Recovery area) Utilization in Oracle ?

asrblogger
text on computer monitor 6 min read

How to check Tablespace Utilization in Oracle ?

asrblogger

You may have missed

4 min read

Create Pluggable Database (PDB) Manually Using SQL Prompt Oracle 19c !!

asrblogger
text on computer monitor 1 min read

How to check FRA ( Fast Recovery area) Utilization in Oracle ?

asrblogger
text on computer monitor 6 min read

How to check Tablespace Utilization in Oracle ?

asrblogger
15 min read

Install and Configure OpenSearch & OpenSearch Dashboard

Avnish Singh