you cannot use export and import in this case.

you'll need to

a) install the 817 client on the machine hosting 9i
b) use that export tool over sqlnet on that machine to create the dmp file

No I don't -- but it shouldn't be too hard to generate.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:612348047 <code>

1) it'll create them. the exp dmp file contains create tablespace statements.

but, that would be the worst of the worst ways to "backup". Please -- use BACKUPS for BACKUPS. exports are *not* a good way (i would go as far to say they are a terrible way) to backup a database. only a backup -- data file copies -- are good for backups.


2) use v9.0 export against v9.0. use the version of the tool for that database.

you cannot really, first it is deprecated and second export would have exported any table that had a single row changed. it exported all tables that had any modifiction, the entire table, all of it.

export is not a database backup tool.

export is a data movement tool, move data from here to there.

BACKUPS are the only backup tools

eh?

deprecated means "the incremental export feature is deprecated"


If you want to read about it, it is documented still in the Utilities guide, but it is not a useful tool for anything

(do you have access to my first book Expert one on one Oracle? I cover this in great detail in the chapter on exp/imp)


I'll have to assume (guess, hypothesize, due to lack of information) that the objects that fail are MULTI-SEGMENT objects (tables with clobs, partitioned tables, IOT's with overflow segments for example)...

imp ... SHOW=Y will just show you what the import WOULD do (not actually do it). No, you will not see passwords this way - that would be a problem.


There is no real relationship between exp/imp and tablespaces/databases -- exp creates a LOGICAL COPY of data with CREATE statements in. imp reads this logical copy and attempts to run the CREATES and do the INSERTS -- just like a script.


<quote src=expert one on one oracle>
Multi-Tablespace CREATES

In the beginning, CREATE TABLE statements were relatively simple. Over the years, they have gotten progressively more and more complex. The 'train tracks' or 'wire diagram' for the simple CREATE TABLE now spans eight pages. One of the newer features of tables is the ability for bits and pieces of them to exist in various tablespaces. For example, a table with a CLOB column will have a table segment, CLOB index segment, and CLOB data segment. We can specify the location of the table and the locations of the CLOB data. An index-organized table (IOT) can have the index segment, and an overflow segment. Partitioned tables of course may have many partitions, each in a separately specified tablespace.

With this complexity comes some confusion for EXP/IMP. It used to be that if you tried to import an object and it failed due to the tablespace either not existing, or because you exceeded your quota on that tablespace, IMP would rewrite the SQL for you to create the object in your DEFAULT tablespace. IMP will not do this with multi-tablespace objects like it will with single tablespace object, even if all of the tablespaces specified in the CREATE are the same. An example will demonstrate the problem, and then I'll describe how we can work around the situation.

First, we'll start with a schema that has a couple of multi-tablespace objects and a simple single tablespace table in a tablespace:

tkyte@TKYTE816> create tablespace exp_test
2 datafile 'c:\oracle\oradata\tkyte816\exp_test.dbf'
3 size 1m
4 extent management local
5 uniform size 64k
6 /
Tablespace created.

tkyte@TKYTE816> alter user tkyte default tablespace exp_test
2 /
User altered.

tkyte@TKYTE816> create table t1
2 ( x int primary key, y varchar2(25) )
3 organization index
4 overflow tablespace exp_test
5 /
Table created.

tkyte@TKYTE816> create table t2
2 ( x int, y clob )
3 /
Table created.

tkyte@TKYTE816> create table t3
2 ( x int,
3 a int default to_char(sysdate,'d')
4 )
5 PARTITION BY RANGE (a)
6 (
7 PARTITION part_1 VALUES LESS THAN(2),
8 PARTITION part_2 VALUES LESS THAN(3),
9 PARTITION part_3 VALUES LESS THAN(4),
10 PARTITION part_4 VALUES LESS THAN(5),
11 PARTITION part_5 VALUES LESS THAN(6),
12 PARTITION part_6 VALUES LESS THAN(7),
13 PARTITION part_7 VALUES LESS THAN(8)
14 )
15 /
Table created.

