Home » RDBMS Server » Server Utilities » How to "clean" Oracle database before importing (Oracle 10.2.0.1)
How to "clean" Oracle database before importing [message #436812] Mon, 28 December 2009 11:28 Go to next message
stg2009
Messages: 3
Registered: December 2009
Junior Member
Hi,

My goal is copy the contents of the Oracle instance (10.2.0.1) running on machine B to the Oracle instance (10.2.0.1) on machine A. I wanted to use the exp/imp commands to accomplish this task.

1). My first question is on export. On machine B, I used the following command,

c:\oracle\product\10.2.0\db_4\bin >exp userB/xxxxx file=c:\export\db_exp_12242009.dmp log=c:\export\db_exp_12242009_log.log full=y

where userB is a user that has the privilege to run exp/imp commands, and xxxxx represents his password. This command seems to have finished fine, and I see no errors in the log file.

My question is, is this the command that gives me the "full" export of the contents of the database?

2). My second question is on import. On machine A, I did a fresh install of Oracle 10.2.0.1, and I made an attempt to do an import, but the system was "hanging", so I had to kill the import job after waiting for about two hours. Now, I wish to "clear the contents" of the Oracle instance on Machine A completely before I attempt to import again? How do I clear the contents and "restore" the Oracle instance to a "clean" state?

Thanks in advance for your help,

ST
Re: How to "clean" Oracle database before importing [message #436813 is a reply to message #436812] Mon, 28 December 2009 11:40 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want to duplicate a database the fastest way is to use the DUPLICATE command of RMAN or to backup the database and restore it in the other, for instance using RMAN.

For instance,
Database Backup and Recovery Advanced User's Guide
Chapter 13 Creating and Updating Duplicate Databases with RMAN

Regards
Michel
Re: How to "clean" Oracle database before importing [message #436814 is a reply to message #436812] Mon, 28 December 2009 11:42 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10671
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
1) Yes.
>> 2) How do I clear the contents and "restore" the Oracle instance to a "clean" state?
Depends on how much work the imp has already done.
Login to the database on Machine A.
Check if any custom schema/user has been created.
If so, Issue
drop user userName cascade constraints

If there is too much of accounts created, just restart.
Drop the database and create a new one. DBCA can do this very easily.

Before import,
Just recreate the tablesapces with same name as in production.
If there are not much schema's i would recreate the users too (manually, before import) and use
imp fromuser=x touser=x

Re: How to "clean" Oracle database before importing [message #436815 is a reply to message #436812] Mon, 28 December 2009 11:44 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In order to perform full database export, user needs to have (who'd say) the EXP_FULL_DATABASE role granted.

The easiest way to "clean" what you've imported is to drop that particular user (as it appears that you didn't perform full database export, but user (schema) export and, consequentially, import (which has never finished)).
Re: How to "clean" Oracle database before importing [message #436818 is a reply to message #436812] Mon, 28 December 2009 12:23 Go to previous messageGo to next message
joy_division
Messages: 4618
Registered: February 2005
Location: East Coast USA
Senior Member
stg2009 wrote on Mon, 28 December 2009 12:28

2). My second question is on import. On machine A, I did a fresh install of Oracle 10.2.0.1, and I made an attempt to do an import, but the system was "hanging", so I had to kill the import job after waiting for about two hours.


Most likely it was not hanging and was importing either a table with LONG datatype or building indexes on a huge table with many indexes.

