Robert,
Here's the script I use.
Jared
-- showdf8i.sql
-- shows autoextend features
-- displays sizes in meg
clear computes
clear breaks
clear columns
set pagesize 60 heading on
col file_name format a30
col tablespace_name format a15
col bytes format 999,999.99 head "BYTES|MEG"
col autoextensible format a4 head "AUTO|XTND"
col maxbytes format 999,999.99 head "MAX|BYTES|MEG"
col increment_by format 999,999.99 head "INCR|BYTES|MEG"
col maxfree format 999,999.99 head "MAX|MEG|FREE"
col cblocksize noprint new_value ublocksize
set term off feed off
select value cblocksize
from v$parameter
where name = 'db_block_size';
set term on feed on
break on tablespace_name skip 1 on report
compute sum of bytes on tablespace_name
compute sum of bytes on report
@@title 'Tablespaces and Data Files' 120
select
ts.name tablespace_name,
f.name file_name,
f.status,
round(f.bytes/1049576,2) bytes,
s.maxfree,
df.autoextensible,
round(df.maxbytes/1048576,2) maxbytes,
round((df.increment_by * &ublocksize) / 1048576,2) increment_by,
f.file# file_id
from v$datafile f, (
select
file_id,
round(max(bytes/1048576),2) MAXFREE
from dba_free_space
group by file_id
) s,
v$tablespace ts,
dba_data_files df
where f.file# = s.file_id(+)
and f.name like '%'
and ts.ts# = f.ts#
and df.file_id = f.file#
--and df.autoextensible = 'YES'
union all
select
ts.name tablespace_name,
t.name file_name,
t.status,
round(t.bytes/1049576,2) bytes,
s.maxfree,
dt.autoextensible,
round(dt.maxbytes/1048576,2) maxbytes,
round((dt.increment_by * &ublocksize) / 1048576,2) increment_by,
t.file# file_id
from v$tempfile t, (
select
file_id,
round(max(bytes/1048576),2) MAXFREE
from dba_free_space
group by file_id
) s,
v$tablespace ts,
dba_temp_files dt
where t.file# = s.file_id
and t.name like '%'
and ts.ts# = t.ts#
and dt.file_id = t.file#
--and dt.autoextensible = 'YES'
order by tablespace_name, file_id
/
Freeman Robert - IL <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/23/2003 12:14 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: Statspack recomendations.
Anyone know a work around to joining v$tempfile and dba_temp_files by
file#?
I'm using 9.2.0.1.0 on XP and I'm issuing the statement:
select a.file_name
from dba_temp_files a, v$tempstat b
where b.file#=a.file_id;
I get an ora-0600 on this with a [ktfthcf-1] [202]
Argument. I looked this up on Metalink and this appears to have been a bug
discovered in 8i and fixed in 9i but alas, it's still here. I tried to
implement the work around, issuing the RULE hint, which removes the error
message but gives me no results.
Anyone experience this?
RF
Robert G. Freeman
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com
904.708.5076 Cell (it's everywhere that I am!)
Author of several books you can find on Amazon.com!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Freeman Robert - IL
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
