Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Oracle Export Utility, with where conditions to export only a part of the table data

Posted on 2008-10-23
9
1,444 Views
Last Modified: 2013-12-18
The Oracle User called QUIZ contains the table called CITY has over 2 million records.
This table has 20 columns, no importance for their names.
Please suppose that 3 columns of 20 are the following:

a) DAYS of type DATE;
b) SUN of type NUMBER;
c) RAIN of type VARCHAR2(50).

We would like to create a PARTIAL COPY of this table in another Oracle user called HISTORICAL, fetching only the records with:

a) DAYS >= to_date('01/01/2007','DD/MM/YYYY');
b) SUN >= 3;
c) RAIN beginning with the string 'SHARK'

I think it's too heavy to open a cursor on the source table, inserting record for record if satisfying the three conditions, so I would like to use the export utility with a file containing the conditions.

Could you please help me?
0
Comment
Question by:hc2342uhxx3vw36x96hq
9 Comments
 
LVL 2

Assisted Solution

by:stanimirp
stanimirp earned 20 total points
ID: 22784305
0
 
LVL 1

Author Comment

by:hc2342uhxx3vw36x96hq
ID: 22784413
Sorry, I need the answer to MY question, not a link to general definition of export utility.
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 250 total points
ID: 22784650
I think you can simple do a INSERT INTO ... SELECT
HISTORICAL should have read access to CITY table of QUIZ.

insert /*+ APPEND */ into HISTORICAL.CITY
select * from QUIZ.CITY
where DAYS >= to_date('01/01/2007','DD/MM/YYYY') and SUN >= 3 and RAIN like 'SHARK%';

If you MUST go for sql loader here is the command.
exp username/pwd tables=CITY file=dump_file_name.dmp QUERY='where DAYS >= to_date(''01/01/2007'',''DD/MM/YYYY'') and SUN >= 3 and RAIN like ''SHARK%'''
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:hc2342uhxx3vw36x96hq
ID: 22784848
sujith80, with your exp command, can I import the table CITY into a different tablespace owned by the HISTORICAL Oracle user?

0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 250 total points
ID: 22784959
>>  into a different tablespace
Yes, create the table in a differnt tablespace before the import and run the import with ignore=Y
0
 
LVL 1

Author Comment

by:hc2342uhxx3vw36x96hq
ID: 22785175
C:\>exp user/password@SID

Export: Release 10.2.0.3.0 - Production on Thu Oct 23 14:05:46 2008

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


EXP-00056: ORACLE error 6550 encountered
ORA-06550: line 1, column 41:
PLS-00302: component 'SET_NO_OUTLINES' must be declared
ORA-06550: line 1, column 15:
PL/SQL: Statement ignored
EXP-00000: Export terminated unsuccessfully

0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 250 total points
ID: 22787805
Contact your dba
0
 
LVL 2

Accepted Solution

by:
barfi earned 230 total points
ID: 22787827
If you are trying to IMPORT object owned by a user into different tablespace- try  using DataPump in10g. This works great without creating any thing. There is a parameter called REMAP_TABLESPACE- USING THIS PARAMETER you can create the objects that belong to a tablespace in the source to another in the target. Syntax is:
Remap_Tablespace=source_tablespace:target_tablespace
 So for example if you need to import objects owned by user1 and are stored in user1_tblspace with data pump you ca import these objects to the different tablespace user1_tblspace. Here is the command:
Syntax:
impdp directory+mydumpdir dumpfile=abc.dmp
remap_schema=user1:user2
above command will create all the objects owned by user1 in user2 schema
but in your case do this:
remap_tablespace=user1tblspace:user2_tablespace
This works if you try to do it thru GUI (OEM) but you have to set directory and authenticate it (read-write privliges). It is one time work but I bet you will not go back to old exp/emp utilities once you use the DATAPUMP in 10g.
Hope it helps...
here is the simple link for all the parameters also:
http://www.oracle-base.com/articles/10g/OracleDataPump10g.php

0
 
LVL 1

Author Closing Comment

by:hc2342uhxx3vw36x96hq
ID: 31509120
Thank you!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
1 FROM DUAL wont work with additional columns ?? 4 45
MULTIPLE DATE QUERY 15 91
Help on model clause 5 37
Sybase and replication server 13 39
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question