Sometimes you need to see a list of all tables in database. There are several views in the data dictionary you can query, and which ones you can see will depend on your privileges. The easiest way to see all tables in the database is to query the all_tables view:

SELECT owner, table_name FROM all_tables;

Output:

SYS	DUAL
SYS	SYSTEM_PRIVILEGE_MAP
SYS	TABLE_PRIVILEGE_MAP
SYS	USER_PRIVILEGE_MAP
SYS	STMT_AUDIT_OPTION_MAP
SYS	AUDIT_ACTIONS
SYS	WRR$_REPLAY_CALL_FILTER
SYS	KU$_PLSQL_SRC_TBL
SYS	HS_BULKLOAD_VIEW_OBJ
SYS	HS$_PARALLEL_METADATA
SYS	HS_PARTITION_COL_NAME
SYS	HS_PARTITION_COL_TYPE
XDB	XDB$IMPORT_TT_INFO
XDB	XDB_INDEX_DDL_CACHE
XDB	XDB$IMPORT_QN_INFO
XDB	XDB$IMPORT_NM_INFO
XDB	XDB$IMPORT_PT_INFO
SYSTEM	HELP
CTXSYS	DR$OBJECT_ATTRIBUTE
CTXSYS	DR$POLICY_TAB
CTXSYS	DR$THS
CTXSYS	DR$THS_PHRASE
CTXSYS	DR$NUMBER_SEQUENCE
MDSYS	SRSNAMESPACE_TABLE
MDSYS	SDO_UNITS_OF_MEASURE
MDSYS	SDO_PRIME_MERIDIANS
MDSYS	SDO_ELLIPSOIDS
...

This will show the owner (the user) and the name of the table. You don’t need any special privileges to see this view, but it only shows tables that are accessible to you.

If you have administrator privileges, you can query the dba_tables view, which gives you a list of all tables in the database.

My site is free of ads and trackers. Was this post helpful to you? Why not