RE: Redo a Tablespace

From: Burton, Laura L. <BurtonL_at_prismplus.com>
Date: Wed, 15 Nov 2000 14:44:09 -0600
Message-Id: <10681.122176@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_000_01C04F44.D3474EB0
Content-Type: multipart/alternative;

boundary="----_=_NextPart_001_01C04F44.D3474EB0"

------_=_NextPart_001_01C04F44.D3474EB0
Content-Type: text/plain;

charset="iso-8859-1"

Sure.

Laura

-----Original Message-----
From: mala singh [mailto:mala_singhm_at_hotmail.com] Sent: Wednesday, November 15, 2000 1:56 PM To: Multiple recipients of list ORACLE-L Subject: Re: Redo a Tablespace

Can you send that scripts please.
Mala

<SNIP>

------_=_NextPart_001_01C04F44.D3474EB0
Content-Type: text/html;

charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2650.12">
<TITLE>RE: Redo a Tablespace</TITLE>
</HEAD>
<BODY>

Sure.


Laura


-----Original Message-----
 From: mala singh [<A HREF="mailto:mala_singhm_at_hotmail.com">mailto:mala_singhm_at_hotmail.com</A>]
 Sent: Wednesday, November 15, 2000 1:56 PM
 To: Multiple recipients of list ORACLE-L
 Subject: Re: Redo a Tablespace

 

Can you send that scripts please.
 Mala

 

<SNIP>


 

</BODY>
</HTML>

------_=_NextPart_001_01C04F44.D3474EB0--

------_=_NextPart_000_01C04F44.D3474EB0
Content-Type: application/octet-stream;

name="SAFEcheck.sql"
Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment;

filename="SAFEcheck.sql"

set termout on

spool c:\SafeCheck.txt
set heading OFF
select 'Database Queried =3D=3D=3D> '||d.name, ' Date =3D=3D=3D> = '||s.logon_time=20
from v$database D, V$SESSION S
where s.username =3D 'LAURAB'
/
set heading ON
prompt RULE 1 - 3 .... RULE 1 - 3 .... RULE 1 - 3 .... RULE 1 - 3 .... = RULE 1 - 3
prompt *** Data tablespaces should have uniform extent size (128K, 4M, = 128M) ***
prompt=20
prompt Tablespaces which do not have an initial extent as indicated = above, or=20
prompt equal to the minimum extent length, or equal to the next extent. = Also=20
prompt lists tablespaces where pctincrease is not equal to 0. prompt
prompt
select tablespace_name, initial_extent, next_extent, pct_increase, = min_extlen
from dba_tablespaces
where (initial_extent not in (128*1024, 4*1024*1024, 128*1024*1024)

    or next_extent !=3D initial_extent
    or pct_increase !=3D 0
    or min_extlen !=3D initial_extent)    and contents =3D 'PERMANENT'
   and tablespace_name !=3D 'SYSTEM'
   and tablespace_name not in (select tablespace_name from = dba_rollback_segs)
/
prompt
prompt RULE 4 ... RULE 4 ... RULE 4 ... RULE 4 ... RULE 4 ... RULE 4 = ... RULE 4
prompt *** Monitor and potentially relocate segments having > 1024 = extents ***
prompt=20
prompt Segments having more than 1024 extents=20 prompt
prompt
select owner, segment_name, extents=20
  from dba_segments
where extents > 1024=20
   and segment_type !=3D 'TEMPORARY'
/
prompt
prompt RULE 5 ... RULE 5 ... RULE 5 ... RULE 5 ... RULE 5 ... RULE 5 = ... RULE 5
prompt ******* Max single segment size should be between 4G and 128G =

prompt=20
prompt Checks for segments that are larger than 4G=20 prompt
prompt
select owner, segment_name, bytes=20
  from dba_segments
