Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Statspack recomendations.

RE: Statspack recomendations.

From: <Jared.Still_at_radisys.com>
Date: Thu, 23 Jan 2003 13:14:31 -0800
Message-ID: <F001.00538F47.20030123131431@fatcity.com>


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'

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 <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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US