missing trigger/procedures after import [message #392953] |
Thu, 19 March 2009 15:45 |
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 #393196 is a reply to message #392953] |
Fri, 20 March 2009 13:03 |
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 #393248 is a reply to message #393213] |
Fri, 20 March 2009 16:41 |
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;
|
|
|