Home » SQL & PL/SQL » SQL & PL/SQL » Move LOB to new tablespace but still visible in old... (Oracle10gR2 RHEL4)
Move LOB to new tablespace but still visible in old... [message #321853] Wed, 21 May 2008 10:10 Go to next message
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dba_tables gives you table tablespace not lob ones.

Regards
Michel
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
BlackSwan
Messages: 25045
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous message
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:
  


Current Time: Wed Dec 07 18:29:14 CST 2016

Total time taken to generate the page: 0.29536 seconds