Do you have the import log?
Re: How to "clean" Oracle database before importing [message #436917 is a reply to message #436812] Tue, 29 December 2009 09:09 Go to previous messageGo to next message
stg2009
Messages: 3
Registered: December 2009
Junior Member
Thanks for all of your quick replies. As an initial attempt, I have gone down Mahesh Rajendran's suggested path. I have deleted and re-created the Oracle database on machine A using DBCA.

On machine B, I did

select tablespace_name from dba_tablespaces;

and I created all the tablespaces that was returned from the above SQL on machine A, with the exception of "SYSTEM, UNDOTBS1, SYSAUX, TEMP, USERS", as they already exist on Machine A.

The command that I used to create one of the tablespaces was
CREATE TABLESPACE CONTROL_INDX
DATAFILE 'C:\databaseFiles\CONTROL_INDX01.dbf' SIZE 1M AUTOEXTEND 
ON
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
LOGGING
ONLINE
PERMANENT
SEGMENT SPACE MANAGEMENT AUTO
/

Lastly, I ran the imp command,

C:\oracle\product\10.2.0Serve\db_1\BIN>imp system/xxxxx file=c:\project\database\databaseB\full_export\pbxx_exp_12242009.dmp log=c:\project\database\import\pbxx_imp_12282009.log full=Y

pbxx_exp_12242009.dmp is only 1.6 GB and it took about 20 minutes for this import to finish.  However, in the import log file, I see a whole bunch of error messages such as

IMP-00015: following statement failed because the object already exists:
 "CREATE UNDO TABLESPACE "UNDOTBS1" BLOCKSIZE 8192 DATAFILE  'C:\ORACLE\PRODU"
 "CT\10.2.0\ORADATA\PBIS\UNDOTBS01.DBF' SIZE 1160M       AUTOEXTEND ON NEXT 5"
 "242880  MAXSIZE 32767M EXTENT MANAGEMENT LOCAL "
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLESPACE "SYSAUX" BLOCKSIZE 8192 DATAFILE  'C:\ORACLE\PRODUCT\10.2"
 ".0\ORADATA\PBIS\SYSAUX01.DBF' SIZE 293601280       AUTOEXTEND ON NEXT 10485"
 "760  MAXSIZE 32767M EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PERMANENT"
 "  SEGMENT SPACE MANAGEMENT AUTO"
IMP-00015: following statement failed because the object already exists:
 "CREATE TEMPORARY TABLESPACE "TEMP" BLOCKSIZE 8192 TEMPFILE  'C:\ORACLE\PROD"
 "UCT\10.2.0\ORADATA\PBIS\TEMP01.DBF' SIZE 79691776       AUTOEXTEND ON NEXT "
 "655360  MAXSIZE 32767M EXTENT MANAGEMENT LOCAL  UNIFORM SIZE 1048576"
...
...
IMP-00015: following statement failed because the object already exists:
 "REVOKE "EXECUTE_CATALOG_ROLE" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
 "CREATE ROLE "DELETE_CATALOG_ROLE""
IMP-00015: following statement failed because the object already exists:
 "REVOKE "DELETE_CATALOG_ROLE" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
 "CREATE ROLE "RECOVERY_CATALOG_OWNER""
IMP-00015: following statement failed because the object already exists:
 "REVOKE "RECOVERY_CATALOG_OWNER" FROM SYSTEM"
....


and I guess the good news is, I see this section in the log file,

. importing PBXX's objects into PBXX
. . importing table             "ACTIVE_FLAG_INFO"         74 rows imported
. . importing table              "ACTIVITY_GROUPS"        230 rows imported
. . importing table                    "ADD_FLAGS"         31 rows imported
. . importing table                      "APPA_DH"          4 rows imported
. . importing table                    "APPN_INFO"      30731 rows imported
. . importing table                 "APPN_MISSING"         84 rows imported
. . importing table           "APPN_UNDIST_FIELDS"        122 rows imported
. . importing table           "APPN_UNDIST_VALUES"          6 rows imported
. . importing table               "APPROPRIATIONS"        208 rows imported
. . importing table     "APPROPRIATION_ATTRIBUTES"      27006 rows imported
. . importing table   "APPROPRIATION_ATTRIBUTES_2"         81 rows imported
. . importing table         "APPROPRIATION_GROUPS"         21 rows imported
. . importing table       "APPROPRIATION_MEASURES"        486 rows imported
. . importing table                 "APP_SETTINGS"         80 rows imported
. . importing table  "AUXILIARY_INFORMATION_CODES"        213 rows imported
. . importing table                          "BAB"          1 rows imported
. . importing table                      "BAD_PKI"         37 rows imported
. . importing table                      "BASE_ID"       1373 rows imported
. . importing table                    "BASE_ID_2"        506 rows imported
. . importing table                          "BBT"         62 rows imported
. . importing table              "BI_SUPPLEMENTAL"       2370 rows imported
. . importing table           "BI_SUPPLEMENTAL_Q1"       3202 rows imported
. . importing table           "BI_SUPPLEMENTAL_Q2"       3666 rows imported
. . importing table           "BI_SUPPLEMENTAL_Q3"       2386 rows imported
. . importing table                    "BOCS_MAIN"     211880 rows imported
. . importing table             "BOCS_NULL_STATUS"         15 rows imported
. . importing table               "BOCS_OBG_RATES"         30 rows imported
. . importing table           "BOCS_OBS_ICC_PLUGS"         31 rows imported
. . importing table             "BRAC_COMMISSIONS"         92 rows imported
. . importing table                "BRAC_PROJECTS"        800 rows imported
. . importing table         "BRAC_RECOMMENDATIONS"        117 rows imported
. . importing table                         "BRBA"          2 rows imported
. . importing table                  "BRC_CONTROL"       3863 rows imported
. . importing table            "BUDGET_ACTIVITIES"        645 rows imported
. . importing table            "BUDGET_LINE_ITEMS"       3244 rows imported
. . importing table        "BUDGET_SUB_ACTIVITIES"         22 rows imported
. . importing table         "C1_MAJOR_INITIATIVES"         18 rows imported
. . importing table                  "C1_PROJECTS"       4529 rows imported
. . importing table                          "CCM"          1 rows imported
. . importing table                       "CD1213"        106 rows imported
. . importing table                      "CD_2008"         59 rows imported
. . importing table                 "CHAINED_ROWS"
Note: table contains ROWID column, values may be obsolete          0 rows imported
. . importing table               "CHANGE_PACKAGE"         14 rows imported
. . importing table           "CHANGE_PACK_ISSUES"       1259 rows imported
. . importing table            "CHANGE_PACK_TYPES"          9 rows imported
. . importing table        "CNTLHOLD_PBIS_COLUMNS"       1079 rows imported
. . importing table    "CNTLHOLD_PBIS_DATA_GROUPS"          5 rows imported
. . importing table         "CNTLHOLD_PBIS_TABLES"          5 rows imported
. . importing table          "CNTLHOLD_PBIS_TYPES"         61 rows imported
. . importing table                     "COLUDC22"          1 rows imported
. . importing table                     "COLUDC24"          1 rows imported
. . importing table                     "COLUDC26"          1 rows imported
. . importing table                     "COLUDC27"          1 rows imported
. . importing table                      "COLUDC8"          1 rows imported
. . importing table                   "COMPONENTS"        146 rows imported
. . importing table                       "CONBRV"          1 rows imported
. . importing table            "CONOPS_CATEGORIES"         55 rows imported
. . importing table                 "CONOPS_CODES"          9 rows imported
. . importing table             "CONSTANT_DOLLARS"        204 rows imported
. . importing table        "CONSTANT_DOLLARS_2008"        195 rows imported
. . importing table "CONSTANT_DOLLARS_EXEMPT_USERS"          9 rows imported
. . importing table         "CONSTANT_DOLLARS_OLD"        204 rows imported
. . importing table           "CONSTRUCTION_CODES"         25 rows imported
. . importing table        "CONSTRUCTION_PROJECTS"      20025 rows imported
. . importing table           "CONTROL_070611_B40"          0 rows imported
. . importing table                   "COST_TYPES"        313 rows imported
. . importing table                           "CP"          1 rows imported
. . importing table                       "CP5NEW"          2 rows imported
. . importing table                     "CPM_JCAS"          9 rows imported
. . importing table                  "CP_CP5_HOLD"          1 rows imported
. . importing table               "CP_DETAIL_SNAP"          0 rows imported
. . importing table                 "CP_FORM_DATA"          8 rows imported
. . importing table                      "CP_LOAD"         20 rows imported
. . importing table               "CP_STRING_DATA"         82 rows imported
. . importing table              "CP_SUMMARY_SNAP"          0 rows imported
. . importing table                "CP_TABLE_DATA"          0 rows imported
. . importing table                         "CT96"          1 rows imported
. . importing table         "CUSTOM_RECORD_LAYOUT"          5 rows imported
. . importing table  "CUSTOM_RECORD_LAYOUT_FIELDS"        579 rows imported
. . importing table                       "CYCLES"         23 rows imported
. . importing table            "DAVE_PERMIT_NOTES"          5 rows imported
. . importing table   "DEFENSE_MISSION_CATEGORIES"        355 rows imported
. . importing table              "DEFINE_ATFP_HII"          0 rows imported
. . importing table          "DEFINE_ATFP_HII_OLD"         84 rows imported
. . importing table          "DEFINE_ATFP_HII_TED"         84 rows imported
. . importing table      "DEFINE_BRAC_COMMISSIONS"         93 rows imported
. . importing table                   "DEFINE_BSO"         16 rows imported
. . importing table     "DEFINE_BUDGET_ACTIVITIES"       5044 rows imported
. . importing table     "DEFINE_C1_PROJECT_TITLES"       2486 rows imported
. . importing table                  "DEFINE_DMCS"       2363 rows imported
. . importing table                "DEFINE_ENT_RS"        519 rows imported
. . importing table            "DEFINE_ENT_RS_OLD"        499 rows imported
. . importing table    "DEFINE_EXPENSE_AGGREGATES"        236 rows imported
. . importing table        "DEFINE_FAC_CATEGORIES"       7394 rows imported
. . importing table                 "DEFINE_FH_PE"         31 rows imported
. . importing table       "DEFINE_FYDP_CONVERSION"        207 rows imported
. . importing table                "DEFINE_ICIPCC"      15377 rows imported
. . importing table      "DEFINE_INFRA_CATEGORIES"       2364 rows imported
. . importing table             "DEFINE_M1_NUMBER"        151 rows imported
. . importing table             "DEFINE_MARKET_LE"        273 rows imported
. . importing table          "DEFINE_MARKET_MODEL"      19497 rows imported
. . importing table                 "DEFINE_MDAPS"         77 rows imported
. . importing table             "DEFINE_MDAPS_OLD"        107 rows imported
. . importing table             "DEFINE_MDAPS_TED"        118 rows imported
. . importing table                   "DEFINE_OES"         59 rows imported
. . importing table               "DEFINE_OM_PERS"        174 rows imported
. . importing table           "DEFINE_OM_PERS_TED"        182 rows imported
. . importing table         "DEFINE_OSD_LOCATIONS"      22742 rows imported
. . importing table                 "DEFINE_OWNER"       6420 rows imported
. . importing table             "DEFINE_PROC_PERS"        176 rows imported
. . importing table         "DEFINE_PROC_PERS_TED"        151 rows imported
. . importing table            "DEFINE_QUAD_CODES"      45775 rows imported
. . importing table             "DEFINE_RA_FIELDS"         24 rows imported
. . importing table              "DEFINE_RDTEN_RS"        173 rows imported
. . importing table          "DEFINE_RDTEN_RS_TED"        159 rows imported
. . importing table                  "DEFINE_RICS"         77 rows imported
. . importing table           "DEFINE_RICS_BACKUP"         71 rows imported
. . importing table            "DEFINE_SAR_ID_QTY"       2819 rows imported
. . importing table                  "DEFINE_SBAS"       3352 rows imported
. . importing table       "DEFINE_SECNAV_ENABLERS"       1664 rows imported
. . importing table  "DEFINE_SECNAV_ENABLERS_HOLD"        435 rows imported
. . importing table         "DEFINE_TRAINING_RADS"       2685 rows imported
. . importing table                    "DERIVE_ID"       5429 rows imported
. . importing table                  "DERIVE_ID_2"       1543 rows imported
. . importing table                       "DLB_PG"         26 rows imported
. . importing table                      "DMDAPRD"         39 rows imported
. . importing table                          "DSP"         14 rows imported
. . importing table                       "DUMUIC"         17 rows imported
. . importing table                "DUTY_STATUSES"         73 rows imported
. . importing table                         "D_RS"          3 rows imported
. . importing table        "EDS_OBS_BSO_VARIANCES"          0 rows imported
. . importing table        "EDS_OBS_FMB_VARIANCES"          0 rows imported
. . importing table                    "EMPLOYERS"          5 rows imported
. . importing table                  "ENTERPRISES"         15 rows imported
. . importing table                      "ERFAPPA"         66 rows imported
. . importing table                       "ERRORS"         16 rows imported
. . importing table           "ESCALATION_FACTORS"         21 rows imported
. . importing table                   "EXCEPTIONS"
Note: table contains ROWID column, values may be obsolete          0 rows imported
. . importing table          "EXECUTION_AUTHORITY"      20059 rows imported
. . importing table              "EXECUTION_SPANS"        736 rows imported
. . importing table           "EXECUTION_STATUSES"         11 rows imported
. . importing table  "EXECUTION_TRANSACTION_TYPES"         26 rows imported
. . importing table              "EXEC_95_96_HOLD"        318 rows imported
. . importing table                  "EXEC_ISSUES"      20267 rows imported
. . importing table                    "EXEC_MAIN"     884733 rows imported
. . importing table            "EXEC_SERIAL_TABLE"      20248 rows imported
. . importing table    "EXEC_SERIAL_TABLE_REMARKS"       4961 rows imported
. . importing table             "EXEC_SERIAL_TEST"       8838 rows imported
. . importing table     "EXEC_SERIAL_TEST_REMARKS"        767 rows imported
. . importing table           "EXPENSE_AGGREGATES"         12 rows imported
. . importing table  "F1_APPROPRIATION_ATTRIBUTES"      20816 rows imported
. . importing table                     "F1_CODES"         15 rows imported
. . importing table                   "F1_COLUMNS"          2 rows imported
. . importing table      "F1_EDIT_DISPLAY_COLUMNS"          2 rows imported
. . importing table          "FACILITY_CATEGORIES"        228 rows imported
. . importing table                         "FHLI"          1 rows imported
. . importing table                        "FIX84"          6 rows imported
. . importing table                      "FIXPYMC"          5 rows imported
. . importing table                        "FLAGS"       1209 rows imported
. . importing table                  "FMB_BLI_UPD"       2787 rows imported
. . importing table              "FMB_ESCALATIONS"         23 rows imported
. . importing table         "FORCE_SPREAD_ENTRIES"          0 rows imported
. . importing table                          "FPG"          1 rows imported
. . importing table                  "FUND_LEVELS"          2 rows imported
. . importing table                    "FYDP_ORGS"         60 rows imported
. . importing table       "GC_ORGANIZATION_TABLES"          1 rows imported
. . importing table              "GC_PBIS_COLUMNS"        233 rows imported
. . importing table          "GC_PBIS_DATA_GROUPS"          1 rows imported
. . importing table               "GC_PBIS_TABLES"          1 rows imported
. . importing table         "GET_DEFAULTS_RESULTS"          0 rows imported
. . importing table                           "H1"          1 rows imported
. . importing table                           "HH"          1 rows imported
. . importing table          "HIGH_INTEREST_ITEMS"        394 rows imported
. . importing table            "HII_CT_CATEGORIES"         22 rows imported
. . importing table                  "H_PB09_MAIN"          0 rows imported
. . importing table                           "II"          1 rows imported
. . importing table     "INFLATION_CATEGORY_CODES"       1167 rows imported
. . importing table    "INFRASTRUCTURE_CATEGORIES"        240 rows imported
. . importing table  "INTEGRATED_CAPABILITY_PLANS"          2 rows imported
. . importing table     "INTEGRATED_CAP_PLANS_OLD"         32 rows imported
. . importing table        "INVESTMENT_CATEGORIES"         22 rows imported
. . importing table              "INVESTMENT_KEYS"         13 rows imported
. . importing table          "INVESTMENT_PROGRAMS"         96 rows imported
. . importing table                      "IS50092"         34 rows imported
. . importing table                       "ISSUES"       4821 rows imported
. . importing table                "ISSUES_BACKUP"          0 rows imported
. . importing table                "ISSUES_ROLLUP"          2 rows imported
. . importing table                "ISSUES_TO_DEL"       7675 rows imported
. . importing table                  "ISSUE_99999"          1 rows imported
. . importing table             "ISSUE_99999_NOTE"          1 rows imported
. . importing table            "ISSUE_ASSIGNMENTS"       2658 rows imported
. . importing table            "ISSUE_ATTACHMENTS"        687 rows imported
. . importing table              "ISSUE_CROSSWALK"          0 rows imported
. . importing table                   "ISSUE_HOLD"          1 rows imported
. . importing table                   "ISSUE_INFO"         14 rows imported
. . importing table                  "ISSUE_NOTES"       4982 rows imported
. . importing table            "ISSUE_NOTES_26000"        666 rows imported
. . importing table           "ISSUE_NOTES_ROLLUP"          3 rows imported
. . importing table              "ISSUE_NOTE_HOLD"          1 rows imported
. . importing table            "ISSUE_NOTIFY_SNAP"       4719 rows imported
. . importing table           "ISSUE_NOTIFY_USERS"         15 rows imported
. . importing table             "ISSUE_STAGE_NBTS"          0 rows imported
. . importing table                   "ISSUE_TEXT"          8 rows imported
. . importing table             "JOINT_DESK_CODES"         19 rows imported
. . importing table                         "JUNK"          0 rows imported
. . importing table                  "JUNK_ISSUES"          0 rows imported
. . importing table              "LEAN_SIX_SIGMAS"          3 rows imported
. . importing table                   "LINE_ITEMS"      27776 rows imported
. . importing table                          "LIP"          2 rows imported
. . importing table "LIZ_APPROPRIATION_ATTRIBUTES"        108 rows imported
. . importing table                "LIZ_DS_CHANGE"        377 rows imported
. . importing table "LIZ_PBIS_EDIT_DISPLAY_COLUMNS"          1 rows imported
. . importing table                  "LOGMNR_TEST"          5 rows imported
. . importing table                "M1_LINE_ITEMS"        144 rows imported
. . importing table               "MAJOR_PROGRAMS"         13 rows imported
. . importing table         "MAJ_DEF_ACQ_PROGRAMS"        117 rows imported
. . importing table             "MANAGEMENT_FLAGS"        757 rows imported
. . importing table                      "MARKETS"          4 rows imported
. . importing table              "MARKUP_PACKAGES"        234 rows imported
. . importing table                   "MARK_TYPES"          0 rows imported
. . importing table                          "MBA"          1 rows imported
. . importing table                        "MCNLI"          6 rows imported
. . importing table                       "MCPERS"        388 rows imported
. . importing table             "MC_PROGRAM_CODES"        435 rows imported
. . importing table                          "MD1"          2 rows imported
. . importing table   "MLOG$_APPROPRIATION_ATTRIB"      38209 rows imported
. . importing table                "MLOG$_PB_MAIN"     308417 rows imported
. . importing table          "MLOG$_PERMIT_GROUPS"       8055 rows imported
. . importing table     "MLOG$_USER_PERMIT_GROUPS"      54882 rows imported
. . importing table                           "MM"          1 rows imported
. . importing table                       "MODELS"         52 rows imported
. . importing table                          "MPG"          1 rows imported
. . importing table                         "MPN6"         32 rows imported
. . importing table                    "MPN_DUPES"        564 rows imported
. . importing table                       "MPN_ID"         58 rows imported
. . importing table                           "MR"          1 rows imported
. . importing table                         "N1OR"          1 rows imported
. . importing table                        "N1ORG"          1 rows imported
. . importing table                 "N61_TOA_XTAB"         11 rows imported
. . importing table                    "N6_ISSUES"        112 rows imported
. . importing table                        "N80PG"          3 rows imported
. . importing table                       "NDSFBA"         14 rows imported
. . importing table                          "NEP"          1 rows imported
. . importing table                   "NEWISRANGE"         32 rows imported
. . importing table                        "NEWPG"          2 rows imported
. . importing table                      "NEWRDBA"          1 rows imported
. . importing table                       "NEWSMC"          1 rows imported
. . importing table                        "NEWSP"          1 rows imported
. . importing table         "NEW_CONSTANT_DOLLARS"         61 rows imported
. . importing table       "NEW_CONSTANT_DOLLARS_2"         56 rows imported
. . importing table    "NEW_CONSTRUCTION_PROJECTS"          0 rows imported
. . importing table                    "NEW_UDC25"       2145 rows imported
. . importing table                   "NEW_UDC_21"          0 rows imported
. . importing table            "NEW_UDC_21_NODUPS"       9442 rows imported
. . importing table                      "NEW_UIC"       1681 rows imported
. . importing table                  "NEW_UIC_MPN"       4988 rows imported
. . importing table                  "NEW_UIC_RPN"       1681 rows imported
. . importing table             "NEW_VALIDATE_UIC"          0 rows imported
. . importing table                     "NEW_YEAR"         27 rows imported
. . importing table                "NEW_YEAR_2008"         27 rows imported
. . importing table                   "NEW_YEAR_8"         59 rows imported
. . importing table                        "NGVAL"         14 rows imported
. . importing table                           "NO"          1 rows imported
. . importing table           "OBJECT_CLASS_CODES"         87 rows imported
. . importing table             "OCC_ICC_AIC_KEYS"       1078 rows imported
. . importing table             "OFFICER_ENLISTED"          4 rows imported
. . importing table                     "OLD10001"        534 rows imported
. . importing table                        "OLD28"        296 rows imported
. . importing table                      "OLDCOLS"          8 rows imported
. . importing table                         "OLDI"       3027 rows imported
. . importing table                    "OLDMCONMC"       1096 rows imported
. . importing table                   "OLDNEWYEAR"         34 rows imported
. . importing table          "OLDVAL_APPN_BSO_PEO"          7 rows imported
. . importing table                    "OLD_MDAPS"         34 rows imported
. . importing table                     "OLD_UDC8"        459 rows imported
. . importing table                         "OMBA"         13 rows imported
. . importing table                    "OP08_MAIN"       4415 rows imported
. . importing table                "ORGANIZATIONS"        300 rows imported
. . importing table     "ORGANIZATION_EDIT_TABLES"          6 rows imported
. . importing table    "ORGANIZATION_ISSUE_RANGES"        254 rows imported
. . importing table   "ORGANIZATION_PERMIT_GROUPS"         38 rows imported
. . importing table          "ORGANIZATION_TABLES"         60 rows imported
. . importing table "ORGANIZATION_TABLES_BACKUP_GC"         23 rows imported
. . importing table "ORGANIZATION_TABLES_NEW_PBIS"         35 rows imported
. . importing table "ORGANIZATION_TABLES_OLD_PBIS"         12 rows imported
. . importing table                "ORG_CNET_HOLD"          4 rows imported
. . importing table             "OSD_C1_LOCATIONS"       2195 rows imported
. . importing table      "OSD_FACILITY_CATEGORIES"        230 rows imported
. . importing table                "OSD_LOCATIONS"       2470 rows imported
. . importing table                    "PAY_RATES"         40 rows imported
. . importing table                "PAY_RATES_OLD"         40 rows imported
. . importing table             "PAY_SYSTEM_CODES"         12 rows imported
. . importing table                   "PBAS_CODES"       5286 rows imported
. . importing table                   "PBD_BACKUP"        491 rows imported
. . importing table                    "PBID_LINK"       8410 rows imported
. . importing table                 "PBIS_COLUMNS"      11693 rows imported
. . importing table            "PBIS_COLUMNS_BPRJ"          1 rows imported
. . importing table        "PBIS_COLUMNS_GLENHIST"          8 rows imported
. . importing table      "PBIS_COLUMNS_MARKUP_PKG"          3 rows imported
. . importing table            "PBIS_COLUMNS_MISC"          8 rows imported
. . importing table         "PBIS_COLUMNS_MKT_MOD"          2 rows imported
. . importing table        "PBIS_COLUMNS_UDC11_21"         11 rows imported
. . importing table   "PBIS_COLUMNS_UDC1_23_26X29"          5 rows imported
. . importing table       "PBIS_COLUMN_DICTIONARY"        134 rows imported
. . importing table          "PBIS_COLUMN_DISPLAY"          9 rows imported
. . importing table             "PBIS_DATA_GROUPS"        128 rows imported
. . importing table    "PBIS_EDIT_DISPLAY_COLUMNS"        369 rows imported
. . importing table "PBIS_EDIT_DISP_COLS_MARKUP_PKG"          3 rows imported
. . importing table             "PBIS_EDIT_TABLES"          7 rows imported
. . importing table              "PBIS_MENU_LINKS"         41 rows imported
. . importing table       "PBIS_MENU_LINKS_BACKUP"         35 rows imported
. . importing table                  "PBIS_TABLES"        133 rows imported
. . importing table           "PBIS_TABLES_MODIFY"         25 rows imported
. . importing table    "PBIS_TABLE_EXTERNAL_LINKS"          2 rows imported
. . importing table         "PBIS_TRANSACTION_LOG"
Note: table contains ROWID column, values may be obsolete          0 rows imported
. . importing table                   "PBIS_TYPES"         61 rows imported
. . importing table            "PBIS_TYPES_BACKUP"         61 rows imported
. . importing table              "PBIS_TYPES_GLEN"          2 rows imported
. . importing table                "PBIS_UNCLASS1"         18 rows imported
. . importing table               "PBIS_UNCLASS1A"       8130 rows imported
. . importing table          "PBIS_UNCLASS_ISSUES"          9 rows imported
. . importing table           "PBIS_UNCLASS_NOTES"          9 rows imported
. . importing table            "PBIS_USER_IMPORTS"        253 rows imported
. . importing table            "PBIS_USER_QUERIES"      30019 rows imported
. . importing table        "PBIS_USER_QUERIES_TED"        518 rows imported
. . importing table             "PBIS_USER_SURVEY"        201 rows imported
. . importing table            "PBIS_USER_UPLOADS"          0 rows imported
. . importing table            "PBMAIN_95_96_HOLD"        111 rows imported
. . importing table                       "PBUDC7"          1 rows imported
. . importing table                      "PB_MAIN"     255967 rows imported
. . importing table                  "PB_MAIN_IDX"          0 rows imported
. . importing table          "PB_MAIN_SECURE_COLS"      31177 rows imported
. . importing table       "PB_MAIN_SPREAD_HISTORY"          0 rows imported
. . importing table         "PB_MAIN_SPREAD_TYPES"          2 rows imported
. . importing table                   "PB_SQLSTMT"          2 rows imported
. . importing table                "PERMIT_GROUPS"        909 rows imported
. . importing table            "PERMIT_GROUPS_OLD"        170 rows imported
. . importing table                       "PERSLI"          1 rows imported
. . importing table         "PERSONNEL_LINE_ITEMS"        826 rows imported
. . importing table                     "PETERSAS"        167 rows imported
. . importing table                       "PE_CPM"       2646 rows imported
. . importing table                     "PE_RATIO"        365 rows imported
. . importing table                   "PE_RATIO_2"        176 rows imported
. . importing table             "PE_RATIO_CLOSEST"        388 rows imported
. . importing table                "PE_RATIO_HOLD"        189 rows imported
. . importing table                          "PG4"          1 rows imported
. . importing table                        "PGC72"         26 rows imported
. . importing table                       "PGC72A"         20 rows imported
. . importing table                          "PGD"        111 rows imported
. . importing table                        "PGRDA"          1 rows imported
. . importing table                      "PILLARS"          5 rows imported
. . importing table                   "PLAN_TABLE"          0 rows imported
. . importing table               "PLAY_BOCS_MAIN"          0 rows imported
. . importing table               "PLAY_EXEC_MAIN"          0 rows imported
. . importing table                 "PLAY_PB_MAIN"          0 rows imported
. . importing table          "PLSQL_PROFILER_DATA"       8297 rows imported
. . importing table          "PLSQL_PROFILER_RUNS"         42 rows imported
. . importing table         "PLSQL_PROFILER_UNITS"        239 rows imported
. . importing table                          "PPG"          2 rows imported
. . importing table                   "PRIORITIES"       1000 rows imported
. . importing table                    "PROCESSES"          6 rows imported
. . importing table   "PROCUREMENT_LINE_ITEMS_OLD"       2994 rows imported
. . importing table     "PROGRAM_BUDGET_DECISIONS"        572 rows imported
. . importing table             "PROGRAM_ELEMENTS"       2696 rows imported
. . importing table       "PROGRAM_ELEMENTS_12909"       2679 rows imported
. . importing table         "PROGRAM_ELEMENTS_OLD"       2618 rows imported
. . importing table    "PROGRAM_EXECUTIVE_OFFICES"         32 rows imported
. . importing table                   "QUAD_CODES"        110 rows imported
. . importing table                 "QUERY_BACKUP"       1150 rows imported
. . importing table             "RAD_CARD_QUERIES"        224 rows imported
. . importing table         "RAD_CARD_QUERIES_OLD"        142 rows imported
. . importing table        "RAD_CARD_QUERIES_READ"         72 rows imported
. . importing table            "RAD_CARD_SETTINGS"         11 rows imported
. . importing table              "RAD_CARD_TITLES"        229 rows imported
. . importing table          "RAD_CARD_TITLES_OLD"        203 rows imported
. . importing table                   "RDTE_MDAPS"         40 rows imported
. . importing table                "RDTE_PROJECTS"       5922 rows imported
. . importing table            "RESOURCE_ID_CODES"        583 rows imported
. . importing table            "RESOURCE_SPONSORS"         49 rows imported
. . importing table                      "RESULTS"          5 rows imported
. . importing table                    "ROLE_INFO"         11 rows imported
. . importing table                       "RPNFIX"          2 rows imported
. . importing table                           "RR"          2 rows imported
. . importing table        "RTF_DEFINE_CATEGORIES"          8 rows imported
. . importing table       "RTF_DEGRADED_EQUIPMENT"         37 rows imported
. . importing table                     "RTF_MAIN"        791 rows imported
. . importing table         "RTF_MAJOR_CATEGORIES"          2 rows imported
. . importing table               "RTF_OPERATIONS"          3 rows imported
. . importing table           "RTF_SUB_CATEGORIES"          8 rows imported
. . importing table                    "RTF_SUPPS"          6 rows imported
. . importing table        "SCREEN_LAYOUT_STAGING"          0 rows imported
. . importing table              "SECNAV_ENABLERS"          4 rows imported
. . importing table             "SECURITY_CLASSES"          1 rows imported
. . importing table                     "SERVICES"          5 rows imported
. . importing table             "SERVICE_REC_2003"     219554 rows imported
. . importing table                      "SMITHTB"          5 rows imported
. . importing table     "SMP_VDD_OPERATIONS_TABLE"          0 rows imported
. . importing table              "SMP_VDF_MASLIST"          0 rows imported
. . importing table              "SMP_VDM_ADDRESS"          0 rows imported
. . importing table          "SMP_VDM_GLOBAL_INFO"          0 rows imported
. . importing table "SMP_VDM_LAST_NOTIF_SEQ_PERTYPE"          6 rows imported
. . importing table         "SMP_VDM_NOTIFICATION"          0 rows imported
. . importing table "SMP_VDM_NOTIFICATION_DETAILS"          0 rows imported
. . importing table "SMP_VDM_NOTIFICATION_NVPAIRS"          0 rows imported
. . importing table "SMP_VDM_NOTIFICATION_SERVICES"          0 rows imported
. . importing table  "SMP_VDM_PAGING_CARRIER_INFO"          0 rows imported
. . importing table "SMP_VDM_SESSION_NOTIFTYPE_PAIR"          0 rows imported
. . importing table        "SMP_VDS_REPOS_VERSION"          1 rows imported
. . importing table       "SMP_VDS_SESSIONS_TABLE"          0 rows imported
. . importing table       "SMP_VDU_CALLBACK_TABLE"          0 rows imported
. . importing table        "SMP_VDU_OBJECTS_TABLE"          3 rows imported
. . importing table     "SMP_VDU_PRINCIPALS_TABLE"          2 rows imported
. . importing table      "SMP_VDU_PRIVILEGE_TABLE"          6 rows imported
. . importing table                          "SP9"          8 rows imported
. . importing table                      "SPEC888"          4 rows imported
. . importing table                    "SPEC888IS"          4 rows imported
. . importing table                   "SPEC888ISN"          4 rows imported
. . importing table            "SPECIAL_INTERESTS"        158 rows imported
. . importing table        "SPECIAL_PROGRAM_CODES"         55 rows imported
. . importing table                     "SPLIT_ID"         81 rows imported
. . importing table                          "SPM"          1 rows imported
. . importing table                        "SPMPG"          1 rows imported
. . importing table                 "SPREAD_FLAGS"          6 rows imported
. . importing table            "SQLN_EXPLAIN_PLAN"        798 rows imported
. . importing table            "STAGING_BOCS_MAIN"          0 rows imported
. . importing table            "STAGING_EXEC_MAIN"      51032 rows imported
. . importing table          "STAGING_EXEC_MAIN_2"      32585 rows imported
. . importing table                 "STAGING_OP08"       2843 rows imported
. . importing table             "STAGING_OP08MAIN"       4415 rows imported
. . importing table              "STAGING_PB_MAIN"     254239 rows imported
. . importing table                       "STATES"        103 rows imported
. . importing table                     "STATUSES"         12 rows imported
. . importing table                 "STATUS_CODES"         39 rows imported
. . importing table                "SUBLINE_ITEMS"         33 rows imported
. . importing table        "SUBMANPOWER_CLAIMANTS"        234 rows imported
. . importing table                   "SUBPILLARS"         18 rows imported
. . importing table        "SUB_BUDGET_ACTIVITIES"        138 rows imported
. . importing table                "SUB_PROCESSES"        292 rows imported
. . importing table                "SUPPLEMENTALS"          2 rows imported
. . importing table      "SUPPLEMENTAL_CATEGORIES"          8 rows imported
. . importing table                       "SUPP_S"       2715 rows imported
. . importing table                      "TED_365"         10 rows imported
. . importing table              "TED_APPN_ATTRIB"       4994 rows imported
. . importing table            "TED_APPN_MEASURES"         61 rows imported
. . importing table                       "TED_CD"          7 rows imported
. . importing table                  "TED_HII_365"       5141 rows imported
. . importing table             "TED_PBIS_COLUMNS"         23 rows imported
. . importing table                       "TED_PC"        144 rows imported
. . importing table         "TED_SPLIT_STAGING_07"       1332 rows imported
. . importing table         "TED_SPLIT_STAGING_08"         64 rows imported
. . importing table         "TED_SPLIT_STAGING_09"         64 rows imported
. . importing table              "TED_VAL_BY_APPN"        243 rows imported
. . importing table                       "TEMPPG"          1 rows imported
. . importing table                    "TFMMSUICS"          1 rows imported
. . importing table                           "TH"          1 rows imported
. . importing table                   "TIME_CODES"        164 rows imported
. . importing table                "TITLE_CHANGES"
Note: table contains ROWID column, values may be obsolete         35 rows imported
. . importing table                "TITLE_COLUMNS"        405 rows imported
. . importing table                 "TITLE_TABLES"         62 rows imported
. . importing table                      "TM_NEWS"          0 rows imported
. . importing table            "TM_TITLE_REQUESTS"        161 rows imported
. . importing table                "TRAINING_RADS"         20 rows imported
. . importing table                   "TYPE_HIRES"         33 rows imported
. . importing table                        "UDC24"          3 rows imported
. . importing table                        "UDC26"          2 rows imported
. . importing table                        "UDC28"       1609 rows imported
. . importing table                       "UDC286"        201 rows imported
. . importing table                        "UDC86"         42 rows imported
. . importing table       "UDC_DEFINITION_CHANGES"          0 rows imported
. . importing table                    "UDC_DUPES"          8 rows imported
. . importing table                   "UDC_FIELDS"         28 rows imported
. . importing table               "UDC_FIELDS_TED"          1 rows imported
. . importing table        "UDC_FIELD_ASSIGNMENTS"      54476 rows imported
. . importing table      "UDC_FIELD_PERMIT_GROUPS"        107 rows imported
. . importing table           "UDC_UPDATE_ENTRIES"
Note: table contains ROWID column, values may be obsolete      33656 rows imported
. . importing table                         "UFPG"          2 rows imported
. . importing table                  "UNCLASS_MAP"       1960 rows imported
. . importing table              "UNDIST_CONTROLS"          8 rows imported
. . importing table    "UNIT_IDENTIFICATION_CODES"      22178 rows imported
. . importing table                           "UP"          1 rows imported
. . importing table                        "USERS"       8631 rows imported
. . importing table   "USER_FIELD_HISTORY_COLUMNS"      13413 rows imported
. . importing table             "USER_PERMISSIONS"     115017 rows imported
. . importing table           "USER_PERMIT_GROUPS"      14245 rows imported
. . importing table             "USER_PREFERENCES"       8450 rows imported
. . importing table           "USER_PREF_DEFAULTS"         94 rows imported
. . importing table                 "USER_QUERIES"          0 rows imported
. . importing table           "USER_QUERY_COLUMNS"          0 rows imported
. . importing table           "USER_QUERY_FILTERS"          0 rows imported
. . importing table              "USER_QUERY_SORT"          0 rows imported
. . importing table                         "USPG"         13 rows imported
. . importing table       "UTL_RECOMP_BACKUP_JOBS"          0 rows imported
. . importing table          "UTL_RECOMP_COMPILED"          0 rows imported
. . importing table           "UTL_RECOMP_INVALID"          0 rows imported
. . importing table               "UTL_RECOMP_LOG"          0 rows imported
. . importing table            "UTL_RECOMP_SORTED"          0 rows imported
. . importing table        "VALIDATE_APPN_BSO_PEO"        703 rows imported
. . importing table         "VALIDATE_APPN_BSO_RS"        105 rows imported
. . importing table       "VALIDATE_APPN_SUBLI_PE"        120 rows imported
. . importing table           "VALIDATE_BRAC_VARI"         42 rows imported
. . importing table    "VALIDATE_BY_APPROPRIATION"       1300 rows imported
. . importing table            "VALIDATE_LI_PE_SI"        778 rows imported
. . importing table          "VALIDATE_OMMC_LI_PE"          3 rows imported
. . importing table               "VALIDATE_PE_RS"       6067 rows imported
. . importing table "VALIDATE_PE_RS_BSO_PEO_RDTEPRJ"        899 rows imported
. . importing table                 "VALIDATE_UIC"       9892 rows imported
. . importing table             "VALIDATE_UIC_OLD"       9577 rows imported
. . importing table         "VALID_COMBOS_STAGING"          0 rows imported
. . importing table                  "VAL_PE_RS_7"       1905 rows imported
. . importing table                           "VU"          3 rows imported
. . importing table                     "V_UIC_MC"       1646 rows imported
. . importing table                "WINPAT_ISSUES"       8358 rows imported
. . importing table           "WINPAT_ISSUE_NOTES"       8510 rows imported
. . importing table                    "WINPAT_PB"      21046 rows imported
. . importing table                           "WR"          1 rows imported
. . importing table                        "XPB10"         10 rows imported
. . importing table                          "XSP"          1 rows imported

but, immediately following that, I see
. importing SYSTEM's objects into SYSTEM
IMP-00015: following statement failed because the object already exists:
 "CREATE PUBLIC SYNONYM "DUAL" FOR "SYS"."DUAL""
IMP-00015: following statement failed because the object already exists:
 "CREATE PUBLIC SYNONYM "SYSTEM_PRIVILEGE_MAP" FOR "SYS"."SYSTEM_PRIVILEGE_MA"
 "P""
IMP-00015: following statement failed because the object already exists:
 "CREATE PUBLIC SYNONYM "TABLE_PRIVILEGE_MAP" FOR "SYS"."TABLE_PRIVILEGE_MAP""
IMP-00015: following statement failed because the object already exists:
 "CREATE PUBLIC SYNONYM "STMT_AUDIT_OPTION_MAP" FOR "SYS"."STMT_AUDIT_OPTION_"
 "MAP""
IMP-00015: following statement failed because the object already exists:
 "CREATE PUBLIC SYNONYM "MAP_OBJECT" FOR "SYS"."MAP_OBJECT""
IMP-00015: following statement failed because the object already exists:
 "CREATE PUBLIC SYNONYM "DBMS_STANDARD" FOR "SYS"."DBMS_STANDARD""
IMP-00015: following statement failed because the object already exists:
 "CREATE PUBLIC SYNONYM "V$MAP_LIBRARY" FOR "SYS"."V_$MAP_LIBRARY""
IMP-00015: following statement failed because the object already exists:
 "CREATE PUBLIC SYNONYM "V$MAP_FILE" FOR "SYS"."V_$MAP_FILE""
IMP-00015: following statement failed because the object already exists:
 "CREATE PUBLIC SYNONYM "V$MAP_FILE_EXTENT" FOR "SYS"."V_$MAP_FILE_EXTENT""
IMP-00015: following statement failed because the object already exists:
 "CREATE PUBLIC SYNONYM "V$MAP_ELEMENT" FOR "SYS"."V_$MAP_ELEMENT""
...

and at the very end, line 244477, I see.

IMP-00003: ORACLE error 30371 encountered
ORA-30371: column cannot define a level in more than one dimension
. importing IX's objects into IX
. importing SYSMAN's objects into SYSMAN
. importing SYS's objects into SYS
. importing IX's objects into IX
. importing SYSMAN's objects into SYSMAN
. importing IX's objects into IX
. importing SYSTEM's objects into SYSTEM
. importing SYSMAN's objects into SYSMAN
. importing IX's objects into IX
. importing SYS's objects into SYS
. importing SYSMAN's objects into SYSMAN
. importing OE's objects into OE
. importing PM's objects into PM
. importing PBIS's objects into PBIS
About to enable constraints...
Import terminated successfully with warnings.


In other words, I'd say 99% of this log reports some of kind exception that it encountered, I have ommitted the "similar" looking errors and have only included some samples. Does this mean it imported correctly? or not? How do I know for sure?

thanks for your help,

ST

[Updated on: Tue, 29 December 2009 09:14] by Moderator

Report message to a moderator

Re: How to "clean" Oracle database before importing [message #436919 is a reply to message #436917] Tue, 29 December 2009 09:13 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10671
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Check the rowcounts from source (from export log?) with the rowcounts from import log.

Ignore the built-in/sample Schemas.
Just look into custom schema like PBXX.

[Updated on: Tue, 29 December 2009 09:16]

Report message to a moderator

Re: How to "clean" Oracle database before importing [message #436920 is a reply to message #436917] Tue, 29 December 2009 09:15 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
> How do I know for sure?
Does application pass acceptance test?
Re: How to "clean" Oracle database before importing [message #436930 is a reply to message #436812] Tue, 29 December 2009 10:53 Go to previous message
stg2009
Messages: 3
Registered: December 2009
Junior Member
In response to Mahesh Rajendran's comment,

Yes, I compared all tables under PBXX schema carefully, it seems that for every single table, the number of rows exported is equal to the number of rows imported. So, I guess this means it imported fine.

thanks for all the help,

ST

[Updated on: Tue, 29 December 2009 11:36]

Report message to a moderator

Previous Topic: Logical error in Exporting
Next Topic: escape single quotes using escape character in sql loader
Goto Forum:
  


Current Time: Sat Oct 01 11:01:10 CDT 2016

Total time taken to generate the page: 0.16079 seconds