1

I'm trying to search for something particular in the Oracle database, but I don't know if it is a table, or schema, or user, etc...

Is there any command that allows to make a search for string if its type is unknown?

For example: SQL> SHOW STRING 'the_string'

Phillip
  • 43
  • 1
  • 12
  • 2
    You may get an exact answer here, but may we ask how you ended up in this situation? I mean, a user is a very different thing than a table. – Tim Biegeleisen Apr 22 '19 at 14:20
  • You could search every column of `INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES WHERE SCHEMA_NAME = @value OR TABLE_NAME = @value OR COLUMN_NAME = @value` – Dai Apr 22 '19 at 14:20
  • That won't work in Oracle, @Dai. But yes, the idea is OK - for tables and columns. The main problem is in Phillip's "etc", because it seems that the "search object" can be virtually *anything*. From my point of view, the question itself is meaningless, but hey - what do I know? – Littlefoot Apr 22 '19 at 14:29
  • Assume that a user has asked to grab something from "OBJECT" but they weren't specific on what "OBJECT" even was. I'm just tired of asking the users to be specific in a followup email. That is the situation. – Phillip Apr 22 '19 at 17:14

3 Answers3

4

Try this:

select * from all_objects where object_name like '%your object name%'

Please note also,

  • ALL_OBJECTS describes all objects accessible to the current user.
  • DBA_OBJECTS describes all objects in the database.

You would need the proper access rights assigned to your user.

OldProgrammer
  • 12,050
  • 4
  • 24
  • 45
  • 1
    If `%your object name%` is lower case characters, , that works; but object names are usually UPPER CASE so you may want to `select * from all_objects where lower(object_name) like '%your object name%';` – Mark Stewart Apr 22 '19 at 15:58
2

Here is a unified query that should help:

SELECT 'User/Schema' match_type,
        username
FROM dba_users -- if you have access to dba_users uses all_users instead
WHERE UPPER(username) LIKE '%THE_STRING%'
UNION
SELECT object_type,
       owner || '.' || object_name object_name
FROM dba_objects  -- or all_objects if no access to dba_objects
WHERE UPPER ( owner || '.' || object_name ) LIKE '%THE_STRING%'
ORDER BY 1, 2
/

Note, the dba_ views will show everything (that matches), the all_ views will only show matches for objects that you have access to.

TenG
  • 3,843
  • 2
  • 25
  • 42
0

In addition to OldProgrammer's answer, if that query returns no information, to find if the name is a user ID (which is basically synonymous with schemas), you need to query ALL_USERS or DBA_USERS:

select username from all_users where lower(username) like '%your_name_to_find%';
Mark Stewart
  • 2,046
  • 4
  • 22
  • 32