Available Balance
List Down Oracle Tables In Schema
January 1, 2015
3

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/[email protected]

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/[email protected] 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';

 

Rate This Content




    1. Can you share the same thing for SQL as well?

       
      • Sure, Sooner I will create an article on how to list down all the databases in Microsoft Sql Server Using SQL DMV. In SQL this is pretty simple, Since all you have to do is to use SSMS best GUI so far for Microsoft. But anyways will share this later.

         
    2. @Rameez I am surprised that a category for Oracle has been created while there could be other programmes too. Any reason why?

       

    Leave a reply

    Your email address will not be published.