How to Find the SQL Definition of a View in Oracle

---

🔍 When working with Oracle databases, it’s often helpful to retrieve the SQL definition of a view — especially when reverse engineering or documenting logic for reporting and data pipelines.

✅ View Definitions via USER_VIEWS

If you own the view or have access to it within your schema, you can use the following query:

SELECT view_name, text
FROM user_views
WHERE view_name = 'YOUR_VIEW_NAME';

💡 Note: Oracle stores the SQL in the TEXT column as a LONG datatype, which can be a bit awkward to work with in tools. Use SQL Developer or SQLcl to view it nicely formatted.

---

🔐 Accessing Views in Other Schemas

If the view is owned by another schema but you have access, replace user_views with all_views:

SELECT owner, view_name, text
FROM all_views
WHERE view_name = 'YOUR_VIEW_NAME';

---

🔑 For DBA Access

If you have DBA privileges, use dba_views to see everything:

SELECT owner, view_name, text
FROM dba_views
WHERE view_name = 'YOUR_VIEW_NAME';

---

📜 Full View DDL (Recommended for Long SQL)

To avoid truncation issues and see the full view creation script, use:

SELECT DBMS_METADATA.GET_DDL('VIEW', 'YOUR_VIEW_NAME', 'VIEW_OWNER')
FROM dual;

---

🛠 Handy Tip

To find all views that reference a table (e.g., PERSON_INFO_TAB), use:

SELECT view_name
FROM user_views
WHERE UPPER(text) LIKE '%PERSON_INFO_TAB%';

---

This helps when tracing upstream logic or preparing to migrate/refactor logic into your DataMart or reporting layer.

---

Let me know if you’d like this styled for your WordPress KnowHow format or embedded with images/snippets.