Two users can query the same database and get different table lists. The data dictionary holds the descriptions. Ownership, access, and container scope decide which rows come back.
Oracle DBA Lesson 30A — Finding Information About Database Objects
Oracle stores descriptions of database objects in the data dictionary: table names, columns, owners, and who can use each object. It updates those descriptions when definitions or privileges change.
A schema is the set of objects owned by one database user. A view turns a selected slice of that information into rows you can query. Dictionary views answer the everyday questions: which tables belong to this account, which tables can this user reach, and which container holds the object.
Choose the scope
| View | Question it answers |
|---|---|
USER_TABLES |
Which relational tables do I own? |
ALL_TABLES |
Which relational tables can I access, including my own? |
DBA_TABLES |
Which relational tables are in the current container's catalog? |
CDB_TABLES |
From the root, which tables appear in the containers this account can see? |
The same prefixes show up on other dictionary families. Columns differ by family. Check the view you are about to use.
USER_TABLES has no owner column. The connected user is the owner. ALL_TABLES has an OWNER column so rows from different schemas stay distinct. Roles and grants change what the ALL_ views return. DBA_TABLES needs catalog privileges. See data dictionary concepts, USER_TABLES, ALL_TABLES, and DBA_TABLES.
Compare ownership and access
COURSE_OWNER owns four starter tables. COURSE_READER owns none and has SELECT on COURSE_OWNER.ORDERS. Both connect to the same PDB.
Run this in each session:
SELECT table_name
FROM user_tables
ORDER BY table_name;The owner sees DEPARTMENTS, EMPLOYEES, ORDERS, and RECOVERY_MARKER. The reader gets no rows. This query asks about ownership, and the reader owns nothing.
As the reader, list accessible tables that belong to the owner:
SELECT owner, table_name
FROM all_tables
WHERE owner = 'COURSE_OWNER'
ORDER BY table_name;With that grant, the filtered result is COURSE_OWNER / ORDERS. The OWNER predicate limits the report to one schema. It does not add privileges. OWNER is the account that owns the table.
For the current container's catalog, an account with dictionary access uses:
SELECT owner, table_name
FROM dba_tables
WHERE owner = 'COURSE_OWNER'
ORDER BY table_name;A missing row is a scope problem until you prove otherwise. Check the connected user, the current container, the owner filter, and the privileges that view actually uses.
Add container scope
From the root, CDB_TABLES can report tables across containers the account is allowed to see. CON_ID is the container for that row.
SELECT con_id, owner, table_name
FROM cdb_tables
WHERE owner = 'COURSE_OWNER'
ORDER BY con_id, table_name;The common account needs access to the view and suitable CONTAINER_DATA visibility. A root user's default visibility is the root. PDB rows appear when those PDBs are open and unrestricted. Inside a PDB, the matching CDB_ view has the same scope as its DBA_ counterpart. For an inventory, record the reporting account, the connected container, and which containers that account can see. CDB views document the container scope.
Find the view and its columns
DICTIONARY lists dictionary object names and short comments. Use it to pick the view that matches the question.
SELECT table_name, comments
FROM dictionary
WHERE table_name IN
('USER_TABLES', 'ALL_TABLES', 'DBA_TABLES', 'CDB_TABLES')
ORDER BY table_name;DICT_COLUMNS describes the columns. Look up a field before you put it in a report.
SELECT column_name, comments
FROM dict_columns
WHERE table_name = 'ALL_TABLES'
AND column_name IN ('OWNER', 'TABLE_NAME')
ORDER BY column_name;OWNER is the table owner. TABLE_NAME is the table name. DICTIONARY and DICT_COLUMNS are the catalogs for the rest of the dictionary.
Practice
On the course Oracle 19c PDB, use the pre-provisioned owner and reader accounts. Confirm both sessions first:
SHOW USER
SHOW CON_NAMECOURSE_OWNER has the four starter tables. COURSE_READER owns none and already has SELECT on COURSE_OWNER.ORDERS. Compare USER_TABLES in each session, then query ALL_TABLES as the reader with OWNER = 'COURSE_OWNER'. Explain the difference from the grants that are actually there. Optional root reporting uses a common account that can query CDB_TABLES for the containers you intend to inventory. These checks are read-only.
No comments:
Post a Comment