[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to keep certain columns in a table when import?

Posted on 2011-10-12
5
Medium Priority
?
485 Views
Last Modified: 2012-05-12
Have a need that only want to import  a few columns to a table and leave the other columns in the table untouched. Is it possible with impdp? For example,
Have a table T with 3 columns as follows:
T
---------
col1 col2 col3

Want to import only columns col1 and col2 and keep col3 untouched.
If impdp can not do that, is there any better way to achieve it?

0
Comment
Question by:jl66
5 Comments
 
LVL 14

Assisted Solution

by:ajexpert
ajexpert earned 400 total points
ID: 36958809
To my knowledge, impdp may not work with selective columns

However, if you export the data in flat file, you can make use of SQL LOADER

0
 

Author Comment

by:jl66
ID: 36960218
Thanks a lot for the info. Do any gurus have different opinion from ajexpert's conclusion?
0
 
LVL 7

Accepted Solution

by:
Javier Morales earned 800 total points
ID: 36960758
The way to export selective columns to a flat file is using UTL_FILE pl/sql package.
or generating a spool file with the only two columns you need, even create a html file with the results easely

SQL> set markup html on
SQL> set echo off            
<br>
SQL&gt; spool html_report.html
<br>
SQL&gt; select sysdate from dual;
<br>
<p>
<table border='1' width='90%' align='center' summary='Script output'>
<tr>
<th scope="col">
SYSDATE
</th>
</tr>
<tr>
<td>
13-OCT-11
</td>
</tr>
</table>
<p>

SQL&gt; spool off
<br>
SQL&gt; set markup html off
<br>

Open in new window


0
 
LVL 7

Assisted Solution

by:Jacobfw
Jacobfw earned 800 total points
ID: 36961751
"keep col3 untouched" could mean that you wish to leave this column NULL or do you mean that there is existing data in col3 that needs to match with the new data being added to col1 and col2.

In this case, using a temporary table to import col1 and col2 and then some PL/SQL routine that updates the col1 and col2 of your table doing the appropriate update on the rows with matching col3 (whatever that criteria is).
0
 

Author Closing Comment

by:jl66
ID: 36970447
Thanks a lot.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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…
Suggested Courses

873 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