List Down Oracle Tables In Schema

List Schema Tables In Oracle

Let say you are a DBA, and you wanted to check that how many tables were created by HR Schema or User. You can check this by following two ways.
1- You can connect with HR Schema by providing HR User and password in our case

Conn HR/HR 
OR
Conn HR/HR@listener

b. Then query the TAB table using following statement.Although you can only extracts your own tables by using given sql query. If you want to get any table from any schema of oracle database then you can go to example number 2.

Select * from tab;

2- You can connect to “Sys” user using Sysdba privileges.

Conn sys/password as sysdba,
Or
Conn sys/password@listener as sysdba

b. Then you can run following query

Select * from dba_tables where owner='HR';

Using above mentioned method, you can list down the tables of HR Schema. You can use this query to extract any schema and their tables.

Examples:

—LIST TABLES OF HR USER/SCHEMA

SELECT * FROM DBA_TABLES X WHERE X.OWNER='SH';
SELECT * FROM DBA_TABLES X WHERE X.OWNER='SCOTT';