tkyte@TKYTE816> create table t4 ( x int )
2 /
Table created.

So, we started by creating a tablespace and making this our DEFAULT tablespace. We then created an IOT with two segments - the index and overflow. We created a table with a CLOB that has three segments. Then we have a partitioned table with seven segments. Lastly, we have the normal, simple 'table'. We export this schema:

tkyte@TKYTE816> host exp userid=tkyte/tkyte owner=tkyte

and proceed to drop that tablespace:

tkyte@TKYTE816> drop tablespace exp_test including contents;
Tablespace dropped.

tkyte@TKYTE816> alter user tkyte default tablespace data;
User altered.

When we import the schema we discover most of the tables won't come back in:

tkyte@TKYTE816> host imp userid=tkyte/tkyte full=y

Import: Release 8.1.6.0.0 - Production on Tue Mar 20 19:03:18 2001

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


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

Export file created by EXPORT:V08.01.06 via conventional path
import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
. importing TKYTE's objects into TKYTE
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "T2" ("X" NUMBER(*,0), "Y" CLOB) PCTFREE 10 PCTUSED 40 INITRA"
"NS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 65536) TABLESPACE "EXP_TEST" LOB "
"("Y") STORE AS (TABLESPACE "EXP_TEST" ENABLE STORAGE IN ROW CHUNK 8192 PCT"
"VERSION 10 NOCACHE STORAGE(INITIAL 65536))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'EXP_TEST' does not exist
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "T3" ("X" NUMBER(*,0), "A" NUMBER(*,0)) PCTFREE 10 PCTUSED 40"
" INITRANS 1 MAXTRANS 255 LOGGING TABLESPACE "EXP_TEST" PARTITION BY RANGE ("
""A" ) (PARTITION "PART_1" VALUES LESS THAN (2) PCTFREE 10 PCTUSED 40 INIT"
"RANS 1 MAXTRANS 255 STORAGE(INITIAL 65536) TABLESPACE "EXP_TEST" LOGGING, P"
"ARTITION "PART_2" VALUES LESS THAN (3) PCTFREE 10 PCTUSED 40 INITRANS 1 MA"
"XTRANS 255 STORAGE(INITIAL 65536) TABLESPACE "EXP_TEST" LOGGING, PARTITION "
""PART_3" VALUES LESS THAN (4) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 25"
"5 STORAGE(INITIAL 65536) TABLESPACE "EXP_TEST" LOGGING, PARTITION "PART_4" "
"VALUES LESS THAN (5) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE"
"(INITIAL 65536) TABLESPACE "EXP_TEST" LOGGING, PARTITION "PART_5" VALUES LE"
"SS THAN (6) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL "
"65536) TABLESPACE "EXP_TEST" LOGGING, PARTITION "PART_6" VALUES LESS THAN ("
"7) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536) TA"
"BLESPACE "EXP_TEST" LOGGING, PARTITION "PART_7" VALUES LESS THAN (8) PCTFR"
"EE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536) TABLESPACE "
""EXP_TEST" LOGGING )"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'EXP_TEST' does not exist
. . importing table "T4" 0 rows imported
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "T1" ("X" NUMBER(*,0), "Y" VARCHAR2(25), PRIMARY KEY ("X") EN"
"ABLE) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOG"
"GING STORAGE(INITIAL 65536) TABLESPACE "EXP_TEST" PCTTHRESHOLD 50 OVERFLOW "
"PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 6553"
"6) TABLESPACE "EXP_TEST""
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'EXP_TEST' does not exist
Import terminated successfully with warnings.

Specifically, the only table that came back in without an error was the simple 'normal' table. For this table, IMP rewrote the SQL, blanking out the first TABLESPACE EXP_TEST that it came across and retried the CREATE. This rewritten CREATE succeeded. The other CREATES, when likewise rewritten, did not succeed. The only solution to this is to create the tables beforehand and then import with IGNORE=Y. If you do not have the DDL for the CREATE TABLES, you can retrieve it of course, from the DMP file with INDEXFILE=Y to modify it. In this case, since I had the DDL readily handy, I just created the three tables with new tablespaces specified where necessary:

tkyte@TKYTE816> create table t1
2 ( x int primary key, y varchar2(25) )
3 organization index
4 overflow tablespace data
5 /
Table created.

tkyte@TKYTE816> create table t2
2 ( x int, y clob )
3 /
Table created.

tkyte@TKYTE816> create table t3
2 ( x int,
3 a int default to_char(sysdate,'d')
4 )
5 PARTITION BY RANGE (a)
6 (
7 PARTITION part_1 VALUES LESS THAN(2),
8 PARTITION part_2 VALUES LESS THAN(3),
9 PARTITION part_3 VALUES LESS THAN(4),
10 PARTITION part_4 VALUES LESS THAN(5),
11 PARTITION part_5 VALUES LESS THAN(6),
12 PARTITION part_6 VALUES LESS THAN(7),
13 PARTITION part_7 VALUES LESS THAN(8)
14 )
15 /
Table created.

and was able to import cleanly:

tkyte@TKYTE816> host imp userid=tkyte/tkyte full=y ignore=y

Import: Release 8.1.6.0.0 - Production on Tue Mar 20 19:03:20 2001

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


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

Export file created by EXPORT:V08.01.06 via conventional path
import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
. importing TKYTE's objects into TKYTE
. . importing table "T2" 0 rows imported
. . importing partition "T3":"PART_1" 0 rows imported
. . importing partition "T3":"PART_2" 0 rows imported
. . importing partition "T3":"PART_3" 0 rows imported
. . importing partition "T3":"PART_4" 0 rows imported
. . importing partition "T3":"PART_5" 0 rows imported
. . importing partition "T3":"PART_6" 0 rows imported
. . importing partition "T3":"PART_7" 0 rows imported
. . importing table "T4" 0 rows imported
. . importing table "T1" 0 rows imported
Import terminated successfully without warnings.

Now, I have seen an issue with some objects, whereby the above workaround does not work. IMP still raises the ORA-00959 tablespace 'name' does not exist. The only way I have found to work around this temporarily, is to do the above step (precreate the object) and then create very small tablespaces with the names that IMP demands. You would create these tablespaces with a file too small to actually create anything. Now the IMP will work and you can drop the tablespaces afterwards.

then it should work, give us a cut and paste of a failed statement to look at please.

import will insert the records for you, yes.


the old records will be inserted, yes.

if the data was in the tablespace you dropped, the data will be gone, the only reason imp would "demand" a tablespace is because it tried to actually create something in there, so dropping that tablespace would either fail (objects in there) or you would say 'including contents' and would drop the objects as well.

only for single segment objects.

you would only need to revoke resource IF the tablespace existed - but you didn't WANT the objects to go into that tablespace. If the tablespace doesn't exist, you do not need to revoke it (you are trying to get rid of the unlimited tablespace that comes with resource - to make it so you could put a quota of 0k on the tablespace you dont' want the objects in )

I wrote just about all I could on this subject right above???

is partitioning available to you?

if so, partition by date - you can easily take that in and out of the database.

Else, you can use query= with export to export a slice of a table, delete it. Subsequent inserts will reuse that space.

commit=n

don't have it commit each batch insert


and if possible, you would import in parallel (having exported in parallel, creating many dump files).

and if possible use datapump with 10g as it can do parallel and direct path operations.

1) yes

2) by running many exports - each getting a separate set of schema objects (different sets of tables, or different user accounts, whatever - do it yourself parallelism, something data pump in 10g does for you)

