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

[jam26]

Hi,

I made it this way: I created this sequence in Schema D06SP04D

In creating Sequence:

CREATE SEQUENCE equip
START WITH 70000
INCREMENT BY 1
NOCACHE
NOCYCLE;

Then I link this to other schema like C08ML03B

update C08ML03B.autokey
set seed =D06SP04D.equip.nextval
where tbname ='ASSETS'

Is there anything wrong with this?


Thanks.

[Pilgrim333]

What happens with the update statement? Is every row updated with the same value? Is that the problem?

If you query the sequence twice, does it give the same number both of the times?

 select  D06SP04D.equip.nextval from dual 

Pilgrim

[jam26]

Actually it gives different numbers. But if I connect this sequence in our Maintenance Application (Maximo), It doesn't work in other schema which I linked on this sequence. I expected that before they generate a number, the schema will look first on the last number generated on the sequence.

Thanks.

[Pilgrim333]

A sequence is nothing more then a value stored in the database. If you fetch a value from the sequence, it will be incremented by one and save that number and return that number. No matter from what schema you fetch the next value of the sequence, it should always give you the next value of the sequence. I think something is implemented wrong in the maintenance application when you fetch the sequence value.

As you stated, the sequence works fine when you query the values from dual or do an update statement.

Pilgrim.

[jam26]

I used only this script to connect the application on the sequence.

update C08ML03B.autokey
set seed =D06SP04D.equip.nextval
where tbname ='ASSETS'

What happend is, at first the system will look into the last number generated by the sequence. For example the last number is 10 then the system generates 11 for new number. Then when I connect to other schema, the system will also gives 11 as the new number but it must be 12. I think there's a problem with the execution on the application because when I tried to run the sequence in sql plus worksheet, it generates correct number series across all schemas

Thanks.

[amitpatel66]

I used only this script to connect the application on the sequence.

update C08ML03B.autokey
set seed =D06SP04D.equip.nextval
where tbname ='ASSETS'

What happend is, at first the system will look into the last number generated by the sequence. For example the last number is 10 then the system generates 11 for new number. Then when I connect to other schema, the system will also gives 11 as the new number but it must be 12. I think there's a problem with the execution on the application because when I tried to run the sequence in sql plus worksheet, it generates correct number series across all schemas

Thanks.

Which application you are using? Yes you will face such kind of issues becuase its risk using a sequence across multiple schemas. Anyways in this case you can add 1 to your nextval value and do an UPDATE. but again not advisable.

Why dont you try generating such sequence in the application that you are using?

[Pilgrim333]

There is no problem with the sequence. There is no risk of using a sequence over multiple schema's. We have implemented the same thing over here, and we are working with it without any problems. This is what sequences are intended for.

If you can narrow down the problem and still are stuck, let us know.

Is the application that you are using an oracle application?

Pilgrim

[jam26]

I am using Maximo Application. I have multiple schemas on the application and I need to connect those schemas in only one number series. I f I use the sequence generated by the application, it will possibly generates a same number when I connected into other schemas. The directive is to have only one number series in all schemas that's why I created a sequence.

Thanks.

[jam26]

Yes. we are using Oracle on the Maximo Application.

[Pilgrim333]

Can you show the code where the sequence is fetched?

Pilgrim

[jam26]

Sorry Pilgrim. But what do you mean by code?

[Pilgrim333]

I dunno how maximo works, but at some point there has to be a statement that will fetch the sequence.
Or is it the update statement you posted earlier and you are submitting that through Maximo?

Pilgrim.

[amitpatel66]

There is no problem with the sequence. There is no risk of using a sequence over multiple schema's. We have implemented the same thing over here, and we are working with it without any problems. This is what sequences are intended for.

If you can narrow down the problem and still are stuck, let us know.

Is the application that you are using an oracle application?

Pilgrim

You will face the hiccup when the sequence is used with CACHE. You really will with multiple instances. Definately there is no issue with the sequence since it is working fine from sqlplus.

[Pilgrim333]

The sequence is stored in the database memory, using multiple schema's accessing the same sequence under one schema won't result into hiccups. The only way the hiccups will occur is when the cached values are erased, eg.
system failure

Anyhoo, if the main concern is keeping your numbering without gaps, you can always use the nocache option.

Pilgrim

[amitpatel66]

The sequence is stored in the database memory, using multiple schema's accessing the same sequence under one schema won't result into hiccups. The only way the hiccups will occur is when the cached values are erased, eg.
system failure

Anyhoo, if the main concern is keeping your numbering without gaps, you can always use the nocache option.

Pilgrim

But again even that is the case alright. you cannot expect system to be 100% accurate. you need to consider those exceptions.

Yes @OP is using NOCACHE option here. But even then some issue while using sequence across schemas.

@OP,

Please post how your application is connecting to different schemas and using a sequence. Is there any concept like a particular functionalities are implemented for using particular schema and so on?

It would really help if you can post about the application - database communication

[Pilgrim333]

Well, that is expected, the hiccups if you use the cache option. But i don't see the relation between this and the use of multiple schemas.

I am really curious about the issues you will encounter when you are using a sequence over multiple schemas. Could you be more specific about the problems that can occur?

Pilgrim.

[amitpatel66]

Well, that is expected, the hiccups if you use the cache option. But i don't see the relation between this and the use of multiple schemas.

I am really curious about the issues you will encounter when you are using a sequence over multiple schemas. Could you be more specific about the problems that can occur?

Pilgrim.

You can see one of them here....An applications using multiple schemas but want a single sequence to be used across. But NEXTVAL gives the old value first time when called from different schemas

[Pilgrim333]

No, you can't say by forehand that this is caused by using multiple schemas. That is absurd. Probably the application is fetching the sequence wrong.

