I apologize if this has been answered elsewhere (There has to be something out there on the topic), but I can't seem to find a concise answer to my question.
I am relatively new to SQL, and when I have worked with it I have only used basic statements. Now I am working with a pretty large database (in Oracle) and was asked to explore it a little bit on the development side to get more familiar.
One of the questions that was sent to me to explore the db involved finding a list of "Run Controls" that are associated with a particular user.
There is a single table that keeps track of the different types of "Run Controls" that exist via a field titled
WHERE oprid = 'jle0010'
AND run_cntl_id = 'Copy_Jrnl';
If all of the 3,000 tables have the same columns then your query is going to look like :
SELECT * FROM ( select * from PS_JRNL_COPY_REQ union all select * from other_table_1 union all select * from other_table_2 union all ... and so on for 2,997 more) WHERE oprid = 'jle0010' AND run_cntl_id = 'Copy_Jrnl';
No guarantees on it parsing or running though.
You can build that inline view by querying user_tables of course.