How do I check if a sequence exists or not in Oracle 11g?

Question

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 1

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' ;

Answer 2

Also, keep in mind that you may not be granted to see all sequences in DB. In this case scripts provided above may not work, and you should run something like

SELECT COUNT(*) FROM DBA_SEQUENCES;

But this also may not work if you have no access to DBA_SEQUENCES view.

Check Oracle docs.