no you won't.

the only thing not committed are the inserts and inserts generate the least amount of undo on unindexed tables. Each table will be committed (because of the subsequent DDL to create the next table) in turn.


At 250g, you really want to rethink using export and import.

Or, do it yourself parallelism...


Or having a long weekend ;)

the concept of the parallel export is a "do it yourself" thing.

You use owner= and export individual schemas

or for big schemas, you use table= and export a couple of tables at the same time, in parallel.

It is all "do it yourself"


(congrats on the new child)

doubtful. you have a single session importing, lgwr is constantly writing out the log buffer contents when it is 1/3 full, every three seconds, every commit as it is.

the manuals have it backwards.


commit=y will decrease performance.
commit=y will negatively impact the performance of large imports.



every time IMP commits, you have to wait for lgwr to write redo to disk.  If you load 100,000 rows and commit 10,000 times - you'll wait 10,000 times for that flush.

If you load 100,000 rows and commit ONCE - you'll wait once (and wait just a short period of time, a commit returns in about the same time regardless of the amount of data you are committing).  I've written about (and demonstrated) this lots in Expert Oracle Database Architecture (and expert one on one Oracle)

As for the undo stuff - yes, it will use more undo - but not a HUGE amount.  inserts into unindexed tables generate the *least* amount of undo.  deletes tend to generate the *most*.  The undo for an insert - is just "delete+rowid".  Small.


