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.
—LIST TABLES OF HR USER/SCHEMA
SELECT * FROM DBA_TABLES X WHERE X.OWNER='SH'; SELECT * FROM DBA_TABLES X WHERE X.OWNER='SCOTT';