Home » SQL & PL/SQL » SQL & PL/SQL » Moving sequences from one schema to other with the next value. (12c , Solaris 10)
Moving sequences from one schema to other with the next value. [message #679714] Wed, 18 March 2020 12:55 Go to next message
daulat01
Messages: 62
Registered: May 2011
Location: Delhi
Member
Hi all

Really need your help.
I want to move few sequences from one schema to other schema with next value not with the existing value. Suppose , if the sequence has the value 123 in schema A now then it should be 124 in the second schema .

Thanks ,
Moving sequences from one schema to other with the next value. [message #679715 is a reply to message #679714] Wed, 18 March 2020 12:56 Go to previous messageGo to next message
daulat01
Messages: 62
Registered: May 2011
Location: Delhi
Member
Hi all

Really need your help.
I want to move few sequences from one schema to other schema with next value not with the existing value. Suppose , if the sequence has the value 123 in schema A now then it should be 124 in the second schema .

Thanks,
Re: Moving sequences from one schema to other with the next value. [message #679716 is a reply to message #679714] Wed, 18 March 2020 13:00 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
There is no sup[ported technique (that I am aware of) for moving an object from one schema to another. No problem - create a new sequence with the start value you want, and drop the old one.
Re: Moving sequences from one schema to other with the next value. [message #679721 is a reply to message #679716] Thu, 19 March 2020 02:50 Go to previous messageGo to next message
_jum
Messages: 573
Registered: February 2008
Senior Member
Is also possible with EXPDP/IMPDP and DBMS_DATAPUMP, but create a new sequence with the wanted start value should be simpelst way.
Re: Moving sequences from one schema to other with the next value. [message #679723 is a reply to message #679721] Thu, 19 March 2020 06:26 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
Registered: January 2010
Location: Connecticut, USA
Senior Member
EXPDP will export sequence with START WITH = LAST_NUMBER which is last cached out value rather than last generated value. In general, "if the sequence has the value 123 in schema A now then it should be 124" isn't possible if sequence has cache unless we know last generated value upfront.

SY.
Re: Moving sequences from one schema to other with the next value. [message #679724 is a reply to message #679723] Thu, 19 March 2020 08:30 Go to previous messageGo to next message
_jum
Messages: 573
Registered: February 2008
Senior Member
Thank you for the correction and clarification!
Re: Moving sequences from one schema to other with the next value. [message #679725 is a reply to message #679724] Thu, 19 March 2020 12:43 Go to previous messageGo to next message
daulat01
Messages: 62
Registered: May 2011
Location: Delhi
Member
Thank you all for your suggestions. let me clarify what i am doing . We have to move few tables from schema A to schema B (New schema).

- Expdp tables with option content =metadata_only and include = procedure, functions from Schema A.
- Impdp dumpfile into schema B.

- Stopping the app and Insert the data from schema A tables to schema B tables using Insert :
INSERT /*+ append */ INTO B.Test select * FROM A.test ;

- Now I have to export the sequences from schema A to schema B
But the sequence last value should be started from the next value not the existing one.

Can we change the value after importing the sequence for schema A.

Re: Moving sequences from one schema to other with the next value. [message #679727 is a reply to message #679725] Thu, 19 March 2020 13:52 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This post from Bill B explains it clearly.

Re: Moving sequences from one schema to other with the next value. [message #679728 is a reply to message #679725] Thu, 19 March 2020 14:08 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
Registered: January 2010
Location: Connecticut, USA
Senior Member
daulat01 wrote on Thu, 19 March 2020 13:43

But the sequence last value should be started from the next value not the existing one.

Can we change the value after importing the sequence for schema A.

Why do you care? Sequence guaranties unique numbers. It doesn't guarantee consecutive numbers. If some session inserts into B.Test and then rolls back you will have a hole in numbering. If you shut down database and sequence has cache all cached out but not used sequence numbers are lost and again you'll have a hole in numbering. The only way to have no holes is using max + 1 and serializing which most likely will bring your app to a screeching halt or/and cause deadlocks.

SY.
Re: Moving sequences from one schema to other with the next value. [message #679739 is a reply to message #679728] Fri, 20 March 2020 13:50 Go to previous messageGo to next message
daulat01
Messages: 62
Registered: May 2011
Location: Delhi
Member
Thanks for your comments. How I can expdp the list of sequence only not other objects.
Re: Moving sequences from one schema to other with the next value. [message #679740 is a reply to message #679739] Fri, 20 March 2020 14:00 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
Registered: January 2010
Location: Connecticut, USA
Senior Member
include=sequence

SY.
Re: Moving sequences from one schema to other with the next value. [message #679741 is a reply to message #679740] Fri, 20 March 2020 14:55 Go to previous messageGo to next message
daulat01
Messages: 62
Registered: May 2011
Location: Delhi
Member
Thanks ! but do we need to give table name as well because if I am using include =sequences names only then i am getting below error


expdp system/welcome parfile=export_sumdb1.par

Export: Release 12.1.0.1.0 - Production on Fri Mar 20 12:51:32 2020

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** parfile=export_sumdb1.par
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-39168: Object path SEQUENCE was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" completed with 2 error(s) at Fri Mar 20 12:51:43 2020 elapsed 0 00:00:07

Parfile details:


directory=data_pump_dir
dumpfile=export_sumdb_metadata1.dmp
logfile=export_sumdb_metadata1.log
PARALLEL=3
INCLUDE=SEQUENCE:" IN('ACCEPT_REJECT_LIST_DOMAIN_SEQ','ADMIN_IACH_SEQ','ALERT_ENTITY_SEQUENCE','AUTH_SEQ','CLNT_ACPT_RJCT_LIST_DOMAIN_SEQ','DBDATACHANGE _ID_SEQ','DBDATACHANGE_TEST_ID_SEQ','EDGE_LOGIN_ID_SEQ','ENTITY_CHANGE_SEQ','MSGSYNC_UPDATE_SEQUENCE','OAUTH_SEQ','SEQ_ADCHOICE_SYNC_ DATA','SEQ_DIM_MEDIA','SUMDB_SEQUENCE','TF_CONTRACT_DB_SEQ','TF_SSL_SCAN_BATCH_SEQ','TF_SSL_SCAN_MEDIA_SEQ','TF_SSL_SCAN_RUN_SEQ','TF _STATE_CODES_SEQ') "
reuse_dumpfiles=yes

Re: Moving sequences from one schema to other with the next value. [message #679742 is a reply to message #679741] Fri, 20 March 2020 15:44 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Are you sure the sequences are in SYSTEM schema?

Re: Moving sequences from one schema to other with the next value. [message #679743 is a reply to message #679742] Fri, 20 March 2020 15:45 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And if you need to specify tables as well as sequences, you can use several INCLUDE options.

Re: Moving sequences from one schema to other with the next value. [message #679746 is a reply to message #679741] Sat, 21 March 2020 06:34 Go to previous message
Solomon Yakobson
Messages: 2985
Registered: January 2010
Location: Connecticut, USA
Senior Member
Your expdp session connects to database as SYSTEM while sequences reside in some other schema. You need to either connect as sequence owner or add schemas=sequence-owner to par file:

I:\>rem Connecting as SYSTEM without providing sequence owner schema

I:\>expdp directory=temp dumpfile=s.dmp logfile=s.log include=sequence:\"" in('SEQ1','SEQ2')"\"

Export: Release 12.2.0.1.0 - Production on Sat Mar 21 07:29:04 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Username: system@pdb1sol122
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@pdb1sol122 directory=temp dumpfile=s.dmp logfile=s.log include=sequence:" in('SEQ1','SEQ2')"
ORA-39168: Object path SEQUENCE was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" completed with 2 error(s) at Sat Mar 21 07:29:16 2020 elapsed 0 00:00:04


I:\>rem Connecting as SYSTEM providing sequence owner schema

I:\>expdp directory=temp dumpfile=s.dmp logfile=s.log schemas=scott include=sequence:\"" in('SEQ1','SEQ2')"\"

Export: Release 12.2.0.1.0 - Production on Sat Mar 21 07:29:24 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Username: system@pdb1sol122
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@pdb1sol122 directory=temp dumpfile=s.dmp logfile=s.log schemas=scott include=sequence:" in('SEQ1','SEQ2')"
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  C:\TEMP\S.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Mar 21 07:29:38 2020 elapsed 0 00:00:05


I:\>del C:\temp\s.dmp

I:\>rem Connecting as sequence owner without providing sequence owner schema

I:\>expdp directory=temp dumpfile=s.dmp logfile=s.log include=sequence:\"" in('SEQ1','SEQ2')"\"

Export: Release 12.2.0.1.0 - Production on Sat Mar 21 07:29:59 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Username: scott@pdb1sol122
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@pdb1sol122 directory=temp dumpfile=s.dmp logfile=s.log include=sequence:" in('SEQ1','SEQ2')"
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  C:\TEMP\S.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Mar 21 07:30:10 2020 elapsed 0 00:00:05


I:\>
SY.
Previous Topic: XS entity with this name already exists.
Next Topic: Cursored FOR loops, PLS-00103 compile error
Goto Forum:
  


Current Time: Tue Sep 22 08:37:56 CDT 2020