Here, lets setup a procedure to array insert 100 rows at a time:

ops$tkyte@ORA10GR2> create table t as select * from all_objects where 1=0;

Table created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace procedure do_inserts( p_num_times in number )
  2  as
  3          type array is table of t%rowtype index by binary_integer;
  4          l_data  array;
  5          l_value number;
  6  begin
  7          select * bulk collect into l_data
  8            from all_objects
  9           where rownum <= 100;
 10
 11          for i in 1 .. p_num_times
 12          loop
 13                  forall i in 1 .. l_data.count
 14                          insert into t values l_data(i);
 15          end loop;
 16          dbms_output.put_line( (100 * p_num_times) || ' rows inserted.' );
 17  end;
 18  /

Procedure created.

<b>now, if we call it to do 1 or 10 iterations:</b>

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec do_inserts( 1 );
100 rows inserted.

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> select used_ublk from v$transaction;

 USED_UBLK
----------
         1

ops$tkyte@ORA10GR2> commit;

Commit complete.

ops$tkyte@ORA10GR2> exec do_inserts( 10 );
1000 rows inserted.

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> select used_ublk from v$transaction;

 USED_UBLK
----------
         1

ops$tkyte@ORA10GR2> commit;

Commit complete.

<b>one block (8k) of UNDO... let's do more:</b>


ops$tkyte@ORA10GR2> exec do_inserts( 1000 );
100000 rows inserted.

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> select used_ublk from v$transaction;

 USED_UBLK
----------
        43

ops$tkyte@ORA10GR2> commit;

Commit complete.

ops$tkyte@ORA10GR2> exec do_inserts( 10000 );
1000000 rows inserted.

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> select used_ublk from v$transaction;

 USED_UBLK
----------
       428

ops$tkyte@ORA10GR2> commit;

Commit complete.

ops$tkyte@ORA10GR2> exec do_inserts( 100000 );
10000000 rows inserted.

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> select used_ublk from v$transaction;

 USED_UBLK
----------
      4268

ops$tkyte@ORA10GR2> commit;

Commit complete.

ops$tkyte@ORA10GR2> select 4268*8/1024 from dual;

4268*8/1024
-----------
   33.34375

<b>so, for 10,000,000 rows - 33.3 mb of undo - not too large....

So, I feel confident in saying it won't grow to outrageous sizes</b> since imp will

a) create table
b) load table
c) AND THEN index table


You don't need more coffee, but I have another doc bug to file :)

 

could be indexing it... have to read it to index it.

typically the table to be imported into doesn't exist???


