I had lots of data in a version enabled table which had been modified in some workspaces. From the LIVE workspace I deleted all the rows in the table and I then rolled back the workspaces (yes I probably should have done it differently). Once the workspaces were rolled back I would have expected the LT table to be empty, but it wasn't.
I can reproduce this in the example below. I insert two rows into the test table and version enable it. In a workspace I update one row in the table, which results in 3 rows in LT as expected. Back in LIVE I delete both rows from the table which results in 5 rows in LT which is fine. Then I rollback and remove the workspace I created for the update. At this point nothing needs the versioned rows in LT so I would expect them to go, but two rows remain. In my real scenario this is causing issues as the LT table is now far larger than it needs to be and queries against the versioned table are slow.
scott@JOHNOT> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
scott@JOHNOT> create table test_wm_table (
2 id number primary key,
3 name varchar2(20));
Table created.
scott@JOHNOT> execute dbms_wm.enableVersioning('test_wm_table');
PL/SQL procedure successfully completed.
scott@JOHNOT> insert into test_wm_table (id, name) values (1, 'first value');
1 row created.
scott@JOHNOT> insert into test_wm_table (id, name) values (2, 'second value');
1 row created.
scott@JOHNOT> commit;
Commit complete.
scott@JOHNOT> select * from test_wm_table_lt;
ID NAME VERSION NEXTVER DELSTATUS LTLOCK
---------- -------------------- ---------- ---------- ---------- ------------------------
1 first value 0 -1 10 *-1,-9,-1*$#SCOTT$#
2 second value 0 -1 10 *-1,-9,-1*$#SCOTT$#
scott@JOHNOT> execute dbms_wm.createWorkspace('test_workspace');
PL/SQL procedure successfully completed.
scott@JOHNOT> execute dbms_wm.goToWorkspace('test_workspace');
PL/SQL procedure successfully completed.
scott@JOHNOT> update test_wm_table set name = 'something else' where id = 1;
1 row updated.
scott@JOHNOT> commit;
Commit complete.
scott@JOHNOT> select * from test_wm_table_lt;
ID NAME VERSION NEXTVER DELSTATUS LTLOCK
---------- -------------------- ---------- ---------- ---------- ------------------------
1 first value 0 ,45, 10 *-1,-9,-1*$#SCOTT$#
2 second value 0 -1 10 *-1,-9,-1*$#SCOTT$#
1 something else 45 -1 11 *0,10,0*$#SCOTT$#
scott@JOHNOT> execute dbms_wm.gotoworkspace('LIVE');
PL/SQL procedure successfully completed.
scott@JOHNOT> delete test_wm_table;
2 rows deleted.
scott@JOHNOT> commit;
Commit complete.
scott@JOHNOT> select * from test_wm_table order by id;
no rows selected
scott@JOHNOT> select * from test_wm_table_lt order by id;
ID NAME VERSION NEXTVER DELSTATUS LTLOCK
---------- -------------------- ---------- ---------- ---------- ------------------------
1 first value 0 ,44,45, 10 *-1,-9,-1*$#SCOTT$#
1 something else 45 -1 11 *0,10,0*$#SCOTT$#
1 first value 44 -1 -11 *-1,-9,0*$#SCOTT$#
2 second value 44 -1 -11 *-1,-9,0*$#SCOTT$#
2 second value 0 ,44, 10 *-1,-9,-1*$#SCOTT$#
scott@JOHNOT> execute dbms_wm.rollbackWorkspace('test_workspace');
PL/SQL procedure successfully completed.
scott@JOHNOT> execute dbms_wm.removeWorkspace('test_workspace');
PL/SQL procedure successfully completed.
scott@JOHNOT> select * from test_wm_table order by id;
no rows selected
scott@JOHNOT> select * from test_wm_table_lt order by id;
ID NAME VERSION NEXTVER DELSTATUS LTLOCK
---------- -------------------- ---------- ---------- ---------- ------------------------
1 first value 0 -1 -11 *-1,-9,0*$#SCOTT$#
2 second value 0 -1 -11 *-1,-9,0*$#SCOTT$#