How To export a table with specific columns in Oracle !!
5 min readHey 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 !! 🙂
