Oracle System Queries for Retrieving Oracle Database Object Information
The following contains information on how to retrieve database information for Oracle objects such as tables, views, indexes, packages, procedures, functions, and triggers. The queries all query the Oracle system views located in the SYS schema.
Tables
This is a query to get all Oracle tables that can be viewed by the current user.
SELECT
TABLE_NAME,
OWNER
FROM
SYS.ALL_TABLES
ORDER BY
OWNER,
TABLE_NAME
The query can be filtered to return tables for a given schema by adding a where OWNER = 'some_schema' clause to the query.
SELECT
TABLE_NAME,
OWNER
FROM
SYS.ALL_TABLES
WHERE
OWNER = 'schema_name'
ORDER BY
TABLE_NAME
Schemas
This is a query to get all Oracle schemas or users in an Oracle database instance.
SELECT
USERNAME
FROM
SYS.ALL_USERS
ORDER BY
USERNAME
Views
This is a query to get all Oracle views that can be viewed by the current user.
SELECT
VIEW_NAME,
OWNER
FROM
SYS.ALL_VIEWS
ORDER BY
OWNER,
VIEW_NAME
The query can be filtered to return views for a specific schema by adding a where OWNER = 'some_schema' clause to the query.
Packages
This is a query to get all Oracle packages that can be viewed by the current user.
SELECT
OBJECT_NAME,
OWNER
FROM
SYS.ALL_OBJECTS
WHERE
UPPER(OBJECT_TYPE) = 'PACKAGE'
ORDER BY
OWNER,
OBJECT_NAME
To query for package bodies, substitute PACKAGE BODY for PACKAGE.
The query can be filtered to return packages for a specific schema by adding a where OWNER = 'some_schema'
clause to the query.
Procedures
This is a query to get all Oracle procedures that can be viewed by the current user.
SELECT
OBJECT_NAME,
OWNER
FROM
SYS.ALL_OBJECTS
WHERE
upper(OBJECT_TYPE) = upper('PROCEDURE')
ORDER BY
OWNER,
OBJECT_NAME
The query can be filtered to return procedures for a specific schema by adding a where OWNER = 'some_schema' clause to the query.
Procedure Columns
This is a query to get the columns in an Oracle procedure.
SELECT
OWNER,
OBJECT_NAME,
ARGUMENT_NAME,
DATA_TYPE,
IN_OUT
FROM
SYS.ALL_ARGUMENTS
ORDER BY
OWNER,
OBJECT_NAME,
SEQUENCE
Functions
This is a query to get all Oracle functions for the current user.
SELECT
OBJECT_NAME,
OWNER
FROM
SYS.ALL_OBJECTS
WHERE
upper(OBJECT_TYPE) = upper('FUNCTION')
ORDER BY
OWNER,
OBJECT_NAME
The query can be filtered to return functions for a specific schema by adding a where OWNER = 'some_schema' clause to the query.
Triggers
This is a query to get all Oracle triggers for the current user.
SELECT
TRIGGER_NAME,
OWNER
FROM
SYS.ALL_TRIGGERS
ORDER BY
OWNER,
TRIGGER_NAME
The query can be filtered to return triggers for a specific schema by adding a where OWNER = 'some_schema' clause to the query.
Indexes
This is a query to get all Oracle indexes.
SELECT
INDEX_NAME,
TABLE_NAME,
TABLE_OWNER
FROM
SYS.ALL_INDEXES
ORDER BY
TABLE_OWNER,
TABLE_NAME,
INDEX_NAME
Listed below are links to articles containing system queries for other databases:
Cassandra: Cassandra System Queries
Firebird: Firebird System Queries
Microsoft SQL Server: SQL Server System Queries
MySQL: MySQL System Queries
PostgreSQL: PostgreSQL System Queries
SQLite: SQLite System Queries
Sybase: Sybase System Queries
