Oracle-L: RE: maxextents unlimited - value in dba_tables, dba

From: Cunningham, Gerald <Gerald.Cunningham_at_usi.net>
Date: Wed, 28 May 2003 12:05:15 -0800
Message-ID: <F001.005A4C18.20030528120515@fatcity.com>

Thanks, Dennis.

Started snooping around some more, looks like I needed to go to DBA_SEGMENTS for partitioned objects:

SQL> select OWNER, SEGMENT_TYPE, SEGMENT_NAME, MAX_EXTENTS from dba_segments
2 where owner = 'APP_OWNER' and SEGMENT_NAME = 'INDEX1';

OWNER                          SEGMENT_TYPE
------------------------------ ------------------

SEGMENT_NAME
MAX_EXTENTS

----------- APP_OWNER INDEX PARTITION INDEX1 2147483645

APP_OWNER                       INDEX PARTITION

INDEX1
2147483645

APP_OWNER                       INDEX PARTITION

INDEX1
2147483645

APP_OWNER                       INDEX PARTITION

INDEX1
2147483645

4 rows selected.

-----Original Message-----
Sent: Wednesday, May 28, 2003 1:06 PM
To: Multiple recipients of list ORACLE-L

Jerry - I did a quick survey of some of my databases and only found a single table that had a null MAX_EXTENTS and it was not partitioned. Its name was ATEMPTAB$ owned by SYS.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Wednesday, May 28, 2003 11:15 AM
To: Multiple recipients of list ORACLE-L

Hi all,

I have a database where all tables and indexes have maxextents set to unlimited. Why, in dba_tables, do some tables have a null value for maxetents and some have a value of 2147483645?

An index I just modified (previous value of maxextents was 249) now has a null value for maxextents:

SQL> alter index app_owner.index1 storage (maxextents unlimited);

Index altered.

SQL> select owner, index_name, max_extents from dba_indexes 2 where owner = 'APP_OWNER' and index_name = 'INDEX1';

OWNER                          INDEX_NAME

MAX_EXTENTS

------------------------------ ------------------------------
-----------
APP_OWNER                       INDEX1

It seems that some of the objects that have maxextents = null are partitioned, but not all. Anybody have an explanation??

Thanks!

Jerry

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Cunningham, Gerald
INET: Gerald.Cunningham_at_usi.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed May 28 2003 - 15:05:15 CDT

RE: maxextents unlimited - value in dba_tables, dba_indexes