Insert Records Into an Oracle Table and Rollback

After we insert records into a table, we can rollback and undo the changes. However, if we run any DDL queries afterwards such as "create table", "create view", "dbms_stats.gather_table_stats" etc., the data inserted are committed and can not be rollback. The following are some examples.

SQL> create table tbl_a (num number);
Table created.

SQL> insert into tbl_a values(1);
1 row created.

SQL> select * from tbl_a;

NUM
1

SQL> commit;
Commit complete.

We insert a new record and can rollback.

SQL> insert into tbl_a values(2);
1 row created.

SQL> select * from tbl_a;

NUM
1
2

SQL> rollback;
Rollback complete.

SQL> select * from tbl_a;

NUM
1

We insert another new record.

SQL> insert into tbl_a values(2);
1 row created.

We create a view on the table.

SQL> create view v_tbl_a as select * from tbl_a;
View created.

Because of "create view", data inserted is committed and can not be rolled back.

SQL> rollback;
Rollback complete.

SQL> select * from tbl_a;

NUM
1
2

We insert another new value.

SQL> insert into tbl_a values(3);
1 row created.

We run dbms_stats.gather_table_stats(). The data inserted can not be rolled back.

SQL> exec dbms_stats.gather_table_stats(null,'tbl_a');
PL/SQL procedure successfully completed.

SQL> rollback;
Rollback complete.

SQL> select * from tbl_a;

NUM
1
2
3

We insert another new value.

SQL> insert into tbl_a values(4);
1 row created.

We create a new table. Because "create table" is a DDL statement, we can not roll back the inserted data even if it is inserted into a different table.

SQL> create table tbl_xb (value varchar2(32));
Table created.

SQL> rollback;
Rollback complete.

SQL> select * from tbl_a;

NUM
1
2
3
4

We insert another new value.

SQL> insert into tbl_a values(5);
1 row created.

We run dbms_stats.gather_table_stats() on tbl_xb and we can not roll back the inserted data even if it is inserted into a different table.

SQL> exec dbms_stats.gather_table_stats(null,'tbl_xb');
PL/SQL procedure successfully completed.

SQL> rollback;
Rollback complete.

SQL> select * from tbl_a;

NUM
1
2
3
4
5

We insert another new value.

SQL> insert into tbl_a values(6);
1 row created.

We create a view on tbl_xb. As a result, data inserted into tbl_a is committed and can not be rolled back.

SQL> create view view_tbl_xb as select * from tbl_xb;
View created.

SQL> rollback;
Rollback complete.

SQL> select * from tbl_a;

NUM
1
2
3
4
5
6

6 rows selected.