LONG data type
got deprecated a while and it is good to switch into LOB’s rather than staying
with LONG, one option for switching from LONG to LOB would be to use MODIFY
option of the ALTER TABLE statement.
Doing so
would lead to increased storage for the segments, which should be taken care –
while doing up this in a live production system.
sys@PDB2>
create tablespace TS_DEMO
2
datafile 'C:\APP\ADMINISTRATOR\ORADATA\CDB1\PDB2\TS_DEMO.dbf'
3 size
1M autoextend on next 256K
4
extent management local
5
uniform size 128K ;
Tablespace
created.
sys@PDB2>
alter user demo default tablespace ts_demo;
User altered.
sys@PDB2>
alter user demo quota unlimited on ts_demo;
User altered.
sys@PDB2>
conn demo/demo@pdb2
Connected.
demo@PDB2>
create table t(x long) tablespace ts_demo;
Table created.
demo@PDB2>
declare
2
txt varchar2(32000);
3
begin
4
for i in 1..30
5
loop
6
for k in (select * from dba_views
7 where text_length <=
32000
8 and text is not
null)
9
loop
10 txt := k.text ;
11 insert into t(x)
values(txt);
12
end loop;
13
commit;
14
end loop;
15 end
;
16 /
PL/SQL
procedure successfully completed.
demo@PDB2> exec
dbms_stats.gather_table_stats(user,'T');
PL/SQL
procedure successfully completed.
demo@PDB2>
column segment_name format a30
demo@PDB2>
column segment_type format a30
demo@PDB2>
select segment_name,segment_type,bytes
2 from
user_segments ;
SEGMENT_NAME SEGMENT_TYPE BYTES
------------------------------
------------------------------ ----------
T TABLE 4587520
demo@PDB2>
select bytes
2 from
v$datafile
3
where name like '%DEMO%';
BYTES
----------
4718592
So the table
sized around 4.5 MB in storage with LONG datatype in place.
When converting
this into CLOB datatype, things gets increased like this.
demo@PDB2>
alter table t modify X CLOB;
Table altered.
demo@PDB2>
select segment_name,segment_type,bytes
2 from
user_segments ;
SEGMENT_NAME SEGMENT_TYPE BYTES
------------------------------
------------------------------ ----------
SYS_IL0000075848C00001$$ LOBINDEX 131072
SYS_LOB0000075848C00001$$ LOBSEGMENT 7340032
T TABLE 2359296
demo@PDB2>
select bytes
2 from
v$datafile
3
where name like '%DEMO%';
BYTES
----------
14680064
demo@PDB2>
The table
has grown up to 14.5 MB during this LONG to CLOB conversion, so be aware of
this space implication before attempting a conversion.
