Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, oracle.

Asked: September 21, 2018 - 11:30 am UTC

Last updated: September 25, 2018 - 1:13 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi,

I have large partitioned table now i want to export and give dump to someone but not all columns some specific columns needs to trim..

My approach i am importing table on dev setup then make specific columns unused and then try to drop then export and giving to them but column drop is taking more then 10 hrs. So, on which way i can hide the columns into export dump.

Thanks

and Chris said...

12c has a new parameter for data pump export: views_as_tables. Using this you can export a view which exposes only the columns you want:

create or replace view vw as 
  select <column subset> from t;

expdp usr/pass views_as_tables=vw


On older releases, instead of:

import
set cols unused
drop unused

You could:

import
create table tmp as select <subset of cols>
Switch tmp with original

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

oracle oracle, September 25, 2018 - 10:41 am UTC

Thanks for the reply.

As you said for older release we need to import and mark unused columns and then drop that columns But we have a table size is approx. 100+GB so i have tried to mark unused and then drop but the drop command executed 24hr but not get any result it was running till now.
Chris Saxon
September 25, 2018 - 1:13 pm UTC

Dropping the unused columns can take a while. But 1+ days seems excessive. To find out why, trace your session to see what it's doing.

on drop the unused columns

Rajeshwaran, Jeyabal, September 25, 2018 - 4:19 pm UTC

.....
As you said for older release we need to import and mark unused columns and then drop that columns
.....

But why do you need to mark the column unused and have them dropped again?

Lets say i need to export an EMP table from DEMO schema only the column empno,ename and hiredate.

create a temp table in DEMO schema with a required set of columns, like this.

demo@ORA11G> create table emp_tmp
  2  as
  3  select empno,ename,hiredate
  4  from emp;

Table created.


then have that export using datapump from the source schema(DEMO) like this.

D:\app\Vnameit\product\11.2.0\dbhome_1\BIN>expdp demo/demo@ora11g tables=emp_tmp directory=TMP dumpfile=exp_emp_tmp.dmp logfile=exp_emp_tmp.log

Export: Release 11.2.0.4.0 - Production on Tue Sep 25 21:32:21 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "DEMO"."SYS_EXPORT_TABLE_01":  demo/********@ora11g tables=emp_tmp directory=TMP dumpfile=exp_emp_tmp.dmp logfile=exp_emp_tmp.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "DEMO"."EMP_TMP"                            6.140 KB      14 rows
Master table "DEMO"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEMO.SYS_EXPORT_TABLE_01 is:
  D:\EXP_EMP_TMP.DMP
Job "DEMO"."SYS_EXPORT_TABLE_01" successfully completed at Tue Sep 25 21:32:42 2018 elapsed 0 00:00:15


then finally import them into the target schema(DEMO1) - with remap_table option to convert the temp table into the actual table name like this.

D:\app\Vnameit\product\11.2.0\dbhome_1\BIN>impdp demo1/demo1@ora11g directory=TMP dumpfile=exp_emp_tmp.dmp logfile=imp_emp_tmp.log remap_table=emp_tmp:emp table_exists_action=replace tables=demo.emp_tmp remap_schema=demo:demo1

Import: Release 11.2.0.4.0 - Production on Tue Sep 25 21:41:31 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "DEMO1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "DEMO1"."SYS_IMPORT_TABLE_01":  demo1/********@ora11g directory=TMP dumpfile=exp_emp_tmp.dmp logfile=imp_emp_tmp.log remap_table=emp_tmp:emp table_exists_action=replace tables=demo.emp_tmp remap_schema=demo:demo1
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DEMO1"."EMP"                               6.140 KB      14 rows
Job "DEMO1"."SYS_IMPORT_TABLE_01" successfully completed at Tue Sep 25 21:41:34 2018 elapsed 0 00:00:02


D:\app\Vnameit\product\11.2.0\dbhome_1\BIN>sqlplus demo1/demo1@ora11g

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 25 21:41:56 2018

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

demo1@ORA11G> select * from emp;

     EMPNO ENAME      HIREDATE
---------- ---------- -----------
      7369 XX         17-DEC-1980
      7499 ALLEN      20-FEB-1981
      7521 WARD       22-FEB-1981
      7566 JONES      02-APR-1981
      7654 MARTIN     28-SEP-1981
      7698 BLAKE      01-MAY-1981
      7782 CLARK      09-JUN-1981
      7788 SCOTT      19-APR-1987
      7839 KING       17-NOV-1981
      7844 TURNER     08-SEP-1981
      7876 ADAMS      23-MAY-1987
      7900 JAMES      03-DEC-1981
      7902 FORD       03-DEC-1981
      7934 MILLER     23-JAN-1982

14 rows selected.

demo1@ORA11G> select * from emp_tmp;
select * from emp_tmp
              *
ERROR at line 1:
ORA-00942: table or view does not exist


demo1@ORA11G>


Hope this helps.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.