1

In Oracle 11g, how do I extract the tables created by me from user_tables (not displaying 100s of tables that are there by default)?

I wrote the following query to get the list of all user tables,

SELECT table_name FROM user_tables;

However, this gives me a very long list of tables, which are not relevant to me. I only want a list of tables that were manually created by me. I know there are only 4-5 tables created by me in the database as of now.

Chintan Dave
  • 43
  • 1
  • 9
  • 1
    What do you mean, "by me"? `user_tables` gives you the tables owned by your Oracle username/schema. I think you'll need to look at the audit logs for any other detail about who created tables (IP address, remote username, time, etc). – kfinity Feb 08 '19 at 20:17
  • 2
    https://stackoverflow.com/questions/50508750/how-to-list-only-the-tables-ive-created-in-oracle-sql – Islam Hassan Feb 08 '19 at 20:23
  • 1
    Just being curious: which user are you connected to? When user is created, its schema is empty. There are no "100s of default tables there", unless you use (for example) SYS schema to test your skills. If that's the case, don't do that. Create your own user and leave SYS (and SYSTEM and such) alone. – Littlefoot Feb 08 '19 at 20:37
  • Thank you @Littlefoot. I was using 'system' schema. I'm a beginner, so I didn't know that I can create a new schema. That was the reason I was getting 100s of table names. I got my answer. Can you please post your comment as answer, so that I can select it as answer, and it can also help other beginners like me? – Chintan Dave Feb 09 '19 at 16:12
  • You're welcome. I hope you'll enjoy Oracle! – Littlefoot Feb 09 '19 at 16:15

3 Answers3

0

I guess that you are not the only one using the user/schema in the database. You can run a query using the user_objects view. Sort it by CREATED descending like the query below. 

 select *
   from user_objects
  where object_type = 'TABLE'
    --and created BETWEEN :P_DATE1 AND :P_DATE2 
  order by created desc

And if you will create another table again, add some hint/mark in the table name. It could be your name initials or prefixes that let you know that it is your table.

Sample table name: ABC_TABLE1

Then you can just use below query to get the table you created.

SELECT *
  FROM user_tables
 WHERE table_name LIKE 'ABC%';
eifla001
  • 1,137
  • 8
  • 8
0

Created by me on an oracle user?

If any other user has a permission to create a table on a schema you cannot find it using user_tables.

If you remember the table names you can filter it out from user_tables.

I hope it helps.

Naresh Gandi
  • 1
  • 1
0

Thanks to @Littlefoot I learned that I was using "System" (default) schema, instead of creating my own. So in that system schema there are tons of default tables that are available. So by querying the "users_tables", I was also getting all the System default tables, along with my created tables.

Chintan Dave
  • 43
  • 1
  • 9