Oracle: EXP - IMP

From to schema

create user u1 identified by u1
   default tablespace data
   quota unlimited on data;

grant
   create session,
   create table to u1;

create user u2 identified by u2
   default tablespace data
   quota unlimited on data;

grant
   create session,
   create table to u2;

connect u1/u1

create table tq84_table_for_export (
  col_1 number,
  col_2 number
);

insert into tq84_table_for_export values (1,2);
insert into tq84_table_for_export values (2,3);

commit;

exp userid=rene/rene tables=(u1.tq84_table_for_export)

host imp userid=rene/rene fromuser=u1 touser=u2

connect rene/rene

select * from u2.tq84_table_for_export;

Schema

connect meta/meta

@drop_schema

create user just_a_schema
  identified by x
  quota unlimited on users;

grant
  create session,
  create table,
  create sequence,
  create trigger
to
  just_a_schema;

connect just_a_schema/x

create table a_tbl (
  id   number primary key,
  txt  varchar2(20)
);

create sequence a_seq;

create trigger a_trg
  before insert or update on a_tbl
  for each row
begin
  :new.id := a_seq.nextval;
end a_trg;
/

insert into a_tbl(txt) values ('record #1');
insert into a_tbl(txt) values ('record #2');
insert into a_tbl(txt) values ('record #3');

-- select * from a_tbl;

create table b_tbl (
  id_a  number not null references a_tbl,
  txt   varchar2(20)
);

insert into b_tbl values (1, 'foo');
insert into b_tbl values (1, 'bar');
insert into b_tbl values (1, 'baz');

insert into b_tbl values (2, 'hello');
insert into b_tbl values (2, 'world');

-- select * from b_tbl;

commit;

@connect_with_admin_right

set head off
set pages 0
set long 5000

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);


spool just_a_user_clone.sql

  select dbms_metadata.get_ddl        ('USER'            , 'JUST_A_SCHEMA') from dual;
--select dbms_metadata.get_granted_ddl('ROLE_GRANT'      , 'JUST_A_SCHEMA') from dual;
  select dbms_metadata.get_granted_ddl('SYSTEM_GRANT'    , 'JUST_A_SCHEMA') from dual;
--select dbms_metadata.get_granted_ddl('OBJECT_GRANT'    , 'JUST_A_SCHEMA') from dual;
  select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', 'JUST_A_SCHEMA') from dual;

spool off

--
--  Create a schema, named «just_a_schema» that
--  will be cloned with imp/exp:
--
@create_schema

-- Create a sql script that is needed to re-create
--«just_a_schema». The name of the created
-- script is «just_a_user_clone.sql» and will be
-- called further below.
@create_user_script

--
-- Do the export.
--
host exp userid=just_a_schema/x owner=just_a_schema file=exp.dmp log=exp.log

@drop_schema

--
-- Re-create an empty schema
-- just_a_user_clone.sql is created by create_user_script.sql
@just_a_user_clone.sql

host imp meta/meta touser=just_a_schema full=y file=exp.dmp log=imp.log

connect just_a_schema/x

--
--  Check if data, trigger and sequence are cloned.
--
insert into a_tbl(txt) values ('record after import');
select * from a_tbl;

Table with trigger

start 01_table
start 01_trigger

insert into tq84_table_with_trigger (col_1) values (1);
commit;
select * from tq84_table_with_trigger;

host exp tables=(tq84_table_with_trigger) 

drop table tq84_table_with_trigger;

host imp full=yes

insert into tq84_table_with_trigger (col_1) values (2);
commit;
select * from tq84_table_with_trigger;

create table tq84_table_with_trigger (
       col_1 number,
       col_2 varchar2(30)
);

create  trigger tq84_table_with_trigger_trg 
before insert
   on tq84_table_with_trigger
   for each row
begin

   :new.col_2 := to_char(sysdate, 'hh24:mi:ss');

end tq84_table_with_trigger_trg;
/

See also