Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recreating Indexes Through EXP / IMP 1

Status
Not open for further replies.
THoey

THoey

IS-IT--Management
Jun 21, 2000
2,142
US
I did a search and came across a couple of threads that showed how to create DDL scripts for indexes by performing an EXP and then an IMP with the following options:

SHOW=Y
INDEXFILE=SOMEFILE.NAME

When I do this, I am getting a lot of errors during the IMP and the INDEXFILE is created but empty. Any ideas? I validated that both are using the same version of Oracle.

Code: 
C:\Temp>C:\oracle\ora92\bin\imp.exe login/password@DB file=c:\
temp\dump.dmp show=y indexfile=c:\temp\indexfile.txt



Import: Release 9.2.0.4.0 - Production on Tue Oct 31 12:38:03 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production

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

Warning: the objects were exported by SMFRPT_PROD, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses US7ASCII character set (possible charset conversion)
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  FREELIST GROUPS 1) TABLESPACE "SMFRPT_DATA" LOGGING NOCOMPRESS, PARTITION "P_2
0060902" VALUES LESS THAN (20060903)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SMFRPT_DAT
A" LOGGING NOCO...
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
   PARTITION "P_20061027"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 13
1072 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SMFRPT_IDX" LOGGING, PARTITION "
P_20061028"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS
 1 FREELIST GRO...
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  ATS TR "CPOSDAILYCOUNTSV1"
IMP-00008: unrecognized statement in the export file:
  ² w
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  ELISTS 1 FREELIST GROUPS 1) TABLESPACE "SMFRPT_DATA" NOLOGGING OVERFLOW  PCTFR
EE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREE
LIST GROUPS 1) TABLESPACE "SMFRPT_DATA" LOGGING, PARTITION "P_20061101" VALUES L
ESS THAN (20061...
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  COMPRESS, PARTITION "P_20060910" VALUES LESS THAN (20060911)  PCTFREE 10 PCTUS
ED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS
 1) TABLESPACE "SMFRPT_DATA" LOGGING NOCOMPRESS, PARTITION "P_20060911" VALUES L
ESS THAN (20060...
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  55 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SMFRPT_ID
X" LOGGING, PARTITION "P_20060902"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(I
NITIAL 131072 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SMFRPT_IDX" LOGGING, PA
RTITION "P_2006...
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  CTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST
GROUPS 1) TABLESPACE "SMFRPT_IDX" LOGGING, PARTITION "P_20060730"  PCTFREE 10 IN
ITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1) TABL
ESPACE "SMFRPT_...
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  NULL,202413.75,701,76233,1,2,178141,2,0); END;
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  131072 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SMFRPT_DATA" NOLOGGING NOCOM
PRESS, PARTITION "P_20060913" VALUES LESS THAN (20060914)  PCTFREE 10 PCTUSED 40
 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1) T
ABLESPACE "SMFR...
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
   PARTITION "P_20060607"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 13
1072 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SMFRPT_IDX" LOGGING, PARTITION "
P_20060608"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS
 1 FREELIST GRO...
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  NG, PARTITION "P_20060607"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL
 131072 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SMFRPT_IDX" LOGGING, PARTITIO
N "P_20060608"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELI
STS 1 FREELIST ...
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  E(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SMFRPT_DATA" LOGGI
NG NOCOMPRESS, PARTITION "P_20060902" VALUES LESS THAN (20060903)  PCTFREE 10 PC
TUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GRO
UPS 1) TABLESPA...
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
   PARTITION "P_20061027"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 13
1072 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SMFRPT_IDX" LOGGING, PARTITION "
P_20061028"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS
 1 FREELIST GRO...
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:

IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  TFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1
 FREELIST GROUPS 1) TABLESPACE "SMFRPT_DATA" LOGGING NOCOMPRESS, PARTITION "P_20
