A reader, August 14, 2001 - 2:10 pm UTC
triggers restored ?
Dave, August 15, 2001 - 5:26 am UTC
Tom, wouldn't the triggers come back with the table data? The question says "without restoring triggers".
Response to previous post
Connor, August 15, 2001 - 7:19 am UTC
True - you could add triggers=n
Import without Trigger
Randy, August 15, 2001 - 10:27 am UTC
You need to export the tables using exp triggers=n...
and then import. Otherwise it will always have trigger inside the tmp file.
triggers on import
Dave, August 15, 2001 - 10:46 am UTC
Yes, I don't think there is a 'triggers' option on imp.
Back to square
Gururaj Kulkarni, August 15, 2001 - 11:42 am UTC
So how do we restore data without triggers ?
Simulating triggers=n on IMP
Connor, August 15, 2001 - 12:36 pm UTC
I haven't tried this but you could revoke any privs to create triggers and then import. You would presumably get a lot of errors but the triggers would not be done
RESTRICTIONS
Sreekanth Unnikrishnan, January 26, 2002 - 2:41 am UTC
There is no mention of the restrictions in parfiles. Say like if the list of tables are exceeding a specific number export command will fail. I agree this is an O/S restriction, but there has to be a workaround rather than firing the export different times. Do let us know if there is a solution to this.
January 26, 2002 - 6:53 pm UTC
Well, I found the schema in my database with the largest number of tables and created a parfile with 236 tables to export in it -- each listed one after the other.
I did not hit an error. You'll have to be a little more clear as to how to reproduce your issue exactly.
Reader
Reader, January 26, 2002 - 11:55 pm UTC
EVEN if the restriction of number of tables exceeds, you
do not have to fire export at different times. You can
always do PARALLEL export of tables at the same time and
do PARALLEL import of tables also. This method also gives
you better exp/imp time.
Restrictions
Sreekanth Unnikrishnan, February 02, 2002 - 12:20 pm UTC
I was not elaborate on my earlier comment since I did not want to make this a full fledged discussion in itself (Ref ur instructions about this forum). We are doing a data warehousing application for Citibank in Singapore. Here we have around 2000 odd tables in a single schema. We have around 19 countries and each country has 2 major schemas. Here the problem is we have to create parfiles with 50 odd tables and run it in parallel. The O/S is UNIX. If the count of tables exceeded 60 it says parfile too large. Oracle manual says that the size of the parfile depends on the O/S. I can pass on the oracle error number to you on Monday. Additionaly what is the maximum number of bytes a parfile in UNIX can accomodate.
February 02, 2002 - 5:52 pm UTC
As I said -- I did 236 files without issue, on solaris.
What is the SIZE of your parfile and what is the cut and paste of the entire error message from EXP?
(the message on the followups is to not ask new questions, talking about something related to the original question is OK)
exp/imp without firing triggers
VA, August 18, 2005 - 11:41 am UTC
See
</code>
http://asktom.oracle.com/pls/ask/f?p=4950:61:::::P61_ID:2200769503934 <code>
my last review about the Metalink Note 66320.1
I have a schema that has a lot of CLOB columns and row-level triggers on insert/delete. It also has FK constraints between tables so I cant do the TRUNCATE that the note mentions. I have to do a DELETE which would fire the triggers and mess up the data.
How do I go about doing a
(1) export
(2) empty the tables (truncate or delete)
(3) import
with just the data and without firing any triggers?
Thanks a lot
August 18, 2005 - 4:35 pm UTC
drop constraints,
disable triggers
exp the data
truncate
do your characterset change
import -- but understand the data will "change" due to the character set change.
enable triggers
create constraints.
exp/imp without firing triggers
A reader, August 18, 2005 - 5:45 pm UTC
I was hoping there was a easier way to do it, oh well.
Why do you suggest dropping the constraints? To make the TRUNCATE not complain about dependent records? If the triggers are disabled, DELETE would do the trick, right? I just need to write a little truncate_or_delete stored proc.
Thanks
August 18, 2005 - 7:05 pm UTC
to make the truncate work.
delete would be *long* and messy.
import only data
matt, October 25, 2005 - 12:48 pm UTC
I have a full export. How do I ensure that I dont import anything else other than data? The fromuser/touser option import triggers and packages - I can exclude other objects easily.
regards.
October 26, 2005 - 8:48 am UTC
tables=(list of tables)
grants=no
indexes=no
triggers=no
constraints=no
for example....
Trigger and import
Jouini, October 11, 2006 - 4:51 am UTC
Hi Tom,
I make an export with following option:
tables=(list of tables)
rows=y
grants=yes
indexes=yes
consistent=yes
compress=yes
constraints=yes
triggers=no
After making the export, I found that one table with a trigger update one columns to 0.
How can I keep all values of that columns before the import ?
Regards,
Jouini
October 11, 2006 - 8:12 am UTC
(i hate triggers)
well, the trigger must have existed - eg: import did not create it, the table was already there, the trigger was already there.
You would therefore have to log in, find them, disable them, import, enable them.