469,366 Members | 2,281 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

Linking of Schemas

19
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
Nov 13 '08 #1
60 4050
amitpatel66
2,367 Expert 2GB
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
Nov 13 '08 #2
Pilgrim333
127 100+
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.
Nov 13 '08 #3
debasisdas
8,127 Expert 4TB
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.
Nov 13 '08 #4
jam26
19
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?
Nov 14 '08 #5
amitpatel66
2,367 Expert 2GB
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?
Nov 14 '08 #6
jam26
19
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?
Nov 17 '08 #7
amitpatel66
2,367 Expert 2GB
Well in that ase they you will need to go with CREATE SEQUENCE and GRANT TO ALL option
Nov 17 '08 #8
jam26
19
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
Nov 17 '08 #9
debasisdas
8,127 Expert 4TB
Please check here , here and here
Nov 17 '08 #10
jam26
19
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.
Nov 18 '08 #11
amitpatel66
2,367 Expert 2GB
You need to GRANT permission of this sequence to all other schemas so that they can use this sequence
Nov 18 '08 #12
Pilgrim333
127 100+
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.
Nov 18 '08 #13
jam26
19
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.
Nov 18 '08 #14
Pilgrim333
127 100+
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
Nov 18 '08 #15
jam26
19
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.
Nov 18 '08 #16
Pilgrim333
127 100+
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?

Expand|Select|Wrap|Line Numbers
  1.  select  D06SP04D.equip.nextval from dual 

Pilgrim
Nov 18 '08 #17
jam26
19
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.
Nov 18 '08 #18
Pilgrim333
127 100+
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.
Nov 18 '08 #19
jam26
19
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.
Nov 18 '08 #20
amitpatel66
2,367 Expert 2GB
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?
Nov 18 '08 #21
Pilgrim333
127 100+
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
Nov 18 '08 #22
jam26
19
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.
Nov 18 '08 #23
jam26
19
Yes. we are using Oracle on the Maximo Application.
Nov 18 '08 #24
Pilgrim333
127 100+
Can you show the code where the sequence is fetched?

Pilgrim
Nov 18 '08 #25
jam26
19
Sorry Pilgrim. But what do you mean by code?
Nov 18 '08 #26
Pilgrim333
127 100+
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.
Nov 18 '08 #27
amitpatel66
2,367 Expert 2GB
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.
Nov 18 '08 #28
Pilgrim333
127 100+
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
Nov 18 '08 #29
amitpatel66
2,367 Expert 2GB
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
Nov 18 '08 #30
Pilgrim333
127 100+
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.
Nov 18 '08 #31
amitpatel66
2,367 Expert 2GB
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
Nov 18 '08 #32
Pilgrim333
127 100+
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.
Nov 18 '08 #33
amitpatel66
2,367 Expert 2GB
Well you can just go thorugh my words in my previous post. an APPLICATION using multiple schemas. You forgot the word APPLICATION my friend.
Nov 18 '08 #34
Pilgrim333
127 100+
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.
Nov 18 '08 #35
amitpatel66
2,367 Expert 2GB
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.
Nov 18 '08 #36
jam26
19
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.
Nov 19 '08 #37
amitpatel66
2,367 Expert 2GB
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.
Nov 19 '08 #38
Pilgrim333
127 100+
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.
Nov 19 '08 #39
jam26
19
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.
Nov 19 '08 #40
Pilgrim333
127 100+
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.
Nov 19 '08 #41
jam26
19
Wow! does it mean if I have 15 schemas, it will be 15 tables? Can you teach me how to do that please.

Thanks.
Nov 19 '08 #42
Pilgrim333
127 100+
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.
Nov 19 '08 #43
jam26
19
Ok I will check this. Is it true that the trigger is risky to use?

Thanks.
Nov 19 '08 #44
Pilgrim333
127 100+
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.
Nov 19 '08 #45
amitpatel66
2,367 Expert 2GB
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
Nov 19 '08 #46
Pilgrim333
127 100+
He means lots of triggers per table. If you have a few triggers per table, that is acceptable.

Pilgrim.
Nov 19 '08 #47
amitpatel66
2,367 Expert 2GB
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.
Nov 19 '08 #48
Pilgrim333
127 100+
@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.
Nov 19 '08 #49
amitpatel66
2,367 Expert 2GB
@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.
Nov 19 '08 #50

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Roderick A. Anderson | last post: by
4 posts views Thread by anonymous | last post: by
2 posts views Thread by John Jenkins | last post: by
6 posts views Thread by Dennis Gearon | last post: by
3 posts views Thread by Sami Marzouki | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.