There's no query to do that.
You need to make a select for every table..... but why would you want this????
I am working on a project, where I want to make a transformation of coordinates. In the table MDSYS.CS_SRS I have created a new SRID (coordinate system) which is based on a datum (European 1950). The information about that datum, I thought was available in the table MDSYS.SDO_DATUMS, but whatever I make of changes in that table, it never effects the transformation. Therefore the data for the datum 'European 1950' must be available somewhere else, but I can't figure out where. That is the reason for my request - I seek all table names containing the text string value 'European 1950'.