82

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.

Pete B.
  • 3,188
  • 6
  • 25
  • 38
sheidaei
  • 9,842
  • 20
  • 63
  • 86

2 Answers2

152
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.

Mark J. Bobak
  • 13,720
  • 6
  • 39
  • 67
  • 1
    I am getting the following error: Error at line 1 ORA-00942: table or view does not exist – sheidaei Feb 12 '14 at 20:08
  • 10
    This one worked for me: select * from all_sequences; – sheidaei Feb 12 '14 at 20:10
  • 2
    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?" – Mark J. Bobak Feb 12 '14 at 20:35
  • 1
    `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;` – Lorenzo Lerate May 16 '17 at 15:20
  • That's correct, Lorenzo. That's why I actually mentioned that it my answer. See the last line. – Mark J. Bobak May 19 '17 at 13:22
49

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

select * from user_sequences;
Pete B.
  • 3,188
  • 6
  • 25
  • 38
  • 6
    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. – Mark J. Bobak Jul 22 '19 at 18:59