Database Objects

Review database users and their current account status. This is useful when establishing which schemas exist and whether accounts are currently available.

SELECT
    username,
    account_status,
    created,
    default_tablespace,
    temporary_tablespace
FROM dba_users
ORDER BY username;

Use this query to review tables owned by a particular schema. Replace the schema name with the owner you want to investigate.

SELECT
    owner,
    table_name,
    tablespace_name,
    num_rows,
    last_analyzed
FROM dba_tables
WHERE owner = UPPER('&OWNER')
ORDER BY table_name;

Review index names, associated tables, status and index type for objects belonging to a particular schema.

SELECT
    owner,
    index_name,
    table_name,
    index_type,
    status,
    visibility
FROM dba_indexes
WHERE owner = UPPER('&OWNER')
ORDER BY table_name, index_name;

Invalid objects can be an important operational clue after deployments, upgrades, migrations or other database changes.

SELECT
    owner,
    object_type,
    object_name,
    status
FROM dba_objects
WHERE status <> 'VALID'
ORDER BY owner, object_type, object_name;

A quick inventory of object counts can help establish the size and composition of a schema before deeper investigation.

SELECT
    owner,
    object_type,
    COUNT(*) AS object_count
FROM dba_objects
GROUP BY owner, object_type
ORDER BY owner, object_type;

Use this query when you need a focused view of all objects and their status within a particular schema.

SELECT
    owner,
    object_type,
    object_name,
    status,
    last_ddl_time
FROM dba_objects
WHERE owner = UPPER('&OWNER')
ORDER BY object_type, object_name;