Find nested tables of a schema in Oracle Database

Script to find nested tables of a schema:

set lines 200 pages 200

set long 999
col owner for a18
col table_name for a20
col table_type_owner for a20
col table_type_name for a20
col parent_table_name for a20
col parent_table_column for a20
SELECT owner, table_name, table_type_owner, table_type_name,
parent_table_name, parent_table_column,
LTRIM (storage_spec) storage_spec, LTRIM (return_type) return_type
FROM dba_nested_tables
WHERE owner='&SCHEMA_NAME'
--And upper(table_name) like '&&TABLE_NAME'
ORDER BY owner;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.