Home » RDBMS Server » Server Administration » Creation Date of Table
Creation Date of Table [message #208571] Mon, 11 December 2006 06:43 Go to next message
sarfraz_attari
Messages: 123
Registered: July 2005
Senior Member
Dear All

I created a table in one of the database schema on 01-may-06.
after that I import data on 10-nov-06 from backup.
Now when I am selecting the Creation date from User_objects it is giving 01-may-06.
Is there any data dictionary view which gives me the actual table creation date in oracle

Thanks

Sarfraz
Re: Creation Date of Table [message #208572 is a reply to message #208571] Mon, 11 December 2006 07:05 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Can you support your claim by posting the session?
I cannot reproduce the case.
What is your oracle version?
Re: Creation Date of Table [message #208574 is a reply to message #208572] Mon, 11 December 2006 07:16 Go to previous messageGo to next message
sarfraz_attari
Messages: 123
Registered: July 2005
Senior Member
we are using Oracle 10g
Re: Creation Date of Table [message #208575 is a reply to message #208574] Mon, 11 December 2006 07:18 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,

i am not sure about ur case what is problem ?
but i just reproduce ur case and i got expected result.
C:\>dir c:\test1.dmp
 Volume in drive C has no label.
 Volume Serial Number is CC55-E5A1

 Directory of c:\

05-12-2006  08:54 PM         5,355,520 test1.dmp
               1 File(s)      5,355,520 bytes
              0 Dir(s)   5,992,534,016 bytes free

C:\>imp hr/hr full=y file=c:\test1.dmp

Import: Release 10.1.0.2.0 - Production on Mon Dec 11 17:13:03 2006

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via conventional path

Warning: the objects were exported by SYSTEM, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SYSTEM's objects into HR
. . importing table                         "TAB1"      49294 rows imported
Import terminated successfully without warnings.

C:\>sqlplus "hr/hr"

SQL*Plus: Release 10.1.0.2.0 - Production on Mon Dec 11 17:13:12 2006

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select created,last_ddl_time
  2  from user_objects
  3  where object_name = 'TAB1';

CREATED   LAST_DDL_
--------- ---------
11-DEC-06 11-DEC-06

SQL>



regards
Taj

[Updated on: Mon, 11 December 2006 07:18]

Report message to a moderator

Re: Creation Date of Table [message #208577 is a reply to message #208574] Mon, 11 December 2006 07:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You are still not answering my question.
>>Can you support your claim by posting the session?
Re: Creation Date of Table [message #208580 is a reply to message #208577] Mon, 11 December 2006 07:27 Go to previous messageGo to next message
sarfraz_attari
Messages: 123
Registered: July 2005
Senior Member
I didnt get u
What session u want me to post
Can u Explain me

Thanks
Re: Creation Date of Table [message #208584 is a reply to message #208580] Mon, 11 December 2006 07:33 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Post what you did ( Instead of explaining what you did) like User52.
But wait.
I re-read your posting.
>>I created a table in one of the database schema on 01-may-06
date of creation = 01-may-06
>>after that I import data on 10-nov-06 from backup
You just imported data on 10-nov-06.
>>Creation date from User_objects it is giving 01-may-06
It should. That is right.
You did not re-create the object. Just inserted the data right?
Re: Creation Date of Table [message #208586 is a reply to message #208584] Mon, 11 December 2006 07:38 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Good Catch... Mahesh

regards
Taj
Re: Creation Date of Table [message #208701 is a reply to message #208584] Mon, 11 December 2006 23:12 Go to previous messageGo to next message
sarfraz_attari
Messages: 123
Registered: July 2005
Senior Member
Yes But the Creation date is currently 10-nov-06
Re: Creation Date of Table [message #208729 is a reply to message #208701] Tue, 12 December 2006 00:23 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,

so now ur problem is solved...

Cool


regards
Taj
Re: Creation Date of Table [message #208731 is a reply to message #208729] Tue, 12 December 2006 00:27 Go to previous messageGo to next message
sarfraz_attari
Messages: 123
Registered: July 2005
Senior Member
No My Problem is not solved
Actually in user objects creation date is 10-nov-06
but actual creation date is 01-may-06
I need to know is there any data dictionary table where i can get 01-may-06

Thanks

Re: Creation Date of Table [message #208789 is a reply to message #208571] Tue, 12 December 2006 04:46 Go to previous messageGo to next message
bwetkstr
Messages: 114
Registered: August 2005
Senior Member
did you drop all objects? Or did you an import with the ignore=y option?
Re: Creation Date of Table [message #208794 is a reply to message #208731] Tue, 12 December 2006 04:47 Go to previous messageGo to next message
bwetkstr
Messages: 114
Registered: August 2005
Senior Member
sarfraz_attari wrote on Tue, 12 December 2006 07:27
No My Problem is not solved
Actually in user objects creation date is 10-nov-06
but actual creation date is 01-may-06
I need to know is there any data dictionary table where i can get 01-may-06

Thanks





If your table was not dropped when importing on 10-nov, then maybe look in dba_objects view or dba_tables

[Updated on: Tue, 12 December 2006 04:48]

Report message to a moderator

Re: Creation Date of Table [message #208799 is a reply to message #208789] Tue, 12 December 2006 04:57 Go to previous messageGo to next message
sarfraz_attari
Messages: 123
Registered: July 2005
Senior Member
Yes I Dropped the Schema and then Import the data Again

Re: Creation Date of Table [message #208821 is a reply to message #208799] Tue, 12 December 2006 05:37 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Actually in user objects creation date is 10-nov-06
As we have been repeatedly saying a zillion times,
export/import is just a mean to move the data around logically.
It is like a backup. But NOT A backup.

This is the expected behaviour.
WHen you export, you are not BACKING UP the database.
Oracle will extract the DDL and data logically.
When you import , oracle will RECREATE the table and import back the data.
So the Creation date is stored NOWHERE. You are RECREATING the table.
If you had restored the database that is a different case.

Still you are not answering my question. That is why i was all the way asking
>>>>Can you support your claim by posting the session?
Previous Topic: Want to know the Lock Details
Next Topic: ORA-01555: snapshot too old
Goto Forum:
  


Current Time: Sat Dec 03 20:10:23 CST 2016

Total time taken to generate the page: 0.04151 seconds