How can I get all sequences in an Oracle database?

Question

Is there any command that I can run so that I can get all the sequences? I am using Oracle 11g. I am using Toad for Oracle to connect to it. I can visually see the sequences in Toad, but I like to know the command line for it.

Roundlet

Answer 1

select sequence_owner, sequence_name from dba_sequences;


DBA_SEQUENCES -- all sequences that exist 
ALL_SEQUENCES  -- all sequences that you have permission to see 
USER_SEQUENCES  -- all sequences that you own

Note that since you are, by definition, the owner of all the sequences returned from USER_SEQUENCES, there is no SEQUENCE_OWNER column in USER_SEQUENCES.

Writeoff

I am getting the following error: Error at line 1 ORA-00942: table or view does not exist – Roundlet

This one worked for me: select * from all_sequences; – Roundlet

Yes, that's why I mentioned all three views, DBA/ALL/USER_SEQUENCES. Everyone has access to USER_ and ALL_ views, but not necessarily DBA_ views. It depends on your local security policies. To be clear, if you don't have access to DBA_ views, then you really can't answer the question "How do I get all the sequences that exist in the database?" – Writeoff

USER_SEQUENCES doesn't have the column 'SEQUENCE_OWNER' so the query would result in select * from USER_sequences; or select sequence_name from USER_sequences; – Jaguarundi

That's correct, Lorenzo. That's why I actually mentioned that it my answer. See the last line. – Writeoff

Answer 2

You may not have permission to dba_sequences. So you can always just do:

select * from user_sequences;

Niggle

No, you cannot. The question was "Is there any command that I can run so that I can get all the sequences?" USER_SEQUENCES only gives you the sequences you own. ALL_SEQUENCES only gives you the sequences you have permissions on. To correctly (and fully) answer the question, you need DBA_SEQUENCES. If you don't have access to DBA_SEQUENCES, you don't have any way to answer the question. – Writeoff