OraFAQ Forum: SQL & PL/SQL » When was a tablespace created?

Home » SQL & PL/SQL » SQL & PL/SQL » When was a tablespace created?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

When was a tablespace created? [message #636627]

Wed, 29 April 2015 08:51

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

Hello friends,

Perhaps I still suffer from Monday dyslexia, but I can't seem to find a view where I can query the date and time a tablespace was created.
I can't rely on the timestamps of the data files because we recently recovered the database.
Sad

Report message to a moderator

Re: When was a tablespace created? [message #636628 is a reply to message #636627]

Wed, 29 April 2015 09:01

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Most likely min(creation_time) in v$datafile for this tablespace files.

Report message to a moderator

Re: When was a tablespace created? [message #636629 is a reply to message #636628]

Wed, 29 April 2015 09:05

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

If it is very recent, the AWR views might help. If licences (though iirc there is one that is a freebie)

Report message to a moderator

Re: When was a tablespace created? [message #636631 is a reply to message #636628]

Wed, 29 April 2015 09:15

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

Thanks Michel,
Did'nt do it.
Weird, the tablespace was dropped and created and it seems it reused the same files
and v$datafile is reflecting the original date the file was created.
I found an approximation can be found in Table SYS.WRH$_TABLESPACE_SPACE_USAGE where TABLESPACE_USEDSIZE changes from > 0 to 0.
Close enough for me.
Again thanks.

Report message to a moderator

Re: When was a tablespace created? [message #636632 is a reply to message #636631]

Wed, 29 April 2015 09:22

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Was going to say the other place you might find it is the audit trails, depending on your settings.

Report message to a moderator

Re: When was a tablespace created? [message #636635 is a reply to message #636631]

Wed, 29 April 2015 09:49

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

If I am not missing something, then you could see the creation date for the datafile for that tablespace_name in the v$datafile_header view:

SELECT tablespace_name, creation_time FROM v$datafile_header;

Regards,
Lalit

Report message to a moderator

Re: When was a tablespace created? [message #636640 is a reply to message #636635]

Wed, 29 April 2015 10:19

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I bet the issue will be the same than with v$datafile although they do not take the information in the same place.

Report message to a moderator

Re: When was a tablespace created? [message #636644 is a reply to message #636640]

Wed, 29 April 2015 11:25

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

Forgot to mention it's a 10.2.0.1 database.
You are right, same issue as with v$datafile.
Also, the drop/create tablespace was not recorded in the alert log...???

PS: I have been pushing for 2 years to upgrade this db to at least 11.2.0.4 perhaps now that it crashed they will listen.

[Updated on: Wed, 29 April 2015 11:29]

Report message to a moderator

Previous Topic:	Leading zero and comma decimal separator
Next Topic:	REGEXP_REPLACE IN A CASE STATEMENT

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 22:31:11 CDT 2024