Re: Importing just one table to another schema using imp

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Tue, 13 Apr 2004 11:09:19 +1000
Message-ID: <opr6dettdr3d8uqx@news.optusnet.com.au>

On Tue, 13 Apr 2004 00:23:34 GMT, Anurag Varma <avdbi_at_hotmail.com> wrote:

>
> "Walt" <walt_askier_at_yahoo.com> wrote in message
> news:4076C3F2.9487DA1F_at_yahoo.com...
>> Brian Peasland wrote:
>> >
>> > > BTW, the (partial) deal with indexes is that unique constraint
>> indexes
>> > > are always imported whether you specify inedxes=n or not. I don't
>> > > understand why it's that way, but at least it's documented.
>> >
>> > But that's why you also want to specify CONSTRAINTS=N. If
>> CONSTRAINTS=Y
>> > and INDEXES=N, then when a UNIQUE or PRIMARY KEY constraint is
>> created,
>> > it will create an index to help enforce that constraint.
>>
>> Unique indexes are imported regardless of the settings of the
>> constraints and indexes parameters. i.e. setting CONSTRAINTS=N and
>> INDEXES=N does not prevent the indexes from being imported.
>>
>> This is not what one would expect, but I've verified the behavior using
>> the show=y parameter. It's also documented on page 2-24 of the Datbase
>> Utilities manual.
>>
>> Strange.
>>
>> --
>> //-Walt
>> //
>> //
>
> ughhhh!!!! Its *NOT* written on page 2-24 that setting indexes=n and
> constraints=n will not prevent
> unique indexes from being imported.
> The only thing thats written is that indexes=n parameter does not affect
> the unique contraint+index creation.
> What Brian states is correct. You however will refuse to accept this!
>
>
> Anurag

This isn't very difficult to prove one way or the other. Here's my test run.

SQL> create table testexp (

2 col1 number(2) constraint testcol1uq unique, 3 col2 varchar2(10));

Table created.

SQL> select constraint_name from user_constraints where constraint_name like 'TEST%';

CONSTRAINT_NAME

TESTCOL1UQ SQL> select index_name from user_indexes where index_name like 'TEST%';

INDEX_NAME

TESTCOL1UQ SQL> host

C:\> exp scott/tiger_at_lx92 owner=scott constraints=n indexes=n file=expdat.dmp

[Notice that the export has specified constraints N and indexes N]

Export: Release 9.2.0.1.0 - Production on Tue Apr 13 10:41:49 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. [snip]
. about to export SCOTT's tables via Conventional Path ...

. . exporting table                        TESTEXP          0 rows exported

C:\> exit

SQL> drop table testexp;
Table dropped.

SQL> host

C:\> imp scott/tiger_at_lx92 file=expdat.dmp full=y

[Notice the import is utterly unselective. It will take anything that is in the dump file]

[snip]
. importing SCOTT's objects into SCOTT

. . importing table                      "TESTEXP"          0 rows imported

C:\Documents and Settings\Howard>exit

SQL> select constraint_name from user_constraints where constraint_name like 'TEST%';
no rows selected

SQL> select index_name from user_indexes where index_name like 'TEST%'; no rows selected

SQL> desc testexp
Name Null? Type

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

COL1 NUMBER(2)
COL2 VARCHAR2(10) So, with INDEXES=N and CONSTRAINTS=N on the export side of things, the import will NOT create unique constraints (and hence their associated indexes) regardless.

Therefore, Walt's statement that "Unique indexes are imported regardless of the settings of the constraints and indexes parameters. i.e. setting CONSTRAINTS=N and INDEXES=N does not prevent the indexes from being imported." is completely wrong, and Anurag's and Brian's statements are 100% correct.

There is, however, a different test that can be done: include everything in the dump file on export, and then use INDEXES=N, CONSTRAINTS=N on the *import* side of things. So we ought to test that to destruction, too.

SQL> create table testexp (col1 number(2) constraint testcol1uq unique, col2 varchar2(10));

Table created.

SQL> select constraint_name from user_constraints where constraint_name like 'TEST%';

CONSTRAINT_NAME

TESTCOL1UQ SQL> select index_name from user_indexes where index_name like 'TEST%';

INDEX_NAME

TESTCOL1UQ SQL> host

C:\> exp scott/tiger_at_lx92 owner=scott constraints=y indexes=y file=expdat2.dmp

[The dump file now contains indexes and constraints]

Export: Release 9.2.0.1.0 - Production on Tue Apr 13 11:00:58 2004 [snip]

. . exporting table                        TESTEXP          0 rows exported

Export terminated successfully with warnings.

C:\>exit

SQL> drop table testexp;
Table dropped.

SQL> host

C:\> imp scott/tiger_at_lx92 file=expdat2.dmp full=y constraints=N indexes=N

[Import is now asked NOT to import constraints or indexes]

Import: Release 9.2.0.1.0 - Production on Tue Apr 13 11:02:37 2004

. . importing table                      "TESTEXP"          0 rows imported

Import terminated successfully with warnings.

C:\>exit

SQL> select constraint_name from user_constraints where constraint_name like 'TEST%';
no rows selected

SQL> select index_name from user_indexes where index_name like 'TEST%';

no rows selected

[And sure enough, no constraints or indexes are created by import]

SQL> desc testexp
Name Null? Type

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

COL1 NUMBER(2)
COL2 VARCHAR2(10) [But the table is definitely there]

So, either way you look at it, Walt's still wrong and Brian and Anurag are still correct.

Why people just don't test these things beats me.

Regards
HJR

-- 
-------------------------------------------
Dizwell Informatics: http://www.dizwell.com
  -A mine of useful Oracle information-
          -Windows Laptop Rac-
    -Oracle Installations on Linux-
===========================================

Received on Mon Apr 12 2004 - 20:09:19 CDT