Thank you very much Mr.Tom. The information is very useful to me.

Could you explain the export processing for COMPRESS option ?
According to Oracle document, this manage the initial extent for table data.
Since Oracle 816 and up, if we use the locally management tablespace method,
The INITIAL and NEXT size are the same, what compress will do, and
Which option should we use ? The default is 'Y'.

Could you elaborate this option please.
1. If we choose default value 'Y', when import into another DB, what impact to
the new DB tablespace storage setting?
2. If we choose value 'N', what impact when import into another DB.
Assume DB1 tablespace A is using EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
the new DB2 tablespace is using the same storage setting. or
3. If I use the larger uniform size, ie 128K.

From the full database export dump file- Is there any benefit in importing twice? First only the objects and its data. Then importing only the indexes with rows=n, ignore=y and indexes=y. Or is it the same as doing a full import?

Hi Tom

Recently I performed a database export from one machine and import to another, used these parameters

userid=sys/sys
file=exp.dmp
log=imp.log
buffer=10000000
commit=y
ignore=y
full=y

sort_area_size set to 30MB in init.ora (bounced database afterwards)
sort_multiblock_read_count to 8
db_block_size 8k
redo log file size 100MB with 6 groups
all tablespace were locally managed except system and RBS (due to numerous bugs when use LMT in RBS)


the dump file was 2GB big (it included a designer's repository), I have done this twice before already but on other servers with RAID 10 (with these raid 10 srvewrs the tablespaces were dictionary managed), the one I did few days back was RAID 5.

With RAID 10 I rememeber the dump file was about 1.7GB and it took roughly 2 hours but with 2GB dump file it took almost 9 hours in RAID 5!!! Does RAID 5 really make so much difference?

Also I didnt generate the indexfile because since I have one CPU only I didnt consider it was necessary (ok I know I can use nologging but I didnt because I was in rush and couldnt spend time on add nologging clause on around 1000 indexes). The creating index part is mistery to me as well, does the index of a table gets created after the data is imported to table first or this does not always happen (some indexes are created with table before data is imported)?

Later I performed another import of designer's respository, itself alone took 3 hours (what a nightmare tool!) because it had so many pl/sql packages that i thought the data dictionary contetion was really high!

Do you think is a good idea use buffer and commit parameters in import or it is better idea take all RBS offline and leave a big one online and ignore these two parameters to decrease commit frequency?

Finally what is the parameter recalculate_statistics for? I thought analyze runs analyze table during import already

Note: By the way if we set analyze=n we should speed up import? or table statistics is obtained from dump file and not by analyze table etc etc, although I read from documentation that setting analyze=y import actually runs

ANALYZE TABLE ETC

and I think the document is wrong in export parameter STATISTICS, it says that by default is estimate but I did a test, I created a dummy table, ran analyze table xxx compute statistics then export the table with default STATISTICS value but when I check the dump file it actually says

ALTER TABLE XXX COMPUTE STATISTICS instead of estimate

Hi

I did a small test, created a table called STATS with 57344 rows did analyze table .... sample 10 rows to yield 57936 in num_rows column in user_tables, good this is what I want, I dont want exact statistics

I then export table with

userid=lsc/lsc
file=exp.dmp
log=exp.log
tables=(lsc.stats)

then import to another user with

userid=lsc/lsc
file=exp.dmp
log=imp.log
fromuser=lsc
touser=ctrl
analyze=y
recalculate_statistics=n

import actually ran analyze table compute statistics because instead of my original stats of 57936 rows it got the right num_rows now, 57344 rows

so basically I was telling him not to recalculate statistics but it did, why? And with COMPUTE (or otherwise how did import got the right statistics?)

select owner, num_rows, table_name from dba_tables
where table_name='STATS'

OWNER NUM_ROWS TABLE_NAME
------------------------------ ---------- ------------------CTRL 57344 STATS
LSC 57936 STATS

from the docs:

</quote>
However, the precomputed optimizer statistics will not be used at export time if:

o A table has indexes with system-generated names (including LOB indexes)
on A table has columns with system-generated names
o There were row errors while exporting
o The client character set or NCHARSET does not match the server character set
or NCHARSET
o You have specified a QUERY clause
o Only certain partitions or subpartitions are to be exported
o Tables have indexes based upon constraints that have been analyzed (check,
unique, and primary key constraints)
o Tables have indexes with system-generated names that have been analyzed
(IOTs, nested tables, type tables that have specialized constraint indexes)

Note: Specifying ROWS=N does not preclude saving the precomputed statistics
in the Export file. This allows you to tune plan generation for queries in a
nonproduction database using statistics from a production database.
<quote>

so, which of these might be applying to you.

Here (my character set = nchar character set) we can see this can work if all of the rules are followed:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t select * from all_objects;

17227 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t select * from all_objects;

17227 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from t;

  COUNT(*)
----------
     51681

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t estimate statistics sample 10 rows;

Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select num_rows from user_tables where table_name = 'T';

  NUM_ROWS
----------
     51112

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> !exp userid=/ tables=t

Export: Release 8.1.7.2.0 - Production on Sun Apr 7 11:43:14 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                              T      51681 rows exported
Export terminated successfully without warnings.

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t;

Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> !imp userid=/ full=t recalculate_statistics=n

Import: Release 8.1.7.2.0 - Production on Sun Apr 7 11:43:19 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production

Export file created by EXPORT:V08.01.07 via conventional path
import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
. importing OPS$TKYTE's objects into OPS$TKYTE
. . importing table                            "T"      51681 rows imported
Import terminated successfully without warnings.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select num_rows from user_tables where table_name = 'T';

  NUM_ROWS
----------
     51112

ops$tkyte@ORA817DEV.US.ORACLE.COM> 

 

sorry import used analyze table stats estimate statistics, I verified in v$sql but this got me another question because you said that import will use the analyze method what you did with your original table so I did another test, I did analyze table stats compute statistics then exported again and imported again but instead of using compute it uses estimate (the default export value) so I guess export uses his own default parameters?

You have said several times you hate compress=Y, and I'm completely agree about this, but why is the default?.Is the default in 9i aswell?


Thanks
Pablo R.

hi tom

I did an exact example as you and I did get your results so I was puzzled but then if instead of importing the table to same user as you did I import to another the statistics is recalculated. Import to different user, that is the only difference from my prrevious example in my tests and yours

Hi Tom

I am trying to import one table .. i am getting the following error
-----
ORA-01658: unable to create INITIAL extent for segment in tablespace TS01
Import terminated successfully with warnings.
-----
I am sure there is lot of free space in tablespace as well as the drive where the datafile located then the dump file .
Could you please tell me what could be the wrong and how to overcome this..

Thanks
Kumar

Tom,

Have a full export from 8.16, the size is just under 2 gigs on NT.

It seems to work fine until it gets to the third table it sits there (sits over 24 hours)...with

Skipping table table_name

I have tried the same file on the instance it came from and another 8.17 w/the same result.

The structure of the table is

NUMBER(10)
NUMBER(10)
VARCHAR2(3)
VARCHAR2(14)
NUMBER(2)

With approx 1.8 million rows.

I have tried pressing "enter" in case it is a display problem".

These tests were done on the file using show=y full=y

With tables=(table) I did not have the output

But, it just sat there when I did tables=(table_name)

Also...the DB seems "healthy" for create tables (based on a version of the table that will not work)...w/ very fast response times.

What type of thing could cause this, where do I start to troubleshoot. Any solution that just gets me the one table I want would work. (But I would like to understand what happened as well).

Thanks,
Meyer

Tom, it only happens with a .dmp file that was copied across our network (not FTP'd) using the win(use-your imagination) standard copy command. Then copied back...to the same server....the original version of the file (that never left the server)...it works fine. Don't ask why I did not use that one first...just didn't.

Seen this before?

Thanks,
Meyer

I do not think chunk of disk because I tried to import it on two servers (both same result).

File sizes are the EXACT same bytes...
dates/times are 6 minutes apart (time to copy over our network)?

To test FC (never used)....I created two text files and put the same words in them...With the /B it does the binary compare..

fc /B first_file second_file

With the text files...it said:

FC: no differences encountered

The FC on the export files give me (LOTS OF) this:

14682383: 06 00
14682385: 36 00
14682386: 32 00
14682387: 35 00
14682388: 37 00
14682389: 38 00
1468238A: 38 00
1468238B: FE 00
(just pasted a few lines...I did a ctrl-C)

Seems copying my export files are corrupting them? I also checked another export copied and same...At this point, not expecting a solution...but do you have any advice?

Thanks,
Meyer

Tom, can you tell me what may be causing the errors below (ora-6550 and ora-922) ? I'm both exporting from and importing to Oracle9i on Windows.

Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
.. importing CIPLAN's objects into CIPLAN
IMP-00017: following statement failed with ORACLE error 6550:
"BEGIN "
"sys.dbms_logrep_imp.instantiate_schema(schema_name=>'CIPLAN', export_db_nam"
"e=>'WFROTA.US.ORACLE.COM', inst_scn=>'10126426');"
"COMMIT; END;"
IMP-00003: ORACLE error 6550 encountered
ORA-06550: line 2, column 1:
PLS-00201: identifier 'SYS.DBMS_LOGREP_IMP' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
IMP-00017: following statement failed with ORACLE error 922:
"CREATE TABLE "ADIANT_V" ("CODIGO" VARCHAR2(13) NOT NULL ENABLE, "ITEM" VARC"
"HAR2(3) NOT NULL ENABLE, "NFROTA" VARCHAR2(10), "NFUN" VARCHAR2(10), "DATA""
" DATE, "VALOR" NUMBER(15, 4), "OBSERVACAO" VARCHAR2(20)) PCTFREE 10 PCTUSE"
"D 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GRO"
"UPS 1) TABLESPACE "CIPLAN" LOGGING NOCOMPRESS"
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
IMP-00017: following statement failed with ORACLE error 922:
"CREATE TABLE "CAMPOS" ("CA_ORDEM" NUMBER(15, 0) NOT NULL ENABLE, "TA_NOME" "
"VARCHAR2(30) NOT NULL ENABLE, "CA_NOME" VARCHAR2(30) NOT NULL ENABLE, "CA_O"
"BRIGATORIO" CHAR(1), "CA_APELIDO" VARCHAR2(120), "CA_SELECIONADO" CHAR(1), "
""CA_PROCURA" CHAR(1), "CA_SORT" CHAR(1), "CA_TIPO" VARCHAR2(20), "CA_FILTRO"
"" CHAR(1), "CA_MANDANTE" CHAR(1), "CA_TAMANHO" NUMBER(15, 0), "CA_DATACRIAC"
"AO" DATE, "CA_TIPOREL" VARCHAR2(20), "CA_MSG" VARCHAR2(50), "CA_CHAVE" CHAR"
"(1), "CA_GRID" NUMBER(15, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 25"
"5 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "CIPLAN" "
"LOGGING NOCOMPRESS"
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
(... several more ora-922 errors ... )
Import terminated successfully with warnings.

imp can be slowed down, very significantly, if you have the following conditions:

- a table with long raw
- commit=y

Unlike other datatypes, long raw does not respect buffer=N, and does not perform any bulk dml. Consequently, it will commit after every row inserted!!!

Typically, if this is the case, v$session_wait will show a lot of log_file_sync waits for the imp session.

Overall, imp performance, just like any other activity, can be diagnosed by reviewing v$session_wait and identifying the event most frequently waited on.

Mr.Tom,

If the current dmp file size is say 20-30 GB. Is it advisable to do incremental export on every day and full export on every 15 days? Is any major disadvantages on Incremental Backup?

Hi Tom,
Can we use 8.1.7 clinet import utility to import 8.0.6 dump into an 8.06 database ?

I'm havin trouble importing data... it takes forever. Will try the compress=N i thought 'N' was the default.

And i get archiving errors that's because the developmental machine is much much much much smaller in scale. Does Imp/Exp generate tbat much redo ? can i minimize it ?

Thanks.

You gave this answer to the first question.

---
1) insufficient tablespace - that means you didn't have enough room. this
typically happens if you do the default export which uses "compress=y" which
generates a create table statement that has an INITIAL extent equal to the sum
of all allocated extents. Recommend you always export with compress=N.
---

Now compress=N is the worst of both settings ? hmmm...

hi Tom ,

i am confused about what Alvin from Philippines has asked about and the solution given by you???

Alvin from Philippines
=========================
You gave this answer to the first question.
---
1) insufficient tablespace - that means you didn't have enough room. this typically happens if you do the default export which uses "compress=y" which generates a create table statement that has an INITIAL extent equal to the sum
of all allocated extents. Recommend you always export with compress=N.
---
Now compress=N is the worst of both settings ? hmmm...

