Re: Export / Import from 8i to 9i and locally managed tablespace

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Tue, 8 Apr 2003 12:53:26 +0100
Message-ID: <3e92b836$0$4851$ed9e5944@reading.news.pipex.net>

I don't know what you mean by no merge. By default however export exports with compress=y which tells Oracle to store a table creation statement in the export file of the form

CREATE TABLE ....
STORAGE(INITIAL <SIZE OF DATA> NEXT <SAME AS NEXT>)

i.e to 'compress' all the data into one extent. My guess would therefore be that you have 1096k worth of data in the table.

When you come to import into your tablespace however this storage clause gets translated into the equivalent of "create my table with the uniform extents that I specified, but allocate enough of them to hold my data upfront." If you look at dba_extents (or all_extents) for your newly created table I'll lay good odds that you have 3 extents of 520k each. (2 extents not being enough to hold 1096k). DBA_SEGMENTS shows the values from the table creation statement. This is illustrated below for a locally managed tablespace with 64k uniform extents.

C:\Documents and Settings\n-litchfield>set oracle_sid=nl9iwk

C:\Documents and Settings\n-litchfield>net start oracleservicenl9iwk The OracleServiceNL9IWK service is starting. The OracleServiceNL9IWK service was started successfully.

C:\Documents and Settings\n-litchfield>sqlplus /nolog

SQL*Plus: Release 9.2.0.3.0 - Production on Tue Apr 8 12:44:20 2003

SQL> sqlplus /nolog
SP2-0734: unknown command beginning "sqlplus /n..." - rest of line ignored. SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 135339388 bytes

Fixed Size                   454012 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes

Database mounted.
Database opened.
SQL> conn niall/niall
Connected.
SQL> desc dba_tablespaces;

 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------

 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
 BLOCK_SIZE                                NOT NULL NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                               NOT NULL NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 MIN_EXTLEN                                         NUMBER
 STATUS                                             VARCHAR2(9)
 CONTENTS                                           VARCHAR2(9)
 LOGGING                                            VARCHAR2(9)
 FORCE_LOGGING                                      VARCHAR2(3)
 EXTENT_MANAGEMENT                                  VARCHAR2(10)
 ALLOCATION_TYPE                                    VARCHAR2(9)
 PLUGGED_IN                                         VARCHAR2(3)
 SEGMENT_SPACE_MANAGEMENT                           VARCHAR2(6)
 DEF_TAB_COMPRESSION                                VARCHAR2(8)

SQL> desc dba_segments;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------

 OWNER                                              VARCHAR2(30)
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 TABLESPACE_NAME                                    VARCHAR2(30)
 HEADER_FILE                                        NUMBER
 HEADER_BLOCK                                       NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 EXTENTS                                            NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 RELATIVE_FNO                                       NUMBER
 BUFFER_POOL                                        VARCHAR2(7)

SQL> desc dba_extents;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------

 OWNER                                              VARCHAR2(30)
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 TABLESPACE_NAME                                    VARCHAR2(30)
 EXTENT_ID                                          NUMBER
 FILE_ID                                            NUMBER
 BLOCK_ID                                           NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 RELATIVE_FNO                                       NUMBER

SQL> create table t3(object_id number,object_name varchar2(30) 2 ) storage (initial 640k next 128k) tablespace users;

Table created.

SQL> select segment_name,initial_extent,next_extent 2 from dba_segments where segment_name='T3';

SEGMENT_NAME                   INITIAL_EXTENT NEXT_EXTENT


------------------------------ -------------- -----------

T3                                     655360       65536

SQL> SELECT EXTENT_ID,BYTES/1024 SIZE_IN_K FROM DBA_EXTENTS 2 WHERE SEGMENT_NAME='T3'; EXTENT_ID SIZE_IN_K
---------- ----------

         0         64
         1         64
         2         64
         3         64
         4         64
         5         64
         6         64
         7         64
         8         64
         9         64

10 rows selected.

SQL> SELECT TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,EXTENT_MANAGEMENT 2 FROM DBA_TABLESPACES
3 WHERE TABLESPACE_NAME='USERS';

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN


------------------------------ -------------- ----------- ----------

USERS                                   65536       65536 LOCAL

SQL> segment space management auto is irrelevant in this case as it determines whether the segment uses freelists or not.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
"achkar" <kachkar_at_tablimited.com.au> wrote in message
news:acb78eab.0304072233.50ee4c98_at_posting.google.com...
> Hi All ,
>
> I have a table on oracle 8i (8.1.7 ), this table resides on dictionary
> managed tablespace: initial size 256k , next size 256k,  that is  for
> both the table and the tablespace .
> then I did export to that table with no merge.
> after that I created locally managed tablespace on 9i with uniform
> allocation 520k ,segment space management is automatic
>
> When I did the import I noticed that the initial size for that table
> on 9i is 1096K instead of 520K , Why is that? , is it because 'segment
> space management  is automatic '
> and if so , how can I stop that , I would like to know what will
> happen if my table is really big ( 20 million record ) , what will
> happen to the initial size for that table when I do the import. or do
> I need to create the table on 9i then do the import.
>
> any help / opinion / thoughts  will  be appreciated.

Received on Tue Apr 08 2003 - 06:53:26 CDT