Re: My tablespace does not have datafiles

On Thu, 12 Apr 2007 03:57:52 -0700, Bohne wrote:

> Using Oracle 10.1.0.4.0 on HP-UX
>
> It's been donkey's years since I have been working with oracle, so maybe
> I am just overlooking something... Either way, I have the following
> problem:
>
> I get an error ' unable to extend temp segment by 128 in tablespace TEMP
> '
> I thought EASY, just enlarge the datafile, BUT...
>
> select a.name tbs,substr(b.NAME,1,50) datafile,b.BYTES/1024/1024 as mb
> from V$datafile b,V$tablespace a where a.ts#=b.ts# and a.name='TEMP'
>
> no rows selected
>
> No Typo, if I look at all the TBS:
>
> select a.name tbs,substr(b.NAME,1,50) datafile,b.BYTES/1024/1024 as mb
> from V$datafile b,V$tablespace a where a.ts#=b.ts#

There is also a view called dba_data_files. You don't have to write queries like this any more. It has been around since Oracle7

>
> TBS
> DATAFILE MB
> ------------------------------
> -------------------------------------------------- ---------- SYSTEM
> /oracle/xxx/data1/ dbs1.dbf
> 300 UNDO01 /oracle/xxx/data1/ undo1.dbf
> 300 SYSAUX /oracle/xxx/data1/
> sysaux01.dbf 400 USERS
> /oracle/xxx/data1/ usr1.dbf 10 TOOLS
> /oracle/xxx/data1/ tool1.dbf
> 50 USER1 /oracle/xxx/data2/ U0011.dbf
> 100 INDEX1 /oracle/xxx/data2/
> I0011.dbf 100
>
> 7 rows selected.
>
> Now I am lost, please help!

http://tinyurl.com/2kjceh

-- 
http://www.mladen-gogala.com