060911" VALUES LESS THAN (20060912)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 2
55 STORAGE(INIT...
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  EE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROU
PS 1) TABLESPACE "SMFRPT_IDX" LOGGING, PARTITION "P_20060728"  PCTFREE 10 INITRA
NS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1) TABLESPA
CE "SMFRPT_IDX"...
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  "P_20060727"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELI
STS 1 FREELIST GROUPS 1) TABLESPACE "SMFRPT_IDX" LOGGING, PARTITION "P_20060728"
  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST
 GROUPS 1) TABL...
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
   1 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1) TABLESPA
CE "SMFRPT_DATA" LOGGING NOCOMPRESS, PARTITION "P_20061007" VALUES LESS THAN (20
061008)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 131072 FR
EELISTS 1 FREEL...
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  ARTITION "P_20060628"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1310
72 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SMFRPT_IDX" LOGGING, PARTITION "P_
20060629"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1
 FREELIST GROUP...
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  , PARTITION "P_20060628"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1
31072 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SMFRPT_IDX" LOGGING, PARTITION
"P_20060629"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELIST
S 1 FREELIST GR...
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  0 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1
) TABLESPACE "SMFRPT_IDX" LOGGING, PARTITION "P_20060511"  PCTFREE 10 INITRANS 2
 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "
SMFRPT_IDX" LOG...
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  , PARTITION "P_20060628"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1
31072 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SMFRPT_IDX" LOGGING, PARTITION
"P_20060629"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELIST
S 1 FREELIST GR...
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  (TO_DATE(' 2006-08-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGO
RIAN'))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 131072 FR
EELISTS 1 FREELIST GROUPS 1) TABLESPACE "SMFRPT_DATA" NOLOGGING NOCOMPRESS, PART
ITION "P_200608...
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
  FREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GR
OUPS 1) TABLESPACE "SMFRPT_IDX" LOGGING, PARTITION "P_20060818"  PCTFREE 10 INIT
RANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1) TABLES
PACE "SMFRPT_ID...
Import terminated successfully with warnings.

C:\Temp>

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...
 
Sort by date Sort by votes
Since the IMP-00032 error indicates that you don't have a large enough buffer length, I would certainly try increasing the size of the buffer with the parameter "buffer=1000000" in your import command. There may be other issues, but that's the place to start.
 
Upvote 0 Downvote
I'll give that a try. There are several partioned tables in that export, so I am sure that is causing the overflow.

Thanks. I'll get back with you in a couple minutes.


Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...
 
Upvote 0 Downvote
Sorry for the delay. Corrupted the dump file and having to repull it. Will make a backup copy this time. (DOH!)

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...
 
Upvote 0 Downvote
Okay, much cleaner, but the results are the same with nothing in the INDEXFILE:
Code: 
C:\Temp>C:\oracle\ora92\bin\imp.exe login/password@DB file=c:\
temp\dump.dmp show=y indexfile=c:\temp\indexfile.txt buffer=1000000

Import: Release 9.2.0.4.0 - Production on Tue Oct 31 14:24:24 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production

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

Warning: the objects were exported by SMFRPT_PROD, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses US7ASCII character set (possible charset conversion)
Import terminated successfully without warnings.

C:\Temp>

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...
 
Upvote 0 Downvote
You didn't specify what you want to import. Try adding "full=y" to your import parameters
 
Upvote 0 Downvote
Well, that's kinda dumb. But, you were completely right and that worked. Thanks a bunch. Works slick. Have a star for being patient with me.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

unixfreak
  • Locked
  • Question
sqlplus backspace through 'screen'
Replies
1
Views
194
Samthyr
Samthyr
BenLoosli
  • Locked
  • Question
Converting from EXP to EXPDB
Replies
5
Views
187
Beilstwh
Beilstwh
sa0309
  • Locked
  • Question
Case expression in the WHERE clause of a delete statment
Replies
2
Views
162
carp
carp
hmckillop
  • Locked
  • Question
Exporting specific data from DB into another DB
Replies
1
Views
153
Beilstwh
Beilstwh
rk68
  • Locked
  • Question
data import using SQLLDR
Replies
5
Views
168
rk68
rk68

Part and Inventory Search

Sponsor

Back
Top