Followup:
sorry about that - i read it wrong. fixed it.

============================================================

why compress=N is the worst of both settings ??

I will be thankfull if you could explain this. Which is recommedned to use compress=y or compress=n ??

Thanks in advance
Thiru

dear tom

you suggest not to use consistent=y during export . you had also posted in a form that you donot like to use this .

In this link below
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:832427034486, <code>

you are suggesting to use consistent=y during full export .

[a complete database export (should use consistent=y). That will generate an export with everything in it.]

during full db export can i proceed with consistent=y or not .

seeking your valuable advice on this

thanks
thirumaran.g

Hi,

We are doing import using designer 6i client,
Its very slow.
I found out that sort_area_size is very low only 20K.

Is import process is related to sort_area_size parameter?
What other parameters in init.ora file can affect performance of import process?

Thanks.
Atul

Hi Tom,

From your quote "diagnose the issue -- see what import is waiting on, waiting for, find the bottleneck."

You told us to see the bottleneck ,but tell me where to find the bottleneck? . I mean which file,etc..

tom, what is the exact command to do <quote> 10046 trace on the import with level 12 (get binds)<quote> in sqlplus. Thanks.

Tom,
We are running oracle9i as OLTP and I would to take an export of the whole schema with CONSISTENT=N (by default). Even at the quite time the database is continually updated due to the concurrent users ~ 500.
The tables are being updated during export with consistent=N

