Pre-General Availability: 2023-04-04
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.
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.
- From the browse objects drop-down list, select the objects from where you want to search the entities (schema, table, or view).
- Specify the search criteria in the Search Catalog field. For example, you want to search for a specific Entity type and a specific owner.
- Select Save the Search from the drop-down next to the magnifier icon.
- Clicking the icon prompts the Catalog User Preferences wizard.
- 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.
- Enter description of the search in Description filed. This is not a mandatory step.
- 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
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 |
|
A plain regular expression. |
'sales' |
|
Similar to the unquoted version, but the search term is not automatically converted to uppercase. |
sales costs |
|
Treated as two independent search terms combined
with the |
'sales costs' |
|
Treated as a single search term. You use single quotes to handle spaces and other special characters, such as colons. |
'don''t' |
|
Escaped single quotes within a search string. |
"sales" |
|
The search string is used as an exact match. |
Property Search Terms
A property search term is a combination of three items:
- The name of a search property (for example,
name
,type
, orowner
) - A search operator (for example,
:
,>
, or!=
) - 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 anIN LIST
. For example,name:X,Y
is equivalent toentity_name IN ('X', 'Y')
and to(entity_name = 'X' OR entity_name = 'Y')
. - If the operator is
!=
, then the condition works like aNOT IN LIST
. For example,type!=TABLE,VIEW
is equivalent toentity_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 |
|
COLUMN |
|
PACKAGE |
|
PROCEDURE |
|
ATTRIBUTE_DIMENSION |
|
HIERARCHY |
|
ANALYTIC_VIEW |
|
MEASURE |
measureType - Type of the OLAP measure:
|
MINING_MODEL |
|
FUNCTION |
|
DIRECTORY |
|
EXTERNAL_LOCATION |
|
LIVE_TABLE_FEED |
|
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, |
~= |
Represents For example, the following two search items are equivalent:
|
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.