Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 792 invoked from network); 26 Jan 2008 03:40:25 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-69-64-49-119.inaddr.intergenia.de with SMTP; 26 Jan 2008 03:40:23 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4AEC97ECAB9; Sat, 26 Jan 2008 04:40:22 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 07010-01; Sat, 26 Jan 2008 04:40:22 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B34D97EC7DC; Sat, 26 Jan 2008 04:40:21 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Sat, 26 Jan 2008 04:38:10 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 990BF7ED249 for ; Sat, 26 Jan 2008 04:38:10 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 06384-06 for ; Sat, 26 Jan 2008 04:38:10 -0500 (EST) Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.183]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E790F7ECF5F for ; Sat, 26 Jan 2008 04:38:09 -0500 (EST) Received: by wa-out-1112.google.com with SMTP id k22so1840186waf.18 for ; Sat, 26 Jan 2008 01:38:08 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=i1xSkSRSr9S10s59hKpmia6JfhHIh/VteefP1xP2j+g=; b=RW3VOB/MmbTzlfvV2RWhkfhutfoK1uzLSTqcpoqFi0aiP5Vz9dlBMDU0GhsmVEDrtxGNZJxWvjlkHQhuYs+X6zYcf2gWiPcUsk/p6PUCS+6+ZTY4PB1HvUN4x7jdZTk4H0jh+K8/BnBxxP9ZiW6RN3wdGb+FDzceJo+h8m9z9Iw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=LAki5fUzlVSGFC9CDvIeBIyFuilVX3r3Zj29xjEecst2VkeQ5cvt1O+IsYubJGPSllsWNnAa5YOqbccMaUzpuvYU3q+WRRrui0PzNVWHozGcn1Uy4/ZXReIq5yG0FRThaFHgB54I3BMnBo4iBVQ7HchxXd9W5ac3XljxfONa+1k= Received: by 10.114.103.1 with SMTP id a1mr1460138wac.45.1201340288887; Sat, 26 Jan 2008 01:38:08 -0800 (PST) Received: by 10.114.173.4 with HTTP; Sat, 26 Jan 2008 01:38:08 -0800 (PST) Message-ID: <486b2b610801260138n1f5a0347mf7b7af82c48ed9a1@mail.gmail.com> Date: Sat, 26 Jan 2008 10:38:08 +0100 From: "Stefan Knecht" To: Mark.Bobak@proquest.com Subject: Re: Help with moving lobsegments and lobindexes.... Cc: oracle-l In-Reply-To: <667C10D184B2674A82068E06A78382B5163F0A56@AAPQMAILBX01V.proque.st> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2999_14105401.1201340288870" References: <667C10D184B2674A82068E06A78382B5163F0A56@AAPQMAILBX01V.proque.st> X-archive-position: 5000 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: knecht.stefan@gmail.com Precedence: normal Reply-to: knecht.stefan@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------=_Part_2999_14105401.1201340288870 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi Mark And another thing to consider -- you never have to worry about the LOBINDEX segment -- in fact, you can't to anything to/with/about it. Oracle handles that transparently under the covers. So it's enough to move the LOBSEGMENT to a new tablespace, the LOBINDEX will follow. This still applies with Securefiles (improved LOB datatype) in 11g BTW. Oh, and if you don't like the system generated names, give them your own. Use the STORE AS "" clause in CREATE TABLE or ALTER TABLE statements: sys@CENTRAL> create table t (x blob) lob(x) store as "T_LOB_X" (tablespace users); Table created. sys@CENTRAL> select column_name,segment_name from user_lobs where table_name='T'; COLUMN_NAME SEGMENT_NAME ------------------------------ ------------------------------ X T_LOB_X sys@CENTRAL> alter table t move lob (x) store as "T_NEW_LOB" (tablespace sites); Table altered. sys@CENTRAL> select column_name,segment_name from user_lobs where table_name='T'; COLUMN_NAME SEGMENT_NAME ------------------------------ ------------------------------ X T_NEW_LOB Cheers Stefan On Jan 25, 2008 7:22 PM, Bobak, Mark wrote: > Ok, I'm a little confused here, and looking for some help. > > > > This is 10gR2 (10.2.0.3) on Linux. > > > > I'm trying to move some LOBSEGMENTs and LOBINDEXes from one tablespace to > another. > > > > If I run this query: > > 1 select tablespace_name, > > 2 owner, > > 3 segment_type, > > 4 segment_name > > 5 from dba_segments > > 6 where (tablespace_name like 'PQDS%' > > 7 or tablespace_name like 'PQDM%' > > 8 or tablespace_name like 'PQDL%') > > 9* and segment_type like 'LOB%' > > SQL> / > > > > TABLESPACE_NAME OWNER > SEGMENT_TYPE SEGMENT_NAME > > ------------------------------ ------------------------------ > ------------------ ------------------------------ > > PQDMDATA ADDS > LOBSEGMENT SYS_LOB0000076746C00005$$ > > PQDMDATA ADDS > LOBINDEX SYS_IL0000076746C00005$$ > > PQDMDATA ADDS > LOBSEGMENT SYS_LOB0000076892C00005$$ > > PQDMDATA ADDS > LOBINDEX SYS_IL0000076892C00005$$ > > PQDMDATA ADDS > LOBSEGMENT SYS_LOB0000076980C00013$$ > > PQDMDATA ADDS > LOBINDEX SYS_IL0000076980C00013$$ > > PQDLINDX ADDS > LOBINDEX SYS_IL0000078274C00002$$ > > PQDLINDX ADDS > LOBSEGMENT SYS_LOB0000078274C00002$$ > > > > 8 rows selected. > > > > I see 4 lobsegments and 4 lobindexes, that I want to move. First off, > they're system generated names, so I have no idea what table/column they are > associated with. I want to move all of these into a tablespace called > PQDDATA. > > > > So, I run another query: > > SQL> select table_name , column_name from dba_tab_columns where > owner='ADDS' and data_type like '%LOB' > > 2 / > > > > TABLE_NAME COLUMN_NAME > > ------------------------------ ------------------------------ > > PM_TAGS PMTG_TAG_VALUE > > MODULE_PARAMETERS MPAR_LOV_QUERY > > CREATE$JAVA$LOB$TABLE LOB > > TEMP_SOH_TEST XML_DATA > > RECOMMENDED_LIST RLT_DOCUMENT > > > > > > And I see 5 tables that have LOB columns. So, based on MetaLink Doc ID > 100548.1, I try to move these by doing something like: > > alter table pm_tags move lob(PMTG_TAG_VALUE) store as (tablespace > pqddata); > > > > This command is syntactically correct, and succeeds, returning "Table > altered.", but, the logsegment and lobindex haven't actually moved. > > > > Can anyone offer me a clue or suggestion as to what I'm missing here? > Also, does anyone know how to map the system generated lobsegment and > lobindex names back to a specific table/column? > > > > AdvThanksance, > > > > -Mark > > > > *-- > Mark J. Bobak* > *Senior Database Administrator, System & Product Technologies* > ProQuest > 789 E. Eisenhower, Parkway, P.O. Box 1346 > Ann Arbor MI 48106-1346 > +1.734.997.4059 or +1.800.521.0600 x 4059 > mark.bobak@il.proquest.com > www.proquest.com > www.csa.com > > *ProQuest...*Start here. > > > -- ========================= Stefan P Knecht Senior Consultant Infrastructure Managed Services Trivadis AG Europa-Strasse 5 CH-8152 Glattbrugg Phone +41-44-808 70 20 Fax +41-808 70 12 Mobile +41-79-571 36 27 stefan.knecht@trivadis.com http://www.trivadis.com OCP 9i/10g SCSA SCNA ========================= ------=_Part_2999_14105401.1201340288870 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi Mark