My questions are:
1. If I need to import it to a new machine, would I be able to do that? Is there any kind of consistency (any kind of SCN # ..) required during import?

2. Since the data in the tables are inconsistent could cause web application not to function properly. Can such a behavior be seen during export with consistent=N

Your help in this regard will be highly appreciated.

LOBs have always been the slowest part both in export and import - in import it's much much worse. Sometimes people just want to get an export or import without LOBs. Is there a good way to exclude them in exp and/or imp? If only there were a "LOB=n" option ;-).

and I just want to exclude one huge LOB table? More precisely, I want to exclude the LOB column. Short of "LOB=n", I guess we can settle with "excluded=<table_names>", if only it's there!

For some reason no one ever seems to include using constraints=N as a tip when people ask about exp/imp. Some common ERP applications have an excessive number of constraints in their database and the constraints can be created using the indexfile after the data is imported. I have seen this one parameter change a 60 hour import into a 8 hour import including building the constraints and indexes. Results of this will vary based on the number of constraints in your database.

Hi,

we are doing import from a designer,so we can't change any parameter.

Our sort are parameters are given below:

sort_area_size
262144

sort_area_retained_size
65536


Could you suggest about that to increase import speed?


Thanks.
atul

Dear Sir

I am getting this message
IMP-00009: abnormal end of export file
imp-00020: long column too large for column buffer size <20>
imp-00028: partial import of previous table rolled back 1208 rows rolled back
import terminated successfully with warnings.

when I try to import the table that is exported in previous oracle 8 , and my current oracle is

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 &#8211; Production

And the table structure that is I am trying to import as follow :

APPLICATION_ID VARCHAR2(15)
MENU_NAME VARCHAR2(15)
OPTION_NUMBER NUMBER(4)
OPTION_TEXT VARCHAR2(70)
COMMAND_LINE VARCHAR2(20)
PROGRAM_TYPE NUMBER(2)
SPECIAL_COND NUMBER(2)

How can I increase the buffer size can you give me example

Best regards

Jamil

Dear Tom,

We have a problem regarding export/import. We have 10 offices located at different geographical sites. And we have a heterogeneous Oracle databases. Some on 8.0.3 and some even on 8.1.6 and 8.1.7

We have a program which transfers data between different offices using exp/imp utilities. This program is to be executed on server. So I have to load the oracle utilities of lowest version on all the oracle servers along with oracle 8i utilities. It means it requires to load oracle 8.0 uilities in all the servers. I have Oracle already working on each servers. Please can you let me know that how to install Oracle 8.0 utilities on all the running oracle 8i servers.

Thanks

I've tried to import a subpartitioned table in a 9.2.0 db using the same parameters that worked well for a partioned table in a 8.1.7 db, but the only thing I get is the 922 error.

Any idea about this?

Our parameter files are:
EXPORT ->
FILE=bck_historico.dmp
LOG=historico_exp.log
TABLES=(TB_HIS_OPER22)
CONSISTENT=Y
FEEDBACK=100
STATISTICS=NONE
IMPORT ->
FILE=bck_historico.dmp
LOG=historico_imp.log
TABLES=(TB_HIS_OPER22)
IGNORE=Y
COMMIT=Y
FEEDBACK=100

We detected that the problem is about subpartitions because if we try with a test partitioned table are working but if import try with this sentence:
CREATE TABLE TB_HIS_OPER22 (
SQ_HOP_01 NUMBER(10, 0) NOT NULL ENABLE,
CO_MSDN_V VARCHAR2(9) NOT NULL ENABLE,
CO_OPER_V VARCHAR2(16) NOT NULL ENABLE,
DS_OPER_V VARCHAR2(64) NOT NULL ENABLE,
FX_OPER_D DATE NOT NULL ENABLE,
CO_USER_V VARCHAR2(15),
CO_REQU_V VARCHAR2(15) NOT NULL ENABLE,
DS_REAS_V VARCHAR2(64),
CO_TIP_PROD_V VARCHAR2(8) NOT NULL ENABLE,
NO_COD_PROD_V VARCHAR2(8) NOT NULL ENABLE,
DS_PROD_V VARCHAR2(32),
SQ_PROD_V VARCHAR2(10) NOT NULL ENABLE,
DS_INI_PROD_V VARCHAR2(64),
TO_QUAN_N NUMBER(10, 0),
FX_INI_VALI_D DATE,
FX_FIN_VALI_D DATE)
PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLESPACE TSP_HOP_D_01 LOGGING PARTITION
BY RANGE (FX_OPER_D ) SUBPARTITION BY HASH (CO_MSDN_V ) SUBPARTITIONS
10 SUBPARTITION TEMPLATE( SUBPARTITION SP1 VALUES () , SUBPARTITION SP2
VALUES () , SUBPARTITION SP3 VALUES () , SUBPARTITION SP4 VALUES () ,
SUBPARTITION SP5 VALUES () , SUBPARTITION SP6 VALUES () , SUBPARTITION
SP7 VALUES () , SUBPARTITION SP8 VALUES () , SUBPARTITION SP9 VALUES
() , SUBPARTITION SP10 VALUES () ) (PARTITION TB_HIS_OPER_MAX VALUES
LESS THAN (MAXVALUE) PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 STORAGE(
INITIAL 512000 NEXT 307200) TABLESPACE TSP_HOP_D_01 LOGGING (
SUBPARTITION TB_HIS_OPER_MAX_SP1 TABLESPACE TSP_HOP_D_01,
SUBPARTITION TB_HIS_OPER_MAX_SP2 TABLESPACE TSP_HOP_D_01,
SUBPARTITION TB_HIS_OPER_MAX_SP3 TABLESPACE TSP_HOP_D_01,
SUBPARTITION TB_HIS_OPER_MAX_SP4 TABLESPACE TSP_HOP_D_01,
SUBPARTITION TB_HIS_OPER_MAX_SP5 TABLESPACE TSP_HOP_D_01,
SUBPARTITION TB_HIS_OPER_MAX_SP6 TABLESPACE TSP_HOP_D_01,
SUBPARTITION TB_HIS_OPER_MAX_SP7 TABLESPACE TSP_HOP_D_01,
SUBPARTITION TB_HIS_OPER_MAX_SP8 TABLESPACE TSP_HOP_D_01,
SUBPARTITION TB_HIS_OPER_MAX_SP9 TABLESPACE TSP_HOP_D_01,
SUBPARTITION TB_HIS_OPER_MAX_SP10 TABLESPACE TSP_HOP_D_01 ) )
The problem appears.
How can we import subpartitioned tables ???
Thanks in advance.

Sorry Tom I haven't explained OK.

The export was made with these contents in the parameter file:
FILE=bck_historico.dmp
LOG=historico_imp.log
TABLES=(TB_HIS_OPER22)
IGNORE=Y
COMMIT=Y
FEEDBACK=100

And the import with these ones:
FILE=bck_historico.dmp
LOG=historico_exp.log
TABLES=(TB_HIS_OPER22)
CONSISTENT=Y
FEEDBACK=100
STATISTICS=NONE

And the import failed. Trying to see why it had failed (error 922)we "extracted" from bck_historico.dmp the create table script I shew before and we got the same error (922), that's why we think the problem is there.

I hope it is clear.

Thanks again.

Here's the entire thing (8.1.7):

1. EXPORT: exp USERID=user/pwd PARFILE=exp_parfile.txt
and this is the resulting log (everything seems OK)

Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.3.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table TB_HIS_OPER
. . exporting composite partition TB_HIS_OPER_20031027
. . exporting subpartition TB_HIS_OPER_20031027_SP1
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031027_SP2
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031027_SP3
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031027_SP4
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031027_SP5
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031027_SP6
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031027_SP7
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031027_SP8
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031027_SP9
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031027_SP10
0 rows exported
. . exporting composite partition TB_HIS_OPER_20031103
. . exporting subpartition TB_HIS_OPER_20031103_SP1
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031103_SP2
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031103_SP3
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031103_SP4
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031103_SP5
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031103_SP6
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031103_SP7
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031103_SP8
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031103_SP9
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031103_SP10
0 rows exported

/* A LOT MORE LINES OF THE SAME KIND*/

0 rows exported
. . exporting composite partition TB_HIS_OPER_20040216
. . exporting subpartition TB_HIS_OPER_20040216_SP1
0 rows exported
. . exporting subpartition TB_HIS_OPER_20040216_SP2
0 rows exported
. . exporting subpartition TB_HIS_OPER_20040216_SP3
0 rows exported
. . exporting subpartition TB_HIS_OPER_20040216_SP4
0 rows exported
. . exporting subpartition TB_HIS_OPER_20040216_SP5
0 rows exported
. . exporting subpartition TB_HIS_OPER_20040216_SP6
0 rows exported
. . exporting subpartition TB_HIS_OPER_20040216_SP7
0 rows exported
. . exporting subpartition TB_HIS_OPER_20040216_SP8
0 rows exported
. . exporting subpartition TB_HIS_OPER_20040216_SP9
0 rows exported
. . exporting subpartition TB_HIS_OPER_20040216_SP10
0 rows exported
Export terminated successfully without warnings.

2. IMPORT: imp USERID=user/pwd PARFILE= imp_parfile.txt with this log:

Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing LOGAN3_ADMIN's objects into LOGAN3_ADMIN
IMP-00017: following statement failed with ORACLE error 922:
"CREATE TABLE "TB_HIS_OPER" ("SQ_HOP_01" NUMBER(10, 0) NOT NULL ENABLE, "CO_"
"MSDN_V" VARCHAR2(9) NOT NULL ENABLE, "CO_OPER_V" VARCHAR2(16) NOT NULL ENAB"
"LE, "DS_OPER_V" VARCHAR2(64) NOT NULL ENABLE, "FX_OPER_D" DATE NOT NULL ENA"
"BLE, "CO_USER_V" VARCHAR2(15), "CO_REQU_V" VARCHAR2(15) NOT NULL ENABLE, "D"
"S_REAS_V" VARCHAR2(64), "CO_TIP_PROD_V" VARCHAR2(8) NOT NULL ENABLE, "NO_CO"
"D_PROD_V" VARCHAR2(8) NOT NULL ENABLE, "DS_PROD_V" VARCHAR2(32), "SQ_PROD_V"
"" VARCHAR2(10) NOT NULL ENABLE, "DS_INI_PROD_V" VARCHAR2(64), "TO_QUAN_N" N"
"UMBER(10, 0), "FX_INI_VALI_D" DATE, "FX_FIN_VALI_D" DATE) PCTFREE 5 PCTUSE"
"D 60 INITRANS 1 MAXTRANS 255 TABLESPACE "TSP_HOP_D_01" LOGGING PARTITION BY"
" RANGE ("FX_OPER_D" ) SUBPARTITION BY HASH ("CO_MSDN_V" ) SUBPARTITIONS 1"
"0 SUBPARTITION TEMPLATE( SUBPARTITION "SP1" VALUES () , SUBPARTITION "SP2" "
"VALUES () , SUBPARTITION "SP3" VALUES () , SUBPARTITION "SP4" VALUES () , S"
"UBPARTITION "SP5" VALUES () , SUBPARTITION "SP6" VALUES () , SUBPARTITION ""
"SP7" VALUES () , SUBPARTITION "SP8" VALUES () , SUBPARTITION "SP9" VALUES ("
") , SUBPARTITION "SP10" VALUES () ) (PARTITION "TB_HIS_OPER_20031020" VALU"
"ES LESS THAN (TO_DATE(' 2003-10-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NL"
"S_CALENDAR=GREGORIAN')) PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "TSP_HOP_D_08" LOGGING (SUBPARTITION "TB_HIS_OPER_20031020_SP1" TABL"
"ESPACE "TSP_HOP_D_08", SUBPARTITION "TB_HIS_OPER_20031020_SP2" TABLESPACE "
""TSP_HOP_D_08", SUBPARTITION "TB_HIS_OPER_20031020_SP3" TABLESPACE "TSP_HO"
"P_D_08", SUBPARTITION "TB_HIS_OPER_20031020_SP4" TABLESPACE "TSP_HOP_D_08""
", SUBPARTITION "TB_HIS_OPER_20031020_SP5" TABLESPACE "TSP_HOP_D_08", SUBPA"
"RTITION "TB_HIS_OPER_20031020_SP6" TABLESPACE "TSP_HOP_D_08", SUBPARTITION"
" "TB_HIS_OPER_20031020_SP7" TABLESPACE "TSP_HOP_D_08", SUBPARTITION "TB_HI"
"S_OPER_20031020_SP8" TABLESPACE "TSP_HOP_D_08", SUBPARTITION "TB_HIS_OPER_"
"20031020_SP9" TABLESPACE "TSP_HOP_D_08", SUBPARTITION "TB_HIS_OPER_2003102"
"0_SP10" TABLESPACE "TSP_HOP_D_08" ), PARTITION "TB_HIS_OPER_20031027" VALU"
"ES LESS THAN (TO_DATE(' 2003-10-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NL"
"S_CALENDAR=GREGORIAN')) PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "TSP_HOP_D_09" LOGGING (SUBPARTITION "TB_HIS_OPER_20031027_SP1" TABL"
"ESPACE "TSP_HOP_D_09", SUBPARTITION "TB_HIS_OPER_20031027_SP2" TABLESPACE "
""TSP_HOP_D_09", SUBPARTITION "TB_HIS_OPER_20031027_SP3" TABLESPACE "TSP_HO"
"P_D_09", SUBPARTITION "TB_HIS_OPER_20031027_SP4" TABLESPACE "TSP_HOP_D_09""
", SUBPARTITION "TB_HIS_OPER_20031027_SP5" TABLESPACE "TSP_HOP_D_09", SUBPA"
"RTITION "TB_HIS_OPER_20031027_SP6" TABLESPACE "TSP_HOP_D_09", SUBPARTITION"
" "TB_HIS_OPER_20031027_SP7" TABLESPACE "TSP_HOP_D_09", SUBPARTITION "TB_HI"
"S_OPER_20031027_SP8" TABLESPACE "TSP_HOP_D_09", SUBPARTITION "TB_HIS_OPER_"
"20031027_SP9" TABLESPACE "TSP_HOP_D_09", SUBPARTITION "TB_HIS_OPER_2003102"
"7_SP10" TABLESPACE "TSP_HOP_D_09" ), PARTITION "TB_HIS_OPER_20031103" VALU"
"ES LESS THAN (TO_DATE(' 2003-11-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NL"
"S_CALENDAR=GREGORIAN')) PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "TSP_HOP_D_10" LOGGING (SUBPARTITION "TB_HIS_OPER_20031103_SP1" TABL"
"ESPACE "TSP_HOP_D_10", SUBPARTITION "TB_HIS_OPER_20031103_SP2" TABLESPACE "
""TSP_HOP_D_10", SUBPARTITION "TB_HIS_OPER_20031103_SP3" TABLESPACE "TSP_HO"
"P_D_10", SUBPARTITION "TB_HIS_OPER_20031103_SP4" TABLESPACE "TSP_HOP_D_10""
", SUBPARTITION "TB_HIS_OPER_20031103_SP5" TABLESPACE "TSP_HOP_D_10", SUBPA"
"RTITION "TB_HIS_OPER_20031103_SP6" TABLESPACE "TSP_HOP_D_10", SUBPARTITION"
" "TB_HIS_OPER_20031103_SP7" TABLESPACE "TSP_HOP_D_10", SUBPARTITION "TB_HI"
"S_OPER_20031103_SP8" TABLESPACE "TSP_HOP_D_10", SUBPARTITION "TB_HIS_OPER_"
"20031103_SP9" TABLESPACE "TSP_HOP_D_10", SUBPARTITION "TB_HIS_OPER_2003110"
"3_SP10" TABLESPACE "TSP_HOP_D_10" ), PARTITION "TB_HIS_OPER_20031110" VALU"
"ES LESS THAN (TO_DATE(' 2003-11-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NL"
"S_CALENDAR=GREGORIAN')) PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "TSP_HOP_D_11" LOGGING (SUBPARTITION "TB_HIS_OPER_20031110_SP1" TABL"
"ESPACE "TSP_HOP_D_11", SUBPARTITION "TB_HIS_OPER_20031110_SP2" TABLESPACE "
""TSP_HOP_D_11", SUBPARTITION "TB_HIS_OPER_20031110_SP3" TABLESPACE "TSP_HO"
"P_D_11", SUBPARTITION "TB_HIS_OPER_20031110_SP4" TABLESPACE "TSP_HOP_D_11""
", SUBPARTITION "TB_HIS_OPER_20031110_SP5" TABLESPACE "TSP_HOP_D_11", SUBPA"
"RTITION "TB_HIS_OPER_20031110_SP6" TABLESPACE "TSP_HOP_D_11", SUBPARTITION"
" "TB_HIS_OPER_20031110_SP7" TABLESPACE "TSP_HOP_D_11", SUBPARTITION "TB_HI"
"S_OPER_20031110_SP8" TABLESPACE "TSP_HOP_D_11", SUBPARTITION "TB_HIS_OPER_"
"20031110_SP9" TABLESPACE "TSP_HOP_D_11", SUBPARTITION "TB_HIS_OPER_2003111"
"0_SP10" TABLESPACE "TSP_HOP_D_11" ), PARTITION "TB_HIS_OPER_20031117" VALU"
"ES LESS THAN (TO_DATE(' 2003-11-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NL"
"S_CALENDAR=GREGORIAN')) PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "TSP_HOP_D_12" LOGGING (SUBPARTITION "TB_HIS_OPER_20031117_SP1" TABL"
"ESPACE "TSP_HOP_D_12", SUBPARTITION "TB_HIS_OPER_20031117_SP2" TABLESPACE "
""TSP_HOP_D_12", SUBPARTITION "TB_HIS_OPER_20031117_SP3" TABLESPACE "TSP_HO"
"P_D_12", SUBPARTITION "TB_HIS_OPER_20031117_SP4" TABLESPACE "TSP_HOP_D_12""
", SUBPARTITION "TB_HIS_OPER_20031117_SP5" TABLESPACE "TSP_HOP_D_12", SUBPA"
"RTITION "TB_HIS_OPER_20031117_SP6" TABLESPACE "TSP_HOP_D_12", SUBPARTITION"
" "TB_HIS_OPER_20031117_SP7" TABLESPACE "TSP_HOP_D_12", SUBPARTITION "TB_HI"
"S_OPER_20031117_SP8" TABLESPACE "TSP_HOP_D_12", SUBPARTITION "TB_HIS_OPER_"
"20031117_SP9" TABLESPACE "TSP_HOP_D_12", SUBPARTITION "TB_HIS_OPER_2003111"
"7_SP10" TABLESPACE "TSP_HOP_D_12" ), PARTITION "TB_HIS_OPER_20031124" VALU"
"ES LESS THAN (TO_DATE(' 2003-11-24 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NL"
"S_CALENDAR=GREGORIAN')) PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "TSP_HOP_D_13" LOGGING (SUBPARTITION "TB_HIS_OPER_20031124_SP1" TABL"
"ESPACE "TSP_HOP_D_13", SUBPARTITION "TB_HIS_OPER_20031124_SP2" TABLESPACE "
""TSP_HOP_D_13", SUBPARTITION "TB_HIS_OPER_20031124_SP3" TABLESPACE "TSP_HO"
"P_D_13", SUBPARTITION "TB_HIS_OPER_20031124_SP4" TABLESPACE "TSP_HOP_D_13""
", SUBPARTITION "TB_HIS_OPER_20031124_SP5" TABLESPACE "TSP_HOP_D_13", SUBPA"
"RTITION "TB_HIS_OPER_20031124_SP6" TABLESPACE "TSP_HOP_D_13", SUBPARTITION"
" "TB_HIS_OPER_20031124_SP7" TABLESPACE "TSP_HOP_D_13", SUBPARTITION "TB_HI"
"S_OPER_20031124_SP8" TABLESPACE "TSP_HOP_D_13", SUBPARTITION "TB_HIS_OPER_"
"20031124_SP9" TABLESPACE "TSP_HOP_D_13", SUBPARTITION "TB_HIS_OPER_2003112"
"4_SP10" TABLESPACE "TSP_HOP_D_13" ), PARTITION "TB_HIS_OPER_20031201" VALU"
"ES LESS THAN (TO_DATE(' 2003-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NL"
"S_CALENDAR=GREGORIAN')) PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "TSP_HOP_D_14" LOGGING (SUBPARTITION "TB_HIS_OPER_20031201_SP1" TABL"
"ESPACE "TSP_HOP_D_14", SUBPARTITION "TB_HIS_OPER_20031201_SP2" TABLESPACE "
""TSP_HOP_D_14", SUBPARTITION "TB_HIS_OPER_20031201_SP3" TABLESPACE "TSP_HO"
"P_D_14", SUBPARTITION "TB_HIS_OPER_20031201_SP4" TABLESPACE "TSP_HOP_D_14""
", SUBPARTITION "TB_HIS_OPER_20031201_SP5" TABLESPACE "TSP_HOP_D_14", SUBPA"
"RTITION "TB_HIS_OPER_20031201_SP6" TABLESPACE "TSP_HOP_D_14", SUBPARTITION"
" "TB_HIS_OPER_20031201_SP7" TABLESPACE "TSP_HOP_D_14", SUBPARTITION "TB_HI"
"S_OPER_20031201_SP8" TABLESPACE "TSP_HOP_D_14", SUBPARTITION "TB_HIS_OPER_"
"20031201_SP9" TABLESPACE "TSP_HOP_D_14", SUBPARTITION "TB_HIS_OPER_2003120"
"1_SP10" TABLESPACE "TSP_HOP_D_14" ), PARTITION "TB_HIS_OPER_20031208" VALU"
"ES LESS THAN (TO_DATE(' 2003-12-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NL"
"S_CALENDAR=GREGORIAN')) PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "TSP_HOP_D_15" LOGGING (SUBPARTITION "TB_HIS_OPER_20031208_SP1" TABL"
"ESPACE "TSP_HOP_D_15", SUBPARTITION "TB_HIS_OPER_20031208_SP2" TABLESPACE "
""TSP_HOP_D_15", SUBPARTITION "TB_HIS_OPER_20031208_SP3" TABLESPACE "TSP_HO"
"P_D_15", SUBPARTITION "TB_HIS_OPER_20031208_SP4" TABLESPACE "TSP_HOP_D_15""
", SUBPARTITION "TB_HIS_OPER_20031208_SP5" TABLESPACE "TSP_HOP_D_15", SUBPA"
"RTITION "TB_HIS_OPER_20031208_SP6" TABLESPACE "TSP_HOP_D_15", SUBPARTITION"
" "TB_HIS_OPER_20031208_SP7" TABLESPACE "TSP_HOP_D_15", SUBPARTITION "TB_HI"
"S_OPER_20031208_SP8" TABLESPACE "TSP_HOP_D_15", SUBPARTITION "TB_HIS_OPER_"
"20031208_SP9" TABLESPACE "TSP_HOP_D_15", SUBPARTITION "TB_HIS_OPER_2003120"
"8_SP10" TABLESPACE "TSP_HOP_D_15" ), PARTITION "TB_HIS_OPER_20031215" VALU"
"ES LESS THAN (TO_DATE(' 2003-12-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NL"
"S_CALENDAR=GREGORIAN')) PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "TSP_HOP_D_16" LOGGING (SUBPARTITION "TB_HIS_OPER_20031215_SP1" TABL"
"ESPACE "TSP_HOP_D_16", SUBPARTITION "TB_HIS_OPER_20031215_SP2" TABLESPACE "
""TSP_HOP_D_16", SUBPARTITION "TB_HIS_OPER_20031215_SP3" TABLESPACE "TSP_HO"
"P_D_16", SUBPARTITION "TB_HIS_OPER_20031215_SP4" TABLESPACE "TSP_HOP_D_16""
", SUBPARTITION "TB_HIS_OPER_20031215_SP5" TABLESPACE "TSP_HOP_D_16", SUBPA"
"RTITION "TB_HIS_OPER_20031215_SP6" TABLESPACE "TSP_HOP_D_16", SUBPARTITION"
" "TB_HIS_OPER_20031215_SP7" TABLESPACE "TSP_HOP_D_16", SUBPARTITION "TB_HI"
"S_OPER_20031215_SP8" TABLESPACE "TSP_HOP_D_16", SUBPARTITION "TB_HIS_OPER_"
"20031215_SP9" TABLESPACE "TSP_HOP_D_16", SUBPARTITION "TB_HIS_OPER_2003121"
"5_SP10" TABLESPACE "TSP_HOP_D_16" ), PARTITION "TB_HIS_OPER_20031222" VALU"
"ES LESS THAN (TO_DATE(' 2003-12-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NL"
"S_CALENDAR=GREGORIAN')) PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "TSP_HOP_D_17" LOGGING (SUBPARTITION "TB_HIS_OPER_20031222_SP1" TABL"
"ESPACE "TSP_HOP_D_17", SUBPARTITION "TB_HIS_OPER_20031222_SP2" TABLESPACE "
""TSP_HOP_D_17", SUBPARTITION "TB_HIS_OPER_20031222_SP3" TABLESPACE "TSP_HO"
"P_D_17", SUBPARTITION "TB_HIS_OPER_20031222_SP4" TABLESPACE "TSP_HOP_D_17""
", SUBPARTITION "TB_HIS_OPER_20031222_SP5" TABLESPACE "TSP_HOP_D_17", SUBPA"
"RTITION "TB_HIS_OPER_20031222_SP6" TABLESPACE "TSP_HOP_D_17", SUBPARTITION"
" "TB_HIS_OPER_20031222_SP7" TABLESPACE "TSP_HOP_D_17", SUBPARTITION "TB_HI"
"S_OPER_20031222_SP8" TABLESPACE "TSP_HOP_D_17", SUBPARTITION "TB_HIS_OPER_"
"20031222_SP9" TABLESPACE "TSP_HOP_D_17", SUBPARTITION "TB_HIS_OPER_2003122"
"2_SP10" TABLESPACE "TSP_HOP_D_17" ), PARTITION "TB_HIS_OPER_20031229" VALU"
"ES LESS THAN (TO_DATE(' 2003-12-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NL"
"S_CALENDAR=GREGORIAN')) PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "TSP_HOP_D_01" LOGGING (SUBPARTITION "TB_HIS_OPER_20031229_SP1" TABL"
"ESPACE "TSP_HOP_D_01", SUBPARTITION "TB_HIS_OPER_20031229_SP2" TABLESPACE "
""TSP_HOP_D_01", SUBPARTITION "TB_HIS_OPER_20031229_SP3" TABLESPACE "TSP_HO"
"P_D_01", SUBPARTITION "TB_HIS_OPER_20031229_SP4" TABLESPACE "TSP_HOP_D_01""
", SUBPARTITION "TB_HIS_OPER_20031229_SP5" TABLESPACE "TSP_HOP_D_01", SUBPA"
"RTITION "TB_HIS_OPER_20031229_SP6" TABLESPACE "TSP_HOP_D_01", SUBPARTITION"
" "TB_HIS_OPER_20031229_SP7" TABLESPACE "TSP_HOP_D_01", SUBPARTITION "TB_HI"
"S_OPER_20031229_SP8" TABLESPACE "TSP_HOP_D_01", SUBPARTITION "TB_HIS_OPER_"
"20031229_SP9" TABLESPACE "TSP_HOP_D_01", SUBPARTITION "TB_HIS_OPER_2003122"
"9_SP10" TABLESPACE "TSP_HOP_D_01" ), PARTITION "TB_HIS_OPER_20040105" VALU"
"ES LESS THAN (TO_DATE(' 2004-01-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NL"
"S_CALENDAR=GREGORIAN')) PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "TSP_HOP_D_02" LOGGING (SUBPARTITION "TB_HIS_OPER_20040105_SP1" TABL"
"ESPACE "TSP_HOP_D_02", SUBPARTITION "TB_HIS_OPER_20040105_SP2" TABLESPACE "
""TSP_HOP_D_02", SUBPARTITION "TB_HIS_OPER_20040105_SP3" TABLESPACE "TSP_HO"
"P_D_02", SUBPARTITION "TB_HIS_OPER_20040105_SP4" TABLESPACE "TSP_HOP_D_02""
", SUBPARTITION "TB_HIS_OPER_20040105_SP5" TABLESPACE "TSP_HOP_D_02", SUBPA"
"RTITION "TB_HIS_OPER_20040105_SP6" TABLESPACE "TSP_HOP_D_02", SUBPARTITION"
" "TB_HIS_OPER_20040105_SP7" TABLESPACE "TSP_HOP_D_02", SUBPARTITION "TB_HI"
"S_OPER_20040105_SP8" TABLESPACE "TSP_HOP_D_02", SUBPARTITION "TB_HIS_OPER_"
"20040105_SP9" TABLESPACE "TSP_HOP_D_02", SUBPARTITION "TB_HIS_OPER_2004010"
"5_SP10" TABLESPACE "TSP_HOP_D_02" ), PARTITION "TB_HIS_OPER_20040112" VALU"
"ES LESS THAN (TO_DATE(' 2004-01-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NL"
"S_CALENDAR=GREGORIAN')) PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "TSP_HOP_D_03" LOGGING (SUBPARTITION "TB_HIS_OPER_20040112_SP1" TABL"
"ESPACE "TSP_HOP_D_03", SUBPARTITION "TB_HIS_OPER_20040112_SP2" TABLESPACE "
""TSP_HOP_D_03", SUBPARTITION "TB_HIS_OPER_20040112_SP3" TABLESPACE "TSP_HO"
"P_D_03", SUBPARTITION "TB_HIS_OPER_20040112_SP4" TABLESPACE "TSP_HOP_D_03""
", SUBPARTITION "TB_HIS_OPER_20040112_SP5" TABLESPACE "TSP_HOP_D_03", SUBPA"
"RTITION "TB_HIS_OPER_20040112_SP6" TABLESPACE "TSP_HOP_D_03", SUBPARTITION"
" "TB_HIS_OPER_20040112_SP7" TABLESPACE "TSP_HOP_D_03", SUBPARTITION "TB_HI"
"S_OPER_20040112_SP8" TABLESPACE "TSP_HOP_D_03", SUBPARTITION "TB_HIS_OPER_"
"20040112_SP9" TABLESPACE "TSP_HOP_D_03", SUBPARTITION "TB_HIS_OPER_2004011"
"2_SP10" TABLESPACE "TSP_HOP_D_03" ), PARTITION "TB_HIS_OPER_20040119" VALU"
"ES LESS THAN (TO_DATE(' 2004-01-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NL"
"S_CALENDAR=GREGORIAN')) PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "TSP_HOP_D_04" LOGGING (SUBPARTITION "TB_HIS_OPER_20040119_SP1" TABL"
"ESPACE "TSP_HOP_D_04", SUBPARTITION "TB_HIS_OPER_20040119_SP2" TABLESPACE "
""TSP_HOP_D_04", SUBPARTITION "TB_HIS_OPER_20040119_SP3" TABLESPACE "TSP_HO"
"P_D_04", SUBPARTITION "TB_HIS_OPER_20040119_SP4" TABLESPACE "TSP_HOP_D_04""
", SUBPARTITION "TB_HIS_OPER_20040119_SP5" TABLESPACE "TSP_HOP_D_04", SUBPA"
"RTITION "TB_HIS_OPER_20040119_SP6" TABLESPACE "TSP_HOP_D_04", SUBPARTITION"
" "TB_HIS_OPER_20040119_SP7" TABLESPACE "TSP_HOP_D_04", SUBPARTITION "TB_HI"
"S_OPER_20040119_SP8" TABLESPACE "TSP_HOP_D_04", SUBPARTITION "TB_HIS_OPER_"
"20040119_SP9" TABLESPACE "TSP_HOP_D_04", SUBPARTITION "TB_HIS_OPER_2004011"
"9_SP10" TABLESPACE "TSP_HOP_D_04" ), PARTITION "TB_HIS_OPER_20040126" VALU"
"ES LESS THAN (TO_DATE(' 2004-01-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NL"
"S_CALENDAR=GREGORIAN')) PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "TSP_HOP_D_05" LOGGING (SUBPARTITION "TB_HIS_OPER_20040126_SP1" TABL"
"ESPACE "TSP_HOP_D_05", SUBPARTITION "TB_HIS_OPER_20040126_SP2" TABLESPACE "
""TSP_HOP_D_05", SUBPARTITION "TB_HIS_OPER_20040126_SP3" TABLESPACE "TSP_HO"
"P_D_05", SUBPARTITION "TB_HIS_OPER_20040126_SP4" TABLESPACE "TSP_HOP_D_05""
", SUBPARTITION "TB_HIS_OPER_20040126_SP5" TABLESPACE "TSP_HOP_D_05", SUBPA"
"RTITION "TB_HIS_OPER_20040126_SP6" TABLESPACE "TSP_HOP_D_05", SUBPARTITION"
" "TB_HIS_OPER_20040126_SP7" TABLESPACE "TSP_HOP_D_05", SUBPARTITION "TB_HI"
"S_OPER_20040126_SP8" TABLESPACE "TSP_HOP_D_05", SUBPARTITION "TB_HIS_OPER_"
"20040126_SP9" TABLESPACE "TSP_HOP_D_05", SUBPARTITION "TB_HIS_OPER_2004012"
"6_SP10" TABLESPACE "TSP_HOP_D_05" ), PARTITION "TB_HIS_OPER_20040202" VALU"
"ES LESS THAN (TO_DATE(' 2004-02-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NL"
"S_CALENDAR=GREGORIAN')) PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "TSP_HOP_D_06" LOGGING (SUBPARTITION "TB_HIS_OPER_20040202_SP1" TABL"
"ESPACE "TSP_HOP_D_06", SUBPARTITION "TB_HIS_OPER_20040202_SP2" TABLESPACE "
""TSP_HOP_D_06", SUBPARTITION "TB_HIS_OPER_20040202_SP3" TABLESPACE "TSP_HO"
"P_D_06", SUBPARTITION "TB_HIS_OPER_20040202_SP4" TABLESPACE "TSP_HOP_D_06""
", SUBPARTITION "TB_HIS_OPER_20040202_SP5" TABLESPACE "TSP_HOP_D_06", SUBPA"
"RTITION "TB_HIS_OPER_20040202_SP6" TABLESPACE "TSP_HOP_D_06", SUBPARTITION"
" "TB_HIS_OPER_20040202_SP7" TABLESPACE "TSP_HOP_D_06", SUBPARTITION "TB_HI"
"S_OPER_20040202_SP8" TABLESPACE "TSP_HOP_D_06", SUBPARTITION "TB_HIS_OPER_"
"20040202_SP9" TABLESPACE "TSP_HOP_D_06", SUBPARTITION "TB_HIS_OPER_2004020"
"2_SP10" TABLESPACE "TSP_HOP_D_06" ), PARTITION "TB_HIS_OPER_20040209" VALU"
"ES LESS THAN (TO_DATE(' 2004-02-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NL"
"S_CALENDAR=GREGORIAN')) PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "TSP_HOP_D_07" LOGGING (SUBPARTITION "TB_HIS_OPER_20040209_SP1" TABL"
"ESPACE "TSP_HOP_D_07", SUBPARTITION "TB_HIS_OPER_20040209_SP2" TABLESPACE "
""TSP_HOP_D_07", SUBPARTITION "TB_HIS_OPER_20040209_SP3" TABLESPACE "TSP_HO"
"P_D_07", SUBPARTITION "TB_HIS_OPER_20040209_SP4" TABLESPACE "TSP_HOP_D_07""
", SUBPARTITION "TB_HIS_OPER_20040209_SP5" TABLESPACE "TSP_HOP_D_07", SUBPA"
"RTITION "TB_HIS_OPER_20040209_SP6" TABLESPACE "TSP_HOP_D_07", SUBPARTITION"
" "TB_HIS_OPER_20040209_SP7" TABLESPACE "TSP_HOP_D_07", SUBPARTITION "TB_HI"
"S_OPER_20040209_SP8" TABLESPACE "TSP_HOP_D_07", SUBPARTITION "TB_HIS_OPER_"
"20040209_SP9" TABLESPACE "TSP_HOP_D_07", SUBPARTITION "TB_HIS_OPER_2004020"
"9_SP10" TABLESPACE "TSP_HOP_D_07" ) )"
IMP-00003: ORACLE error 922 encountered
ORA-00922: missing or invalid option
Import terminated successfully with warnings.


