Oracle System Queries

INTRODUCTION

This tutorial will help get the oracle database details.

1. Find the Version

select * from v$version;

2. Find the Tables Spaces

select * from dba_tablespaces;

3. Find the Tables

select TABLE_NAME, OWNER from SYS.ALL_TABLES order by OWNER, TABLE_NAME;

4. Find Schemas

select USERNAME from SYS.ALL_USERS order by USERNAME;

5. Find Views

select VIEW_NAME, OWNER from SYS.ALL_VIEWS order by OWNER, VIEW_NAME;

6. Find Packages

select OBJECT_NAME, OWNER from SYS.ALL_OBJECTS where UPPER(OBJECT_TYPE) = 'PACKAGE' order by OWNER, OBJECT_NAME;

7. Find Procedures

select OBJECT_NAME, OWNER from SYS.ALL_OBJECTS where upper(OBJECT_TYPE) = upper('PROCEDURE') order by OWNER, OBJECT_NAME;

8. Find Procedure Columns

select OWNER, OBJECT_NAME, ARGUMENT_NAME, DATA_TYPE, IN_OUT from SYS.ALL_ARGUMENTS order by OWNER, OBJECT_NAME, SEQUENCE;

9. Find Functions

select OBJECT_NAME, OWNER from SYS.ALL_OBJECTS where upper(OBJECT_TYPE) = upper('FUNCTION') order by OWNER, OBJECT_NAME;

10. Find Triggers

select TRIGGER_NAME, OWNER from SYS.ALL_TRIGGERS order by OWNER, TRIGGER_NAME;

11. Find Indexes

select INDEX_NAME, TABLE_NAME, TABLE_OWNER from SYS.ALL_INDEXES order by TABLE_OWNER, TABLE_NAME, INDEX_NAME;