Indexes are not creating. Please help!!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Indexes are not creating. Please help!!!

  1. #1
    Join Date
    Apr 2002
    Posts
    291
    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






















    PNRDBA

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    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

    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    May 2002
    Posts
    37
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width