Thanks for your patience Tom.

The same version than the export: 8.1.7 (in fact we are testing on the same DB dropping the subpartioned table before the import, although the problem isn't because the object already existed).

Sorry Tom.

I haven't reported the problem right -meanwhile trying to solve this problem, I'm working with another DB version 8.1.7, and that took me to say it bad- but in my import problem both DB are 9.2.0 (the same DB)

1. Import log beginning ($more historico_imp.log)

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

Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing LOGAN3_ADMIN's objects into LOGAN3_ADMIN
IMP-00017: following statement failed with ORACLE error 922:
"CREATE TABLE "TB_HIS_OPER" ("SQ_HOP_01" NUMBER(10, 0) NOT NULL ENABLE, "CO_"
"MSDN_V" VARCHAR2(9) NOT NULL ENABLE, "CO_OPER_V" VARCHAR2(16) NOT NULL ENAB"
"LE, "DS_OPER_V" VARCHAR2(64) NOT NULL ENABLE, "FX_OPER_D" DATE NOT NULL ENA"
"BLE, "CO_USER_V" VARCHAR2(15), "CO_REQU_V" VARCHAR2(15) NOT NULL ENABLE, "D"
"S_REAS_V" VARCHAR2(64), "CO_TIP_PROD_V" VARCHAR2(8) NOT NULL ENABLE, "NO_CO"
"D_PROD_V" VARCHAR2(8) NOT NULL ENABLE, "DS_PROD_V" VARCHAR2(32), "SQ_PROD_V"
"" VARCHAR2(10) NOT NULL ENABLE, "DS_INI_PROD_V" VARCHAR2(64), "TO_QUAN_N" N"
"UMBER(10, 0), "FX_INI_VALI_D" DATE, "FX_FIN_VALI_D" DATE) PCTFREE 5 PCTUSE"
"D 60 INITRANS 1 MAXTRANS 255 TABLESPACE "TSP_HOP_D_01" LOGGING PARTITION BY"
" RANGE ("FX_OPER_D" ) SUBPARTITION BY HASH ("CO_MSDN_V" ) SUBPARTITIONS 1"
"0 SUBPARTITION TEMPLATE( SUBPARTITION "SP1" VALUES () , SUBPARTITION "SP2" "
"VALUES () , SUBPARTITION "SP3" VALUES () , SUBPARTITION "SP4" VALUES () , S"
"UBPARTITION "SP5" VALUES () , SUBPARTITION "SP6" VALUES () , SUBPARTITION ""
"SP7" VALUES () , SUBPARTITION "SP8" VALUES () , SUBPARTITION "SP9" VALUES ("
") , SUBPARTITION "SP10" VALUES () ) (PARTITION "TB_HIS_OPER_20031027" VALU"
"ES LESS THAN (TO_DATE(' 2003-10-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NL"
"S_CALENDAR=GREGORIAN')) PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "TSP_HOP_D_09" LOGGING (SUBPARTITION "TB_HIS_OPER_20031027_SP1" TABL"
"ESPACE "TSP_HOP_D_09", SUBPARTITION "TB_HIS_OPER_20031027_SP2" TABLESPACE "
""TSP_HOP_D_09", SUBPARTITION "TB_HIS_OPER_20031027_SP3" TABLESPACE "TSP_HO"
"P_D_09", SUBPARTITION "TB_HIS_OPER_20031027_SP4" TABLESPACE "TSP_HOP_D_09""
", SUBPARTITION "TB_HIS_OPER_20031027_SP5" TABLESPACE "TSP_HOP_D_09", SUBPA"
"RTITION "TB_HIS_OPER_20031027_SP6" TABLESPACE "TSP_HOP_D_09", SUBPARTITION"
" "TB_HIS_OPER_20031027_SP7" TABLESPACE "TSP_HOP_D_09", SUBPARTITION "TB_HI"
"S_OPER_20031027_SP8" TABLESPACE "TSP_HOP_D_09", SUBPARTITION "TB_HIS_OPER_"
"20031027_SP9" TABLESPACE "TSP_HOP_D_09", SUBPARTITION "TB_HIS_OPER_2003102"
"7_SP10" TABLESPACE "TSP_HOP_D_09" ), PARTITION "TB_HIS_OPER_20031103" VALU"
"ES LESS THAN (TO_DATE(' 2003-11-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NL"
"S_CALENDAR=GREGORIAN')) PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "TSP_HOP_D_10" LOGGING (SUBPARTITION "TB_HIS_OPER_20031103_SP1" TABL"
"ESPACE "TSP_HOP_D_10", SUBPARTITION "TB_HIS_OPER_20031103_SP2" TABLESPACE "
""TSP_HOP_D_10", SUBPARTITION "TB_HIS_OPER_20031103_SP3" TABLESPACE "TSP_HO"
"P_D_10", SUBPARTITION "TB_HIS_OPER_20031103_SP4" TABLESPACE "TSP_HOP_D_10""
", SUBPARTITION "TB_HIS_OPER_20031103_SP5" TABLESPACE "TSP_HOP_D_10", SUBPA"
"RTITION "TB_HIS_OPER_20031103_SP6" TABLESPACE "TSP_HOP_D_10", SUBPARTITION"
" "TB_HIS_OPER_20031103_SP7" TABLESPACE "TSP_HOP_D_10", SUBPARTITION "TB_HI"
"S_OPER_20031103_SP8" TABLESPACE "TSP_HOP_D_10", SUBPARTITION "TB_HIS_OPER_"

.......
.......
.......



2. Export log beginning (more historico_exp.log)

Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.3.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table TB_HIS_OPER
. . exporting composite partition TB_HIS_OPER_20031027
. . exporting subpartition TB_HIS_OPER_20031027_SP1
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031027_SP2
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031027_SP3
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031027_SP4
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031027_SP5
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031027_SP6
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031027_SP7
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031027_SP8
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031027_SP9
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031027_SP10
0 rows exported
. . exporting composite partition TB_HIS_OPER_20031103
. . exporting subpartition TB_HIS_OPER_20031103_SP1
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031103_SP2
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031103_SP3
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031103_SP4
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031103_SP5
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031103_SP6
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031103_SP7
0 rows exported
. . exporting subpartition TB_HIS_OPER_20031103_SP8
0 rows exported

.......
.......
.......

Is it possible to import only triggers from database.

no, you can however do this:


ops$tkyte@ORA920PC> select trigger_name from user_triggers where table_name = 'T';
 
no rows selected

 
ops$tkyte@ORA920PC> !imp userid=/ tables=t ignore=y grants=n indexes=n rows=n constraints=y
 
Import: Release 9.2.0.4.0 - Production on Mon Jan 26 09:44:42 2004
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
 
Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing OPS$TKYTE's objects into OPS$TKYTE
Import terminated successfully without warnings.
 
ops$tkyte@ORA920PC> select trigger_name from user_triggers where table_name = 'T';
 
TRIGGER_NAME
------------------------------
T_TRIGGER
 
1 row selected.
 
 

Tom,
We are doing a data center move which will take us from Windows to UNIX. I've been working on nailling doing the exp/imp to build the new instance in short time during
'go live' weekend. I grabbed a couple of really great suggestions from this thread.

Thanks

Steve

Hi,

We have a live 8i DB and wish to upgrade to 9i. We have a data warehouse setup, which entails populating fact tables each day. During the "trying all the new bells & whistles" stage, we wish to load the 9i DB with data from the 8i DB on a daily basis; the schemas in both DBs are almost identical, but some tables are partitioned in the 9i DB, and not so in the 8i DB. We cannot populate the 9i DB as we do the 8i DB, owing to various time and other constraints. Is it sane to populate the 9i DB from the 8i DB using INSERT INTO statements, or is there a better way to go?

Many thanks for the feedback.

It has been deemed by better folk than me to do the data loading on a daily basis, so that's that.

I think I'll see how effective it would be to load the 9i DB from the CSV files that the 8i DB is loaded from, then do something to see if they have same number of rows in their tables, etc.

Cheers!

Tom,
Even if I do not export indexes (indexes=n), the primary key indexes will still be created during import. Only the non-unique indexes will be skipped. Is this correct?
Thanks

Tom,

In an earlier response you said to do this

tom, what is the exact command to do <quote> 10046 trace on the import with
level 12 (get binds)<quote> in sqlplus. Thanks.


Followup:
if you install dbmssupp.sql in $ORACLE_HOME/rdbms/admin

exec dbms_support.start_trace_in_session( sid=> NN, serial => NN );

will do it. start the import, query v$session to get the sid/serial

MY Question is -
I am doing an import on a Linux Server ( ORacle 9.2.0.3.0)
ps -ef | grep imp
oracle 16166 16100 94 10:56 pts/1 04:20:05 imp
oracle 16253 16203 93 11:05 pts/0 04:08:55 imp
oracle 17635 16812 0 15:31 pts/3 00:00:00 grep imp

but when I connect to the database and try to get sid, serial# so that I can use the above procedure , it does not show me the sid , serial# for the import. I am confused ?

Thanks

Oracle Database - 9.2.0.3.0

I am doing an export / import , my export file is 17066688512MB . Since its a full export .

I am doing an import for only certain tablespaces . The import runs for around 12 hours , there is no change in the datafile size. And no errors for the import . What could be the problem

Tom,

Got to know that the imp was scanning the dmp file .

Tom,

As I discussed above that the import was taking a long time , after that we got the IMP-00008 Error from 2-3 different export files for different days .
We are taking 2 exports a) Full direct =Y - we get the
IMP-00008 Errors. Metalink says to increase the buffer size and we did that but we still get the error.

