Solved

imp and exp commands

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Clone Oracle 12c Database 5 52
Query to identify changes between rows of two tables 8 47
SQL Query 34 99
sort a spool into file output in oracle 1 30
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

773 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