Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Problem with imp : no sequences imported

912609Jan 23 2012 — edited Jan 27 2012

Hi,

We recently meet a problem when using export and import with Oracle 10g version 10.2.0.1.0 32 bits on a Windows plateform.

Our export full procedure is completed successfully without warning but when we try to do the import, only the tables are imported, all the sequences are missing.
In the log, we can find the following error messages :

IMP-00032: L'instruction SQL dépasse la longueur du tampon d'enregistrement (sql instruction over record buffer length)
IMP-00008: instruction non reconnue dans le fichier d'export: (instruction no recognized in the export file)
STORAGE(INITIAL 65536 FREELISTS 4 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSAUX" LOGGING NOCOMPRESS

We also get some IMP-00041 on the triggers because the sequences are missing

Our exp commande-line :
exp system@noviprod full=y grants=y statistics=none consistent=Y file=%1\sauvegarde\production\oracle\exp\%2.dmp log=%1\sauvegarde\production\oracle\log\explog.log

Our imp command-line :
imp system/system@caria log=c:\import_cari\log_import_dsi.txt analyze=no fromuser=dsi touser=dsi file=c:\import_cari\import.dmp full=N buffer=1000000

Why are the sequences not imported ?
We've already tried to increase the buffer parameter or to remove it, but it didn't solve the problem.

We've never been facing such a problem.
Thank you for your assistance,

Best regards,
Matthieu KREMBEL
Gip symaris

Nicolas Gasparotto

Hi,

Try this :

SQL> select * from tflag;

        ID       FLAG
---------- ----------
         1          1
         1          1
         1          0
         2          1
         2          1

SQL> select id
  2  from (select id, count(*) num_id, sum(flag) sum_flag from tflag group by id)
  3  where num_id=sum_flag
  4  /

        ID
----------
         2

SQL>

Nicolas.

146850

Nicolas' query is not correct if flag has 3 or more values.
So, more general method is this.

SELECT   ID
    FROM tflag
GROUP BY ID
  HAVING MIN (flag) = 1 AND MIN (flag) = MAX (flag)

for more SQL problems & questions, please visit
http://cafe.daum.net/oraclesqltuning

Nicolas Gasparotto

theaos,

Nicolas' query is not correct if flag has 3 or more values.

That's Ok, but the requirement of flag column sound to me like a flag with only two values : 0 or 1.

Even if your sql plain is very nice, if you permit me to complete my first answer, I can modify my query with adding decode :

SQL> select * from tflag;

        ID       FLAG
---------- ----------
         1          1
         1          1
         1          0
         2          1
         2          1
         3          3
         3          0
         3          0

8 rows selected.

SQL> select id
  2  from (select id, count(*) num_id, sum(flag) sum_flag from tflag group by id)
  3  where num_id=sum_flag
  4  /

        ID
----------
         2
         3 <-- This is false, as theaos said

SQL> ed
Wrote file afiedt.buf

  1  select id
  2  from (select id,
  3               count(*) num_id,
  4               sum(decode(flag,1,1,0)) sum_flag from tflag group by id)
  5* where num_id=sum_flag
SQL> /

        ID
----------
         2

SQL>

Nicolas.

Anyway, the OP can keep the theaos' query...
Message was edited by:
N. Gasparotto

146850

As you know, the last argument 0 of decode function is unnecessary in your modified query.
Thanks.

for more SQL problems & questions, please visit
http://cafe.daum.net/oraclesqltuning

shishu

Please try to use this query.

select id from temp
group by id
having min(flag)=max(flag)

Thanks

Shishu Paul

478769

Please try to use this query.

select id from temp
group by id
having min(flag)=max(flag)

The above query will also select the id that has all the flag values as 0.
So it can be modified as

select id from temp
group by id
having min(flag)=max(flag)
and max(flag)=1;

Nicolas Gasparotto

As you know, the last argument 0 of decode function is unnecessary in your modified query.

Yes theaos ;-) but I think it's more readable with 0.

Best regards,

Nicolas.

Laurent Schneider

SQL> select id from t having count(decode(flag,1,null,1))=0 group by id;
ID
----------
2

will return all rows where all id whose all flags are 1

Laurent Schneider

Please try to use this query.

select id from temp
group by id
having min(flag)=max(flag)

The above query will also select the id that has all
the flag values as 0.
So it can be modified as

select id from temp
group by id
having min(flag)=max(flag)
and max(flag)=1;

well, min(flag)=1 and max(flag)=1 will not work for nulls, but maybe this column is not nullable..

William Robertson

This is fun:

SELECT * FROM t t1
WHERE  1 = ALL
       ( SELECT flag FROM t
         WHERE  id = t1.id );

Laurent Schneider

very nice indeed !

John Spencer

Can I play too?

SELECT * FROM t o
WHERE NOT EXISTS (SELECT 1 FROM t i
                  WHERE o.id = o.id and
                        i.flag = 0);

or, to accomodate more than two flag values:

SELECT * FROM t o
WHERE NOT EXISTS (SELECT 1 FROM t i
                  WHERE o.id = t.id and
                        DECODE(i.flag, 1, 1, 0) = 0);

TIMTOWTDI
John

245482

Don't tell me that the good Mr. Spencer is a Perl afficionado. Oh the horror.

Laurent Schneider

select id from t minus select id from t where lnnvl(flag=1);

Message was edited by:
Laurent Schneider
of course the condition is flag=1 not id=1...

474126

SELECT ID FROM (
SELECT ID,COUNT(1) CNT FROM (SELECT ID,FLAG
FROM SAMPLE
GROUP BY ID,FLAG)
GROUP BY ID)
WHERE CNT=1

245482

It seems that you just want to count the number of flags that are not 1. This way you only hit the table once -- which is nice if there's a bit more data.

create table t (
  id  number,
  flag  number );

insert into t values(1,1);         
insert into t values(1,1);       
insert into t values(1,0);       
insert into t values(2,1);       
insert into t values(2,1);       
insert into t values(3,3);       
insert into t values(3,0);       
insert into t values(3,0);
commit;

SQL> SELECT   id
  2      FROM t
  3  GROUP BY id
  4    HAVING COUNT (DECODE (flag, 1, NULL, 'x')) = 0;

        ID
----------
         2

SQL>

245482

'Course I'm just re-stating what Nicolas meant to say. Theaos' solution has trouble with NULLs.

SQL> insert into t values(4,1);

1 row created.

SQL> insert into t values(4,NULL);

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> SELECT   ID
  2      FROM t
  3  GROUP BY ID
  4    HAVING MIN (flag) = 1 AND MIN (flag) = MAX (flag);

        ID
----------
         2
         4

SQL>

Nicolas Gasparotto

I have to say that the most funny query is the William's query, and work for null value.

SQL> create table t (
  2    id  number,
  3    flag  number );

Table created.

SQL>  
SQL> insert into t values(1,1);       

1 row created.

SQL> insert into t values(1,1);       

1 row created.

SQL> insert into t values(1,0);       

1 row created.

SQL> insert into t values(2,1);       

1 row created.

SQL> insert into t values(2,1);       

1 row created.

SQL> insert into t values(3,3);       

1 row created.

SQL> insert into t values(3,0);       

1 row created.

SQL> insert into t values(3,0);

1 row created.

SQL> insert into t values(4,1);

1 row created.

SQL> insert into t values(4,NULL);

1 row created.

SQL>  SELECT distinct id FROM t t1
    WHERE  1 = ALL
           ( SELECT flag FROM t
            WHERE  id = t1.id )
SQL> /

        ID
----------
         2

SQL>

Nicolas.

146850

Using group functions only,

SELECT   ID
    FROM t
GROUP BY ID
  HAVING SUM (flag) / COUNT (*) = 1 AND MAX (flag) = 1

for more SQL problems & questions, please visit
http://cafe.daum.net/oraclesqltuning

Laurent Schneider

still 3 aggregations when one is enough. check examples above

1 - 20

Locked Post

New comments cannot be posted to this locked post.

Locked on Feb 24 2012

Added on Jan 23 2012

#database-key-features, #export-import-sql-loader-external-tables, #not

6 comments

1,412 views

Database Software

Problem with imp : no sequences imported

Comments

Post Details