b) Users - , Direct = N , Here we are not getting the IMP-00008 Error.

The Error is occurring on one table which has approx 90,000 rows and has blob, clob data. Though there are other tables with blob , clob data

My question is that is direct=N is the problem ???

Tom,

The Oracle versions are 9.2.0.3.0
and the error was IMP-00008 - Unrecognizable character in export problem.

We solved the problem by not using Direct=N. Since we have a lot of images in our data ( blob , clob fields ) , I think by using direct=Y somehow corrupts the data . Now we are able to import the data without any hassle

Thanks

Dear Mr.Tom,

I have a schema with over 200 tables with ditionary managed tablespace. Amoung 200 tables 20 over tables are big in size ( 400meg to 2 GB).I need to import the same schema structure to new schema with locally manged tablespace. I can do it from your method get the indexfile and edit the index file like that. Is any other way to do that? That is I don't want the existing schema initial, next values.

with best regards
S.Priyadarshan

Thank you very much for your information. I have exported dmp file only. Now I can not do it in source schema. Is any other method to overwrite the storage clause?

With best regards
S.Priyadarshan

If I use while imp, buffer=100000000 and commit=n and large rollback segments, calculated as about 71 bytes/row inserted, the import performance will increase.

Is this increase in perormance due only to less commits
in log_buffer or is there any other rational behind the
increase speed of the import

