• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » how to move lob segment from one tablespace to another

how to move lob segment from one tablespace to another

August 18, 2019 by techgoeasy Leave a Comment

Here we are presenting ways on how to move lob segment from one tablespace to another

(1) First take the initial size of the LOB segment

SELECT table_name, column_name, segment_name, a.bytes
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE b.table_name = 'TABLE_NAME';

More queries can be found at

How to find space usage of lob segments in Oracle database

(2) Suppose you know the lob segment name and you want to know which tables it belongs to.

Here is  how to find table name for lob segment

SELECT table_name, column_name, segment_name, a.bytes
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE a.segment_name = '&logsegment';

(3) Now let’s take a look at various ways to rebuild the lob

Alter table move command

If you just want to move the table without impacting LOB Segments, then use below

SQL>ALTER TABLE table_name MOVE;

Or
SQL>ALTER TABLE table_name MOVE TABLESPACE tablespace_name;

With this statement, it does not affect any of the lob segments associated with the lob columns in the table.

To move log segments to different tablespace use the below command. But it is important to mention that it will also rebuild the table in its own tablespace

ALTER TABLE table_name MOVE LOB(lob-col) STORE AS (TABLESPACE new_tablespace_name);

Along with the log segment, you can also move the table as well as storage attribute of table and log by the following query,

ALTER TABLE table_name MOVE
TABLESPACE new_tablespace STORAGE(new_storage)
LOB (lob-col) STORE AS
(TABLESPACE new_tablespace STORAGE (new_storage));

Shrink command

(a) Shrinking both the table and lob segments

ALTER TABLE table_name ENABLE ROW MOVEMENT;

Table altered.

ALTER TABLE table_name SHRINK SPACE CASCADE;

Table altered.

(b) Shrink the lob segment only

ALTER TABLE table_name ENABLE ROW MOVEMENT;

Table altered.

ALTER TABLE <table_name> MODIFY LOB (<lob_column>) (SHRINK SPACE);

(4) Finally we can measure the after size using the below query and get the spaced freed value as a result of this rebuild

SELECT table_name, column_name, segment_name, a.bytes
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE b.table_name = 'TABLE_NAME';

Note

  • We dont talk about LOBINDEX here as it just points to the chunks that make up the LOB.It is just created to find the chunks for the lob fast and a segment that holds the lob data (chunks).
  • We have NO control over the lob index, it is an internal structure and goes where LOBSEGMENT goes.
  • Hence LOBINDEX also moves when you move the LOB segment. So we dont need worry about it

Hope you like this post on how to move lob segment from one tablespace to another. Please do provide the feedback to improve

Related Articles
FND_LOBS
alter table move
alter table add column oracle
oracle create tablespace
how to change default tablespace in oracle
How to check temp tablespace in Oracle
https://docs.oracle.com/cd/B10501_01/appdev.920/a96591/adl01int.htm

Filed Under: Oracle, Oracle Database Tagged With: lob index, lob segments

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Oracle tkprof utility
  • What is Skew in Database?
  • how to find file based on size in linux
  • SQL trace, 10046 event in Oracle
  • How to use sed replace string in file

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us