How to Search for Objects in Oracle Database

This post explains how to search for objects (tables, views, sequences, etc.) in an Oracle database using SQL.

Sample SQL Command

 1set pages 999
 2col owner format a15
 3col object_name format a40
 4col object_type format a20
 5select owner
 6,      object_name
 7,      object_type
 8from   dba_objects
 9where  lower(object_name) like lower('%&object%')
10order by owner, object_type, object_name
11/

Sample Oracle Output:

 1OWNER           OBJECT_NAME                              OBJECT_TYPE
 2--------------- ---------------------------------------- -----------------
 3ORDSYS          flashType172_T                           TYPE
 4PUBLIC          DBMS_FLASHBACK                           SYNONYM
 5PUBLIC          FLASHBACK_TRANSACTION_QUERY              SYNONYM
 6PUBLIC          GV$FLASHBACK_DATABASE_LOG                SYNONYM
 7PUBLIC          GV$FLASHBACK_DATABASE_LOGFILE            SYNONYM
 8PUBLIC          GV$FLASHBACK_DATABASE_STAT               SYNONYM
 9PUBLIC          V$FLASHBACK_DATABASE_LOG                 SYNONYM
10PUBLIC          V$FLASHBACK_DATABASE_LOGFILE             SYNONYM
11PUBLIC          V$FLASHBACK_DATABASE_STAT                SYNONYM
12PUBLIC          V$FLASH_RECOVERY_AREA_USAGE              SYNONYM
13SYS             DBMS_FLASHBACK                           PACKAGE
14SYS             DBMS_FLASHBACK                           PACKAGE BODY
15SYS             FLASHBACKTBLIST                          TYPE
16SYS             FLASHBACK_TRANSACTION_QUERY              VIEW
17SYS             GV_$FLASHBACK_DATABASE_LOG               VIEW
18SYS             GV_$FLASHBACK_DATABASE_LOGFILE           VIEW
19SYS             GV_$FLASHBACK_DATABASE_STAT              VIEW
20SYS             V_$FLASHBACK_DATABASE_LOG                VIEW
21SYS             V_$FLASHBACK_DATABASE_LOGFILE            VIEW
22SYS             V_$FLASHBACK_DATABASE_STAT               VIEW
23SYS             V_$FLASH_RECOVERY_AREA_USAGE             VIEW
24
2521 rows selected.

Purpose:

The provided Oracle Database SQL code aims to search for objects (tables, views, sequences, etc.) within the database based on a user-defined search term. It retrieves the owner, object name, and object type for matching objects, presented in a user-friendly format.

Breakdown:

1. SET PAGES 999: This command sets the output display to accommodate potentially large result sets, preventing truncation.
2. Column Formatting:
   • col owner format a15: Defines the output width for the owner column as 15 characters, ensuring alignment and readability.
   • col object_name format a40: Sets the object_name column width to 40 characters.
   • col object_type format a20: Allocates 20 characters for the object_type column.
3. SELECT Clause:
   • SELECT owner, object_name, object_type: Specifies the columns to be retrieved from the query.
   • FROM dba_objects: Indicates the source table, dba_objects, which contains information about database objects.
4. WHERE Clause:
   • lower(object_name) LIKE lower('%&object%'): Filters the results based on the user's input (&object), converted to lowercase for case-insensitive matching. The wildcard characters (%) allow for partial matches within the object name.
5. ORDER BY Clause:
   • ORDER BY owner, object_type, object_name: Sorts the output by owner, object type, and then object name, providing a logical organization.

Key Points:

• Replace &object: Substitute this placeholder with the actual search term you want to use.
• Case-Insensitive Search: The LOWER function ensures case-insensitive matching, making the search more flexible.
• Wildcard Matching: The % wildcards enable finding objects with names containing the search term at any position.
• Object Types: The query retrieves information for various object types, including tables, views, sequences, indexes, and more.
• Customization: You can modify the column formatting and sorting criteria to suit your preferences.

Insights and Explanations:

• The dba_objects view is a valuable resource for exploring the database schema and identifying objects.
• The LIKE operator with wildcards provides a versatile way to search for objects based on patterns.
• Lowercase conversion in the WHERE clause ensures that the search is not case-sensitive, making it more user-friendly.
• The ORDER BY clause helps organize the results in a meaningful way, facilitating easier browsing and identification of relevant objects.

By understanding these aspects, you can effectively use this code to search for objects in your Oracle database and gain insights into its structure.