doing any of this before EXPORT won't have any effect.


I would assume the table and indexes are not in place when you import, if they are - get rid of them? import creates them

if objects are not already there, there is nothing to disable logging on, there is nothing to change the degree of, there are no indexes to drop.

that and imp does conventional path inserts , they are ALWAYS logged.

why would dropping an index before an EXPORT make sense?


it will not materially affect the export.


If you just leave the index in place, export will export the definition of the index and import will create the index AFTER loading the data.

only one answer for a question like this:

a) yes
b) no
c) maybe


or more simply "it depends"


sure, it is ok if you want about 2gig of storage allocated and wish to use system allocated extents (i like them in most cases) with ASSM.

why does the table and index even exist, import creates them


You kept saying above

I am planning to drop index on big tables before export
^^^^^^

<b>Documention bug I believe, using sql_trace we can see that it is array binding with dates.
</b>

ops$tkyte@ORA9IR2> create table t as select * from all_objects;

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> host exp userid=/ tables=t

Export: Release 9.2.0.6.0 - Production on Sat Nov 19 09:55:35 2005

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


Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.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                              T      30818 rows exported
Export terminated successfully without warnings.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace trigger trace_trigger
  2  after logon on schema
  3  begin
  4  execute immediate 'alter session set events ''10046 trace name context forever, level 12'' ';
  5  end;
  6  /

Trigger created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table t;

Table dropped.

ops$tkyte@ORA9IR2> host imp userid=/ full=y

Import: Release 9.2.0.6.0 - Production on Sat Nov 19 09:55:36 2005

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


Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.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 OPS$TKYTE's objects into OPS$TKYTE
. . importing table                            "T"      30818 rows imported
Import terminated successfully without warnings.

ops$tkyte@ORA9IR2> drop trigger trace_trigger;

Trigger dropped.



INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "T" ("OWNER", "OBJECT_NAME",
  "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED",
  "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED",
  "SECONDARY")
VALUES
 (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0          0          0           0
Execute    230      0.31       0.71          0        580       2695       30818
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      231      0.32       0.74          0        580       2695       30818

<b>
It used an array size of about 134 rows per execute.

If you change the create to:
create table t as select all_objects.*, (select rowid from dual) r from all_objects;


then you would see 30818 executes for 30818 rows (showing rowid does prevent the array insert)

</b>



 

because that is my generic trigger I use and I like to have binds and waits dumped to the trace file.

sql_trace=true would have been sufficient.

export/import are platform agnostic - yes, you can export from "X" and import into "Y"



You must use the 9i export tool to create a dmp file that 9i import can read. So, to export from 10g, you'll use 9i export.


However, if you are going to be moving data back and forth and back and forth and back and forth repeatedly - you might consider, well, that you need a single database and be done with it. (or dblinks or something other than export import)


However, metalink says....


Sun Solaris OS 10 Information:

* The following database products are certified on the Sun Solaris 10 operating system:
o Oracle Database 9iR2 (with 9.2.0.6 Patchset) for Sun Solaris SPARC32
o Oracle Database 9iR2 (with 9.2.0.6 Patchset) for Sun Solaris SPARC64
o Oracle Database 10gR1 (with 10.1.0.3 Patchset) for Sun Solaris SPARC64

I told you that 9i is on solaris 10???


but anyway, you would use the 9i export tool against "database x" in order to create a dmp file that the 9i import tool can read.


you would use 9i OR 10g export tool against "database x" in order to create a dmp file that 10g can read.


You can also use - well, consolidation (sounds truly like you want a single database), database links, anything you want.

you just create database links, they work - create database link foo .....;


there is nothing special about this configuration at all.

I did not understand:

If we can afford single file system then what you think how much file system
size we need for 225 GB?

sorry, I still don't understand the intent of the question.

a 225gb schema would take - 225gb.

so I must be missing something in the question here.

It'll slow performance....

I'll note it (add my comment to it)

you can use the 8i export tool to create the dump file and then the 10gr2 imp tool to import it of course.

I know of no reason why using the 8i import would not work - are you sure about what you said above? That would be a message I might expect if the 10g CLIENT (10g export) tried to connect to 8173 or before.

but if you use the 8i export tool and the 10gr2 import tool - it should work

nfs, smb - attach some disk temporarily to do this (say from the server that will want to have the dmp file in the first place)

or find another machine with 8i on it (install the software if you have to) and use the 8i export over the network.

direct=y does not apply to import, hence - ?

you get the 9i export tool and create a new dmp file that can be read by the 9i IMP table.

In other words - you cannot import the 10g DMP file, which is using 10g specific features, using the 9i imp tool.

You can however use the 9i EXP tool against 10g to create a file that the 9i imp too can read.

for that you require nothing on that server.

exp userid=u/p@10gdatabase_over_network

there will be undo generated for the DDL executed.
then there will be undo generated for the inserts (delete + rowid for each row inserted basically)


the rowid of a row is known from the instant the row is placed on a block. That block belongs to a file and the row occupies some slot on that block - that is all we need - instant rowid.

import server uses AL32UTF8 character set (possible charset conversion)
export client uses UTF8 character set (possible charset conversion)


looks like you have multibyte data and in the conversion - one of your strings needs one more byte to be stored in the new characterset.

I'll guess, you are using locally managed tablespaces right (initial, next, pctincrease - phooey - don't need or want them with locally managed tablespaces)

