Table Owner Lookup (towner.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides Oracle database functionality via the towner.sql script.

The Script

rem  towner.sql
rem
ttitle 'Table Owners'
rem
col name format a41 heading 'OWNER'
rem
select DISTINCT OWNER
  from DBA_TABLES
 where TABLE_NAME like UPPER('&name')
 order by OWNER;

What This Script Does

This simple but essential utility script helps locate which schemas contain tables with a specific name or name pattern. It’s particularly useful in environments with multiple schemas where table names might be duplicated across different owners, or when you need to find all instances of tables matching a pattern.

Key Features

• Cross-Schema Search: Searches all schemas in the database
• Pattern Matching: Supports SQL wildcards for flexible table name searches
• Distinct Results: Shows unique schema owners only
• Case Insensitive: Automatically converts search pattern to uppercase
• Quick Reference: Fast way to locate table ownership

Usage

Run the script and provide a table name pattern when prompted:

@towner.sql

Input Parameters:

• Table Name: Table name or pattern (supports % and _ wildcards)

Required Privileges

SELECT on DBA_TABLES

Sample Output

                                    Table Owners

OWNER
-----------------------------------------
FINANCE
HR
PAYROLL
REPORTING
SCOTT

Common Usage Examples

Find Exact Table Name

Enter value for name: EMPLOYEES

Shows all schemas containing a table named “EMPLOYEES”

Find Tables Starting with Pattern

Enter value for name: EMP%

Shows schemas with tables starting with “EMP”

Find Tables Ending with Pattern

Enter value for name: %_HIST

Shows schemas with tables ending in “_HIST”

Find Tables Containing Pattern

Enter value for name: %CUSTOMER%

Shows schemas with tables containing “CUSTOMER”

Use Cases

1. Table Location

   • Find which schema contains a specific table
   • Locate tables across multiple environments

2. Schema Analysis

   • Identify duplicate table names across schemas
   • Understand table distribution patterns

3. Migration Planning

   • Inventory table ownership before migrations
   • Identify schema dependencies

4. Security Auditing

   • Find all schemas with sensitive table names
   • Audit table access across schemas

5. Development Support

   • Help developers find the correct schema for tables
   • Resolve ambiguous table references

Analysis Examples

Finding All Customer-Related Tables

@towner.sql
Enter value for name: %CUSTOMER%

Result shows all schemas with customer tables:
HR
SALES
FINANCE
MARKETING

Locating Audit Tables

@towner.sql
Enter value for name: %AUDIT%

Result shows schemas with audit tables:
SECURITY
COMPLIANCE
APP_SCHEMA

Finding Temporary Tables

@towner.sql
Enter value for name: TEMP_%

Result shows schemas with temporary tables:
ETL_SCHEMA
DATA_WAREHOUSE
STAGING

Advanced Usage

Combine with Other Queries

After finding owners, you can drill down further:

-- After running towner.sql, check table details
SELECT owner, table_name, num_rows, last_analyzed
FROM dba_tables
WHERE table_name LIKE 'EMPLOYEES%'
AND owner IN ('HR', 'PAYROLL');

Integration with Schema Analysis

-- Find all tables in schemas identified by towner.sql
SELECT table_name, num_rows
FROM dba_tables
WHERE owner = 'HR'
ORDER BY table_name;

Troubleshooting

No Results Returned

• Check if table name is spelled correctly
• Verify the table exists in the database
• Ensure you have privileges to see DBA_TABLES

Too Many Results

• Use more specific patterns to narrow the search
• Consider exact table names instead of wildcards

Missing Expected Schemas

• Some schemas might not be visible due to privilege restrictions
• Check if tables are actually tables (not views or synonyms)

Performance Considerations

• DBA_TABLES is a data dictionary view that’s generally fast
• Using wildcards may slow down the query on very large databases
• Results are cached by Oracle’s dictionary cache

Related Scripts

• dobj.sql - Complete object listing with status
• dtable.sql - Detailed table analysis
• dobjectf.sql - Objects with timestamps
• duser.sql - User account analysis

Script Variations

Enhanced Version with Table Count

SELECT owner, COUNT(*) table_count
FROM dba_tables
WHERE table_name LIKE UPPER('&name')
GROUP BY owner
ORDER BY owner;

With Table Size Information

SELECT DISTINCT owner
FROM dba_tables
WHERE table_name LIKE UPPER('&name')
AND num_rows > 0  -- Only tables with data
ORDER BY owner;

Include Synonyms

SELECT DISTINCT table_owner
FROM dba_synonyms
WHERE synonym_name LIKE UPPER('&name')
UNION
SELECT DISTINCT owner
FROM dba_tables
WHERE table_name LIKE UPPER('&name')
ORDER BY 1;