Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

imp and exp commands

Posted on 2009-04-03
4
Medium Priority
?
932 Views
Last Modified: 2013-12-18
Hi,

I am using following exp and later imp commands to load the entire schema/user including data, structure, triggers, sequences etc between two database schemas within same oracle database machine using dump file.

exp schema_user1/password indexes=n full=y file=dump_file1.dmp

imp schema_user2/password full=y file=dump_file1.dmp ignore=y

After importing If I would like to drop triggers, sequences alone and leave rest of the objects, data intact is that is possible. If the data , other objects still out there if I drop sequences, triggers will it cause any errors. My intention is to run new set of sequences, triggers ( due to some change in approach of using oracle built in sequences instead of using external custom table 'sequence'). Please advise. Any ideas, sample code, resources, links highly appreciated. Thanks in advance.
 

 
 
 
 
0
Comment
Question by:gudii9
[X]
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
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 800 total points
ID: 24066613
you can, of course, after the IMP, run some sql to drop triggers and sequences,,,
but that is not a "exp/imp" issue as such.

I would probably create a procedure/sql script, that loops on all the triggers/sequences for a given user (owner), which then dynamically runs the DROP SEQUENCE / TRIGGER (using EXECUTE IMMEDIATE ' DROP .... ' )
0
 
LVL 7

Assisted Solution

by:Fayyaz
Fayyaz earned 400 total points
ID: 24072079
Be careful, I think there wil be a problem after dropping sequences if the sequences are referenced in some application code or may be in triggers also.
If you feel that the sequences are not referenced any where in the application code or triggers code then of course you can drop them after import.
 
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 800 total points
ID: 24077304
You can export with TRIGGERS=N, then run dynamic SQL to drop the sequences after the import.
0
 
LVL 7

Author Closing Comment

by:gudii9
ID: 31566526
thank you
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

618 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