How to Find Oracle Synonym Target Tables

Problem: What Table Does This Synonym Point To?

In real-world Oracle environments, you often encounter queries like:

SELECT * FROM XMAR_SOME_TABLE;

At first glance, this looks like a table.  
But in many cases, it's actually a **Synonym**.

So the real question becomes:

> What is the actual table behind this synonym?

---

## Understanding Oracle Synonym Internals

CREATE SYNONYM A FOR B;

- A → Synonym name
- B → Actual table (`schema.table`)

Here’s the key insight:

> Oracle does NOT store the FOR clause as a string

Instead, it stores structured metadata:

|Column|Meaning|
|---|---|
|SYNONYM_NAME|Alias name|
|TABLE_OWNER|Schema|
|TABLE_NAME|Actual table|

---

## Solution: Query ALL_SYNONYMS

### Basic Query

SELECT  
    SYNONYM_NAME,  
    TABLE_OWNER,  
    TABLE_NAME,  
    TABLE_OWNER || '.' || TABLE_NAME AS TARGET  
FROM ALL_SYNONYMS  
WHERE SYNONYM_NAME LIKE 'XMAR%';

This gives you:

- All synonyms with a specific prefix
- The exact target table (`schema.table`)

---

## Detect Broken Synonyms

Sometimes the synonym exists, but the underlying table does not.

### Validation Query

SELECT  
    s.SYNONYM_NAME,  
    CASE   
        WHEN t.TABLE_NAME IS NULL THEN 'BROKEN'  
        ELSE 'OK'  
    END AS STATUS  
FROM ALL_SYNONYMS s  
LEFT JOIN ALL_TABLES t  
       ON t.OWNER = s.TABLE_OWNER  
      AND t.TABLE_NAME = s.TABLE_NAME  
WHERE s.SYNONYM_NAME LIKE 'XMAR%';

---

## Which View Should You Use?

|View|Description|
|---|---|
|USER_SYNONYMS|Your own synonyms|
|ALL_SYNONYMS|Accessible synonyms|
|DBA_SYNONYMS|All synonyms (requires privileges)|

In most cases, **ALL_SYNONYMS is the right choice**.

---

## Key Takeaways

- Synonyms are metadata-driven aliases, not simple text mappings
- Oracle stores the FOR clause as structured columns
- `TABLE_OWNER + TABLE_NAME` gives you the real target
- Use joins to validate broken references