1

this query produces a list of all the tables that begin with V$.

select TABLE_NAME
from DICTIONARY
where TABLE_NAME LIKE 'V$%';

how do I add the remaning prefixes to this query? I have tried parenthesis, using AND, ||, OR. I cannot find the answer to this in googling although I have gotten this far in searching, I believe the % I learned from a answer to a diff question.

If not for the single query requirement I would just make a script that runs the query three times once for each prefix which works great, I just cant figure out how to consolodate it into one query. thanks.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
Robert Pait
  • 11
  • 4
  • `or` should work fine. How did you write that query? – GolezTrol Feb 15 '15 at 08:54
  • thanks to this post I think I solved it http://stackoverflow.com/questions/16364187/combining-2-sql-queries-and-getting-result-set-in-one. – Robert Pait Feb 15 '15 at 08:55
  • select table_name from dictionary where table_name like 'V$_%' or table_name like 'ALL_%' or table_name like 'USER_%'; can someone verify this is the best way to do this as it did produce the desired resulting list.... – Robert Pait Feb 15 '15 at 08:55
  • Good that it's solved. Can you post the answer? Maybe it will be helpful to future visitors. – GolezTrol Feb 15 '15 at 08:57
  • For all the v$ views and filter the tables, you could follow my answer. – Lalit Kumar B Feb 15 '15 at 13:59
  • @GolezTrol, the query I posted above is the answer I submitted that produced the results I needed, I just wanted to make sure that it was the most efficient query to get a single response that selected tables with the three prefixes. – Robert Pait Feb 16 '15 at 15:33
  • @Lalit Kumar B, The goal was to get an answer that included the three prefixes and excluded all the other tables. – Robert Pait Feb 16 '15 at 15:34

2 Answers2

1

How about V$FIXED_TABLE.

From the docs,

This view displays all dynamic performance tables, views, and derived tables in the database. Some V$ tables (for example, V$ROLLNAME) refer to real tables and are therefore not listed.

You could filter the TABLE or VIEW using the TYPE column.

For a broader result, you could have a look at V$FIXED_VIEW_DEFINITION. From the docs,

This view contains the definitions of all the fixed views (views beginning with V$). Use this table with caution. Oracle tries to keep the behavior of fixed views the same from release to release, but the definitions of the fixed views can change without notice. Use these definitions to optimize your queries by using indexed columns of the dynamic performance tables.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
0
SELECT table_name
FROM dictionary 
WHERE table_name LIKE 'V$\_%' ESCAPE '\'
  OR table_name LIKE 'ALL\_%' ESCAPE '\'
  OR table_name LIKE 'USER\_%' ESCAPE '\';
GolezTrol
  • 114,394
  • 18
  • 182
  • 210
Robert Pait
  • 11
  • 4
  • 1
    you should append `ESCAPE '\'` to every `LIKE` condition. Underscore is a wildcard for any character. Like '?' in Unix. – ibre5041 Feb 16 '15 at 15:51
  • @ibre5041- can you give an example? I used underscore because all the tablenames began like the prefix example, are you saying its redundant because the % sign would take it anyways? – Robert Pait Feb 16 '15 at 15:55