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: Redo a Tablespace

RE: Redo a Tablespace

From: Burton, Laura L. <BurtonL_at_prismplus.com>
Date: Wed, 15 Nov 2000 13:20:30 -0600
Message-Id: <10681.122157@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_01C04F39.205E60A0


Content-Type: multipart/alternative;


        boundary="----_=_NextPart_001_01C04F39.205E60A0"




------_=_NextPart_001_01C04F39.205E60A0


Content-Type: text/plain;


        charset="iso-8859-1"



Sure thing!!
 


Laura



-----Original Message-----


From: Sandy Druar [mailto:sdruar_at_eckerd.com]
Sent: Wednesday, November 15, 2000 11:31 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Redo a Tablespace




Can you please forward these scripts to me too. 



Thanks, 


Sandy Druar 


Oracle DBA 


sdruar_at_eckerd.com 




"Anandarao, KrishnamurthyX" wrote: 




 Laura, I would definitely be interested in it.  Can you forward me those
scripts please.ThanksKrish 

 




------_=_NextPart_001_01C04F39.205E60A0


Content-Type: text/html;


        charset="iso-8859-1"



<!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN">

<HTML>

<HEAD>

<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">






<META content='"MSHTML 4.72.3110.7"' name=GENERATOR>

</HEAD>

<BODY>

<DIV><SPAN class=900221919-15112000><FONT color=#0000ff face=Arial size=2>Sure 

thing!!</FONT></SPAN></DIV>


<DIV><SPAN class=900221919-15112000><FONT color=#0000ff face=Arial 

size=2></FONT></SPAN>&nbsp;</DIV>


<DIV><SPAN class=900221919-15112000><FONT color=#0000ff face=Arial 

size=2>Laura</FONT></SPAN></DIV>


<BLOCKQUOTE>



    <DIV class=OutlookMessageHeader><FONT face="Times New Roman" 
    size=2>-----Original Message-----<BR><B>From:</B> Sandy Druar 
    [mailto:sdruar_at_eckerd.com]<BR><B>Sent:</B> Wednesday, November 15, 2000 
    11:31 AM<BR><B>To:</B> Multiple recipients of list 
    ORACLE-L<BR><B>Subject:</B> Re: Redo a Tablespace<BR><BR></FONT></DIV>Can 
    you please forward these scripts to me too. 

    <P>Thanks, <BR>Sandy Druar <BR>Oracle DBA <BR>sdruar_at_eckerd.com 
    <P>&quot;Anandarao, KrishnamurthyX&quot; wrote: 
    <BLOCKQUOTE TYPE = CITE>&nbsp;<SPAN class=860375916-15112000><FONT 
        face="MS Sans Serif"><FONT color=#0000ff>Laura, I would definitely be 
        interested in it.&nbsp; Can you forward me those scripts 
        please.</FONT></FONT></SPAN><SPAN class=860375916-15112000></SPAN><SPAN 
        class=860375916-15112000><FONT face="MS Sans Serif"><FONT 
        color=#0000ff>Thanks</FONT></FONT></SPAN><SPAN 
        class=860375916-15112000><FONT face="MS Sans Serif"><FONT 
        color=#0000ff>Krish</FONT></FONT></SPAN> 
        <BLOCKQUOTE style="MARGIN-RIGHT: 0px">
            <DIV class=OutlookMessageHeader dir = 


    ltr>&nbsp;</DIV></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>



------_=_NextPart_001_01C04F39.205E60A0--



------_=_NextPart_000_01C04F39.205E60A0


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 - 13:20:30 CST












Original text of this message













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