Thanks

I do exp from 817 to 9.2 50 GB of data
It's application upgrade and whole DB must be 9i. on new machine
So my plan is : disable all constraints on application schema, put DB in restrict mode then
a) exp full=y direct=y rows=y GRANTS=y compress=n constraints=y buffer=100000000
b) imp in 2 stages first just : full=y, rows=n
- imp to indexfile create_idx.sql (to create indexes)
c) this will give me a chance to organize tables into new tablespaces, will create all users with grants.
d) I plan to run import for one main application user (98% of data) into 4 streams , cause I've got 4 CPU's, for tables only, then
d) import in 4 streams those tables with indexes=n
e) in another streams the remaining users
f) create indexes from indexfile
g) enable all constraints
My questions:
1) is imp full=y with rows=n create all users with all grants
2) should I run exp/imp in more streams than 4 ?
3) would make any difference to imp with indexes=y or create them from indexfile with nologging parallel ?
4) other obvious helplfull hint to improve the speed, it is windows system, and normal standard import takes 30 hours.

From Documentation:

BUFFER
Default: operating system-dependent

The integer specified for BUFFER is the size, in bytes, of the buffer through which data rows are transferred.

BUFFER determines the number of rows in the array inserted by Import. The following formula gives an approximation of the buffer size that inserts a given array of rows:

