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

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
> > application. In some cases
> > these apps just have two tablespaces, data and
> > index.
> >
> > If I have a tablespace that is composed of tables
> > that are various sizes and
> > activity, will LMT help or hinder ?
> >
> > I have read the paper about creating tablespaces
> > based on extent size,
> > which is the concept
> > I would follow, but I am unsure how the vendor(s)
> > would feel if I re-org'd
> > their database(s).
> >
> > Thanks
> >
> > Darren
> >
> --------------------------------------------------------------------------
> --
> > ----------------------------------------------
> > Darren Browett P.Eng This
> message
> > was transmitted
> > Data Administrator using 100%
> > recycled electrons
> > Information and Communication Technology
> > City of Coquitlam
> > P:(604)927 - 3614
> > E:dbrowett_at_city.coquitlam.bc.ca
> >
> --------------------------------------------------------------------------
> --
> > -----------------------------------------------
> >
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Browett, Darren
> > INET: dbrowett_at_city.coquitlam.bc.ca
> >
> > 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: DENNIS WILLIAMS
> > INET: DWILLIAMS_at_LIFETOUCH.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: Browett, Darren
> > INET: dbrowett_at_city.coquitlam.bc.ca
> >
> > 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).
>
>
> __________________________________________________
> 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).