RE: Locally managed tablespaces/What a DBA can do ?

Lisa,
What version you are talking about? I am using locally managed tablesspace with uniform extents for temporary tablespace with tempfile for last one year with oracle version 8.1.6.2 on HP-UX 11 for datawarehouse database without any issue/problem. Now we have migrated it to 8.1.7.2 and put it into production in a month time...

Regards
Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Wed, 17 Apr 2002 08:53:45 -0800

That's not a temp tablespace (TEMP_DATA_A). It's a place to put temporary load tables. My temporary tablespace is TEMP, which is dictionary. From the research I did on LMT's before I decided to use them, it looked like there were several bugs associated with temporary tablespaces being LMT's so I left my temporary ts dictionary.

LK

> -----Original Message-----
> From: Robert Pegram [SMTP:pegramrg_at_yahoo.com]
> Sent: Wednesday, April 17, 2002 11:34 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Locally managed tablespaces/What a DBA can do ?
>
> What about your locally managed "temporary"
> tablespace?
>
>
> --- "Koivu, Lisa" <lisa.koivu_at_efairfield.com> wrote:
> > What? I was just able to do it. 8.1.7, W2K
> >
> > (INV-SYSTEM)>@ts
> >
> > TS_NAME LMT MB_FREE MB_TOTAL
> > PCT_USED
> > --------------- ---------- --------- ---------
> > ---------
> > LOAD_DATA_A LOCAL 740 2000
> > 63
> > LRG_INDEX_A LOCAL 390 2500
> > 84.4
> > LRG_INDEX_B LOCAL 980 1000
> > 2
> > LRG_TABLE_A LOCAL 2440 4000
> > 39
> > LRG_TABLE_B LOCAL 360 1000
> > 64
> > MED_INDEX_A LOCAL 480 1000
> > 52
> > MED_TABLE_A LOCAL 680 1000
> > 32
> > RBS DICTIONARY 299 1000
> > 70.1
> > SMALL_INDEX_A LOCAL 183 200
> > 8.5
> > SMALL_TABLE_A LOCAL 169 200
> > 15.5
> > SYSTEM DICTIONARY 110 200
> > 45
> > TEMP DICTIONARY 961 1000
> > 3.9
> > TEMP_DATA_A LOCAL 900 1000
> > 10
> > TOOLS LOCAL 19 20
> > 5
> > USERS LOCAL 34 75
> > 54.7
> >
> > 15 rows selected.
> >
> > (INV-SYSTEM)>alter tablespace load_data_a begin
> > backup;
> >
> > Tablespace altered.
> >
> > (INV-SYSTEM)>alter tablespace load_data_a end
> > backup;
> >
> > Tablespace altered.
> >
> >
> >
> > > -----Original Message-----
> > > From: Robert Pegram [SMTP:pegramrg_at_yahoo.com]
> > > Sent: Wednesday, April 17, 2002 9:58 AM
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: RE: Locally managed tablespaces/What a
> > DBA can do ?
> > >
> > > Darren,
> > >
> > > If using a hot backup strategy (other than Rman),
> > > make sure to test your backup/recovery if you
> > switch
> > > the temporary tablespace to locally managed. You
> > > can't put a locally managed temporary tablespace
> > in
> > > backup mode.
> > >
> > > Rob Pegram
> > > Oracle Certified DBA
> > >
> > > ----------------
> > >
> > > SQL> create tablespace temp_dict
> > > 2 datafile 'c:oracleoradataorcl emp_dict.dbf'
> > size
> > > 10M
> > > 3 default storage (initial 1M next 1M
> > maxextents
> > > unlimited pctincrease 0)
> > > 4* temporary
> > > SQL> /
> > >
> > > Tablespace created.
> > >
> > > SQL> create temporary tablespace temp_local
> > > 2 tempfile 'c:oracleoradataorcl emp_local.dbf'
> > size
> > > 10M
> > > 3 extent management local
> > > 4 uniform size 1M;
> > >
> > > Tablespace created.
> > >
> > >
> > > SQL> alter tablespace temp_dict begin backup;
> > >
> > > Tablespace altered.
> > >
> > > SQL> alter tablespace temp_local begin backup;
> > > alter tablespace temp_local begin backup
> > > *
> > > ERROR at line 1:
> > > ORA-03217: invalid option for alter of TEMPORARY
> > > TABLESPACE
> > >
> > >
> > > --- "Browett, Darren"
> > <dbrowett_at_city.coquitlam.bc.ca>
> > > wrote:
> > > > Okay, maybe re-org would be a little too much,
> > as
> > > > you say, I would need
> > > > to know the application better, and with 8
> > > > distinctly different apps,
> > > > including
> > > > oracle financials, I maybe heading for trouble.
> > > >
> > > > But what about simply turning the existing
> > > > dictionary-managed tablespaces
> > > > into
> > > > locally managed tablespaces, other then
> > maintenance,
> > > > would I gain
> > > > performance ?
> > > >
> > > > From what I read on this list, the growth of my
> > > > tables is very small
> > > > compared
> > > > to some of the other org's, so maybe staying
> > with
> > > > dictionary-managed would
> > > > be
> > > > best, except for the temporary tablespace.
> > > >
> > > > Darren.
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > Sent: April 15, 2002 3:58 PM
> > > > To: Multiple recipients of list ORACLE-L
> > > >
> > > >
> > > > Darren - Do you have to reorganize the vendor's
> > > > files now? The objective of
> > > > LMT's is to make Oracle more self-managing (you
> > > > know, so the MS SQL people
> > > > can't say it takes a lot more DBA time to manage
> > the
> > > > database). To me in
> > > > your situation, the bottom line is whether you
> > have
> > > > to spend quite a bit of
> > > > time reorganizing those files today. If the
> > answer
> > > > is "no", then LMT's
> > > > probably aren't going to help you much. If the
> > > > answer is "yes", then that
> > > > gives you some opportunity try something with
> > their
> > > > files, since you are
> > > > doing that anyway. The key point is whether you
> > know
> > > > how the vendor's
> > > > software interfaces with Oracle. If you have to
> > > > reorg quite a bit, then I am
> > > > confident you know that interface well
> > (otherwise
> > > > you are jeopardizing the
> > > > data). If the vendor has made it pretty
> > automated
> > > > (management of the data),
> > > > then you probably aren't that familiar with the
> > > > details of how the vendor
> > > > accomplishes that, and shouldn't mess with their
> > > > stuff. I apologize for
> > > > being wordy here, but maybe you can see my
> > point.
> > > > Dennis Williams
> > > > DBA
> > > > Lifetouch, Inc.
> > > > dwilliams_at_lifetouch.com
> > > >
> > > >
> > > > -----Original Message-----
> > > > Sent: Monday, April 15, 2002 4:33 PM
> > > > To: Multiple recipients of list ORACLE-L
> > > >
> > > >
> > > > I am looking into locally managed tablespaces,
> > have
> > > > done some research, and
> > > > have a basic
> > > > understanding of how they work, but I am unsure
> > > > about the benefits if any
> > > > for my environment.
> > > >
> > > > I have multiple instances running, one for each
> >
> === message truncated ===
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Tax Center - online filing with TurboTax
> http://taxes.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Robert Pegram
> INET: pegramrg_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Koivu, Lisa
   INET: lisa.koivu_at_efairfield.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).




MOHAMMAD RAFIQ


_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mohammad Rafiq
  INET: rafiq9857_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).