Searching for Entities

Search for entities in the Catalog by entering a search string (query) into the Search catalog field at the top of the Catalog page. You can type or paste in a new search string, edit an existing one, or construct a search string by clicking in the field and selecting items from the drop-down list.

The search operates on the entities that meet the filter criteria you have set in Filters panel. To clear the Search catalog field, click the X icon.

Entering Search Strings

Syntax and instructions for creating search strings are presented below.

You can enter a search string into the Search catalog field in several ways:

• Click an item under Suggestions on the right side of the page. When you click one of these items, a search string is entered into the Search catalog field and the search is run. This is the default type of search named, Simple search.

• Type or paste a string directly. When you start typing, suggestions are shown in the drop-down list below the Search catalog field. You can keep typing or you can select a suggestion from the list (see next bullet).

• Select or construct a search string by clicking the Search catalog field and selecting items using Advanced search toggle button.

  If the Search catalog field is empty, the drop-down list contains basic search parameters you can select to start building the query. When you select one, or when you click one of the links under Suggestions, the string is inserted in the Search catalog field, and the drop-down list is updated to present possible additional parameters. You can choose the additional parameters, or edit the string directly, or both.

  If the Search catalog field is empty and if you set Save last 5 search queries in your preferences, the last five search strings that you entered into the Search catalog field will also appear in the drop-down. (Searches initiated by clicking one of the items under Suggestions won't appear in this list.)

When you finish constructing the string, press Enter or click the arrow button on the right side of the field.

Click the magnifier icon at the end of the Search field to accept the default query and search for all the current owner's tables, views, and analytic views.

Enter a string in the Search Catalog field to find the entities (schema, table, or view) whose label includes the specific string you enter. Click on the Use as Query Scope icon to save your search. This allows you to store frequently used searches and access them instantly without any inconvenience.

Selecting Query Scope icon prompts a Catalog User Preferences wizard which enables you to save your catalogs based on your preferences.

Description of the illustration query-scopes-tab.png

You can view, create, and edit the previously saved query scopes in this wizard. The query scopes are categorized based on by whom its created.

• Select Custom to view, create, edit, or delete the query scopes created by you.
• Select Predefined to view the query scopes which are already defined by the Database Actions. This option does not allow you to create, edit, or delete the query scopes.
• Select All to view all the query scopes. This includes Custom and Predefined query scopes.

Specify the following fields in the Query Scope tab.

• Name: Enter the name of the Query Scope. This is a mandatory field.
• Label: This is a mandatory field. Enter a descriptive name here. You will use this field to refer to a query scope.
• Definition: Enter the Oracle Autonomous Database Data Definition Language (DDL) that creates the search entity. This is the same search criteria you enter in the Search Catalog field.

Click Create to create the Query Scope. Click Cancel to cancel its creation.

Once you have created the new query scope, it is visible in the list of query scopes in the Catalog User Preferences wizard. Click New to create a new query scope

This wizard also appears on selecting Catalog User Preferences. See Catalog User Preferences for details.

Create a saved search

You can save your time from redefining the same search again in the future. You can diagnose problems faster since you are just few clicks away from accessing a saved search. Here is how you can create a saved search. Ensure the Advanced search toggle is on.

1. From the browse objects drop-down list, select the objects from where you want to search the entities (schema, table, or view).
2. Specify the search criteria in the Search Catalog field. For example, you want to search for a specific Entity type and a specific owner.
3. Select Save the Search from the drop-down next to the magnifier icon.
4. Clicking the icon prompts the Catalog User Preferences wizard.
5. Enter the name of the saved search in the Title field. The wizard automatically generates the Scope of the search The definition of the search is also automatically created by concatenating fields used in the Search Catalog field, for example, type: TABLE units AND owner: ADPTEST.
6. Enter description of the search in Description filed. This is not a mandatory step.
7. Select Create to create the saved search. Click Cancel to cancel its creation.

After the creation of the saved search, it appears on the list of Saved Searches.

You can change the columns displayed in the search results by clicking the pencil icon in the Actions column. Click the delete icon in the Actions column to delete the search you save. The saved searches you create are available for selection in the Saved Search panel in the right of the Catalog page.

Click New to create a new saved search.

This wizard also appears on selecting Catalog User Preferences. See Catalog User Preferences for details.

Basic Structure

The query string consists of a set of search terms. Here are some examples.

• sales
• type:TABLE
• owner!=SH
• #deployment
• type:TABLE,VIEW

Combine search terms by using the two Boolean operators AND and OR:

• sales and type:TABLE
• sales or type:TABLE

If you don't specify an explicit operator, then AND is assumed. All of the following are equivalent:

• sales type:TABLE owner:sh
• sales AND type:TABLE owner:sh
• sales type:TABLE AND owner:sh
• sales AND type:TABLE AND owner:sh

Negate a single search term by prefacing it by either NOT or by a - (hyphen). All of the following exclude tables from the search:

• sales and NOT type:TABLE
• sales -type:TABLE

Enclose search terms in parentheses to control order:

• sales and (type:TABLE or type:VIEW)
• sales and NOT (type:TABLE or type:VIEW)

If you don't add parentheses, the operators are read from left to right. The following are equivalent:

• sales or type:TABLE and owner:SH
• (sales or type:TABLE) and owner:SH

Search Terms

Search terms come in three forms:

• Simple Search Terms
• Property Search Terms
• Classification Search Terms

Simple Search Terms

A simple search term is a simple string, with or without quotes.

• Unquoted string: sales
• Single-quoted string: ‘sales’
• Double-quoted string: “sales”

All three types compare the given value, sales in the examples above, to the ENTITY_NAME, but they differ in how the comparison works, as shown in the table below:

Search String	Equivalent SQL WHERE Clause	Comment
sales	WHERE REGEXP_LIKE(entity_name, 'SALES')	A plain regular expression.
'sales'	WHERE REGEXP_LIKE(entity_name, 'sales')	Similar to the unquoted version, but the search term is not automatically converted to uppercase.
sales costs	WHERE REGEXP_LIKE(entity_name, 'SALES') AND REGEXP_LIKE(entity_name, 'COSTS')	Treated as two independent search terms combined with the AND operator.
'sales costs'	WHERE REGEXP_LIKE(entity_name, 'sales costs')	Treated as a single search term. You use single quotes to handle spaces and other special characters, such as colons.
'don''t'	WHERE REGEXP_LIKE(entity_name, 'don''t')	Escaped single quotes within a search string.
"sales"	WHERE entity_name = 'sales'	The search string is used as an exact match.

Property Search Terms

A property search term is a combination of three items:

1. The name of a search property (for example, name, type, or owner)
2. A search operator (for example, :, >, or !=)
3. A search value (for example, sales, 'sales', or "sales")

For example:

• name:sales
• owner=SH
• daysSinceCreated>20

You can also specify a comma-delimited list of search values when using the operators :, =, or ~=.

• If the operator is : or =, then the condition works like an IN LIST. For example, name:X,Y is equivalent to entity_name IN ('X', 'Y') and to (entity_name = 'X' OR entity_name = 'Y').
• If the operator is !=, then the condition works like a NOT IN LIST. For example, type!=TABLE,VIEW is equivalent to entity_type NOT IN ('TABLE', 'VIEW') and to (entity_type != 'TABLE' AND entity_type != 'VIEW').

The property name must be one of the following predefined strings. Property names are case sensitive, so TYPE, for example, is not supported.

Some properties apply to all entity types, and some properties apply only to a specific entity type, as shown in the two tables below.

The following table shows those properties that apply to all entity types.

Property	Meaning
application	The name of the entity application as defined by the ALL_LINEAGE_APPLICATIONS view. Examples include DATABASE and INSIGHTS.
created	The timestamp when the entity was created.
dateCreated	The date when the entity was created. This is the same as created, but truncated to the nearest day.
dateUpdated	The date when the entity was last updated. This is the same as updated, but truncated to the nearest day.
daysSinceCreated	The number of days since the entity was created. The value is zero if the entity was created today.
daysSinceUpdated	The number of days since the entity was last updated. The value is zero if the entity was updated today.
link	The name of the database link where the entity is defined. This can be used to search entities in other, linked, databases.
local	The value YES if the entity is defined within the database itself; otherwise, the value NO. Tables or Insights defined in a schema are examples of local entities. Objects in cloud storage, such as CSV or Parquet files, are examples of entities that are not local.
name	The name of the entity.
namespace	The namespace of the entity as defined by ALL_LINEAGE_NAMESPACES.
oracleMaintained	The value YES, if the entity is created and maintained by Oracle; otherwise, the value NO. The ALL_TABLES view is an example of an Oracle maintained entity.
owner	The owner of the entity.
parent	The full entity path of the parent entity, if it exists.
parentName	The name of the parent entity, if it exists.
parentPath	The full entity path of the parent entity, if it exists.
parentType	The type of the parent, if it exists.
path	The full path of the entity.
rootName	The name of the outermost containing entity. If the entity has no parent, then the rootName is equal to the entity name. If the entity does have a parent, then the rootName is defined, recursively, as the rootName of the parent entity.
rootNamespace	The namespace of the outermost containing entity. If the entity has no parent, then the rootNamespace is equal to the entity namespace. If the entity does have a parent, then the rootNamespace is defined, recursively, as the rootNamespace of the parent entity.
type	The entity type, as defined by ALL_LINEAGE_ENTITY_TYPES.
updated	The timestamp when the entity last updated.

Some searchable properties are specific to certain entity types. The following table shows those entity-type-specific properties. When searching for entities with these properties, it will speed up your search to specify the entity type. For example, instead of just searching on numRows, specify the entity type that has the numRows property:

TABLE AND numRows > 10

Entity Type	Properties
TABLE	numRows - The number of rows in the table. status - The status of the object: VALID or INVALID. partitioned - Indicates whether the table is partitioned (YES) or not (NO). external - Indicates whether the table is an external table (YES) or not (NO). sharded - Indicates whether the object is sharded (Y) or not (N).
COLUMN	dataType - The data type of the column. nullable - Indicates whether a column allows NULLs. The value is N if there is a NOT NULL constraint on the column or if the column is part of a PRIMARY KEY. Otherwise, the value is Y.
PACKAGE	status - The status of the object: VALID or INVALID.
PROCEDURE	status - The status of the object: VALID or INVALID.
ATTRIBUTE_DIMENSION	status - The status of the object: VALID or INVALID.
HIERARCHY	status - The status of the object: VALID or INVALID.
ANALYTIC_VIEW	status - The status of the object: VALID or INVALID.
MEASURE	dataType - Data type of the measure, such as NUMBER. nullable - Indicates whether a column allows NULLs. The value is N if there is a NOT NULL constraint on the column or if the column is part of a PRIMARY KEY. Otherwise, the value is Y. measureType - Type of the OLAP measure: BASE - Base measures store the data DERIVED - Derived measures calculate the data from base measures; also called calculated measures.
MINING_MODEL	status - The status of the object: VALID, INVALID, or N/A.
FUNCTION	status - The status of the object: VALID, INVALID, or N/A.
DIRECTORY	status - The status of the object: VALID, INVALID, or N/A.
EXTERNAL_LOCATION	fileName - The name of a file in Oracle Directories. url - The URI of file a file in object storage.
LIVE_TABLE_FEED	enableNotifications - Indicates whether a live table feed is enabled for notifications (TRUE) or not (FALSE).
ANALYTIC_DASHBOARD	status - The status of the object: VALID or INVALID.
ANALYTIC_REPORT	status - The status of the object: VALID or INVALID.
SHARE	status - The status of the object: VALID or INVALID.
SHARE_PROVIDER	status - The status of the object: VALID or INVALID.
SHARE_RECIPIENT	status - The status of the object: VALID or INVALID.

Note:

You can find the list of query-type-specific properties by running the following query on The SQL Page:

select *
from (
   select
      entity_type,
      JSON_QUERY(annotation, '$.searchProperties') properties
   from all_lineage_entity_types)
where properties is not null;

The operator must be one of the following.

Property	Meaning
=	Equal to
!=	Not equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
:	Equal to For example, type:TABLE is the same as type=TABLE.
~=	Represents REGEXP_LIKE For example, the following two search items are equivalent: sales name~=sales

The value of the property search term is a string. As with the simple search term, this string can be unquoted, single-quoted, or double-quoted. Unquoted strings are converted to uppercase and quoted strings are left as they are.

Search String	Equivalent SQL WHERE Clause
name=sales	WHERE entity_name = 'SALES'
name='sales'	WHERE entity_name = 'sales'
type:table	WHERE entity_type = 'TABLE'
parent~='COSTS'	WHERE REGEXP_LIKE(entity_parent_path, 'COSTS')
daysSinceCreated>10	WHERE TRUNC(CURRENT_TIMESTAMP AT TIME ZONE ''UTC'') - TRUNC("CREATED") > 10
owner!="DemoUser"	WHERE owner != 'DemoUser'
parentName:null	WHERE entity_parent_name IS NULL
parentName!=null	WHERE entity_parent_name IS NOT NULL

Classification Search Terms

Classifications are metadata about an entity, such as a caption or a description. Classification search terms are similar to property search terms, but they work with entity classifications such as captions or descriptions instead of standard entity properties. The name of the classification must be prefixed by a hash tag (#) and may be unquoted or enclosed in double quotation marks ("). A query converts unquoted classifications to uppercase, which means that the following forms of the CAPTION classification are equivalent.

• #caption
• #Caption
• #CAPTION
• #"CAPTION"

Classifications are, by their nature, multi-lingual in the sense that the value can vary by National Language Support (NLS) language. By default, the query syntax uses the value of the classification in the current NLS language. You can specify a specific NLS language by adding the name of the language after a forward slash. The language can be quoted, but the query converts it to uppercase in all forms.

• #caption/French
• #caption/"ITALIAN"

If the name of the specified language contains a space (for example, "CANADIAN FRENCH"), you must either enclose the name in quotes or replace the space with an underscore character (or both). The following are equivalent:

• #caption/Canadian_French
• #caption/"CANADIAN FRENCH"
• #caption/"Canadian_French"

If you use a classification name on its own as a search term, then the search returns entities that have any non-null value for that classification. If you've defined a classification called DEPLOYED, for example, then you can see all entities with the classification by using a simple term:

• #deployed

As with other search terms, you can negate it using NOT or - (hyphen). To exclude all entities with the DEPLOYED classification, you can use either of the following searches:

• NOT #DEPLOYED
• -#deployed

You can also use classifications with operator/value pairs, with the same semantics used by property search items; for example:

• #caption~=sales
• #description/Spanish=Ventas

Searching on Multiple Properties

The default for this is ENTITY_NAME, so that if you enter a query string like "sales" it will search for entities with "sales" in the name.

You can also list multiple properties and classifications in this argument; for example, entity_name #caption #description, which will cause it to search for entities with sales in their name or in any CAPTION or DESCRIPTION classifications they may have.