Oracle System Queries

The below  information is on 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:

SELECT TABLE_NAME, OWNER FROM SYS.ALL_TABLES ORDER BY OWNER, TABLE_NAME ;

Schemas:

SELECT USERNAME FROM SYS.ALL_USERS ORDER BY USERNAME;

Views:

SELECT VIEW_NAME, OWNER FROM SYS.ALL_VIEWS ORDER BY OWNER, VIEW_NAME;

Packages:

SELECT OBJECT_NAME, OWNER FROM SYS.ALL_OBJECTS WHERE UPPER(OBJECT_TYPE) = 'PACKAGE' ORDER BY OWNER, OBJECT_NAME ;

Procedures:

SELECT OBJECT_NAME, OWNER FROM SYS.ALL_OBJECTS WHERE UPPER(OBJECT_TYPE) = UPPER('PROCEDURE') ORDER BY OWNER, OBJECT_NAME ;

Procedure columns:

SELECT OWNER, OBJECT_NAME, ARGUMENT_NAME, DATA_TYPE, IN_OUT FROM SYS.ALL_ARGUMENTS ORDER BY OWNER, OBJECT_NAME, SEQUENCE;

Functions:

SELECT OBJECT_NAME, OWNER FROM SYS.ALL_OBJECTS WHERE UPPER(OBJECT_TYPE) = UPPER('FUNCTION') ORDER BY OWNER, OBJECT_NAME ;

Triggers:

SELECT TRIGGER_NAME, OWNER FROM SYS.ALL_TRIGGERS ORDER BY OWNER, TRIGGER_NAME ;

Indexes:

SELECT INDEX_NAME, TABLE_NAME, TABLE_OWNER FROM SYS.ALL_INDEXES ORDER BY TABLE_OWNER, TABLE_NAME, INDEX_NAME;