OraFAQ Forum: SQL & PL/SQL » Move LOB to new tablespace but still visible in old...

Home » SQL & PL/SQL » SQL & PL/SQL » Move LOB to new tablespace but still visible in old... (Oracle10gR2 RHEL4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Move LOB to new tablespace but still visible in old... [message #321853]

Wed, 21 May 2008 10:10

shaseeb
Messages: 113
Registered: April 2007
Location: Madison, WI

Senior Member

Hello all,

I just finished moving some LOB columns from an old tablespace to a new one using the following command:

ALTER TABLE <schema_name.table_name> MOVE LOB(<column_name>) STORE AS (TABLESPACE <tablespace_name>);

They were successfully moved. HOWEVER, when I queried to check the dba_tab_columns view, I found that they were still listed in the old tablespace. Now why would this happen?

Thanks.

Report message to a moderator

Re: Move LOB to new tablespace but still visible in old... [message #321854 is a reply to message #321853]

Wed, 21 May 2008 10:14

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

when I queried to check the dba_tab_columns view, I found that they were still listed in the old tablespace.

I doubt, there is no tablespace information in dba_tab_columns.
Instead of posting wrong information, you should COPY & PASTE what you did.

Regards
Michel

Report message to a moderator

Re: Move LOB to new tablespace but still visible in old... [message #321858 is a reply to message #321854]

Wed, 21 May 2008 10:31

shaseeb
Messages: 113
Registered: April 2007
Location: Madison, WI

Senior Member

I did query the dba_tab_columns view and joined it with the dba_tables view. That is what you probably wanted to know. Well here are the commands:

ALTER TABLE CTMS.DOCUMENT MOVE LOB(BLOB_CONTENT) STORE AS (TABLESPACE CTMSLOB01);

Table altered.

select c.owner, c.table_name, c.column_name, c.data_type, t.tablespace_name
from dba_tab_columns c, dba_tables t
where c.data_type IN ('LOB','CLOB','BLOB')
and t.tablespace_name LIKE '%DAT%'
and c.table_name = t.table_name
and c.owner = t.owner;

OWNER TABLE_NAME COLUMN_NAME DATA_TYP TABLESPACE_NAME
------- ---------- ------------ --------- ---------------
CTMS DOCUMENT BLOB_CONTENT BLOB CTMSDAT01

Report message to a moderator

Re: Move LOB to new tablespace but still visible in old... [message #321863 is a reply to message #321858]

Wed, 21 May 2008 10:43

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

dba_tables gives you table tablespace not lob ones.

Regards
Michel

Report message to a moderator

Re: Move LOB to new tablespace but still visible in old... [message #321868 is a reply to message #321863]

Wed, 21 May 2008 10:53

shaseeb
Messages: 113
Registered: April 2007
Location: Madison, WI

Senior Member

Ok, but why are the LOB columns still listed in the old tablespaces? Once I have moved the columns then shouldn't they not be listed in the old tablespace?

Thanks.

Report message to a moderator

Re: Move LOB to new tablespace but still visible in old... [message #321869 is a reply to message #321853]

Wed, 21 May 2008 10:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SELECT SEGMENT_NAME, SUM(BYTES) 
FROM DBA_TABLESPACES 
WHERE OWNER = 'CTMS' AND TABLESPACE_NAME = 'CTMSLOB01' 
GROUP BY SEGMENT_NAME;

What gets returned by SQL above?

[Updated on: Wed, 21 May 2008 12:23] by Moderator

Report message to a moderator

Re: Move LOB to new tablespace but still visible in old... [message #321870 is a reply to message #321869]

Wed, 21 May 2008 11:08

shaseeb
Messages: 113
Registered: April 2007
Location: Madison, WI

Senior Member

Well, there is no segment_name column in dba_tablespaces. But I think this query might do the trick.

select l.segment_name, sum(bytes)
from dba_lobs l, dba_data_files d
where l.tablespace_name = d.tablespace_name
and l.owner = 'CTMS'
and l.tablespace_name = 'CTMSLOB01'
group by l.segment_name;

SEGMENT_NAME SUM(BYTES)
------------------------------ ----------
SYS_LOB0000055620C00005$$ 2147483648
SYS_LOB0000366688C00005$$ 2147483648

Report message to a moderator

Re: Move LOB to new tablespace but still visible in old... [message #321872 is a reply to message #321870]

Wed, 21 May 2008 11:46

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Ana meant DBA_SEGMENTS.

Quote:

why are the LOB columns still listed in the old tablespaces?

It is not, you displayed table tablespace and not LOB tablespace.

Quote:

select l.segment_name, sum(bytes)
from dba_lobs l, dba_data_files d
where l.tablespace_name = d.tablespace_name
and l.owner = 'CTMS'
and l.tablespace_name = 'CTMSLOB01'
group by l.segment_name;

What dba_data_files has to do here?

Actually you have to mix up your and Ana queries and add condition on table_name.

I think you have a problem understanding your own query.
In addition, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: Move LOB to new tablespace but still visible in old... [message #321877 is a reply to message #321872]

Wed, 21 May 2008 12:20

shaseeb
Messages: 113
Registered: April 2007
Location: Madison, WI

Senior Member

SELECT SEGMENT_NAME, SUM(BYTES) FROM DBA_SEGMENTS WHERE OWNER = 'CTMS' AND TABLESPACE_NAME = 'CTMSLOB01' GROUP BY SEGMENT_NAME;

SEGMENT_NAME                               SUM(BYTES)
------------------------------------------ ----------
SYS_IL0000055620C00005$$                   262144
SYS_IL0000366688C00005$$                   131072
SYS_LOB0000055620C00005$$                  562036736
SYS_LOB0000366688C00005$$                  131072

[Updated on: Wed, 21 May 2008 12:23]

Report message to a moderator

Previous Topic:	Join question
Next Topic:	Reorganize values in columns

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Sep 06 03:18:05 CDT 2025