do you want to change charactersets like that?

if so, your data doesn't fit, create the table (pre-create) with a larger size.
if not, use the same characterset.

there is no concept of "extent compression" with locally managed tablespaces.

You either have

a) uniform (they are all the same size)
b) system allocated (you have NO CONTROL over the size)

it is a meaningless concept with locally managed tablespaces.

Not that segments should be in a single extent, not that a single extent is desirable.

import the tables, use tables= on the import.

1) review your imp log and determine what happened, if there was an error - it would be recorded. import showed you exactly what it was doing as it did it.

2) that does not compute, don't know what you mean.

ummm, a .sql file is a script. It contains sql statements. I'm not sure what yours contain.


sqlplus is not a "data loading tool"

sqlldr on the other hand....

looks good to me.

looks like mighty_test didn't have anything to contribute.

if you have a full database export, it has the create users, create tablespaces - everything.

exp userid=user1 tables=(product,prices)
imp userid=user2 full=y touser=user2

ops$tkyte%ORA10GR2> drop user user1 cascade;

User dropped.

ops$tkyte%ORA10GR2> drop user user2 cascade;

User dropped.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create user user1 identified by user1 default tablespace users quota unlimited on users;

User created.

ops$tkyte%ORA10GR2> create user user2 identified by user2 default tablespace users quota unlimited on users;

User created.

ops$tkyte%ORA10GR2> grant create table, create session to user1;

Grant succeeded.

ops$tkyte%ORA10GR2> grant create table, create session to user2;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect user1/user1
Connected.
user1%ORA10GR2> create table t1 as select 1 x, 'y' y, sysdate z from dual;

Table created.

user1%ORA10GR2> create table t2 as select 1 x, 'y' y, sysdate z from dual;

Table created.

user1%ORA10GR2> create table t3 as select 1 x, 'y' y, sysdate z from dual;

Table created.

user1%ORA10GR2>
user1%ORA10GR2> !exp userid=user1/user1 tables=(t1,t2)

Export: Release 10.2.0.3.0 - Production on Sun Apr 29 09:22:57 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
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                             T1          1 rows exported
. . exporting table                             T2          1 rows exported
Export terminated successfully without warnings.

user1%ORA10GR2> !imp userid=user2/user2 full=y touser=user2

Import: Release 10.2.0.3.0 - Production on Sun Apr 29 09:22:59 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


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

