Message-Id: <10681.122152@fatcity.com> From: "Burton, Laura L." Date: Wed, 15 Nov 2000 13:15:04 -0600 Subject: RE: Redo a Tablespace 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_01C04F38.6117F83C Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C04F38.6117F83C" ------_=_NextPart_001_01C04F38.6117F83C Content-Type: text/plain; charset="iso-8859-1" Then you can definitely have it!!! Laura -----Original Message----- From: Anandarao, KrishnamurthyX [mailto:krishnamurthyx.anandarao@intel.com] Sent: Wednesday, November 15, 2000 11:01 AM To: Multiple recipients of list ORACLE-L Subject: RE: Redo a Tablespace Laura, I would definitely be interested in it. Can you forward me those scripts please. Thanks Krish ------_=_NextPart_001_01C04F38.6117F83C Content-Type: text/html; charset="iso-8859-1" RE: Redo a Tablespace
Then you can definitely have it!!!
 
Laura
-----Original Message-----
From: Anandarao, KrishnamurthyX [mailto:krishnamurthyx.anandarao@intel.com]
Sent: Wednesday, November 15, 2000 11:01 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Redo a Tablespace

Laura, I would definitely be interested in it.  Can you forward me those scripts please.
 
Thanks
Krish
 
<snip>
------_=_NextPart_001_01C04F38.6117F83C-- ------_=_NextPart_000_01C04F38.6117F83C 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