How to list ONLY the tables I've created in Oracle SQL?

Question

I am a beginner using SQL Plus, so this might sound like a stupid question but I really don't know how to do it. I cannot find a way to view all the tables I've created. I try a large number of commands like

SELECT owner, table_name
  FROM dba_tables`

or

SELECT table_name
  FROM user_tables

But i always end with a few thousand results. Pic after executing the first command

SELECT owner, table_name
  FROM dba_tables

Basically i created about 15 tables since i started using SQL Plus, and i forgot some of the names. I would like to have a way to check just the tables I created, without having a few thousand unnecessary other names in there. Does anybody know how to do that? I will add that i'm using the SYSTEM user and oracle express.

Possible duplicate of [Get list of all tables in Oracle?](https://stackoverflow.com/questions/205736/get-list-of-all-tables-in-oracle) — Aleksej, May 24 '18 at 11:57
In `USER_TABLES` you get only tables "you" (the current user) own(s). That's not necessarily the creator but maybe close. Unless you are logged in as `SYS` (or `SYSTEM`) and were, when you created the tables. Then you get a lot of results as a lot of the "default" tables in the database are owned by `SYS` (or `SYSTEM`). — sticky bit, May 24 '18 at 12:39

score 3 · Accepted Answer · answered May 24 '18 at 12:26

First of all, you should stop using the SYSTEM account. That is for the Oracle database only. Create and use a new database user and you will save yourself many headaches worse than your current one.

To solve your current problem, you want to filter out the "thousands of tables" that are owned by SYSTEM out of the box. (I'm taking your word it's thousands -- I have never counted them).

One idea would be this:

SELECT object_name
FROM   dba_objects
WHERE  object_type = 'TABLE'
AND    owner = 'SYSTEM'
AND    created >= to_date('01-JAN-2018','DD-MON-YYYY')

... replacing 01-JAN-2018 with the date when you started using SQL*Plus, which hopefully wasn't the same day that the database was installed (or else this might not work well)!

thatjeffsmith · Answer 2 · 2018-05-24T14:09:30.843

What @matthew-mcpeak said above - QUIT USING SYSTEM.

After you've created a proper schema to contain your objects, you can query USER_TABLES. No WHERE clause is necessary: this is a VIEW of the TABLES in the schema belonging to your SESSION user account.

So login as PETE, query

 SELECT * FROM USER_TABLES

Will show every table in the PETE schema.

or

select *
  from ALL_OBJECTS
 where OBJECT_TYPE = 'TABLE'
   and OWNER = 'PETE';

or query

 SELECT * FROM ALL_TABLES where owner = 'PETE'

We also have DBA_ views, such as DBA_TABLES and DBA_OBJECTS. They are faster than the ALL_ views - because there is no security model in place. ALL_ views only show you what you are able to see. So if you have access to DBA_ views, use them.

But if you just want to see what's in your schema, the USER_ views are just dandy.

However.

This won't show you every table you've created. For example, what about all those tables you polluted the SYSTEM schema with?

The Oracle Data Dictionary doesn't keep track of who created what. You would need a AFTER CREATE TRIGGER to capture the session owner any time a CREATE TABLE was fired and log that to a new table for reporting. Or enable auditing on your system and let that take care of it. ORACLE-BASE (Tim) demonstrates that here.

score 0 · Answer 3 · edited May 24 '18 at 12:40

0

If you do know any of the tables you have created, make a query like this:

select owner,table_name from user_tables where table_name='NAME OF YOUR TABLE';

Then, you can change the query to:

select owner, table_name  from user_tables where ower='THE OWNER OF THE TABLE YOU SELECTED BEFORE'

This should return only the tables created by you. owner might be your username, but, I'm not sure since I have no experience in Oracle, nor ever tried anything of the sort, but, seems logical to me that this should work.

edited May 24 '18 at 12:40

sticky bit

36,626
12
31
42

answered May 24 '18 at 12:24

Osmar Gascho

1
2

Thanks for the help with formatting, not used to it :D – Osmar Gascho May 24 '18 at 13:09
For a full explanation of how to format text on StackOverflow, [have a look at this page](https://stackoverflow.com/editing-help). – Bob Jarvis - Слава Україні May 24 '18 at 13:56
user_ views - don't need a predicate on OWNER - it will always only show tables in your login user's schema – thatjeffsmith May 24 '18 at 14:00
@thatjeffsmith: you *can't* have a predicate on OWNER when accessing one of the `USER_` views as there's no OWNER field, at least on 11.2. This is at least the case for `USER_TABLES` - haven't looked at every possible `USER_` view, but I expect this to carry over. – Bob Jarvis - Слава Україні May 24 '18 at 14:06
@BobJarvis - right, owner isn't required. Are we splitting hairs? I have a full answer below. – thatjeffsmith May 24 '18 at 14:10

score 0 · Answer 4 · answered Jul 10 '22 at 23:16

Since Oracle 12g, tables maintained by Oracle can be ignored by the following query:

SELECT *
FROM sys.user_tables
LEFT JOIN sys.user_objects ON user_objects.object_type = 'TABLE'
  AND user_objects.object_name = user_tables.table_name
WHERE user_objects.oracle_maintained != 'Y'

How to list ONLY the tables I've created in Oracle SQL?

4 Answers4

Linked