Get list of all tables in Oracle?
To fetch visible tables according to your access credentials, go with SELECT table_name FROM all_tables;. In case you are after only the tables in your possession, it's SELECT table_name FROM user_tables; to the rescue. For power users i.e., DBAs probing into the entirety of database tables, SELECT table_name FROM dba_tables; plays the part. Your scholarly privilege reflects through your table list.
Opting to extend beyond just obtaining table names, sprinkle some readability magic onto your queries with sqlplus configuration, say set linesize 167, set pagesize 1000, or set colsep '|'. These settings ensure your results to not only enchant but also comfort your eyes.
Schema surfboarding
Masterclass on your tables
Graduate from just getting table names to uncovering their mysteries with SELECT statements! Querying all_tab_columns provides a mapping quest from tables to columns and other metadata, such as data_type and nullable status. When trying to decipher the riddle of joining tables or demystifying their relationships, column-level investigations could be your secret parchment map.
Precision-seeking with pattern matching
Befriend the LIKE operator for an in-depth query filtering and pattern matching. Hunting for tables sharing a common prefix, say 'EMP_', you would go like SELECT table_name FROM all_tables WHERE table_name LIKE 'EMP_%';. Scouting for columns containing 'DATE', go ahead and use SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%DATE%';.
Surfboarding sans system tables
To dodge Oracle's system furniture and focus keenly on user-crafted tables, exclude SYS and SYSTEM tables. You could pull this off applying a WHERE clause such as WHERE owner NOT IN ('SYS', 'SYSTEM'). Cleanse your results from Oracle's internal melee, and bask in the beauty of user-relevant tables.
Backward compatibility callbacks
Don't fret if you've vaulted onto legacy treasure chests! Oracle maintains timeworn dictionary views like DBA_TABS, TAB, and CAT for our rusty friends. These views house treasure keys for legacy codes or transitions towards the fresher and sparklier views. A word of caution - these are primarily backward compatibility knight riders and may lack the glint of newer metadata twinkles present in dba_tables or all_tab_columns.
The Oracle's guide to problem-solving
Dictionary views and system privileges
DBA views contain hidden Oracle riddles, not all users are handshake-worthy. In case you are among the chosen ones (not a DBA) but require a greater dictionary view passage, you might need blessings in the form of SELECT ANY DICTIONARY or a role like SELECT_CATALOG_ROLE. As always, be a responsible wizard and ensure proper security practices and mandates have been catered before accessing these views.
High level quests and Column discovery
Sometimes, table names and simple column details just don't cut the deal. It's not you, it's complex queries. Fear not, richer metadata hides in dba_tab_columns where you can summon column details, data types, and even the tablespace to which the table pays taxes! The complexity might require alliances with dba_constraints for finding columns of specific data types or those under certain constraints.
Namespace navigation
Remember, Oracle schemas behave as namespaces. The owner field separates tables by schema, much like kingdoms separated by a king's rule. To list tables owned by a specific user, use SELECT table_name FROM all_tables WHERE owner = 'YOUR_SCHEMA_NAME';. Ever dealt with a multi-schema database where objects hold similar names but pledge loyalty to different users? You got this!
Was this article helpful?