I did an export at the user level from a user X. Then I imported all the objects of the user X to a user Y. All was fine, but some triggers are not working. Those triggers kept the schema of the user X in the triggering statement.
2. Is there a way to resolve this problem without dropping and recreating the triggers?
1) Triggers are owned by the person who created them. When you export into another user's schema, the trigger will still be owned by the original creator. In other words, the trigger will be imported but it will not "switch ownership". The trigger would still fire when appropriate, but will be ran under the definer rights model. This means that whoever executes the object (in this case, the trigger), will execute it with the rights/privileges of the definer (i.e. the owner/creator) so the objects used in the trigger (tables and such) will be pointing to the original owner's tables. If, however, the trigger was written to execute using the invoker rights model, then when the trigger executed, it would run with the rights/privileges of the invoker and therefore would point to the "touser" user's objects.
2) If you want the new user to own the trigger, then they need to be created by the new user. If that's what you want, it'd likely be best to export with the triggers=n parameter and then create a script to run after the import which would create all the triggers under the new user's schema.
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database export, import and migration
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.