Skip to Main Content
  • Questions
  • My CLOB is occupying more than double the size it should take.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Anoop.

Asked: September 29, 2020 - 4:56 am UTC

Last updated: September 29, 2020 - 5:25 am UTC

Version: Oracle 12cR2

Viewed 1000+ times

You Asked

CREATE TABLESPACE av_ag_temp_tablespace
LOGGING
DATAFILE 'C:\Oracle\oradata\DEMO1\temptablespace.dbf'
SIZE 500M
AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
/

create table c ( x int, y clob ) TABLESPACE AV_AG_TEMP_TABLESPACE;


I inserted 1000 rows( each row has 500000 size ) into table C and then checked for the size occupied versus expected.

Then dropped the table C and table space and recreated the both table space and table.

Again I performed Insert 1000 rows, then deleted them and then inserted them again.
and dropped again and created table space and table and then performed insert - update operation.

Rows Clob Size DML Total Size(GB) Expected Size (GB)
1000 500000 Insert 1.14 0.5
1000 500000 Insert-Delete-Insert 1.81 0.5
1000 500000 Insert-Update 2.01 0.5

I got the total size using bytes from user_extents for that clob column.
Why the size is being occupied more than it needs to be.

and Connor said...


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database