Home » RDBMS Server » Server Utilities » missing trigger/procedures after import (Oracle9i 9.2.0.1.0;TNS for 32-bit Windows: Version 9.2.0.1.0;NLSRTL Version 9.2.0.1.0)
missing trigger/procedures after import [message #392953] Thu, 19 March 2009 15:45 Go to next message
Jim_Fisher
Messages: 15
Registered: January 2009
Location: CANADA
Junior Member
It appears I have missed something in my notes from effort with regard to exp/imp process. After the imp was completed, I did counts on various tables and everything matched. However, after some testing in new development area, I noticed triggers, views and procedures were missing. It has to be something obvious, but right now, I can see the trees in the forest. Please suggest an area for my exploration.

Thank you in advance.



BAT file attached to scheduler on server running at 23:00

exp ORCL_trg/PASS@ORACLE.world file=d:\backup\ORCL.dmp log=d:\backup\ORCL.log
ren ORCL.dmp ORCL_%date:~-4,4%%date:~-10,2%%date:~-7,2%.dmp
ren ORCL.log ORCL_%date:~-4,4%%date:~-10,2%%date:~-7,2%.log



Regret I only have printout from notes; I did not save the text from import.
Hardcopy notes from earlier load.

>> imp ORCL_demo/password
import file : EXPDAT.DMP >> d:\backup\ORCL.dmp
buffer size (min is 8192) 30720 >> [enter,, taking default]

export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by ORCL_trg, not you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
List contents of import file only (yes/no): no > [enter default]

Ignore create error due to object existence (yes/no): no > [enter default]

Import grants (yes/no): yes > [enter default]

Import table date (yes/no): yes > [enter default]

Import entire export file (yes/no): no > [enter default]
Username: ORCL_trg [enter]

Enter table(T) or partition(T:P) names Null list means all tables for user
Enter table(T) or partition(T:P) name or . if done: [enter, default]

At this point the . importing ORCL_trg objects into ORCL_demo continues until successful import.



excerpt of  log=d:\backup\ORCL.log below

Connected to: Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ISC_TRG 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ISC_TRG 
About to export ISC_TRG's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ISC_TRG's tables via Conventional Path ...
. . exporting table                       BANKRECO       5067 rows exported
. . exporting table  B_OM_BAR_CODE_PRICE_LIST_HIST          0 rows exported
. . exporting table                 DEVICE_HISTORY       3781 rows exported
. . exporting table     DEVICE_HISTORY_NOV_14_2008          0 rows exported
. . exporting table        DEVICE_HISTORY_ORIGINAL       5581 rows exported
. . exporting table          FD_COA_FIN_DATA_LAYER          0 rows exported

... BASICALLY 250+ TABLES ARE EXPORTED WITH ROW COUNTS IDENTIFIED
... MANY OF THE NAMES ARE PROPITARY IN NATURE AND I DO NOT WANT TO
... IDENTOFY THE VENDOR

. . exporting table                       TAG_ITEM          0 rows exported
. . exporting table                   TAG_ITEM_TAG        463 rows exported
. . exporting table                          TBAIJ          1 rows exported
. . exporting table                          TEMP1          0 rows exported
. . exporting table                         TEMP11          0 rows exported
. . exporting table                  TEMP_ITEM_TAG         30 rows exported
. . exporting table                     TEMP_TABLE          0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.



[Updated on: Thu, 19 March 2009 15:47]

Report message to a moderator

Re: missing trigger/procedures after import [message #392959 is a reply to message #392953] Thu, 19 March 2009 16:49 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
User used to export
>>exp ORCL_trg
The actual schema exported (according to log).
>>About to export ISC_TRG's objects .

YOu are importing using this
>> imp ORCL_demo
Specified schema is
>>ORCL_trg

Seems you are looking into the wrong file.
Your export logs tells a different story.
Re: missing trigger/procedures after import [message #393196 is a reply to message #392953] Fri, 20 March 2009 13:03 Go to previous messageGo to next message
Jim_Fisher
Messages: 15
Registered: January 2009
Location: CANADA
Junior Member


I believe my mistake is in this line, which should have been YES

Import entire export file (yes/no): no > [enter default]



my system is isc_trg export; but need to imp to backup system isc_trg_demo. I tried to hid the vendors name in the text, and missed one entry.


If I'm still off base, please anyone, suggest a search path.

Re: missing trigger/procedures after import [message #393210 is a reply to message #393196] Fri, 20 March 2009 14:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Can you startover?
Do an export.
exp dbauser/password@source owner=sourceSchema file=sourceSchema.dmp consistent=y statistics=none compress=n

Do an import
imp dbauser/password@target fromuser=sourceSchema touser=targetSchema file=sourceSchema.dmp


To make it simple, precreate the tablespaces in target with same name in source (for index and data, if they are kept separately).
Grant unlimited quota on these to targetSchema.
This is not a must, but will give you a clean, simple start.
Re: missing trigger/procedures after import [message #393212 is a reply to message #393210] Fri, 20 March 2009 14:41 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Also,
You may want to "cleanup" the targetSchema before you import.
Drop all the objects.
Re: missing trigger/procedures after import [message #393213 is a reply to message #392953] Fri, 20 March 2009 14:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I noticed triggers, views and procedures were missing.
Just to double check reality, can you provide proof these objects are actually owned by the schema being exported?
Re: missing trigger/procedures after import [message #393248 is a reply to message #393213] Fri, 20 March 2009 16:41 Go to previous message
Jim_Fisher
Messages: 15
Registered: January 2009
Location: CANADA
Junior Member
Blackswan,
Yes, the owner is as noted ... ISC_TRG. The backup area for the IMP is ISC_TRG_DEMO. I dropped the user, created as DBA, started the IMP.

I will attempt reload Saturday with spool to capture all related data.

Again thank to all who have suggested ideas. Just here to learn, but need to learn too.


SQL> select
  2  object_type,owner,count(*)
  3  from    dba_objects
  4  where    status = 'VALID'
  5  and  owner = 'ISC_TRG'
  6  group by object_type,owner;

OBJECT_TYPE        OWNER        COUNT(*)   
------------------ ------------ ---------       
LOB                ISC_TRG              1    
VIEW               ISC_TRG            218  
INDEX              ISC_TRG           2081  
TABLE              ISC_TRG           2314 
PACKAGE            ISC_TRG             74 
SYNONYM            ISC_TRG            639 
TRIGGER            ISC_TRG            612   
FUNCTION           ISC_TRG            151 
SEQUENCE           ISC_TRG             61 
PROCEDURE          ISC_TRG           1641          
PACKAGE BODY       ISC_TRG             71  

11 rows selected.

SQL> SPOOL OFF;


Previous Topic: Schema Refresh
Next Topic: How to write import scripts in batch file
Goto Forum:
  


Current Time: Mon Dec 23 20:21:15 CST 2024