oracle query to find any table AUTO_INCREMENT column

how about 10c ?

any query to find tables which has this kind of type, I want to try converting it

Counterquestion, respectively rhetorical question:how is this done in 10g ?!

I am sorry, I don't know much on oracle schema so I'd like to know. you can focus on 10g not 12c

One salient fact that's gone unmentioned: whatever the source of the bean-counter, and whatever the column is named, the column must be constrained with a unique key. I expect that I would find online a good argument or two that it should be the primary key -- ah, I just remembered that UKs allow NULLS, so PK it is. You should be able to filter down to those columns that are positive integers, not null, uniquely indexed, and constrained as a primary key.

Alex [***Alex140181***],

yes tks. we are using oracle 10g now and so you all are saying they must use sequence for it and most likely in trigger level.

that's why our DB has so many sequence (6xx), most like because of this !

"If every programmer tries to solve that "problem" on their own, you WILL fail with virtually any approach trying to find that "manual" auto increment ;-)"

what you suggest them to do ?


Mark Geerlings,

"That may be true, not as likely, but it is possible.  My statement is still true though: "look for pre-insert triggers with trigger_text that includes "nextval.[sequence_name]" *AND* assigning that value to a column in the table".  "

this mean if I want to check in detail I need to look down to trigger level ?

> the column must be constrained with a unique key
not necessary

> columns that are positive integers,
not necessary either
a sequence can be used to generate an arbitrary string
some even translate the "integer" to a hex string and use that

what you suggest them to do ?

In 10g, it would be great if sequences were used for this task. BUT the implementation should always be following the same pattern, otherwise you will NOT be able (at least not be sure) to capture all location for your research ;-)

Geert pointed it out, too: there are too many ways, this could be done and if your dev team fails to work truly cooperative, then you'll end with a very messy and heterogenous environment :-(

"there are too many ways, this could be done and if your dev team fails to work truly cooperative, then you'll end with a very messy and heterogenous environment :-("

it will always be .. !

"In 10g, it would be great if sequences were used for this task. "

yeah, that;s why you and mark talk about the same thing, sequence with after insert trigger, right?

BEFORE insert trigger!!

I think AFTER insert it give auto increment, right? BEFORE usually for check condition if the input is vaild ?

ok . got it.

tks all.

how about one thing, right now we are using 10g R2 and will upgrade to 12c, then once upgrade do you know how it could be ?

so now 12c has auto_increment type and it is no need trigger and sequence any more ?

see my link from above:
 https://www.experts-exchange.com/questions/29142682/oracle-query-to-find-any-table-AUTO-INCREMENT-column.html?anchorAnswerId=42842221#a42842221

sure.


but assuming no change on table and once migrated to 12c, it is the same table and i can trace like in 10g.

it is the same table and i can trace like in 10g.

??????

The current "code" in 10g will aso work on 12c, yes

so it will be the same !

but it also means developer has to know that one they upgraded to 12c, they can ignore sequence and trigger and start using auto increment ..

but it also means developer has to know that one they upgraded to 12c, they can ignore sequence and trigger and start using auto increment ..

NO! In general: NO! The developers must be aware of what's there before they start "ignoring" stuff! Again: this is NOT just a switch! You have to change the table/column definition in some way and you might have to change existing triggers!

"Again: this is NOT just a switch! You have to change the table/column definition in some way and you might have to change existing triggers"

agree !

I think these 2  link is good to show developer what is before and after:

https://lalitkumarb.wordpress.com/2015/01/20/auto-increment-primary-key-in-pre-12c-releases-identity-functionality/

https://lalitkumarb.wordpress.com/2015/01/20/identity-column-autoincrement-functionality-in-oracle-12c/

Mark Geerlings,

"My statement is still true though: "look for pre-insert triggers with trigger_text that includes "nextval.[sequence_name]" *AND* assigning that value to a column in the table".  "

I am sorry, how can I find out all sequence related to this kind of auto increment design ?

nextval.[sequence_name]

Btw: this syntax is NOT correct!

A good starting point would be:

select *
  from dba_source a
 where a.type = 'TRIGGER'
   and upper(a.text) like '%.NEXTVAL%';

Select allOpen in new window

You may consider filtering OWNER, too...

"A good starting point would be"

you mean there are a lot more information can show and must extend the script further?

"You may consider filtering OWNER, too..."
did

so the NAME column is the sequence name ?


please also input more :

https://www.experts-exchange.com/questions/29142557/is-there-any-queries-that-show-how-many-Oracle-views-objects-has-referring-Oracle-system-table-and-Synonyms.html?headerLink=workspace_participated_questions

I cannot speak for other experts here on EE, but I will NOT do the whole work for you! You should be able to abstract the stuff we provide to solve the problem in the end!

you can see that I already modifying the query myself. just want to have more hints from you.

you can see that I already modifying the query myself.

Sorry, man, you're not really abstracting anything so far!

you mean there are a lot more information can show and must extend the script further?

My intention here was/is, to point out, that you need to abstract what we experts provide you. I will NOT do your work!

so the NAME column is the sequence name ?

This has virtually NO context!! You need to be way more precise!

this case should be done .. I am checking how the conversion tools handle this !