Linking of Schemas

[jam26]

Hello,

Is there anybody can help me on how can I make an auto number across all my 15 schemas. I need to create it for tagging of my equipment.

Thanks,
Jam

[amitpatel66]

in c ase if you data is to be maintained globally then why are you using multiple schemas. Use one schema and create a sequence in that schema will help

[Pilgrim333]

Hi,

Create the sequence under one schema and then grant the rights to the other schemas.

You can grant rights on sequences to other users and roles.

Pilgrim.

[debasisdas]

if you have the previlages create a sequence in system and grant to all. But this is not a good idea to implement this logic.

[jam26]

in c ase if you data is to be maintained globally then why are you using multiple schemas. Use one schema and create a sequence in that schema will help

Thanks for Answering my concern. Can you teach me how to do that?

[amitpatel66]

In case if you have many schemas and many tables across schemas then it will be a daunting task making it to single schema. Again, why you need to use a single sequence across schema?..Why not a seperate one for each schema?

[jam26]

Well there's a directives from management for the new equipments to have a number series that can be generated everytime they encoded it on the system across all 15 schemas. For Example, I encoded in Schema 1 with 00001 number series and then if I encoded again Schema 2, the system must give a 00002 number. How can I do that?

[amitpatel66]

Well in that ase they you will need to go with CREATE SEQUENCE and GRANT TO ALL option

[jam26]

Can you teach me on how to do that or if you have some documentation with regards onto this, can you please send it to my email address

[debasisdas]

Please check here , here and here

[jam26]

Thanks for this. I already created a sequence. Just one more question, How can I connect this sequence on multiple schemas? That all my schemas will look on the last number sequnce before they generate the next number.

[amitpatel66]

You need to GRANT permission of this sequence to all other schemas so that they can use this sequence

[Pilgrim333]

Hi,

Log in as the schema owning the sequence and issue the following command:

GRANT SELECT ON <sequence name> TO <schema/role>

Either create a role and grant the sequence to the role and give the role to other schema's or directly grant them to the schema. Do not grant it to all, it's easier but more hazerdous.

Pilgrim.

[jam26]

Hi Pilgrim,

I already grant a priveleges in other schema. But when I tried to do the sequnce, the system generates the same number in all schema. The sequence number generated in Schema 1 also the same number generated in Schema 2. How does it happen? Is there any procedure that I missed?

Thanks.

[Pilgrim333]

Hi,

What was the create statement of the sequence?
How are you fetching the value of the sequence?
If you fetch from the owner of the sequence do you get the same number everytime as well?

Pilgrim