Home » SQL & PL/SQL » SQL & PL/SQL » Schema populate (11.2.0.3.0)
Schema populate [message #575801] Mon, 28 January 2013 01:08 Go to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Hi

I have two schemas SCHEMA1 and SCHEMA2, both has same tables everything same but
SCHEM1 has data in tables whereas SCHEMA2 is empty

Q1) Best way to populate data in SCHEMA2?

Q2)To populate data for primary keys i use sequences, For instance if i use insert into select from SCHEMA1, then i guess sequence value won't update?Is there any way to counter this issue?

Thanks in advance
Re: Schema populate [message #575803 is a reply to message #575801] Mon, 28 January 2013 01:25 Go to previous messageGo to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Export schema1 and import it into schema2. Use data pump to do that.
Re: Schema populate [message #575805 is a reply to message #575803] Mon, 28 January 2013 01:34 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Thanks Littlefoot,

But what about the sequence value?Will the sequence value be updated?
Re: Schema populate [message #575807 is a reply to message #575805] Mon, 28 January 2013 01:38 Go to previous messageGo to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Updated to what? Export will take the sequence from schema1 and put it into schema2 - it won't move anywhere, so NEXTVAL in both schemas would return the same value.
Re: Schema populate [message #575809 is a reply to message #575801] Mon, 28 January 2013 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 59505
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Q2) Do you mean that both schemas will use the same sequence? If they have both their own sequence then there is no problem with the export/import LittleFoot mentioned

Regards
Michel


Re: Schema populate [message #575817 is a reply to message #575809] Mon, 28 January 2013 02:20 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
No Michel,just that sequence name is same

Schema1

SQL> select seq1.nextval from dual;

   NEXTVAL
----------
        24

SQL> sho user
USER is "P1"


After Import
impdp p2/batman  directory=ris_dir schemas=p1 dumpfile=p1_
dup_exp.dmp logfile=p1_dump_imp.log remap_schema=p1:p2 TABLE_EXISTS_ACTION=REPLA
CE

I have used all TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE}
ORA-31684: Object type SEQUENCE:"P2"."SEQ1" already exists

Schema2
SQL> select seq1.currval from dual;

   CURRVAL
----------
         1

SQL> sho user
USER is "P2"


How to exp/imp missing DB objects which exist in SCHEMA1 but not in SCHEMA2 with table data?

[Updated on: Mon, 28 January 2013 02:23]

Report message to a moderator

Re: Schema populate [message #575820 is a reply to message #575817] Mon, 28 January 2013 02:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59505
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-31684: Object type SEQUENCE:"P2"."SEQ1" already exists


So the schema already contains the sequence.
If you want to have the same current number in the sequence then you have to set it:
alter sequence seq1 increment by <p1.seq1 value>-<p2.seq2 value>;
select seq1.nextval from dual;
alter sequence seq1 increment by 1;

Regards
Michel


Re: Schema populate [message #575822 is a reply to message #575820] Mon, 28 January 2013 02:39 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Thanks Michel,

But the problem is i have 300-400 sequences in my schema, and i don't want to use alter to fix my sequences , i was looking for something which oracle can do for me automatically may be by expdp/impdp
Re: Schema populate [message #575825 is a reply to message #575822] Mon, 28 January 2013 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59505
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle does not do it.
So you have to do it by yourself.
I think it is not difficult to generate a script to do it.

Regards
Michel
Re: Schema populate [message #575826 is a reply to message #575825] Mon, 28 January 2013 03:34 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Thanks Michel

I dropped all sequences in target schema and then imported , it worked Cool
Re: Schema populate [message #575827 is a reply to message #575826] Mon, 28 January 2013 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 59505
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Take care and double check the values of your sequences as dbms_metadata (which is used by expdp) adds the cache value to the sequence last one when returning the DDL:
SQL> select increment_by, cache_size from user_sequences where sequence_name='S';
INCREMENT_BY CACHE_SIZE
------------ ----------
           1         20

1 row selected.

SQL> select s.nextval from dual;
   NEXTVAL
----------
        58

1 row selected.

SQL> select dbms_metadata.get_ddl('SEQUENCE','S') from dual;
DBMS_METADATA.GET_DDL('SEQUENCE','S')
--------------------------------------------------------------------------------

   CREATE SEQUENCE  "MICHEL"."S"  MINVALUE 1 MAXVALUE 99999999999999999999999999
9 INCREMENT BY 1 START WITH 78 CACHE 20 NOORDER  NOCYCLE

Note the "start with" value in the generated statement.

Regards
Michel
Re: Schema populate [message #575839 is a reply to message #575827] Mon, 28 January 2013 05:47 Go to previous messageGo to next message
cookiemonster
Messages: 11089
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since gapless sequences don't really exist, why should anyone care if oracle does that?
Re: Schema populate [message #575841 is a reply to message #575839] Mon, 28 January 2013 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 59505
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Me? I don't care but it seems important for OP to have the same values in both schemas (at least after the import) so I mentioned the point.

Regards
Michel
Re: Schema populate [message #575843 is a reply to message #575841] Mon, 28 January 2013 06:02 Go to previous message
cookiemonster
Messages: 11089
Registered: September 2008
Location: Rainy Manchester
Senior Member
I imagine he just needs same value or higher. If they restarted from 1 he'd get unique constraint errors.
Previous Topic: ult_http
Next Topic: Procedure Working But Not Solving Mutating Table
Goto Forum:
  


Current Time: Fri Oct 31 05:46:59 CDT 2014

Total time taken to generate the page: 0.07228 seconds