How do I check if a sequence exists or not in Oracle 11g?
I am using Oracle 11g. I want to be able to determine whether a particular sequence exists or not. I have tried the code below but it is not working. (It is returning 0 as count value when there should be more):
SELECT COUNT(*)
FROM user_sequences
WHERE sequence_name = 'SCHEMA.SEQUENCE_NAME';
If anyone knows why this is, please help me.
Answer
If you are running the query as user MP then try it like this:
SELECT COUNT(*)
FROM user_sequences
WHERE sequence_name = 'SEQ_SSO_KEY_AUTHENTICATION';
else, try it like this:
SELECT COUNT(*)
FROM all_sequences
WHERE sequence_name = 'SEQ_SSO_KEY_AUTHENTICATION'
AND sequence_owner = 'MP' ;