RE: Redo a Tablespace

From: Eric D. Pierce <PierceED_at_csus.edu>
Date: Wed, 15 Nov 2000 13:38:06 -0800
Message-Id: <10681.122187@fatcity.com>

careful about the "reply to all"

In case you haven't noticed, here is what you are sending to the entire list multiple times:

> Received: from SpoolDir by DSS_486 (Mercury 1.46); 15 Nov 00 12:59:15 -0700
> Return-path: <root_at_fatcity.cts.com>
> Received: from csus.edu (130.86.90.1) by sswdserver.sswd.csus.edu (Mercury 1.46) with ESMTP;
> 15 Nov 00 12:59:02 -0700
> Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.192.199])
> by csus.edu with ESMTP id MAA14889;
> Wed, 15 Nov 2000 12:59:02 -0800 (PST)
> Received: from fatcity.UUCP (uucp_at_localhost)
> by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id MAA22906;
> Wed, 15 Nov 2000 12:57:37 -0800 (PST)
> Received: by fatcity.com (04-May-2000/v1.0f-b69/bab) via UUCP id 0026A686; Wed, 15 Nov 2000 12:50:39 -0800
> Message-ID: <F001.0026A686.20001115125039_at_fatcity.com>
> Date: Wed, 15 Nov 2000 12:50:39 -0800
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> X-Comment: Oracle RDBMS Community Forum
> X-Sender: "Burton, Laura L." <BurtonL_at_prismplus.com>
> Sender: root_at_fatcity.com
> Reply-To: ORACLE-L_at_fatcity.com
> Errors-To: ML-ERRORS_at_fatcity.com
> From: "Burton, Laura L." <BurtonL_at_prismplus.com>
> Subject: RE: Redo a Tablespace
> Organization: Fat City Network Services, San Diego, California
> X-ListServer: v1.0f, build 69; ListGuru (c) 1996-2000 Bruce A. Bergman
> Precedence: bulk
> Mime-Version: 1.0
> Content-Type: multipart/mixed; boundary="----_=_NextPart_000_01C04F44.D3474EB0"
> X-PMFLAGS: 570949760 0 1 Y0956C.CNM
>
> 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-----
> Sent: Wednesday, November 15, 2000 1:56 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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>]<
> /FONT> Sent: Wednesday, November 15, 2000 1:56 PM
> To: Multiple recipients of list ORACLE-L <FONT
> SIZE=2>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
> and t.tablespace_name !=3D 'SYSTEM'
> and t.tablespace_name =3D s.tablespace_name
> and mod(bytes, initial_extent) !=3D 0
Received on Wed Nov 15 2000 - 15:38:06 CST