Home » SQL & PL/SQL » SQL & PL/SQL » Identifying tablespaces
Identifying tablespaces [message #227771] Thu, 29 March 2007 06:32 Go to next message
Muddassar
Messages: 28
Registered: March 2007
Junior Member
From all the existing tablespaces, how can one figure out the tablespaces that were created during database creation automatically.
Re: Identifying tablespaces [message #227790 is a reply to message #227771] Thu, 29 March 2007 07:26 Go to previous messageGo to next message
gkodakalla
Messages: 49
Registered: March 2005
Member
can we find it out like this. assuming the database creation and the tablespace creation happens on the same day.


select ddf.tablespace_name from
dba_data_files ddf , v$datafile vdf
where ddf.file_name = vdf.name and
trunc(creation_time) = (select trunc(creation_time)
from v$datafile where status = 'SYSTEM');
Re: Identifying tablespaces [message #227796 is a reply to message #227790] Thu, 29 March 2007 07:33 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
gkodakalla wrote on Thu, 29 March 2007 08:26
can we find it out like this. assuming the database creation and the tablespace creation happens on the same day.

select ddf.tablespace_name from
dba_data_files ddf , v$datafile vdf
where ddf.file_name = vdf.name and
trunc(creation_time) = (select trunc(creation_time)
from v$datafile where status = 'SYSTEM');

Will also need to accommodate for TEMP tablespaces.
Re: Identifying tablespaces [message #227799 is a reply to message #227771] Thu, 29 March 2007 07:35 Go to previous messageGo to next message
gkodakalla
Messages: 49
Registered: March 2005
Member
Is it not sufficient to take SYSTEM tablespace alone into consideration? May i know why we should also consider TEMP tablespaces?

Thank You
Giridhar
Re: Identifying tablespaces [message #227801 is a reply to message #227799] Thu, 29 March 2007 07:39 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
The OP was asking:

Muddassar wrote on Thu, 29 March 2007 07:32
... how can one figure out the tablespaces that were created during database creation automatically.

therefore, your query doesn't consider TEMP tablespaces that were created during DB creation (or to be more specific, your query will not include TEMP tablespaces if they are using tempfiles).
Re: Identifying tablespaces [message #227803 is a reply to message #227801] Thu, 29 March 2007 07:49 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
And if this is one of those Mensa questions, if you started creating the database really really late at night and the first tablespace was created at 11:55pm or so, the last tablespace might not be created until after midnight. That is a possibility.
Re: Identifying tablespaces [message #227805 is a reply to message #227771] Thu, 29 March 2007 08:00 Go to previous message
gkodakalla
Messages: 49
Registered: March 2005
Member
Thanks ebrian. I thought we need to display the tablespaces which are not created automatically.

yes joy_division, you are right.
Thats why i mentioned that
"assuming the database creation and the tablespace creation happens on the same day"
Previous Topic: Changing the schema within procedure
Next Topic: Delete all the tables within a schema
Goto Forum:
  


Current Time: Wed Dec 07 20:28:57 CST 2016

Total time taken to generate the page: 0.09165 seconds