where bytes > 4*1024*1024*1024=20    and segment_type !=3D 'TEMPORARY'
/
prompt
prompt RULE 6 ... RULE 6 ... RULE 6 ... RULE 6 ... RULE 6 ... RULE 6 = ... RULE 6
prompt ** Very large tables/indexes should be placed in a private = tablespace **
prompt=20
prompt Checks for tables and indexes that are larger than 4G=20 prompt
prompt
select owner, segment_name, sum(bytes)=20   from dba_segments
group by owner, segment_name
having sum(bytes) > 4*1024*1024*1024
/
prompt
prompt RULE 7 ... RULE 7 ... RULE 7 ... RULE 7 ... RULE 7 ... RULE 7 = ... RULE 7
prompt ** Temporary segments should be restricted to temporary = tablespaces **
prompt=20
prompt Identifies users that do not have their Temp Tablespace set = correctly=20
prompt
prompt
SELECT USERNAME=20
  FROM DBA_USERS, DBA_TABLESPACES
WHERE TEMPORARY_TABLESPACE =3D TABLESPACE_NAME    AND CONTENTS !=3D 'TEMPORARY' /
prompt
prompt RULE 8 .... RULE 8 .... RULE 8 .... RULE 8 .... RULE 8 .... RULE = 8 .... RULE 8
prompt *** Place rollback segments in tablespaces dedicated to = rollback segments ***
prompt=20
prompt Identifies tablespaces containing rollback segments and user = data=20
prompt
prompt
select tablespace_name=20
  from dba_segments
where segment_type !=3D 'ROLLBACK'
   and tablespace_name !=3D 'SYSTEM'
   and tablespace_name in (select tablespace_name from = dba_rollback_segs)
/
prompt
prompt RULE 9 .... RULE 9 .... RULE 9 .... RULE 9 .... RULE 9 .... RULE = 9 .... RULE 9
prompt *** TEMP and RBS tablespaces should contain between 1024 and = 4096 extents ***
prompt=20
prompt Checks for RBS and TEMP tablespaces that do not obey the extent = size rules=20
prompt
prompt
select tablespace_name, initial_extent, next_extent, pct_increase, = min_extlen
  from dba_tablespaces t,

       ( select  tablespace_name          tbspc,
                 sum(bytes)               tbspc_sz,
                 count(*)                 num_files,
                 sum(bytes) / sum(blocks) blk_sz
           from  dba_data_files
       group by  tablespace_name) f

where
( initial_extent < (tbspc_sz - blk_sz * num_files) / 4096

     or initial_extent >  (tbspc_sz - blk_sz * num_files) / 1024
     or next_extent     !=3D initial_extent
     or pct_increase    !=3D 0
     or min_extlen      !=3D initial_extent)
    and tablespace_name  =3D tbspc

and tablespace_name !=3D 'SYSTEM'
and ( contents =3D 'TEMPORARY'

or tablespace_name in (select tablespace_name from = dba_rollback_segs))
/
prompt
prompt RULE 10 ..... RULE 10 ..... RULE 10 ..... RULE 10 ..... RULE 10 = ..... RULE 10=20

prompt ***             Never place User Data in the System tablespace   =
         ***

prompt=20
prompt Identifies User Data in the System Tablespace prompt
prompt
select owner, segment_name=20
from dba_segments

 where tablespace_name  =3D 'SYSTEM'
   and owner           !=3D 'SYS'
   and owner           !=3D 'SYSTEM'

/
prompt
prompt RULE 11 ..... RULE 11 ..... RULE 11 ..... RULE 11 ..... RULE 11 = ..... RULE 11=20

prompt ***         Datafile size should be a multiple of extent size + =
1         ***

prompt=20
prompt Checks for datafiles that do not obey the file size rules prompt
prompt
select t.tablespace_name, file_name, bytes file_size, initial_extent from dba_tablespaces t, dba_data_files f where mod(f.bytes - f.bytes/f.blocks, initial_extent) !=3D 0

   and f.tablespace_name                                =3D =

t.tablespace_name
/
prompt
prompt RULE 12 ..... RULE 12 ..... RULE 12 ..... RULE 12 ..... RULE 12 = ..... RULE 12=20

prompt ***     Never defragment the space within a uniform extent =
tablespace     ***

prompt=20
prompt Checks for a tablespace using a uniform extent size that has a = free extent
prompt that is not a multiple of the extent size prompt
prompt
select t.tablespace_name, file_id, block_id, bytes, initial_extent from dba_tablespaces t, dba_free_space s

 where next_extent                 =3D initial_extent
   and pct_increase                =3D 0
   and min_extlen                  =3D initial_extent

Received on Wed Nov 15 2000 - 14:44:09 CST