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

[vili]

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 ..

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

[burleson]

Hi Vili,

>> the client is toad.

Dump the TOAD, it;s a crutch for beginners.

Take the time to learn the native SQL*Plus commands!

I would get the easy Oracle pack:

http://www.rampant-books.com/menu_six_packs_bundles.htm#easy


*********************************************
>> history for the all tables that are created by a user

You would need to use LogMiner for this, it's NOT kept automatically:

http://www.dba-oracle.com/t_cbo_using_logminer.htm

If you want to track this moving forward, implement DDL triggers:

http://www.dba-oracle.com/t_ddl_triggers.htm

[vili]

thank you for you replay, very useful the links .. well i was try it with

CODE

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

but i want to search in these tables for a explicit date .. let say 01/02/2011., i mean i want to know the tables that was created or the tables that have modificacions from 01/02/2011 to 01/09/2011 by 'SYSTEM' user .. and i can not make the query that can show me these result

regards,

[burleson]

Hi Vili,

The Oracle dba_tables view keeps the date_created for all tables, but it goes away after the table has been deleted.

Check the last_ddl_time in dba_objects.

You may need the dictionary reference poster:

http://www.rampant-books.com/poster_oracle_database.htm


*******************************************

>> i want to know the tables that was created or the tables that have modificacions from 01/02/2011 to 01/09/2011 by 'SYSTEM' user

The SYSTEM user should never create objects!

Try something like this:

CODE

select,
object_name,
   object_type,
   last_ddl_date
from
   dba_objects
where
   last_ddl_date > sysdate - 7;