Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: September 03, 2002 - 12:11 pm UTC

Last updated: September 10, 2012 - 5:59 pm UTC

Version: 8.1.7

Viewed 50K+ times! This question is

You Asked

Hello Tom,



We have a huge partitioned table(partitioned by day). The data in this
partitioned table has to be moved to another(development) instance on a regular basis. We do not need all the days of data but at the most 3 or 4 days of data at any point in time.

The problem here is we cannot use transportable tablespaces(which we would like to) as the transportable set is not complete.

Export / Import is time consuming with such huge partitions.
(As with each partition is exchanged with a table and that table is exported.)

What is the best alternative to have just a subset of partitioned table but not whole?

thanks and regards,





and Tom said...

Well, if you do the swap -- you can transport. If you swap a partition P1 of table T1 with a table XXXX and P1 was in its own tablespace before the swap, you can transport that tablespace.

Otherwise, you can just export a partition:

$ exp help=y
....
Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
....


use tables=(T1:P1)


You can also use a WHERE CLAUSE on export as well "query=....." to get a slice different from a partition.

Rating

  (5 ratings)

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

Comments

import Partitioned table as unpartitioned table

rKaur, October 11, 2004 - 3:43 pm UTC

I have exported a partitioned table but I need to import it to another database where that type of tablespace does not exist and I am getting message tablespace not found. How can I import that partitioned table

Tom Kyte
October 11, 2004 - 5:01 pm UTC 

<b>just pre-create the table and import with ignore=y</b>

if you don't have the ddl for the table, imp userid=... tables=table indexfile=table.sql will get it for you and you can edit it.

ops$tkyte@ORA9IR2> CREATE TABLE t
  2  (
  3    temp_date  date,
  4    x          int,
  5    y int
  6  )
  7  PARTITION BY RANGE (temp_date)
  8  subpartition by hash(x)
  9  (
 10    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) tablespace users,
 11    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) tablespace tools,
 12    PARTITION junk VALUES LESS THAN (MAXVALUE)
 13  )
 14  ;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> inset into t select sysdate, 1, 1 from dual;
SP2-0734: unknown command beginning "inset into..." - rest of line ignored.
ops$tkyte@ORA9IR2> insert into t select sysdate, 1, 1 from dual;
 
1 row created.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2> !exp userid=/ tables=t
 
Export: Release 9.2.0.4.0 - Production on Mon Oct 11 16:39:11 2004
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
 
About to export specified tables via Conventional Path ...
. . exporting table                              T
. . exporting composite partition                PART1
. . exporting subpartition                   SYS_SUBP1          0 rows exported
. . exporting composite partition                PART2
. . exporting subpartition                   SYS_SUBP2          0 rows exported
. . exporting composite partition                 JUNK
. . exporting subpartition                   SYS_SUBP3          1 rows exported
Export terminated successfully without warnings.
 
ops$tkyte@ORA9IR2> drop table t;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table t ( temp_date date, x int, y int );
 
Table created.
 
ops$tkyte@ORA9IR2> !imp userid=/ full=y ignore=y
 
Import: Release 9.2.0.4.0 - Production on Mon Oct 11 16:39:46 2004
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
 
Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing OPS$TKYTE's objects into OPS$TKYTE
. . importing subpartition             "T":"SYS_SUBP1"          0 rows imported
. . importing subpartition             "T":"SYS_SUBP2"          0 rows imported
. . importing subpartition             "T":"SYS_SUBP3"          1 rows imported
Import terminated successfully without warnings.
 
ops$tkyte@ORA9IR2>

Steve, February 17, 2005 - 10:42 pm UTC

Hi Tom,

Can I export a table A (with 12 range partitons)
and then import into to table B with a different partition range?
For example, table A has range of 100,200 and 300 and table B has 150, 250 and 300.

Thanks!


Steve



Tom Kyte
February 18, 2005 - 7:28 am UTC 

precreate the new table and import with ignore=y


ops$tkyte@ORA9IR2> CREATE TABLE t
  2  (
  3    temp_date  date,
  4    x          int,
  5    y int
  6  )
  7  PARTITION BY RANGE (temp_date)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) tablespace users,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) tablespace tools,
 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )
 13  /
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t
  2  select to_date('10-mar-2003','dd-mon-yyyy')+mod(rownum,10), rownum, rownum
  3    from all_users;
 
25 rows created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> !exp userid=/ tables=t
 
Export: Release 9.2.0.5.0 - Production on Fri Feb 18 07:22:35 2005
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
 
About to export specified tables via Conventional Path ...
. . exporting table                              T
. . exporting partition                          PART1          8 rows exported
. . exporting partition                          PART2          3 rows exported
. . exporting partition                           JUNK         14 rows exported
Export terminated successfully without warnings.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table t;
 
