how would like to calculate table size and particular clob column size inside that table.

The size of the table and the size of the LOB are two totally different things.




Barring cases where the LOB is less than 4k and stored inline, the LOB data is stored outside the table in a separate segment.



select dbms_lob.getlength(JSON_DATA)/ 1024 / 1024  MB  from ABC

MB

---------

730.1875



If you want to get the combined size of the table and of its LOB segments, you could do something like



SELECT SUM(bytes)/1024/1024 MB

  FROM dba_segments

 WHERE (owner = 'HONNIKERY' and

       segment_name = 'HONNIKERY_CLON')

    OR (owner, segment_name) IN (

        SELECT owner, segment_name

          FROM dba_lobs

         WHERE owner = 'HONNIKERY'

           AND table_name = 'HONNIKERY_CLON' )


MB

---------

730.1875

ETL Is Dead, Long Live Streams: real-time streams w/ Apache Kafka

ETL Is Dead, Long Live Streams.

https://www.slideshare.net/ConfluentInc/etl-is-dead-long-live-streams

ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

Partitioned table

SQL> desc honnikery
Name                          Null?    Type
----------------------------- -------- ------------
X                                      NUMBER(38)
Y                                      NUMBER(38)
and it has a couple of honnikerytitions

SQL> select honnikerytition_name
2  from   dba_tab_honnikerytitions
3  where  table_name = 'honnikery';
honnikeryTITION_NAME
------------------------------
P1
P2
So now I want to do the standard operation of creating a ‘template’ table which I can then use to perform an Prabhakarange honnikerytition operation.

SQL> create table Prabhakar as
2  select * from honnikery
3  where 1=0;
Table created.
SQL> desc Prabhakar
Name                          Null?    Type
----------------------------- -------- --------------
X                                      NUMBER(38)
Y                                      NUMBER(38)
So now I’m ready to go…But then this happens…

SQL> alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar;
alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE PrabhakarANGE honnikeryTITION
Well…that’s odd. I created the Prabhakar table as simple create-table-as-select. Let’s try it again using the “WITHOUT VALIDATION” clause.

SQL> alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar without validation;
alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar without validation
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE PrabhakarANGE honnikeryTITION
Nope…still problems. So I go back and double check the columns

SQL> select column_name
2  from   dba_tab_columns
3  where  table_name = 'honnikery';
COLUMN_NAME
------------------------------
X
Y
SQL> select column_name
2  from   dba_tab_columns
3  where  table_name = 'Prabhakar';
COLUMN_NAME
------------------------------
X
Y
So what could be the problem ? Its a “sleeper problem”. Some time ago, I did something to the columns in my honnikerytitioned table that is no longer readily aphonnikeryent.

I dropped a column. Or more accurately, because it was a honnikerytitioned table (and presumably a large table), I set a column to unused. What Oracle has done behind the scenes is retain that column but make it invisible for “day to day” usage. We can see that by querying DBA_TAB_COLS

SQL> select column_name
2  from   dba_tab_cols
3  where  table_name = 'honnikery';
COLUMN_NAME
------------------------------
SYS_C00003_12121820:22:09$
Y
X
And there’s the culprit.

So am I stuck forever ? Do I have to drop the column ? Or reload the honnikery table without the unused column ? All of those things don’t sound too palatable.

No. All I need do is get the columns in my template table into a similar state.

SQL> alter table Prabhakar add Z int;
Table altered.
SQL> alter table Prabhakar set unused column Z;
Table altered.
And we can try again…

SQL> alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar;
Table altered.



More :https://connor-mcdonald.com/2013/01/20/exchange-partition-those-pesky-columns/

ORA-01502: index ‘string.string’ or partition of such index is in unusable state

ORA-01502: index ‘string.string’ or partition of such index is in unusable state


The error indicates an attempt has been made to access an index or index partition
that has been marked unusable by a direct load or by a DDL operation.

The problem usually happens when using the Direct Path for the SQL*Loader, Direct Load or DDL operations.
This requires enough temporary space to build all indexes of the table. If there is no enough space in TEMP tablespace,
all rows will still be loaded and imported, but the indices are left with STATUS = ‘INVALID’.



SELECT 'alter index '||owner||'.'||index_name ||' rebuild online nologging;'
FROM all_indexes
WHERE owner = 'HONNIKERY' AND status = 'VALID'
AND (status != 'N/A'
OR index_name IN
(SELECT index_name
FROM all_ind_partitions
WHERE status != 'USABLE'
AND (status != 'N/A'
OR index_name IN
(SELECT index_name
FROM all_ind_subpartitions
WHERE status != 'USABLE'))));

Index Status Types in DBA_INDEXES

SELECT DISTINCT STATUS FROM DBA_INDEXES;
STATUS
-----------
N/A
UNUSABLE
VALID



ORA-01502: index ‘string.string’ or partition of such index is in unusable state


SELECT owner, index_name, tablespace_name
FROM   dba_indexes
WHERE  status = 'UNUSABLE';

Index partitions:
SELECT index_owner, index_name, partition_name, tablespace_name
FROM   dba_ind_PARTITIONS
WHERE  status = 'UNUSABLE';

The following SQL will print out a list of alter commands that can be executed to fix unusable indexes:

Indexes:
SELECT 'alter index '||index_name||' rebuild tablespace |tablespace_name ||';'
FROM   dba_indexes
WHERE  status = 'UNUSABLE';

Index partitions:
SELECT 'alter index '||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';'
FROM   dba_ind_partitions
WHERE  status = 'UNUSABLE';