buffer_size = rows_in_array * maximum_row_size

Q:
Is there a limit on the buffer size and is it allocated
from PGA

Dear Tom,


I'm trying your solution to do one export for each and every tables so that i can do this in parallel. My questions is after exporting every tables (and associated indexes) how can i export the views, stored procedures... everything else but tables.


Best regards,

Laly.

Thanks Tom, but i finally used exp with rows=n since I want everything except table, it is simplier

Can you please tell me what you think of this strategy to duplicate a schema on another database (we suppose that nobody else is modifying the schema while we are exporting) :
1a. export with direct=y rows=y table=... for each and every table in parallel
1b. import with indexfile=... to get indexes to create : modifying this script to increase sort_area_size and to create index in nologging mode and with parallel clause
2. export with direct=y rows=n

On the target database :
- import the dump got in step 2
- import in parallel every individual table dump file
- run in parallel every create index script.

Can you see any flaw in this ?


Thanks,

Laly.

Thanks a lot for your answer, i missed that constraint point.

I will give a try to transportable tablespace.


Best regards,

Laly.

Tom,

I have exported some tables from a database where all the tables belonged to TS01 tablespace. Now I am importing these tables into a different database schema whose default tablespace is RPT001. However this target database also has a tablespace TS01 but for another schema. So when I start the imports, it attempts to create the tables in this TS01 ts rather than RPT001 and then the import fails due to insufficient space or not able to create enough segments.

My question is how can I export/import so that these tables are created in the RPT001 tablespace only.

Thanks for all the help that you have been providing to us.

Regards.

Should we set commit =y or create a rollback segment large enough as written in Tuning Considerations when import is slow Note 93763.1 because in automatic undo mode I am not able to create any rollback segment.
I need to export/import tables, each of 10-15GB
My database is in archive mode after setting to nonarechive mode
I am using
exp userid = system tables = tag_data indexes= n file=d:\tag_data.dmp log = d:\tag_data.log compress = n

imp uerid= testuser tables = tag_data indexes = n file=d:\tag_data.dmp log = d:\tag_dataimp.log fromuser=system touser=testuser commit = y

Dear Sir
I have done an export as follow in my Server
EXP TEST/TEST FILE=GOODS.DMP
And the file has been created successfully
My oracle version is
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 &#8211; Production

But when I try to import the file GOODS.DMP
It display this message
Export file created by export :V09.00.01 via conventional path
Import done in we8mswin1252 character set and AL16UTF16 NCHAR character set
Import server uses AR8ISO8859P6 character set < possible character conversion >
Imp-00031: Must specify Full=y or provide fromuser/touser or tables arguments
Imp-00000: Import terminated unsuccessfully
Can you give me a proper use of Import statement, in details?
Best Regards
Ali

i

Hi

I am importing a oracle 8i dump file into a oracle 9i itanium database.

I am lookign to speed up my import process. I was wondering if import analyze the tables using dbms_stats?

I did a test (all in 9i), created a table with 20 rows then set num_rows to 5 with dbms_stats, exported the table, drop the table and import back. num_rows still shows 5 so I think import does NOT run analyze as far as this test concerns.

However in my production import I constantly see these messages (using TOAD)

ANALYZE TABLE "D2G_AOPPV_TMP_00" ESTIMATE STATISTICS;
ANALYZE TABLE "EPC_99" ESTIMATE STATISTICS

etc etc

I also see import issues full scan queries against the tables. So I am confused, in my production it does seem import analyze table. So what is the reality?

Thank you

Hi

This is the log message in the import session where I see ANALYZE TABLE messages


Export file created by EXPORT:V08.01.07 via conventional path

Warning: the objects were exported by SYS, not by you
import done in WE8ISO8859P1 character set and UTF8 NCHAR character set
export server uses WE8ISO8859P1 NCHAR character set (possible ncharset conversion)

NCHAR is different, does that affect statistics parameter?

In my test session the charcater sets are the same

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

Export file created by EXPORT:V09.02.00 via direct path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

The difference between the dump files is one is exported using 8.1.7 exp and the other 9.2.0.6 (the test session)

Hi

Is above statement written anywhere?
So are you saying that when the character sets are different (NLS_LANG not NLS_DATABASE) import runs analyze and when the character set are the same import doesnt do that?

Cheers

Tom,

I use to do import like this..! for existing tables and indexes..

1.Truncate the tables which need to be import.
2.import with
userid='...'
fromuser=
touser=
tables=*
file='...'
log=
ignore=y
buffer=10000000
statistics=safe
feedback=50000
indexes=n
constraints=n

Hence table would be import without indexes and constraints.
Ofcourse, INDEXFILE option is there,but i feel thats good for fresh table and index importing..

In this,Once table imported, get into OBJECT BROWSER (like E R WIN)just by right clicking i use to compile(rebulid) all indexes.

Q)Is this good way to continue to reduce time or using INDEXFILE (to create index and constraint)is better than this?

Hi

Last week I was attending a 9i new features course at Oracle University. During one of lessons about Backups someone rose a question regarding export direct=y, the lecturer from Oracle said that export direct=y is faster but the import is much much slower, the reason is since export using direct bypass the buffer and blocks are dumped directly to the dump file when import these blocks need to be rebuilt in the buffer before import can read it. Is this true???

Hi,
I am working on Oracle 9.2.0.6. I am getting this error "ORA-37002 OLAP failed to initialize. Contact Oracle Support" and "ORA-00600 Error". I get this error, while export is running (this is a scheduled process, which is run daily). Whenever I restart the database, then this problem is not raised for some days, but, again resurfaces. My OLAP_PAGE_POOL_SIZE is 32 MB. Pls. advice as to what needs to be done to get rid of this problem once and for all ? And, will this result in any performance issue ? Or will this effect the database, since, mine is an 24 * 7 db.
Thanks in Advance,
Ram

Tom:
I am just wondering why any one want to take the export with consistent=N? Oracle can force us to use consistent=Y by undocumenting this parameter right?

Thanks and Regards,
Satheesh Babu.S

Hi

I am creating the indexes after importing the data. I am monitoring memory usage by these index creation processes. I am interested in what happens to uga and pga.
I did this with 1 minute interval

select a.sid, name, value
from v$session a, v$sesstat b, v$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and a.username = 'WIX_ADMIN'
and c.name like 'sess%ga%'
order by a.sid, name



SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
8 session pga memory 8003392
8 session pga memory max 8003392
8 session uga memory 7470152
8 session uga memory max 7470152
13 session pga memory 4435504
13 session pga memory max 5549616
13 session uga memory 2242792
13 session uga memory max 2242792
14 session pga memory 4107824
14 session pga memory max 5221936
14 session uga memory 2242792
14 session uga memory max 2242792
15 session pga memory 510712
15 session pga memory max 175950584
15 session uga memory 209864
15 session uga memory max 209864
17 session pga memory 518568
17 session pga memory max 175958440
17 session uga memory 209864
17 session uga memory max 209864


SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
8 session pga memory 8068928
8 session pga memory max 8068928
8 session uga memory 7535560
8 session uga memory max 7535560
13 session pga memory 5549616
13 session pga memory max 5549616
13 session uga memory 3291960
13 session uga memory max 3291960
14 session pga memory 5221936
14 session pga memory max 5221936
14 session uga memory 3291960
14 session uga memory max 3291960
15 session pga memory 175950584
15 session pga memory max 175950584
15 session uga memory 162029256
15 session uga memory max 162029256
17 session pga memory 175958440
17 session pga memory max 175958440
17 session uga memory 162029256
17 session uga memory max 162029256

SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
8 session pga memory 8068928
8 session pga memory max 8068928
8 session uga memory 7535560
8 session uga memory max 7535560
13 session pga memory 2207280
13 session pga memory max 5549616
13 session uga memory 144456
13 session uga memory max 3291960
14 session pga memory 1879600
14 session pga memory max 5221936
14 session uga memory 144456
14 session uga memory max 3291960
15 session pga memory 38587128
15 session pga memory max 175950584
15 session uga memory 35030888
15 session uga memory max 162029256
17 session pga memory 35252648
17 session pga memory max 175958440
17 session uga memory 31883384
17 session uga memory max 162029256


The memory goes from low to high then high to low, the memory freed are returned to OS?

Hi

I am using sort area not pga, in that case is that memory freed to OS?

Hi

My server is 8.1.7 so I cannot use automatic PGA, I understand that in 9i these memory are reused but in 8i I dont think so correct? If that is the case why the memory doesnt stay in their maxumu size? Why gores down and up, down and up

Kinda confused with compress=Y with LMT on 9.2.

I created a table from dba_objects and then exported that table. Prior to export, the table had 19 (16 - 64k and 3 - 1M) extents. I did an export of just that table and used the default for compress (compress = Y).

I then did an import with indexfile set and looked at the file created. It has a create table statement with initial extent set to 64k. I was expecting to see the create table statement with an initial extent set at 4M based on the theory that compress = Y will add up the existing extents for the object?

You are a genius, TOM. Solved all my problems. I was able to complete the imp very qucikly.

Hi,TOM

The IMP is based on INSERT statment.

Is there a way to avoid IMP to produce UNDO and REDO in oracle9i?

Thanks

To your last poster you said there is no way to avoid UNDO and REDO on an import. That is not entirely true. You CAN avoid (most) redo with the undocumented parameter "_disable_logging". Obviously this is very, very dangerous and in general should never be used (and in fact is the source of a significant bug in 10g). However, I execute numerous migrations for my clients from one platform to another, and downtime is a major consideration. I frequently use this technique (followed IMMEDIATELY by a full database backup) to improve the performance of the migration. I've found it shaves at least 20% off the time of the import, and coupled with many other techniques (nologging/unrecoverable on indexes, parallel, pre-migrating historical data, turning off archiving if on same disks as other things, sort_area/PGA tuning, etc.), I can migrate most systems with a minimum of "hard" downtime. I think it is simply one trick in the bag to be used as needed, but ONLY when absolutely needed.

