|
|
|
|
|
|
|
|
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 |
Solomon Yakobson
Messages: 3275 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 #679741 is a reply to message #679740] |
Fri, 20 March 2020 14:55 |
|
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 #679746 is a reply to message #679741] |
Sat, 21 March 2020 06:34 |
Solomon Yakobson
Messages: 3275 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.
|
|
|