Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Closed TopicStart new topic
> search the history for the all tables that are created by a user
vili
post Mar 28 2012, 07:39 AM
Post #1


Newbie
*

Group: Members
Posts: 2
Joined: 28-March 12
Member No.: 46,994



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.
Go to the top of the page
 
+Quote Post
burleson
post Mar 28 2012, 08:47 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,425
Joined: 26-January 04
Member No.: 13



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









--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
vili
post Mar 28 2012, 09:04 AM
Post #3


Newbie
*

Group: Members
Posts: 2
Joined: 28-March 12
Member No.: 46,994



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,
Go to the top of the page
 
+Quote Post
burleson
post Mar 28 2012, 12:25 PM
Post #4


Advanced Member
***

Group: Members
Posts: 11,425
Joined: 26-January 04
Member No.: 13



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;


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Closed TopicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 28th August 2014 - 08:26 PM