The OP already stated that it works when used from the different schemas, only when using it from the application it is not incrementing the sequence. So, this is not caused by using multiple schemes.

Pilgrim.

[amitpatel66]

Well you can just go thorugh my words in my previous post. an APPLICATION using multiple schemas. You forgot the word APPLICATION my friend.

[Pilgrim333]

In that case the multiple schemas is irrelevant. Oracle ensures that a sequence can be used over multiple schemas. So if an application is causing problems, the fault is with the application and not with multiple schemas.

Just to set things rights, the use of a sequence over multiple schemas is no problem. When using an applicationm the application has to fetch the sequence correctly, if it doesn't then it won't work correctly using multiple schemas or not.

Pilgrim.

[amitpatel66]

Well you are saying the same that I said in different phrase.


Couple of issues that might be the reason are:

1. Application not able to fetch correct value
2. Since multiple schemas are used, applications is not able to generate NEXTVAL instead it gives OLD value first.

Let @OP come back with some more inputs which would help.

[jam26]

Well what happened here is there's an original autokey generated by our application per schema. What I did is I changed the autokey of application to the sequence I created.

I used this script to change the autokey of application to the sequence.

update C08ML03B.autokey
set seed = D06SP04D.equip.nextval
where tbname ='ASSETS'

The D06SP04D is the original schema where I created the sequence. The C08ML03B is another schema which I am trying to connect to the sequence of schema D06SP04D.

Thanks.

[amitpatel66]

Well if the application itself generates an auto key then why dont you make use of that. It should be probably the better way to do so.

[Pilgrim333]

Well what happened here is there's an original autokey generated by our application per schema. What I did is I changed the autokey of application to the sequence I created.

I used this script to change the autokey of application to the sequence.

update C08ML03B.autokey
set seed = D06SP04D.equip.nextval
where tbname ='ASSETS'

The D06SP04D is the original schema where I created the sequence. The C08ML03B is another schema which I am trying to connect to the sequence of schema D06SP04D.

Thanks.

There is your problem. The autokey from the application and the sequence are out of sync. Probably you just updated the autokey once, and then are fetching numbers from the autokey. But if you do that, the sequence won't get updated. Your options are as follows:

1. Use the autokey of the application, as stated above.
2. Link every column of every table in all the schemas that make use of the sequence to the sequence.
3. Find a way to link the autokey to the sequence, so that it fetches a new value from the sequence every time it's used.


Pilgrim.

[jam26]

My objective is to have a number series across all 15 schemas. If I let the autokey generates the number it will also generates 15 same numbers.

I think Pilgirm is right, I need to link all the schemas to the sequence I made but my problem is how the sequence automatically increment once I link all these schemas on it.

Thanks.

[Pilgrim333]

You can do it by creating a row level trigger on all the tables that need the sequence as numbering. In the trigger, get the next value from the sequence and put it in the apropriate column, overwriting any value inserted. If you have a lot of tables, it will be a lot of work. But then you are sure that the right value of the sequence will be used.

Pilgrim.

[jam26]

Wow! does it mean if I have 15 schemas, it will be 15 tables? Can you teach me how to do that please.

Thanks.

[Pilgrim333]

Well, If you have more table per schema that use the sequence then it will be more tables.

Here is some more info on triggers, and there even is an example of how to use the sequence for a table key. Experiment with it and if you have any questions, just ask.

Triggers

Pilgrim.

[jam26]

Ok I will check this. Is it true that the trigger is risky to use?

Thanks.

[Pilgrim333]

Risky in what way? You just have to be sure that the code does exactly what you want. Test it good before implementing it in production.

Pilgrim.

[amitpatel66]

Wow! does it mean if I have 15 schemas, it will be 15 tables? Can you teach me how to do that please.

Thanks.

You need to find how many tables are being used in each schema after which you can decide on what can be done. Ofcourse having lots of triggers is not advisable

[Pilgrim333]

He means lots of triggers per table. If you have a few triggers per table, that is acceptable.

Pilgrim.

[amitpatel66]

He means lots of triggers per table. If you have a few triggers per table, that is acceptable.

Pilgrim.

I understood what @OP said and I had explained him.

@OP,

Its question of, if there are 10 tables in each schema used by application, then 10 * 15 = 150 tables and creating 150 triggers one each for a table is ofcourse not smart way of implementing.

And again you are going to connect to database from an application, and insert a record in the table which in turn will fire a trigger. So you need to make sure if this way you are reallly getting unique value and not the old value.

[Pilgrim333]

@OP

The whole idea of the trigger is that the trigger will fetch from the sequence and not the application. The unique values should not be a problem.
The whole idea of triggers is that, no matter from which interface you want to access the tables, everything is done on the database side, so the interface need not worry about unique values, bussiness rules etc. This is the Oracle way of solving the problem you are encountering.

As i stated before, he has to test the code in the trigger before implementing. Furthermore, if you have a system that has 150+ tables, then 150+ triggers should be no problem at all. If it is, then your system is way too big for your budget.

Pilgrim.

[amitpatel66]

@OP

The whole idea of the trigger is that the trigger will fetch from the sequence and not the application.

Ofcourse the application is going to connect to the database and perform some insert so that the trigger gets invoked. If there was no application at all then the sequence was working perfeclty from sqlplus

As i stated before, he has to test the code in the trigger before implementing. Furthermore, if you have a system that has 150+ tables, then 150+ triggers should be no problem at all. If it is, then your system is way too big for your budget.

Budget does not come in to play here. The application is already available. It need to be customized by including the functionality of having a auto number generation across multiple schemas of a database. And yes why not an application have more than 150 tables if there are 15 schemas involved. Its just an assumption and nothing else which can be clarified if @OP answers the question of letting know to how many tables are used in each schema by the application.