Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Statspack recomendations.
Robert,
Here's the script I use.
Jared
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'
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'
Freeman Robert - IL <FREEMANR_at_tusc.com>
Sent by: root_at_fatcity.com
01/23/2003 12:14 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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: FREEMANR_at_tusc.com
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: ListGuru_at_fatcity.com (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: Jared.Still_at_radisys.com
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: ListGuru_at_fatcity.com (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). Received on Thu Jan 23 2003 - 15:14:31 CST
![]() |
![]() |