Export file created by EXPORT:V10.02.01 via conventional path

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

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing USER1's objects into USER2
. . importing table                           "T1"          1 rows imported
. . importing table                           "T2"          1 rows imported
Import terminated successfully without warnings.

user1%ORA10GR2>
user1%ORA10GR2> connect user2/user2
Connected.
user2%ORA10GR2> select * from t1;

         X Y Z
---------- - ---------
         1 y 29-APR-07

user2%ORA10GR2> select * from t2;

         X Y Z
---------- - ---------
         1 y 29-APR-07

user2%ORA10GR2>

did you change character sets - when you exported, was the character set of the exp environment the same as the database, when you imported - same question.

are you sure, do you have the logs from the exp and imp - do this as a test for us

create a schema, put the procedure in there
do the export of just that schema
do the import of just that schema
verify problem exists

and save the exp/imp headers (which'll tell us if character set translation happened).

that the databases have the same character set doesn't tell us much. It is all about the client that does the exp/imp

all that is required is:

you read the upgrade guide, it tells you everything you need to know.

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14238/toc.htm


I would almost certainly not want a dmp file from exp, I would want a 9i database to upgrade.

why are you worried about dblinks? they upgrade.

just upgrade???

do not import, upgrade. do it right.

upgrade to 10g

do cross platform transports of datafiles, or if possible (same byte order on the servers) full database transports

answer will persist: upgrade.

documentation is a good thing, isn't it.

http://www.oracle.com/pls/db102/search?remark=advanced_search&word=cross+platform+transport&format=ranked&book=&preference=

query= parameter for EXP or EXPDP
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96652/ch01.htm#1005843
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm#sthref154

$ grep --text ^CONNECT expdat.dmp | sort -u
CONNECT OPS$TKYTE
CONNECT SCOTT

is one approach - using show=y with a filter would be another.

impdp (data pump) available with 10g can remap tablespaces.

prior to that, if you have a multi-segment object ( partitioned tables, tables with lobs, IOTs with overflows...) you need to have the tablespaces - because import will only rewrite the FIRST tablespace reference - not the rest.

you could use dbms_metadata to extract the ddl from source (without tablespaces) and run that in target and then import with ignore=y

my car won't start.

why not?

we are even now.

....
One of my test databases will be safed each night with all schemas in a full
dump..
....

Using my universal translator I'll read that as:

we mistakenly believe EXP is a backup tool, to be used to backup databases and are using EXP as our "backup"

Well- we can stop right there, EXP is NOT a backup tool, a dmp file is not a backup, it is a logical copy of data in the database and NOT suitable for a proper backup.

Only - well - a BACKUP is - you are using the WRONG tool. You need to use RMAN and make real backups.



Now that said, I'll guess:

import is NOT frozen, you are just not patient enough. The data in the dmp file is accessed sequentially - and schema1 must be near the "front" of the file and schema2 near the "end" of the file. You just have not given imp sufficient time to read and process (parse) the contents of the dmp file and get to the schema2 data.

I'll vote for number 4

4) unreasonable expectations

and there are an unlimited number of other possible reasons too...


1) not likely, when correctly set up - archiving affects database runtime performance by 0%. It happens in the background (arch) and is not very cpu intensive - it does IO but given sufficient IO (and we do pay attention to our logging set up disk wise right....) it won't cause a pause (you would see "cannot allocate new log, archival required" in the alert log)

2) that could have something to do with it as lobs and longs will cause IMP to do things ONE ROW AT A TIME.

3) that isn't a good idea, especially in light of #2

export is a logical data copy tool, it does full copies pretty much.

it is NOT a data synchronization tool at all - it would not be appropriate to even consider exp/imp to synchronize two databases.

you do not do that

don't even try, it doesn't work that way.

you use 9i export against 9i
you use 9i import against 9i
you use 10g import against 10g (even with a 9i dmp file)

you can use 9i export against 10g


you cannot use 10g against 9i