How to List All Tables in a Schema in Oracle SQL

How do I list all tables in a schema in Oracle SQL?

To see all tables in another schema, you need to have one or more of the following system privileges:

SELECT ANY DICTIONARY
(SELECT | INSERT | UPDATE | DELETE) ANY TABLE

or the big-hammer, the DBA role.

With any of those, you can select:

SELECT DISTINCT OWNER, OBJECT_NAME 
  FROM DBA_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'
   AND OWNER = '[some other schema]'

Without those system privileges, you can only see tables you have been granted some level of access to, whether directly or through a role.

SELECT DISTINCT OWNER, OBJECT_NAME 
  FROM ALL_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'
   AND OWNER = '[some other schema]'

Lastly, you can always query the data dictionary for your own tables, as your rights to your tables cannot be revoked (as of 10g):

SELECT DISTINCT OBJECT_NAME 
  FROM USER_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'

Get list of table names in different schema of an Oracle database

SELECT TABLE_NAME 
FROM ALL_TABLES 
WHERE OWNER='OTHER-SCHEMA'

List all tables of a given user in Oracle

This will get all the tables where the "JOHN" user is the owner:

SELECT * FROM USER_TABLES;

or

SELECT * FROM ALL_TABLES WHERE OWNER = 'JOHN';

([TL;DR] 'JOHN' typically needs to be in upper-case. Assuming that the user john was created using the CREATE USER john ... statement then Oracle's default behaviour is to convert all object names (i.e. tables, columns, users, etc) to upper case. When you query the data-dictionary the table details will be stored in this case (and not the case you used in the original command unless you wrap it in double quotes).)

Get list of all tables in Oracle?

SELECT owner, table_name
  FROM dba_tables

This is assuming that you have access to the DBA_TABLES data dictionary view. If you do not have those privileges but need them, you can request that the DBA explicitly grants you privileges on that table, or, that the DBA grants you the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role (either of which would allow you to query any data dictionary table). Of course, you may want to exclude certain schemas like SYS and SYSTEM which have large numbers of Oracle tables that you probably don't care about.

Alternatively, if you do not have access to DBA_TABLES, you can see all the tables that your account has access to through the ALL_TABLES view:

SELECT owner, table_name
  FROM all_tables

Although, that may be a subset of the tables available in the database (ALL_TABLES shows you the information for all the tables that your user has been granted access to).

If you are only concerned with the tables that you own, not those that you have access to, you could use USER_TABLES:

SELECT table_name
  FROM user_tables

Since USER_TABLES only has information about the tables that you own, it does not have an OWNER column – the owner, by definition, is you.

Oracle also has a number of legacy data dictionary views-- TAB, DICT, TABS, and CAT for example-- that could be used. In general, I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6. Oracle has not changed these views in a long time so they often have problems with newer types of objects. For example, the TAB and CAT views both show information about tables that are in the user's recycle bin while the [DBA|ALL|USER]_TABLES views all filter those out. CAT also shows information about materialized view logs with a TABLE_TYPE of "TABLE" which is unlikely to be what you really want. DICT combines tables and synonyms and doesn't tell you who owns the object.

How to list all tables that have a matching string in its name

You may query the all_tables table:

SELECT table_name
FROM all_tables
WHERE table_name LIKE '%student%';

How do I list all tables in a schema having specific column in oracle sql?

You could use USER_TAB_COLS view.

For example,

SQL> select table_name, column_name, data_type from user_tab_cols where column_name ='DEPTNO';

TABLE_NAME COLUMN_NAM DATA_TYPE
---------- ---------- ----------
DEPT       DEPTNO     NUMBER
EMP        DEPTNO     NUMBER

SQL>

You could try,

select * 
   from user_tab_cols
 where column_name in ('STARTTIME_DATE', 'STARTTIME');

Listing All Tables and Columns from my Database (ORACLE)

Use listagg to get all the column names in a table as a list.

create table table_column_list as 
SELECT table_name,listagg(column_name,',') within group(order by column_id) column_list
FROM user_tab_cols
group by table_name;

Get counts of all tables in a schema

This should do it:

declare
    v_count integer;
begin

    for r in (select table_name, owner from all_tables
              where owner = 'SCHEMA_NAME') 
    loop
        execute immediate 'select count(*) from ' || r.table_name 
            into v_count;
        INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED)
        VALUES (r.table_name,r.owner,v_count,SYSDATE);
    end loop;

