Solved

imp and exp commands

Posted on 2009-04-03
4
929 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

737 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