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.