How to Move LOBSEGMENT LOBINDEX to Another Tablespace

Check LOB current tablespace

SQL> select owner, segment_name, segment_type, tablespace_name , bytes from dba_segments where owner='USER1';

OWNER    SEGMENT_NAME                SEGMENT_TYPE       TABLESPACE_NAME    BYTES
-------- --------------------------- ------------------ ----------------- ----------
USER1    SYS_IL0000057639C00010$$    LOBINDEX           TEMPDATA	       65536
USER1    SYS_LOB0000057639C00010$$   LOBSEGMENT         TEMPDATA	    12713984
USER1    SYS_IL0000057685C00008$$    LOBINDEX           TEMPDATA	       65536
USER1    SYS_LOB0000057685C00008$$   LOBSEGMENT         TEMPDATA	     2228224
USER1    SYS_IL0000057693C00008$$    LOBINDEX           TEMPDATA	       65536
USER1    SYS_LOB0000057693C00008$$   LOBSEGMENT         TEMPDATA	      131072

6 rows selected.

Generate moving LObs sql

SQL> select 'alter table  '||owner||'.'||table_name||' move lob ('||column_name||') store as (tablespace USER1) ;' from dba_lobs where  owner='USER1';

'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'MOVELOB('||COLUMN_NAME||')STOREAS(TABLESPACE USER1;'
---------------------------------------------------------------------------------------------
alter table  USER1.TABLE1 move lob (DATA) store as (tablespace USER1);
alter table  USER1.TABLE2 move lob (DATA) store as (tablespace USER1);
alter table  USER1.TABLE3 move lob (DATA) store as (tablespace USER1);

move lobs into new tablespace

SQL>alter table  USER1.TABLE1 move lob (DATA) store as (tablespace USER1);
Table altered.

SQL>alter table  USER1.TABLE2 move lob (DATA) store as (tablespace USER1);
Table altered.

SQL>alter table  USER1.TABLE3 move lob (DATA) store as (tablespace USER1);
Table altered.

check lobs moved into new tablespace

SQL> select owner, segment_name, segment_type, tablespace_name , bytes from dba_segments where owner='USER1';

OWNER    SEGMENT_NAME                SEGMENT_TYPE       TABLESPACE_NAME    BYTES
-------- --------------------------- ------------------ ----------------- ----------
USER1    SYS_IL0000057639C00010$$    LOBINDEX           USER1	       65536
USER1    SYS_LOB0000057639C00010$$   LOBSEGMENT         USER1	    12713984
USER1    SYS_IL0000057685C00008$$    LOBINDEX           USER1	       65536
USER1    SYS_LOB0000057685C00008$$   LOBSEGMENT         USER1	     2228224
USER1    SYS_IL0000057693C00008$$    LOBINDEX           USER1	       65536
USER1    SYS_LOB0000057693C00008$$   LOBSEGMENT         USER1	      131072

6 rows selected.

rebuild UNUSABLE indxes

SQL>  select 'alter index '||owner||'.'||INDEX_NAME||' rebuild;' from dba_indexes where owner='USER1' and STATUS='UNUSABLE';

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
------------------------------------------------------
alter index USER1.idx_customers rebuild;
alter index USER1.idx_prods rebuild;
alter index USER1.idx_orders rebuild;
....
..
.

SQL>alter index USER1.idx_customers rebuild;
Index altered.

SQL>alter index USER1.idx_prods rebuild;
Index altered.

SQL>alter index USER1.idx_orders rebuild;
Index altered.

Please note LOBINDEX will be automatically relocated onto the same new tablespace as LOBSEGMENT, so there is no need to run SQL to relocate LOBINDEX . Otherwise the error will occur.

How to Check Oracle Database Table and Index Fragmentation

The following posts demonstrated how to check fragmentations in PostgreSQL:
How to Identify Fragmentation in PostgreSQL RDS
Code to Report Table Index Schema or Database Fragmentation in PostgreSQL

This post will show how to check table and index fragmentations in Oracle database.

Subscribe to get access

Read more of this content when you subscribe today.

Oracle Enterprise Manager Corrective Action (CA) Execution Stats

How many times Corrective Action (CA) has been executed one year so far ?

SQL> select CA_NAME,  count(*) 
       from sysman.MGMT$CA_EXECUTIONS 
      where START_TIME>=sysdate-365  
   group by CA_NAME order by 1

CA_NAME                                  COUNT(*)
---------------------------------------- ----------
...
..
.
EXTEND_TABLESPACE_NORAC                         3
EXTEND_TABLESPACE_PDB                          16
EXTEND_TABLESPACE_RAC                          70
NON-RAC_RMAN_AUTO_BACKUP_ARCHIVELOGS           56
RMAN_AUTO_BACKUP_ARCHIVELOGS                  533
...
..
.

10 rows selected.

About view MGMT$CA_EXECUTIONS , Oracle official documentation is here or just click the following link.

https://docs.oracle.com/en/enterprise-manager/cloud-control/enterprise-manager-cloud-control/13.5/emvws/job-views.html#GUID-CBDDC556-498B-4458-BCB4-CF28CAB3A834

How to Get All Oracle Databases Size From OEM Repository

Database Manager asks you as a DBA about all the database size in the supported environment .

SQL> select host_name,target_name, sum( FILE_SIZE/1024/1024/1024)
     from mgmt$db_datafiles 
     group by host_name,target_name 
     order by host_name, target_name;

HOST_NAME                                TARGET_NAME                              SUM(FILE_SIZE/1024/1024/1024)
---------------------------------------- ---------------------------------------- -----------------------------
HOST1                                    DB1                                                    38.65
HOST2                                    DB2                                                    385.88
HOST3                                    DB3                                                    27.52
...
..
.

OR we can use another another view mgmt$db_tablespaces :

SQL> select host_name,target_name,sum( TABLESPACE_SIZE/1024/1024/1024) 
     from mgmt$db_tablespaces 
     group by host_name,target_name 
     order by 1,2;