troubleshooting Question

How to change the default tablespace storage for a lob

Avatar of BadHatHarry
BadHatHarry asked on
Oracle Database
3 Comments1 Solution1776 ViewsLast Modified:
Hi, I have a table that is partitioned and has a lob column. I have moved the partitions to a different tablespace and the DDL for the table shows that the partitions are in the new tablespace, however the ddl still shows the lob definition in the old tablespace:

CREATE TABLE OEM_USR.PS_D_EX_ADV_HDR_S
(
  ...
)
LOB (COMMENTS) STORE AS (
  TABLESPACE ZTEST_DATA_XXX...
  NOLOGGING)
TABLESPACE ZTEST_DATA
...
PARTITION BY RANGE (D_LAST_CHNG_TMSTMP)
(  
  PARTITION P20110300 ...
TABLESPACE ZTEST_DATA
LOB (COMMENTS) STORE AS SECUREFILE (
      TABLESPACE ZTEST_DATA
...
...

As can be seen the table partitions are in the new ZTEST_DATA tablespace, and even the LOB partitions show the tablespace as ZTEST_DATA. However, the main LOB definition still shows the older ZTES_DATA_XXX tablespace.

How can I change that tablespace?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros