Daniel Westermann's Blog

another ouch: test-case

do a datapump export on 11.2.0.4 Linux x64:

connect / as sysdba
drop user u15 cascade;
create user u15 identified by u15 default tablespace users temporary tablespace temp quota unlimited on users;
grant create session, create table to u15;
connect u15/u15
create table test ( a number, b timestamp with time zone );
begin
for i in 1..10000
loop
insert into test values ( i
, to_timestamp_tz ('2014-06-06 21:07:46.214879 +2:00','YYYY-MM-DD HH24:MI:SSXFF TZH:TZM' )
);
end loop;
commit;
end;
/
select count(*) from test;
SQL> select min(b),max(b) from test;

MIN(B)
---------------------------------------------------------------------------
MAX(B)
---------------------------------------------------------------------------
06-JUN-14 09.07.46.214879 PM +02:00
06-JUN-14 09.07.46.214879 PM +02:00

connect / as sysdba
create or replace directory dir_tmp as '/var/tmp/';
!expdp userid="'/ as sysdba'" parallel=4 dumpfile=test_%U.dmp logfile=test.log schemas=u15 directory=dir_tmp;

import the dump into a 12.1.0.1 database on linux x64:

connect / as sysdba
create or replace directory dir_tmp as '/var/tmp/';
!impdp parallel=2 userid="'/ as sysdba'" schemas=u15 dumpfile=test_%U.dmp logfile=test.log directory=dir_tmp;

… and check the timestamps:

U15@dbs101> select * from test order by 2;

A B
---------- ---------------------------------------------------------------------------
636 06-JUN-14 09.07.46.000000 PM +02:00
637 06-JUN-14 09.07.46.000000 PM +02:00
638 06-JUN-14 09.07.46.000000 PM +02:00
639 06-JUN-14 09.07.46.000000 PM +02:00

… outch

UPDATE: One workaround is to use tradtional exp/imp
26-AUG-2014 UPDATE 2: the workaround is no workaround if you use encrypted tablespaces as exp/imp can not handle this

simple test-case for 11.2.0.4 and 12.1.0.1 on linux x64:

connect / as sysdba
drop user u21 cascade;
create user u21 identified by u21 default tablespace users temporary tablespace temp;
grant create session, create table to u21;
connect u21/u21
declare
  lv_statement varchar2(32000) := 'create table t1 ( ';
  ln_max_indexes pls_integer := 1000;
  ln_max_columns pls_integer := 1000;
  ln_max_ind_columns pls_integer := 32; 
begin
  for i in 1..ln_max_columns
  loop
    if i < ln_max_columns
    then
      lv_statement := lv_statement || ' c'||i||' number,';
    else
      lv_statement := lv_statement || ' c'||i||' number';
    end if;
  end loop;
  lv_statement := lv_statement || ')';
  execute immediate lv_statement;
  for i in 1..ln_max_indexes
  loop
    execute immediate 'create index ii'||i||' on t1 ( c'||i||')';
  end loop;
  execute immediate 'create index i1001 on t1 (c1,c2)';
end;
/
select count(*) from user_indexes;
connect / as sysdba
drop user u21 cascade;
drop user u21 cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01418: specified index does not exist

ouch …

does not happen with 1000 indexes:

connect / as sysdba
drop user u22 cascade;
create user u22 identified by u22 default tablespace users temporary tablespace temp;
grant create session, create table to u22;
connect u22/u22
declare
  lv_statement varchar2(32000) := 'create table t1 ( ';
  ln_max_indexes pls_integer := 1000;
  ln_max_columns pls_integer := 1000;
  ln_max_ind_columns pls_integer := 32; 
begin
  for i in 1..ln_max_columns
  loop
    if i < ln_max_columns
    then
      lv_statement := lv_statement || ' c'||i||' number,';
    else
      lv_statement := lv_statement || ' c'||i||' number';
    end if;
  end loop;
  lv_statement := lv_statement || ')';
  execute immediate lv_statement;
  for i in 1..ln_max_indexes
  loop
    execute immediate 'create index ii'||i||' on t1 ( c'||i||')';
  end loop;
end;
/
select count(*) from user_indexes;
connect / as sysdba
drop user u22 cascade;

happens again with 1002:

connect / as sysdba
drop user u22 cascade;
create user u22 identified by u22 default tablespace users temporary tablespace temp;
grant create session, create table to u22;
connect u22/u22
declare
  lv_statement varchar2(32000) := 'create table t1 ( ';
  ln_max_indexes pls_integer := 1000;
  ln_max_columns pls_integer := 1000;
  ln_max_ind_columns pls_integer := 32; 
begin
  for i in 1..ln_max_columns
  loop
    if i < ln_max_columns
    then
      lv_statement := lv_statement || ' c'||i||' number,';
    else
      lv_statement := lv_statement || ' c'||i||' number';
    end if;
  end loop;
  lv_statement := lv_statement || ')';
  execute immediate lv_statement;
  for i in 1..ln_max_indexes
  loop
    execute immediate 'create index ii'||i||' on t1 ( c'||i||')';
  end loop;
  execute immediate 'create index i1001 on t1 (c1,c2)';
  execute immediate 'create index i1002 on t1 (c1,c2,c3)';
end;
/
select count(*) from user_indexes;
connect / as sysdba
drop user u22 cascade;

drop user u22 cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01418: specified index does not exist

seems to be limit at 1000, somehow.

even more weird:

SYS@dbs101> select count(*) from dba_indexes where owner = 'U22';

  COUNT(*)
----------
	 0

SYS@dbs101> select count(*) from dba_objects where owner='U22';

  COUNT(*)
----------
       996