exp не видит "девственные" таблицы

Кажется нашел баг в Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit for Linux.
Таблицы над которыми не производилось операции INSERT, не экспортируются утилитой exp.

c:\>sqlplus /nolog

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Nov 30 21:54:46 2009

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

SQL> connect system/manager@suse
Connected.
SQL> create user user_test identified by manager;

User created.

SQL> grant connect, resource to user_test;

Grant succeeded.

SQL> connect user_test/manager@suse
Connected.
SQL> create table test_table (col1 number);

Table created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

c:\>exp system/manager@suse FILE=c:\export.dmp ROWS=Y OWNER=(user_test)

Export: Release 11.1.0.7.0 - Production on Mon Nov 30 22:02:02 2009

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user USER_TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user USER_TEST
About to export USER_TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export USER_TEST's tables via Conventional Path ...
. 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.

c:\>sqlplus /nolog

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Nov 30 22:03:02 2009

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

SQL> connect user_test/manager@suse
Connected.
SQL> insert into test_table values(1);

1 row created.

SQL> rollback;

Rollback complete.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

c:\>exp system/manager@suse FILE=c:\export.dmp ROWS=Y OWNER=(user_test)

Export: Release 11.1.0.7.0 - Production on Mon Nov 30 22:05:48 2009

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user USER_TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user USER_TEST
About to export USER_TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export USER_TEST's tables via Conventional Path ...
. . exporting table                     [color=red][b]TEST_TABLE[/b][/color]          0 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.

c:\>

Пробовал эту же последовательность операций выполнить на сервере (SUSE Linux Enterprise Server 11 x86_64) - тот же эффект.
Для версий 11.1 и 10.2 таблица нормально экспортируется.

а вы пробовали "первый" дамп импортировать в пустую или другую схему... пустая таблица действительно не создается?

_Alex_SMIRNOV_,

Пробовал. На таблице был триггер, и при импорте получил ошибку при создании триггера - table not found.

Freddy Krueger,

думаю это не бага а последствия новой фичи:

create table t(n number);

Table created.

SQL> select * from user_extents;

no rows selected

SQL> insert into t values(1);

1 row created.

SQL> select * from user_extents;

SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
T
                               TABLE              USERS
         0      65536          8

к тому же exp desupported

Недавно встречал какую-то статью (а может и в доке вычитал, чем черт не шутит), что в 11gR2 реализовано отложенное выделение экстентов для создаваемых объектов. Т.е. созданный пустой объект не занимает места на диске, только описание в словаре. Дескать, удобно при развертывании пустых схем клонов

t11> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

t11> create table t1(a number);

Table created.

t11> create index t1_idx on t1(a);

Index created.

t11> column segment_name format a30
t11> select segment_name, segment_type, blocks from user_segments;

no rows selected

t11> insert into t1 values(1);

1 row created.

t11> select segment_name, segment_type, blocks from user_segments;

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS
------------------------------ ------------------ ----------
T1                             TABLE                       8
T1_IDX                         INDEX                       8

бл*

Вячеслав Любомудров

бл*

и таки да )))

Oracle

Beginning in Oracle Database 11g Release 2, when creating a non-partitioned heap-organized table in a locally managed tablespace, table segment creation is deferred until the first row is inserted. In addition, creation of segments is deferred for any LOB columns of the table, any indexes created implicitly as part of table creation, and any indexes subsequently explicitly created on the table. The advantages of this space allocation method are the following: A significant amount of disk space can be saved for applications that create hundreds or thousands of tables upon installation, many of which might never be populated. Application installation time is reduced There is a small performance penalty when the first row is inserted, because the new segment must be created at that time. To enable deferred segment creation, compatibility must be set to '11.2.0' or higher. You can disable deferred segment creation by setting the initialization parameter DEFERRED_SEGMENT_CREATION to FALSE. The new clauses SEGMENT CREATION DEFERRED and SEGMENT CREATION IMMEDIATE are available for the CREATE TABLE statement. These clauses override the setting of the DEFERRED_SEGMENT_CREATION initialization parameter. Note that when you create a table with deferred segment creation (the default), the new table appears in the *_TABLES views, but no entry for it appears in the *_SEGMENTS views until you insert the first row. There is a new SEGMENT_CREATED column in *_TABLES, *_INDEXES, and *_LOBS that can be used to verify deferred segment creation.

Эхх... надо начинать читать доки не по 11.1 а по 11.2 (

_Alex_SMIRNOV_

Эхх... надо начинать читать доки не по 11.1 а по 11.2 (

Но такая лень читать английскую по 11.2, когда есть переведенная по 11.1

Ну и до кучи по теме топика:

Oracle® Database Utilities 11g Release 2 (11.2) 21 Original Export

Note: The original Export utility does not export any table that was created with deferred segment creation and has not had a segment created for it. The most common way for a segment to be created is to store a row into the table, though other operations such as ALTER TABLE ALLOCATE EXTENTS will also create a segment. If a segment does exist for the table and the table is exported, the SEGMENT CREATION DEFERRED clause will not be included in the CREATE TABLE statement that is executed by the original Import utility.

что есть весьма нехорошо, поскольку приходится помнить о таких вещах, иначе импорт может поломаться (зависимые объекты, ограничения целостности). Могли бы и какой-нибудь параметр сделать

Хоть он и объявлен desupported, но перенос схемы в более ранние версии никто не отменял (предыдущие версии утилиты exp тоже не видят этих объектов, естественно)

Вячеслав Любомудров

[Но такая лень читать английскую по 11.2, когда есть переведенная по 11.1

Вячеслав, где есть переведённая ?