P.S. One minor but often over looked tip - sometimes it helps to have a tiny data cache (db_block_buffers/db_cache_size) on an import. It's not a major boost, but it helps when importing a LOT of rows that aren't particularly big, because there is less overhead to maintain the cache. There is a "sweet spot" that you have to find - if you can make your cache bigger than your dump file, then you don't need this trick, but otherwise I find that a data cache <48Mb can actually help vs. a large cache. I have never done hard tests - I'd be curious to hear of anyone that has.

Yes, I completely agree - this is only for creating a database, never for "casual" use. But I have seen it (by itself) give a major boost, and it completely avoids "checkpoint not complete" problems (I know, these are avoidable other ways, but some customers don't have the best hardware).

Hi Tom,
We have production and UAT database. UAT database is not uptodate. Because our testing team needs to do some tests.

When they request to update the UAT database, then we update as follows:

(1). Delete schema objects in UAT
(2). Import latest dump from PROD into UAT

This will take bit long time. So could you please advice us much professional way to do this?

Thanks

Tom,

The ironic part is to cut some quarters lots of business build uat boxes much smaller(configuration/storage wise) than the prod.

Thanks.

Hi Tom,

I've a biggish export dump file having hundreds of tables/indexes/views & packages.
Now what I want is to import ONLY packages and not anything else.
Is it possible to do with imp command?

Thanks aas always.

Tom,

When I use an index file the CREATE TABLE, ADD CONSTRAINT, and ENABLE CONSTRAINT commands are all REM'd out.

Is it possible to have imp not REM the constraint commands? Or do I need to process the file myself to unREM these lines?

My Parfile:
File=../pmmtest1.dmp, ../pmmtest2.dmp
Filesize=4096MB
Log=pmmtest_ind.imp.log
FromUser=('PMM', 'PPR')
ToUser=('PMM', 'PPR')
Indexes=Y
Constraints=Y
IndexFile=pmmtest_index.sql

Thanks!

Thanks tom....but i would like to know something...i am using a Oracle 734 ver on SCO Unix 5.0.4....the server having Raid5 architecture.....import will be slow but is there any other options to make it faster by changing the kernel parameters as well as the initsid.ora and redologs?

Dear Mr.Tom
I have problem with import.
Export is from 9.2.0.6
(exp xxx/xxx@xx FILE=$DUMP/yyy.dmp LOG=$DUMP/yyy.log OWNER=xxx CONSISTENT=Y BUFFER=5000000)

But in import in other DB 9.2.0.8

imp xxx/xxx@xx FILE=yyy.dmp LOG=yyy.log BUFFER=5000000 FROMUSER=xxx TOUSER=xxx COMMIT=Y ANALYZE=Y

There is the problem:
IMP-00020: Long column too large for column buffer size (7)

It is same for BUFFER=1000,10000,50000,500000000, only in the error message is various (num).. 7,22... and other.


What does the number in error message mean ?

Hi Tom,

I am importing into 10g database from Oracle9i. It worked very well when I ran it first time, but few hours later it gave archiever error. I restarted the Oracle 10g database and now when I am running again, it is not going further
The import screen looks like

Import: Release 9.2.0.7.0 - Production on Thu May 1 22:57:10 2008

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

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

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

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)

****************************
And then it is stopping for unlimited time. On looking at currently executed command from session browse I saw

BEGIN
SYS.dbms_logrep_imp.set_import_mode (:1);
END;

Do you have any idea what could be the reason ?

Regards
Shrikant

Hi sir,

Regarding compress=y
Say existing table has 50m allocated to it.
I know that, If we import into a LMT with 1meg extents, it'll allocate 50 of 1meg extents initially and do the inserts.

If we set compress=n
It will
allocate 1mb
insert
allocate 1mb
insert
and so on.

Is that right?

ops$tkyte%ORA11GR2> create table t1 ( x int ) storage ( initial 10m );

Table created.

ops$tkyte%ORA11GR2> create table t2 ( x int );

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select segment_name, bytes from user_segments where segment_name in ('T1', 'T2') and segment_type = 'TABLE';

no rows selected

ops$tkyte%ORA11GR2> insert into t1 values ( 1 );

1 row created.

ops$tkyte%ORA11GR2> insert into t2 values ( 1 );

1 row created.

ops$tkyte%ORA11GR2> select segment_name, bytes from user_segments where segment_name in ('T1', 'T2') and segment_type = 'TABLE';

SEGMENT_NAME                        BYTES
------------------------------ ----------
T1                               10485760
T2                                  65536

thanks sir,

1-)
Regarding compress=y,
Is 2gb the limit?
Even if the table size is bigger than 2gb, it would only allocate 2gb as an initial extent.
Is that right?


2-) Does compress=y perform faster since it doesnt have to extent the table every time?

Hi Tom,
Thanks for your explaination on compress parameter of export,but i have certain questions

1> what if I use compress=y during export and then import it,as here the initial extent will be of very large size ,what performance issues might generate out of this.

2>you commented not to use conventional export any more,any specific reason for the same

3>I want to see the content of the export dump,is it possible any way?

4>once in your comment you have mentioned to use alter table move to rebuild tables,but at our place there is one table of 300gb size, so what about using expdp for rebuilding the same as time would be constraint

5>what is the role of PCTFREE during import?

I am exporting a 7 GB schema/dump file from 9i into 11g.

9i> exp myapp/myapp@tips file=/pics/workarea/tips_exp.dmp log=/myapp/workarea/tips_exp.log compress=N direct=Y

11g> imp myapp/myapp@tips file=/home/oracle/dumps/tips_exp.dmp full=y log=/home/oracle/dumps/tips_imp.log

The import is taking a very long time due to one TABLE that has 5 million records.

Sort area size is 65537.

I noticed there is a lot of redo generation in the trace files.

Is there a way to speed this up and turn the redo off?

how do you run exp/imp in parallel instead of table by table.

shall i do this before import or set INDEXFILE=N and run indexes separately.

ALTER SESSION SET sort_area_size=100000000;

Tom:

LOG_MODE=NOARCHIVELOG (no archival) so maybe I was wrong about generation of redo records in the logs with imports.

It is doing 10,000 rows per minute so for 5,000,000 it will be 500 minutes (about 8 hours).

The table has a couple of CLOB columns.

This is an archive table and i dont need it. Is there a way to *EXCLUDE* this table when i import the dump file or do i need to recreate the export dump based on tables only. I want everything else in the dump though (packages, triggers, sequences) except this table. pleas advise.



TABLE_NAME ROWS_PROCESSED MINUTES ROWS_PER_MIN
------------------------------ -------------- ---------- ------------
INTO "ARCH_MESG_LOG" ("M 3379760 320.9 10531

1 row selected.

Name Null? Type
----------------------------------------- -------- ----------------------------
MESG _NO NOT NULL NUMBER(10)
DATE_IN DATE
DATE_OUT DATE
PROCEDURE_NAME VARCHAR2(30)
MESG_RECV CLOB
MESG_SENT CLOB
PKG_NAME VARCHAR2(30)
CREATED_DATE DATE

ops$tkyte%ORA11GR2> create table t1 as select * from all_users;

Table created.

ops$tkyte%ORA11GR2> create table t2 as select * from all_users;

Table created.

ops$tkyte%ORA11GR2> create table t3 as select * from all_users;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> !exp userid=/ 'owner=ops$tkyte'

Export: Release 11.2.0.3.0 - Production on Wed May 23 08:49:55 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user OPS$TKYTE 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user OPS$TKYTE 
About to export OPS$TKYTE's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export OPS$TKYTE's tables via Conventional Path ...
. . exporting table                             T1         50 rows exported
. . exporting table                             T2         50 rows exported
. . exporting table                             T3         50 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> drop table t1 purge;

Table dropped.

ops$tkyte%ORA11GR2> drop table t2 purge;

Table dropped.

ops$tkyte%ORA11GR2> drop table t3 purge;

Table dropped.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> !imp userid=/ tables=t2 rows=N

Import: Release 11.2.0.3.0 - Production on Wed May 23 08:49:55 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing OPS$TKYTE's objects into OPS$TKYTE
. importing OPS$TKYTE's objects into OPS$TKYTE
Import terminated successfully without warnings.

ops$tkyte%ORA11GR2> !imp userid=/ full=y IGNORE=N

Import: Release 11.2.0.3.0 - Production on Wed May 23 08:49:55 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing OPS$TKYTE's objects into OPS$TKYTE
. . importing table                           "T1"         50 rows imported
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "T2" ("USERNAME" VARCHAR2(30) NOT NULL ENABLE, "USER_ID" NUMBE"
 "R NOT NULL ENABLE, "CREATED" DATE NOT NULL ENABLE)  PCTFREE 10 PCTUSED 40 I"
 "NITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREE"
 "LISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                    LOGGING N"
 "OCOMPRESS"
. . importing table                           "T3"         50 rows imported
Import terminated successfully with warnings.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select count(*) from t1;

  COUNT(*)
----------
        50

ops$tkyte%ORA11GR2> select count(*) from t2;

  COUNT(*)
----------
         0

ops$tkyte%ORA11GR2> select count(*) from t3;

  COUNT(*)
----------
        50

ops$tkyte%ORA11GR2> desc t2
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 USERNAME                                 NOT NULL VARCHAR2(30)
 USER_ID                                  NOT NULL NUMBER
 CREATED                                  NOT NULL DATE

<<no, you weren't wrong, the inserts will all generate redo. >>

Why is it generating redo data if the archivelogmode is OFF?
Is there a wayt to turn this off completely to speed up the import.


<<!imp userid=/ full=y IGNORE=N>>

I originally thought if the table structure exists it will ignore it but continue to load data.
Was that with "ignore=Y"? ignore=N will not even load data if table exists?


You provided great example.

I assume nothing special can be done via ex/imp to speed up the CLOBS.

Tom:

You are certainly the "Master of Tricks".

I used that trick and it cut the import time to 30 minutes without any issues.

I will try the other trick later for slicing the table with CLOBS to several export dumps (using sequence or TIMESTAMP) and then importing all dumps in parallel.

I am kind of surprised that oracle has not done anything special to speed up logical imports such as TURN OFF LOGGING before running the import since they do have it for bulk loads and CTAS operations.

I heard also that Data Pump tools are 30% faster than the traditional imp/exp tools so when I will use that when going from 11g DB -->> 11g DB to verify that. I can't use it now with 9i.