CODEWITHSUNDEEP
sqloracle-database
vili vanili
vili vanili

Reputation: 3

search the history for the all tables that are created by a user

i'm new in oracle/sql world., and i know how to find a answer by myself searching., and searching again .. but for my new issue i d'ont find a good answer, so i just want to find the history of manipulation the database by filtering the user who created the last tables, what tables are, when he created it etc .. is sql oracle

I'm using oracle XE and the client is toad.

i try it with 

select table_name from sys.dba_tables where owner='system'

but is not display the tables that i was created with the system user a few months ago., and now i forget what tables i was created.

Upvotes: 0

Views: 421

Answers (2)

Dave Costa
Dave Costa

Reputation: 48121

String matching is case-sensitive, and most things in the data dictionary are stored in all upper-case as a general rule. So your example query should return some rows if you change the literal to upper case:

select table_name from sys.dba_tables where owner='SYSTEM'

If you want to see recently-created tables, you'll need to join this with dba_objects and use the created column there to filter or sort.

Of course, if you really just want to see tables for the schema you are currently logged into, user_tables is a better view to query.

Per your comment, here's how to get the last-modified time for each table:

select table_name, last_ddl_time
  from dba_tables t
       join dba_objects o
         on o.object_name=t.table_name and o.object_type='TABLE' and o.owner = t.owner
  where t.owner='SYSTEM'
    and last_ddl_time >= date '2011-01-02'
    and last_ddl_time <  date '2011-01-10'

(Note that "modified" means a change to the table definition, not to the data it contains.)

Upvotes: 2

Korhan Ozturk
Korhan Ozturk

Reputation: 11308

Try one of the following: 

select * from tab;

or 

select * from user_tables;

Both will give the list of tables created by user (you).

Upvotes: 0

Related Questions