Règle d'automatisation Jira pour le merge d'une pull request
Posted by: AJ Welch
As with most relational databases, there may come a situation where you need to view the the underlying metadata and look through the actual table list and ownership of your database. Thankfully, there are multiple ways to perform this relatively simple task in Oracle, so we’ll briefly explore each option below to find which best suits your needs.
Au-delà d'Agile
A data dictionary in Oracle is a collection of read-only tables that provide useful information about the database including schemas, users, privileges, and even auditing data. The values in these stored dictionaries are updated automatically by Oracle anytime a statement is executed on the server that modifies data.
From there, the read-only dictionaries can be read and queried just like any standard table, which as we’ll see below provides some very useful functionality.
Au-delà d'Agile
At the most basic level, you may wish to view a list of all the tables owned by the current Oracle user. This can be accomplished with a simple SELECT query on the USER_TABLES data dictionary.
Once connected to Oracle, issue this statement:
SELECT
table_name, owner
FROM
user_tables
ORDER BY
owner, table_nameThis will return a list of all tables that the current user is owner of, as specified in the owner column.
Au-delà d'Agile
In a situation where you’re only interested in what tables the current Oracle user has access to, regardless of ownership, you’ll use the ALL_TABLES data dictionary instead.
SELECT
table_name, owner
FROM
all_tables
ORDER BY
owner, table_nameIt’s likely that this query will return far more results than you are interested in since you’re viewing everything even remotely accessible to the user, so you may wish to limit your query by specifying an appropriate owner, like so:
SELECT
table_name, owner
FROM
all_tables
WHERE
owner='schema_name'
ORDER BY
owner, table_nameAu-delà d'Agile
Lastly, when you absolutely need to view every table in the system, look no further than the great and powerful DBA_TABLES data dictionary.
SELECT
table_name, owner
FROM
dba_tables
WHERE
owner='schema_name'
ORDER BY
owner, table_nameIt is important to note that this final DBA_TABLES dictionary may require user privileges beyond what the current user has. If necessary, you may need to be granted the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role. More information on granting these privileges can be found in the official documentation.