07:31:46 155 147 TEST@orcl> select start_scn, commit_scn, undo_sql from flashback_transaction_query where table_name = 'F';
ORA-01031: insufficient privileges
--
07:48:00 155 4294967295 SYS@orcl> grant select any transaction to test;
07:48:26 155 148 TEST@orcl> select start_scn, commit_scn, undo_sql from flashback_transaction_query where table_name = 'F';

START_SCN | COMMIT_SCN
------------------------------------ | ------------------------------------
UNDO_SQL
--------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------

646,961.0 | 646,963.0
delete from "TEST"."F" where ROWID = 'AAAMi7AAFAAAAANAAA';

648,154.0 | 648,155.0
delete from "TEST"."F" where ROWID = 'AAAMi7AAAAAAAAAAAA';

647,577.0 | 647,579.0
delete from "TEST"."F" where ROWID = 'AAAMi7AAFAAAAAMAAA';

648,011.0 | 648,013.0
insert into "TEST"."F"("ID") values ('3');

shrink lob

-- create table (should be lmt tablespace or ORA-10635: Invalid segment or tablespace type will raise)
create table t_lob (id clob);
-- insert data
insert into t_lob select to_clob(lpad('x', 4000))||to_clob(lpad('x', 4000)) from dual connect by rownum <= 3000;
-- check size
select segment_name from dba_lobs where table_name = 'T_LOB';
SEGMENT_NAME
------------------------------
SYS_LOB0000015701C00001$$
select bytes/1024/1024 m from dba_segments where segment_name = 'SYS_LOB0000015701C00001$$';
M
------------------------------------
24.0
-- delete rows
delete from t_lob;
commit;
-- shrink
alter table t_lob shrink space cascade;
ORA-10636: ROW MOVEMENT is not enabled
--
alter table t_lob enable row movement;
alter table t_lob shrink space cascade;
Table altered.
select bytes/1024/1024 m from dba_segments where segment_name = 'SYS_LOB0000015701C00001$$';

M
------------------------------------
.0625