Table dropped.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE TABLE t
  2  (
  3    temp_date  date,
  4    x          int,
  5    y int
  6  )
  7  PARTITION BY RANGE (temp_date)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('10-mar-2003','dd-mon-yyyy')) tablespace users,
 10    PARTITION part2 VALUES LESS THAN (to_date('11-mar-2003','dd-mon-yyyy')) tablespace tools,
 11    PARTITION part3 VALUES LESS THAN (to_date('12-mar-2003','dd-mon-yyyy')) tablespace tools,
 12    PARTITION junk VALUES LESS THAN (MAXVALUE)
 13  )
 14  /
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> !imp userid=/ full=y ignore=y
 
Import: Release 9.2.0.5.0 - Production on Fri Feb 18 07:22:35 2005
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
 
Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing OPS$TKYTE's objects into OPS$TKYTE
. . importing partition                    "T":"PART1"          8 rows imported
. . importing partition                    "T":"PART2"          3 rows imported
. . importing partition                     "T":"JUNK"         14 rows imported
Import terminated successfully without warnings.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select count(*) from t;
 
  COUNT(*)
----------
        25

exporting a table partition

DSR, April 21, 2008 - 10:36 pm UTC

Hi Tom,

i have written a small shell script to export a partition. i capture the partition name into variable and then use the varible in the export command.

part_name=`sqlplus -s part_test/part <<eof
set heading off;
select 'P_'||to_char(trunc(sysdate-21,'day'),'DDMMYY')||' ' from dual;
exit;
eof`
echo $part_name
exp part_test/part log=$part_name.log tables=T4:${part_name} rows=n file=test.dmp statistics=none
exit;


this is the error i get when i run the script

P_300308

Export: Release 10.2.0.1.0 - Production on Mon Apr 21 22:44:55 2008

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported

About to export specified tables via Conventional Path ...
EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00011: PART_TEST.P_300308 does not exist
Export terminated successfully with warnings.


i am using a Korn shell. how can i export the partition in my case.
Tom Kyte
April 23, 2008 - 5:37 pm UTC

EXP-00011: PART_TEST.P_300308 does not exist

that doesn't seem to match your example?

but, use -vx and see what the ksh is doing.... eg:

[tkyte@localhost ~]$ cat test.ksh
#!/bin/ksh -vx
part_name=P_300308
echo $part_name
exp / log=$part_name.log tables=T:${part_name} rows=n file=test.dmp statistics=none

[tkyte@localhost ~]$ ./test.ksh
#!/bin/ksh -vx
part_name=P_300308
+ part_name=P_300308
echo $part_name
+ echo P_300308
P_300308
exp / log=$part_name.log tables=T:${part_name} rows=n file=test.dmp statistics=none
+ exp / log=P_300308.log tables=T:P_300308 rows=n file=test.dmp statistics=none

Export: Release 10.2.0.1.0 - Production on Wed Apr 23 17:45:48 2008

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported

About to export specified tables via Conventional Path ...
. . exporting table                              T
Export terminated successfully without warnings.

expdp-impdp partition table

Mil, August 28, 2012 - 12:57 am UTC

Hi tom, I have a partition table of size 190gb on 10g DB. I need to import it with query clause in the same DB to delete some data. complete expdp with parallel clause fails as it goes to another node whereas expdp w/o parallel keeps on running.
Can i have partition wise export & then re-import the data or is thr some other optimal way.
I tried deleting data from table using delete but same is taking huge time with worst output hence the above approach.
Thanks,
Mil.
Tom Kyte
August 29, 2012 - 1:28 pm UTC

what do you mean by "as it goes to another node"?


but - do NOT use expdp.

Just

o create table as select the data to keep
o drop old
o rename new
o add indexes, constraints, grants


that can be done nologging, parallel.

MIL, August 30, 2012 - 2:19 am UTC

Its a 10G 3 node RAC Database, and creating a table with insert-select clause is taking time due to high fragmentation, whereas nearly 24crs out of 90Crs of data is to be deleted from the partitioned table.
hence we opted for expdp with query clause and also used parallel=5 which results it to jump on other node for parallelism. We have cluster=n parameter in 11g which can help us avoid this scenario but now required some optimal solution with less downtime window.
Tom Kyte
September 10, 2012 - 5:59 pm UTC

what is a crs?

and what is 'high fragmentation' and how did you determine it to be the root cause?

and did you even test what I suggested?

expdp is just GOING TO READ THE DATA - no different than anything else.

did you even test what I've suggested?
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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.