Common Oracle SQL Queries to fetch the database table in Oracle fusion

Here are some of the common Oracle SQL queries to retrieve metadata in Oracle databases, including table details, columns, constraints, indexes, and more. 

These queries are especially useful when working with Oracle Fusion to find the underlying table metadata information.

Note : These are the sample queries, please modify accordingly as per your requirements. 

1. SQL Query to list All Tables in a Schema

SELECT table_name
FROM all_tables
WHERE owner = 'FUSION';     -- Replace 'FUSION' with the schema name

2. SQL Query to List All Columns in a Table

SELECT column_name, data_type, data_length
FROM all_tab_columns
WHERE table_name = 'PER_ALL_PEOPLE_F'; -- Replace with your table name

3. SQL Query to Search for Tables with Specific Column Names

SELECT table_name, column_name
FROM all_tab_columns
WHERE column_name LIKE '%PERSON_ID%'; -- Replace with the column keyword

4. SQL Query to View Primary Key Information

SELECT cols.table_name, cols.column_name, cons.constraint_name
FROM all_cons_columns cols
JOIN all_constraints cons
  ON cols.constraint_name = cons.constraint_name
WHERE cons.constraint_type = 'P'
  AND cols.table_name = 'PER_ALL_PEOPLE_F'; -- Replace with your table name

5. SQL Query to View Foreign Key Information

SELECT a.table_name, a.column_name, a.constraint_name, 
c_pk.table_name AS referenced_table, c_pk.constraint_name AS referenced_constraint
FROM all_cons_columns a
JOIN all_constraints c
  ON a.constraint_name = c.constraint_name
JOIN all_constraints c_pk
  ON c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R'
  AND a.table_name = 'PER_ALL_ASSIGNMENTS_F'; -- Replace with your table name

6. SQL Query to View All Indexes on a Table

SELECT index_name, column_name, uniqueness
FROM all_ind_columns
WHERE table_name = 'PER_ALL_PEOPLE_F'; -- Replace with your table name

7. SQL Query to View Constraints on a Table

SELECT constraint_name, constraint_type, status
FROM all_constraints
WHERE table_name = 'PER_ALL_PEOPLE_F'; -- Replace with your table name

8. SQL Query to Search for a Keyword in Table or Column Names

SELECT table_name, column_name
FROM all_tab_columns
WHERE table_name LIKE '%PERSON%' OR column_name LIKE '%PERSON%';

9. SQL Query to List All Views in a Schema

SELECT view_name
FROM all_views
WHERE owner = 'FUSION'; -- Replace with the schema name

10. SQL Query to View Dependencies of a Table

SELECT name, type, referenced_name, referenced_type
FROM all_dependencies
WHERE name = 'PER_ALL_PEOPLE_F'; -- Replace with your table name

11. SQL Query to Identify Data Types and Lengths

SELECT column_name, data_type, data_length, data_precision, data_scale
FROM all_tab_columns
WHERE table_name = 'PER_ALL_PEOPLE_F'; -- Replace with your table name

12. SQL Query to List All Synonyms in a Schema

SELECT synonym_name, table_owner, table_name
FROM all_synonyms
WHERE owner = 'FUSION'; -- Replace with your schema name

13. SQL Query to Search Materialized Views

SELECT mview_name
FROM all_mviews
WHERE owner = 'FUSION'; -- Replace with the schema name

14. SQL Query to Find Columns with Date Data Type

SELECT table_name, column_name
FROM all_tab_columns
WHERE data_type = 'DATE';

Oracle Metadata Tables

• ALL_TABLES: Contains details about all tables accessible to the user.
• ALL_TAB_COLUMNS: Stores metadata about all columns.
• ALL_CONSTRAINTS: Contains information about constraints on tables.
• ALL_VIEWS: Provides details on views.
• ALL_SYNONYMS: Contains synonym information.