-
Hi All,
I got a very strange problem. I took an export from one box and created the user on another bx with same tablespace name and imported. For none of the tables, index were created. There are near about 250 tables, for which no table has got index created. I'm wondering how is this happened. Import was successful till the views are created, there it gave the problem and terminated.Now we formatted the machine from which we took the export. I'm having only this newly imported schema. But this schema (All the objects) is very much similar to the production. Now i want all those indexes to be back. Is there any way? Please help me. I'm pasting my export.log file and followed by import.log . Please go through these files, and suggest me what to do. There is no OS backup. Suggest me how to get all the indexes from the existing prodcution schema please.
EXPORT LOG FILE:
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user
CMS2ADMOLD
. exporting object type definitions for user CMS2ADMOLD
About to export CMS2ADMOLD's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export CMS2ADMOLD's tables via Conventional Path ...
. . exporting table ACTION_TEMP 0 rows exported
. . exporting table ACT_TEMP 0 rows exported
. . exporting table ALLINAUB92 25416 rows exported
. . exporting table ALLINAUB92D 166272 rows exported
. . exporting table AUTO_POSTING_HANDLE 0 rows exported
. . exporting table A_ASSIGNMENTHISTORY 658751 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 snapshots
. 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.
[oper@cmsprod exp]$ exit
logout
----------------------------------------------------------
IMPORT LOG FILE:
-----------------------------------------------------------
export server uses US7ASCII NCHAR character set (possible ncharset conversion)
. importing CMS2ADMOLD's objects into CMS2ADMOLD
. . importing table "ACTION_TEMP" 0 rows imported
. . importing table "ACT_TEMP" 0 rows imported
. . importing table "ALLINAUB92" 25416 rows imported
. . importing table "ALLINAUB92D" 166272 rows imported
. . importing table "AUTO_POSTING_HANDLE" 0 rows imported
. . importing table "A_ASSIGNMENTHISTORY" 658751 rows imported
imported
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "CMS2ADMOLD"."VR_ACTDTL" ("FACILITYG"
"RP","FACILITYCODE","FCDESC","FCCODE","INSCODE","INSDESC","USERID","ACTIONCO"
"DE","FD","NFD","LFUSERID","LACTIONCODE","LDENIALCODE","LFCOMMENTS","ACTIOND"
"ESC","ACCOUNTBAL","TOTCHARGES","INSBAL","SPBAL","TOTPAYMENT","CLAIMNO","PAT"
"LNAME","PT") AS "
"select "FACILITYGRP","FACILITYCODE","FCDESC","FCCODE","INSCODE","INSDESC",""
"USERID","ACTIONCODE","FD","NFD","LFUSERID","LACTIONCODE","LDENIALCODE","LFC"
"OMMENTS","ACTIONDESC","ACCOUNTBAL","TOTCHARGES","INSBAL","SPBAL","TOTPAYMEN"
"T","CLAIMNO","PATLNAME","PT" from cms2adm.VR_ACTDTL where 1=2"
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "CMS2ADMOLD"."VR_ACTIONCODEDTL" ("FA"
"CILITYGRP","DCFCCODE","DCFCDESC","INSCODE","INSDESC","DCPTCODE","ACTIONCODE"
"","CLAIMNO","PATLNAME","FD","COMMENTS","ACTIONDESC","ACCOUNTBAL","TOTCHARGE"
"S","INSBAL","SPBAL","TOTPAYMENT","FCCODE") AS "
"select "FACILITYGRP","DCFCCODE","DCFCDESC","INSCODE","INSDESC","DCPTCODE",""
"ACTIONCODE","CLAIMNO","PATLNAME","FD","COMMENTS","ACTIONDESC","ACCOUNTBAL","
""TOTCHARGES","INSBAL","SPBAL","TOTPAYMENT","FCCODE" from cms2adm.VR_ACTIONC"
"ODEDTL where 1=2"
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "CMS2ADMOLD"."VR_ACTIONCODEDTL_EXP" "
"("CLAIMNO","ACCOUNTNO","PATLNAME","PATTYPECODE","MEDRECNO","INS1POLICY","AD"
"MITDT","DISCHDT","DCPTCODE","TOTCHARGES","INSCODE","FCCODE","ACTIONCODE","F"
"D","ORGACCOUNTBAL","ACCOUNTBAL","INSBAL","DATEADDED","AGEFROM","TOTPMTS","T"
"OTADJS","COMMENTS","FACILITYCODE","FACILITYGRP") AS "
"select "CLAIMNO","ACCOUNTNO","PATLNAME","PATTYPECODE","MEDRECNO","INS1POLIC"
"Y","ADMITDT","DISCHDT","DCPTCODE","TOTCHARGES","INSCODE","FCCODE","ACTIONCO"
"DE","FD","ORGACCOUNTBAL","ACCOUNTBAL","INSBAL","DATEADDED","AGEFROM","TOTPM"
"TS","TOTADJS","COMMENTS","FACILITYCODE","FACILITYGRP" from cms2adm.VR_ACTIO"
"NCODEDTL_EXP where 1=2"
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "CMS2ADMOLD"."VR_ACTIONCODESUMM" ("F"
"ACILITYGRP","DCFCCODE","DCFCDESC","CLAIMNO","INSCODE","DCPTCODE","ACTIONCOD"
"E","FD","ACTIONDESC","ACCOUNTBAL","TOTCHARGES","INSBAL","SPBAL","TOTPAYMENT"
"","FCCODE") AS "
"select "FACILITYGRP","DCFCCODE","DCFCDESC","CLAIMNO","INSCODE","DCPTCODE",""
"ACTIONCODE","FD","ACTIONDESC","ACCOUNTBAL","TOTCHARGES","INSBAL","SPBAL","T"
"OTPAYMENT","FCCODE" from cms2adm.VR_ACTIONCODESUMM where 1=2"
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "CMS2ADMOLD"."VR_ACTIONCODESUMM_INSPLAN_LF" "
" ("FACILITYGRP","DCFCCODE","DCFCDESC","INSCODE","INSDESC","CLAIMNO","
""DCPTCODE","ACTIONCODE","FD","ACTIONDESC","ACCOUNTBAL","TOTCHARGES","INSBAL"
"","SPBAL","TOTPAYMENT","FCCODE") AS "
"select "FACILITYGRP","DCFCCODE","DCFCDESC","INSCODE","INSDESC","CLAIMNO","D"
"CPTCODE","ACTIONCODE","FD","ACTIONDESC","ACCOUNTBAL","TOTCHARGES","INSBAL","
""SPBAL","TOTPAYMENT","FCCODE" from cms2adm.VR_ACTIONCODESUMM_INSPLAN_LF whe"
"re 1=2"
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "CMS2ADMOLD"."VR_ATBFC" ("DATEADDED""
","FCCODE","FCDESC","FACILITYGRP","PT","LFUSERID","ACCTTYPE","SUMBAL","CNTBA"
"L","D01","C01","D31","C31","D61","C61","D91","C91","D121","C121","D151","C1"
"51","D181","C181","D211","C211","D241","C241","D271","C271","D301","C301",""
"D331","C331","GD360","GC360") AS "
"select "DATEADDED","FCCODE","FCDESC","FACILITYGRP","PT","LFUSERID","ACCTTYP"
"E","SUMBAL","CNTBAL","D01","C01","D31","C31","D61","C61","D91","C91","D121""
","C121","D151","C151","D181","C181","D211","C211","D241","C241","D271","C27"
"1","D301","C301","D331","C331","GD360","GC360" from cms2adm.VR_ATBFC where "
"1=2"
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "CMS2ADMOLD"."VR_ATBFCBILLDTINSBAL" "
"("DATEADDED","FCCODE","FCDESC","FACILITYGRP","PT","LFUSERID","SUMBAL","CNTB"
"AL","D01","C01","D31","C31","D61","C61","D91","C91","D121","C121","D151","C"
"151","D181","C181","D211","C211","D241","C241","D271","C271","D301","C301","
""D331","C331","GD360","GC360") AS "
"select "DATEADDED","FCCODE","FCDESC","FACILITYGRP","PT","LFUSERID","SUMBAL""
","CNTBAL","D01","C01","D31","C31","D61","C61","D91","C91","D121","C121","D1"
"51","C151","D181","C181","D211","C211","D241","C241","D271","C271","D301",""
"C301","D331","C331","GD360","GC360" from cms2adm.VR_ATBFCBILLDTINSBAL where"
" 1=2"
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "CMS2ADMOLD"."VR_ATBFCIP" ("DATEADDE"
"D","INSCODE","INSDESC","FACILITYGRP","PT","LFUSERID","SUMBAL","CNTBAL","D01"
"","C01","D31","C31","D61","C61","D91","C91","D121","C121","D151","C151","D1"
"81","C181","D211","C211","D241","C241","D271","C271","D301","C301","D331",""
"C331","GD360","GC360") AS "
"select "DATEADDED","INSCODE","INSDESC","FACILITYGRP","PT","LFUSERID","SUMBA"
"L","CNTBAL","D01","C01","D31","C31","D61","C61","D91","C91","D121","C121",""
"D151","C151","D181","C181","D211","C211","D241","C241","D271","C271","D301""
","C301","D331","C331","GD360","GC360" from cms2adm.VR_ATBFCIP where 1=2"
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "CMS2ADMOLD"."VR_ATBFCIPBILLDTINSBAL" "
" ("DATEADDED","FCCODE","FCDESC","FACILITYGRP","PT","LFUSERID","SUMBAL","CN"
"TBAL","D01","C01","D31","C31","D61","C61","D91","C91","D121","C121","D151","
""C151","D181","C181","D211","C211","D241","C241","D271","C271","D301","C301"
"","D331","C331","GD360","GC360") AS "
"select "DATEADDED","FCCODE","FCDESC","FACILITYGRP","PT","LFUSERID","SUMBAL""
","CNTBAL","D01","C01","D31","C31","D61","C61","D91","C91","D121","C121","D1"
"51","C151","D181","C181","D211","C211","D241","C241","D271","C271","D301",""
"C301","D331","C331","GD360","GC360" from cms2adm.VR_ATBFCIPBILLDTINSBAL whe"
"re 1=2"
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "CMS2ADMOLD"."VR_CARITASLETTER" ("PA"
"TFNAME","PATMNAME","PATLNAME","PATSSN","MEDRECNO","FACILITY","FACILITYDESC""
","CLAIMNO","ADMITDT","AGEFROM","LBILLDT","INSDESC","INSDESC1","CLAIMACCTBAL"
"","CLAIMCHARGES","INSBAL","OWNERUSERID","PHONEWORK","ADDR1","ADDR2","ADDR3""
","ZIPCODE") AS "
"select "PATFNAME","PATMNAME","PATLNAME","PATSSN","MEDRECNO","FACILITY","FAC"
"ILITYDESC","CLAIMNO","ADMITDT","AGEFROM","LBILLDT","INSDESC","INSDESC1","CL"
"AIMACCTBAL","CLAIMCHARGES","INSBAL","OWNERUSERID","PHONEWORK","ADDR1","ADDR"
"2","ADDR3","ZIPCODE" from cms2adm.VR_CARITASLETTER where 1=2"
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "CMS2ADMOLD"."VR_BOTSFORDLETTER" ("P"
"ATLNAME","FACILITY","FACILITYDESC","CLAIMNO","ADMITDT","DISCHDT","LBILLDT","
""INSDESC","INSDESC1","ACCOUNTBAL","OWNERUSERID","PHONEWORK","PATID","POLICY"
"") AS "
"select "PATLNAME","FACILITY","FACILITYDESC","CLAIMNO","ADMITDT","DISCHDT",""
"LBILLDT","INSDESC","INSDESC1","ACCOUNTBAL","OWNERUSERID","PHONEWORK","PATID"
"","POLICY" from cms2adm.VR_BOTSFORDLETTER where 1=2"
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "CMS2ADMOLD"."VR_ATBLACTIONCD_LFD" ("
""LACTIONCODE","FACILITYGRP","PT","LFUSERID","ACCTTYPE","SUMBAL","CNTBAL","D"
"01","C01","D31","C31","D61","C61","D91","C91","D121","C121","D151","C151",""
"D181","C181","D211","C211","D241","C241","D271","C271","D301","C301","D331""
","C331","GD360","GC360") AS "
"select "LACTIONCODE","FACILITYGRP","PT","LFUSERID","ACCTTYPE","SUMBAL","CNT"
"BAL","D01","C01","D31","C31","D61","C61","D91","C91","D121","C121","D151",""
"C151","D181","C181","D211","C211","D241","C241","D271","C271","D301","C301""
","D331","C331","GD360","GC360" from cms2adm.VR_ATBLACTIONCD_LFD where 1=2"
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "CMS2ADMOLD"."VR_ATBLACTIONCD" ("LAC"
"TIONCODE","FACILITYGRP","PT","LFUSERID","ACCTTYPE","SUMBAL","CNTBAL","D01","
""C01","D31","C31","D61","C61","D91","C91","D121","C121","D151","C151","D181"
"","C181","D211","C211","D241","C241","D271","C271","D301","C301","D331","C3"
"31","GD360","GC360") AS "
"select "LACTIONCODE","FACILITYGRP","PT","LFUSERID","ACCTTYPE","SUMBAL","CNT"
"BAL","D01","C01","D31","C31","D61","C61","D91","C91","D121","C121","D151",""
"C151","D181","C181","D211","C211","D241","C241","D271","C271","D301","C301""
","D331","C331","GD360","GC360" from cms2adm.VR_ATBLACTIONCD where 1=2"
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "CMS2ADMOLD"."VR_ATBFCSERVINSBAL" (""
"DATEADDED","SERVICECODE","SERVICEDESC","FACILITYGRP","PT","LFUSERID","ACCTT"
"YPE","SUMBAL","CNTBAL","D01","C01","D31","C31","D61","C61","D91","C91","D12"
"1","C121","D151","C151","D181","C181","D211","C211","D241","C241","D271","C"
"271","D301","C301","D331","C331","GD360","GC360") AS "
"select "DATEADDED","SERVICECODE","SERVICEDESC","FACILITYGRP","PT","LFUSERID"
"","ACCTTYPE","SUMBAL","CNTBAL","D01","C01","D31","C31","D61","C61","D91","C"
"91","D121","C121","D151","C151","D181","C181","D211","C211","D241","C241",""
"D271","C271","D301","C301","D331","C331","GD360","GC360" from cms2adm.VR_AT"
"BFCSERVINSBAL where 1=2"
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "CMS2ADMOLD"."VR_ATBFCSERVBILLDTINSBAL" "
" ("DATEADDED","SERVICECODE","SERVICEDESC","FACILITYGRP","PT","LFUSERID","
""ACCTTYPE","SUMBAL","CNTBAL","D01","C01","D31","C31","D61","C61","D91","C91"
"","D121","C121","D151","C151","D181","C181","D211","C211","D241","C241","D2"
"71","C271","D301","C301","D331","C331","GD360","GC360") AS "
"select "DATEADDED","SERVICECODE","SERVICEDESC","FACILITYGRP","PT","LFUSERID"
"","ACCTTYPE","SUMBAL","CNTBAL","D01","C01","D31","C31","D61","C61","D91","C"
"91","D121","C121","D151","C151","D181","C181","D211","C211","D241","C241",""
"D271","C271","D301","C301","D331","C331","GD360","GC360" from cms2adm.VR_AT"
"BFCSERVBILLDTINSBAL where 1=2"
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "CMS2ADMOLD"."VR_ATBFCSERVBILLDT" (""
"DATEADDED","SERVICECODE","SERVICEDESC","FACILITYGRP","PT","LFUSERID","ACCTT"
"YPE","SUMBAL","CNTBAL","D01","C01","D31","C31","D61","C61","D91","C91","D12"
"1","C121","D151","C151","D181","C181","D211","C211","D241","C241","D271","C"
"271","D301","C301","D331","C331","GD360","GC360") AS "
"select "DATEADDED","SERVICECODE","SERVICEDESC","FACILITYGRP","PT","LFUSERID"
"","ACCTTYPE","SUMBAL","CNTBAL","D01","C01","D31","C31","D61","C61","D91","C"
"91","D121","C121","D151","C151","D181","C181","D211","C211","D241","C241",""
"D271","C271","D301","C301","D331","C331","GD360","GC360" from cms2adm.VR_AT"
"BFCSERVBILLDT where 1=2"
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "CMS2ADMOLD"."VR_ATBFCSERV" ("DATEAD"
"DED","SERVCODE","SERVDESC","FACILITYGRP","PT","LFUSERID","ACCTTYPE","SUMBAL"
"","CNTBAL","D01","C01","D31","C31","D61","C61","D91","C91","D121","C121","D"
"151","C151","D181","C181","D211","C211","D241","C241","D271","C271","D301","
""C301","D331","C331","GD360","GC360") AS "
"select "DATEADDED","SERVCODE","SERVDESC","FACILITYGRP","PT","LFUSERID","ACC"
"TTYPE","SUMBAL","CNTBAL","D01","C01","D31","C31","D61","C61","D91","C91","D"
"121","C121","D151","C151
-
Did you do the export with INDEXES=Y otherwise only the table definitions will be exported. The view errors are compilation errors (possibly due to unavailaible dependencies)
Regards
-
Try to run export from your production database with following parameters
FULL=Y
ROWS=N
INDEXES=Y
This export will export all database objects' structure but no data.
Then run import with
FULL=Y
IGNORE=Y
Import will create all database objects and will ignore errors on objects that already exist. So, at the end you'll get all your missing indexes.
Hope that helps.
Regards.