Solved

imp and exp commands

Posted on 2009-04-03
4
930 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 200 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 100 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 200 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

688 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