RE: Segment Space Management

From: <oracle-l-bounce_at_freelists.org>
Date: Fri, 4 May 2007 08:22:42 -0400
Message-ID: <02C2FA1C9961934BB6D16DE35707B27B02E2D9B6@JAX-MBH-01.crowley.com>

Thanks!

Joel Patterson
Database Administrator
joel.patterson_at_crowley.com
x72546
904 727-2546

-----Original Message-----
From: Siva Valiveru [mailto:SValiveru_at_looksmart.net] Sent: Thursday, May 03, 2007 5:06 PM
To: Patterson, Joel; oracle-l_at_freelists.org Subject: RE: Segment Space Management

Right, see below.

SQL> -- Invalid
SQL> drop tablespace temp_undo including contents and datafiles   2 /
drop tablespace temp_undo including contents and datafiles
*

ERROR at line 1:
ORA-00959: tablespace 'TEMP_UNDO' does not exist

SQL>
SQL> create undo tablespace temp_undo
  2 datafile '/tmp/temp_undo_001.dbf' size 1m reuse   3 segment space management auto
  4 /
segment space management auto
*

ERROR at line 3:
ORA-30024: Invalid specification for CREATE UNDO TABLESPACE

SQL>
SQL> drop tablespace temp_temp including contents and datafiles   2 /

Tablespace dropped.

SQL> create temporary tablespace temp_temp   2 tempfile '/tmp/temp_temp01.dbf' size 1m reuse   3 extent management local uniform size 50k   4 segment space management auto
  5 /
segment space management auto

                         *

ERROR at line 4:
ORA-30573: AUTO segment space management not valid for this type of tablespace

SQL>
SQL> -- Valid
SQL>
SQL> drop tablespace temp_tbs including contents and datafiles

  2 /

Tablespace dropped.

SQL>
SQL> create tablespace temp_tbs
  2 datafile '/tmp/temp_tbs_001.dbf' size 1m   3 extent management local uniform size 64k   4 segment space management auto
  5 /

Tablespace created.

SQL>
SQL> drop tablespace temp_undo including contents and datafiles   2 /
drop tablespace temp_undo including contents and datafiles
*

ERROR at line 1:
ORA-00959: tablespace 'TEMP_UNDO' does not exist

SQL> create undo tablespace temp_undo
  2 datafile '/tmp/temp_undo_001.dbf' size 1m reuse   3 /

Tablespace created.

SQL>
SQL> drop tablespace temp_temp including contents and datafiles   2 /
drop tablespace temp_temp including contents and datafiles
*

ERROR at line 1:
ORA-00959: tablespace 'TEMP_TEMP' does not exist

SQL>
SQL> create temporary tablespace temp_temp   2 tempfile '/tmp/temp_temp01.dbf' size 1m reuse   3 extent management local uniform size 50k   4 /

Tablespace created.

SQL>
SQL>
SQL> select tablespace_name,
  2            contents,
  3            extent_management,
  4            allocation_type,
  5            segment_space_management

  6 from dba_tablespaces
  7 where tablespace_name in ('TEMP_TBS','TEMP_UNDO','TEMP_TEMP')   8 /

TableSpace                     CONTENTS  EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ --------- ---------- --------- ------
TEMP_TBS                       PERMANENT LOCAL      UNIFORM   AUTO
TEMP_TEMP                      TEMPORARY LOCAL      UNIFORM   MANUAL
TEMP_UNDO                      UNDO      LOCAL      SYSTEM    MANUAL 

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joel.Patterson_at_crowley.com
Sent: Thursday, May 03, 2007 12:55 PM
To: oracle-l_at_freelists.org
Subject: Segment Space Management

Does Segment Space management Have to be MANUAL for SYSTEM, UNDO, and TEMP tablespaces?

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 04 2007 - 07:22:42 CDT