end;

I removed various bugs from your code.

Note: For the benefit of other readers, Oracle does not provide a table called STATS_TABLE, you would need to create it.

How to execute 'Select' query on all Tables within a schema on Oracle

You're doing it wrong, apparently. I, as the others, can't see a valid reason doing it that way but - as you've said - you asked a question and need an answer. So, here's one option to do that: it is based on Scott's schema which contains several tables.

• I'm purging recyclebin to get rid of garbage
• Select from TAB shows which tables I expect to get as the output
• PL/SQL, in a loop, creates set of begin spool into a TXT file - select statement itself - end spooling SQL*Plus commands
• DBMS_OUTPUT result should be copy/pasted into SQL*Plus and executed
• the final result is list of TXT files which contain data
• I'm setting LINESIZE and PAGESIZE so that the output looks prettier than usual; you might want to set some other parameters to. Check the SQL*Plus documentation for more info

OK, here you go:

SQL> purge recyclebin;

Recyclebin purged.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

SQL> set serveroutput on;
SQL> begin
  2    for cur_r in (select tname from tab) loop
  3      dbms_output.put_line('spool ' || cur_r.tname ||'.txt');
  4      dbms_output.put_line('select * from ' || cur_r.tname ||';');
  5      dbms_output.put_line('spool off');
  6    end loop;
  7  end;
  8  /
spool BONUS.txt
select * from BONUS;
spool off
spool DEPT.txt
select * from DEPT;
spool off
spool EMP.txt
select * from EMP;
spool off
spool SALGRADE.txt
select * from SALGRADE;
spool off

PL/SQL procedure successfully completed.

SQL>

Now, running that result:

SQL> set pagesize 100
SQL> set linesize 100
SQL> spool BONUS.txt
SQL> select * from BONUS;

ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
KING       PRESIDENT       1000        100

SQL> spool off
SQL> spool DEPT.txt
SQL> select * from DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> spool off
SQL> spool EMP.txt
SQL> select * from EMP;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17.12.80        800                    20
      7499 ALLEN      SALESMAN        7698 20.02.81       1600        300         30
      7521 WARD       SALESMAN        7698 22.02.81       1250        500         30
      7566 JONES      MANAGER         7839 02.04.81       2975                    20
      7654 MARTIN     SALESMAN        7698 28.09.81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01.05.81       2850                    30
      7782 CLARK      MANAGER         7839 09.06.81       2450                    10
      7839 KING       PRESIDENT            17.11.81       5000                    10
      7844 TURNER     SALESMAN        7698 08.09.81       1500          0         30
      7900 JAMES      CLERK           7698 03.12.81        950                    30
      7902 FORD       ANALYST         7566 03.12.81       3000                    20
      7934 MILLER     CLERK           7782 23.01.82       1300                    10

12 rows selected.

SQL> spool off
SQL> spool SALGRADE.txt
SQL> select * from SALGRADE;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200
         2       1201       1400
         3       1401       2000
         4       2001       3000
         5       3001       9999

SQL> spool off
SQL>

In current directory, there are now several TXT files:

SQL> $dir *.txt
 Volume in drive C is OSDisk
 Volume Serial Number is 7635-F892

 Directory of C:\Users\littlefoot

16.08.2018.  21:12               353 BONUS.txt
16.08.2018.  21:12               658 DEPT.txt
16.08.2018.  21:12             1.494 EMP.txt
16.08.2018.  21:12               764 SALGRADE.txt
               4 File(s)          3.269 bytes
               0 Dir(s)  304.432.480.256 bytes free

SQL>

Now, you're free to use them the best you can.

---

Related Topics