And another thing to consider -- you never have to worry about the LOBINDEX segment -- in fact, you can't to anything to/with/about it. Oracle handles that transparently under the covers.

So it's enough to move the LOBSEGMENT to a new tablespace, the LOBINDEX will follow.

This still applies with Securefiles (improved LOB datatype) in 11g BTW.

Oh, and if you don't like the system generated names, give them your own. Use the STORE AS "<Your Lob Name>" clause in CREATE TABLE or ALTER TABLE statements:

sys@CENTRAL> create table t (x blob) lob(x) store as "T_LOB_X" (tablespace users);

Table created.

sys@CENTRAL> select column_name,segment_name from user_lobs where table_name='T';

COLUMN_NAME                    SEGMENT_NAME
------------------------------ ------------------------------
X                              T_LOB_X

sys@CENTRAL> alter table t move lob (x) store as "T_NEW_LOB" (tablespace sites);

Table altered.

sys@CENTRAL> select column_name,segment_name from user_lobs where table_name='T';

COLUMN_NAME                    SEGMENT_NAME
------------------------------ ------------------------------
X                              T_NEW_LOB



Cheers

Stefan



On Jan 25, 2008 7:22 PM, Bobak, Mark <Mark.Bobak@proquest.com> wrote:

Ok, I'm a little confused here, and looking for some help.

 

