Importing in ORACLE 10 an ORACLE 8.1.7 export

[Enrico Venturi]

Hi colleagues,
I got a suspicious error while importing an ORACLE 8.1.7 export in an ORACLE 10 database.
The error says:
IMP-00017: following statement failed with ORACLE error 1659:
"CREATE TABLE "TRANS" ("BELONGID" NUMBER, "TRANSPCONNTYPE" NUMBER CONSTRAINT"
" "NN_TRANS_TRANSPCONNTYPE" NOT NULL ENABLE, "TRANCONNID" NUMBER CONSTRAINT "
""NN_TRANS_TRANCONNID" NOT NULL ENABLE) PCTFREE 5 PCTUSED 70 INITRANS 1 MAX"
"TRANS 255 LOGGING STORAGE(INITIAL 3153920 NEXT 3145728 MINEXTENTS 1 MAXEXTE"
"NTS 121 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TA"
"BLESPACE "DATA""
IMP-00003: ORACLE error 1659 encountered
ORA-01659: unable to allocate MINEXTENTS beyond 2 in tablespace DATA

The fact is that before importing I create a DATA datafile having exactly the same size of the source database.
Does it mean that ORACLE 8.1.7 and ORACLE 10 have different needs in terms of data storage?
I mean: does a database ORACLE 10 require more space to store the informations in respect to an ORACLE 8.1.7?

thanks
Enrico

[Yogeeraj_1]

hi Enrico,

See metalink note: 144808.1
Subject: Examples and limits of BYTE and CHAR semantics usage

Below a short extract:

E.1) Use exp/imp.

The import utility uses a value of NLS_LENGTH_SEMANTICS from the export dump
file, not from the target database settings. So you will likely run into errors like
" ORA-01401: inserted value too large for column " during the import from a single
byte characterset to an unicode database.
If you want to migrate tables from BYTE to CHAR semantics using export/import
you need to precreate the tables with the NLS_LENGTH_SEMANTICS set to CHAR and
then import with IGNORE=Y.

There was a incorrect article on this subject in the Oracle Magazine of March/April 2003
You find the corrected here: http://otn.oracle.com/oramag/oracle/03-mar/o23sql.html


hope this helps!
kind regards
yogeeraj

[patrik rybar_1]

you have no space in the tablespace DATA, try extend datafiles

[Enrico Venturi]

Yes, this is clear, my question is:
why the size used by ORACLE 8.1.7 isn't enough anymore with ORACLE 10?

Thanks

[patrik rybar_1]

sorry, i have overlooked
there might be some differences
have you used parameter compress=yes during the export?
your 8i and 10g db's have same block size ?
tablespace data have same storage parameters in the old 8.1.7 and in your new 10g db ?

[Enrico Venturi]

see the differences below.....
could they explain the error during the import?

Oracle 8i

TABLESPACE_NAME: DATA
BLOCK_SIZE: 2048
INITIAL_EXTENT: 3145728
NEXT_EXTENT: 1048576
MIN_EXTENTS: 1
MAX_EXTENTS: 121
PCT_INCREASE: 0
MIN_EXTLEN: 0

Oracle 10g

TABLESPACE_NAME: DATA
BLOCK_SIZE: 8192
INITIAL_EXTENT: 65536
NEXT_EXTENT: -
MIN_EXTENTS: 1
MAX_EXTENTS: 2147483645
PCT_INCREASE: -
MIN_EXTLEN: 65536

[Ariel Cary · Accepted solution]

Enrico,

Besides your observation on the block size, the extent allocation policy is most likely making the difference. In 10g, tablespaces are locally managed (LMT), as opposed to the old dictionary managed tablespaces, which means the extent size is determined by either the database or grows uniformly according to what option you're using; most of the table storage parameters are just ignored on LMT. Thus, there is not necessarily a match between the extents size on 8i and 10g. You can further read on LMT and extent allocations at:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tspaces.htm#i1013496

Take a look at the example I ran:

CASE 1: Table on LMT with autoallocate extent size.

SQL>
1 create tablespace ts1
2 datafile 'E:\U02\ORADATA\XE\TS1.DBF' size 10M
3 extent management local autoallocate
4* segment space management auto

Tablespace created.


SQL> create table table1
2 tablespace ts1
3 as
4 select *
5 from all_objects
6 where rownum<=5000;

Table created.

-- Check extents
SQL> r
1 select extent_id, bytes/1024 KB, blocks
2 from user_extents
3* where segment_name='TABLE1'

EXTENT_ID KB BLOCKS
---------- ---------- ----------
0 64 8
1 64 8
2 64 8
3 64 8
4 64 8
5 64 8
6 64 8
7 64 8

8 rows selected.

-- Check table size.
SQL> r
1 select bytes/1024 KB, blocks
2 from user_segments
3* where segment_name='TABLE1'

KB BLOCKS
---------- ----------
512 64


CASE 2: Table on LMT with uniform size extents.

SQL>
1 create tablespace ts2
2 datafile 'E:\U02\ORADATA\XE\TS2.DBF' size 10M reuse
3 extent management local uniform size 2M
4* segment space management auto
SQL> /

Tablespace created.

SQL> create table table2
2 tablespace ts2
3 as
4 select *
5 from table1;

Table created.


--Extents and size.
SQL> r
1 select extent_id, bytes/1024 KB, blocks
2 from user_extents
3* where segment_name='TABLE2'

EXTENT_ID KB BLOCKS
---------- ---------- ----------
0 2048 256


So it seems table2 occupies "more" space than table1, though they both contain identical rows. It's all about how extents are allocated; of course table2's single extent has plenty of room to allocate more rows. Table1's extent sizes are determined by Oracle, but in practice they will belong to a finite, controlled set like 64K, 1M, 8M, etc. as the table gets populated. You can imagine how this scenario is replicated in you migration setting from 8i to 10g.

HTH,

-Ariel