Solved

imp and exp commands

Posted on 2009-04-03
4
921 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
4 Comments
 
LVL 142

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 31

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

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.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
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…
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 Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now