This is 10gR2 (10.2.0.3) on Linux.

 

I'm trying to move some LOBSEGMENTs and LOBINDEXes from one tablespace to another.

 

If I run this query:

  1       select tablespace_name,

  2              owner,

  3              segment_type,

  4              segment_name

  5         from dba_segments

  6        where (tablespace_name like 'PQDS%'

  7            or tablespace_name like 'PQDM%'

  8            or tablespace_name like 'PQDL%')

  9* and segment_type like 'LOB%'

SQL> /

 

TABLESPACE_NAME                OWNER                          SEGMENT_TYPE       SEGMENT_NAME

------------------------------ ------------------------------ ------------------ ------------------------------

PQDMDATA                       ADDS                           LOBSEGMENT         SYS_LOB0000076746C00005$$

PQDMDATA                       ADDS                           LOBINDEX           SYS_IL0000076746C00005$$

PQDMDATA                       ADDS                           LOBSEGMENT         SYS_LOB0000076892C00005$$

PQDMDATA                       ADDS                           LOBINDEX           SYS_IL0000076892C00005$$

PQDMDATA                       ADDS                           LOBSEGMENT         SYS_LOB0000076980C00013$$

PQDMDATA                       ADDS                           LOBINDEX           SYS_IL0000076980C00013$$

PQDLINDX                       ADDS                           LOBINDEX           SYS_IL0000078274C00002$$

PQDLINDX                       ADDS                           LOBSEGMENT         SYS_LOB0000078274C00002$$

 

8 rows selected.

 

I see 4 lobsegments and 4 lobindexes, that I want to move.  First off, they're system generated names, so I have no idea what table/column they are associated with.  I want to move all of these into a tablespace called PQDDATA.

 

So, I run another query:

SQL> select table_name , column_name from dba_tab_columns where owner='ADDS' and data_type like '%LOB'

  2  /

 

TABLE_NAME                     COLUMN_NAME

------------------------------ ------------------------------

PM_TAGS                        PMTG_TAG_VALUE

MODULE_PARAMETERS              MPAR_LOV_QUERY

CREATE$JAVA$LOB$TABLE          LOB

TEMP_SOH_TEST                  XML_DATA

RECOMMENDED_LIST               RLT_DOCUMENT

 

 

And I see 5 tables   that have LOB columns.  So, based on MetaLink Doc ID 100548.1, I try to move these by doing something like:

alter table pm_tags move lob(PMTG_TAG_VALUE) store as (tablespace pqddata);

 

This command is syntactically correct, and succeeds, returning "Table altered.", but, the logsegment and lobindex haven't actually moved.

 

Can anyone offer me a clue or suggestion as to what I'm missing here?  Also, does anyone know how to map the system generated lobsegment and lobindex names back to a specific table/column?

 

AdvThanksance,

 

-Mark

 

--
Mark J. Bobak
Senior Database Administrator, System & Product Technologies
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059  or +1.800.521.0600 x 4059
mark.bobak@il.proquest.com
www.proquest.com
www.csa.com

ProQuest...Start here.

 




--
=========================

Stefan P Knecht
Senior Consultant
Infrastructure Managed Services

Trivadis AG
Europa-Strasse 5
CH-8152 Glattbrugg

Phone +41-44-808 70 20
Fax +41-808 70 12
Mobile +41-79-571 36 27
stefan.knecht@trivadis.com
http://www.trivadis.com

OCP 9i/10g SCSA SCNA
========================= ------=_Part_2999_14105401.1201340288870-- -- http://www.freelists.org/webpage/oracle-l