Find column semantics

When migrating from single-byte code to multi-byte code character sets, one way to move your data without data loss is to change the semantics used. In other words, VARCHAR2 and CHAR type columns can be redefined to use character semantics instead of the default byte semantics.

A schema-by-schema overview of the tables and column involved can be extracted by using the query below:

select owner "owner",
                CASE 
                    WHEN char_used = 'B' then 'BYTE'
                    WHEN char_used = 'C' then 'CHAR'
                END "semantics"
,count(*) "antall"
from dba_tab_columns
where owner in ('SCOTT','RYAN')
and data_Type in ('CHAR','VARCHAR2')
group by OWNER, ROLLUP(char_used);

owner	semantics	antall
SCOTT	BYTE	62
SCOTT	CHAR	610
SCOTT		672
RYAN	BYTE	486
RYAN		486

In this example, the SCOTT schema contains columns of both semantics types.
The schema RYAN contains only the default, BYTE.

Use the dbms_flashback to set your session back in time

A convenient way to set your session back in time, is to execute the dbms_flashback.enable_at_time procedure.
This way you can work with your data as they were at a previous point-in-time, given that your UNDO settings support it.

Oracle states

"The DBMS_FLASHBACK package acts as a time machine: you can turn back the clock, perform normal queries as if you were at that earlier time, and then return to the present. Because you can use the DBMS_FLASHBACK package to perform queries on past data without special clauses such as AS OF or VERSIONS BETWEEN, you can reuse existing PL/SQL code to query the database at earlier times."



A simple test of the procedure follows.

First, the user would need execute privileges on the package in which enable_at_time belongs, the dbms_flashback package:

connect sys/passwd as sysdba
grant execute on dbms_flashback to scott;

SQL>connect scott/passwd 
SQL>select album_id,album_title,artist_id from album where artist_id=10;

  ALBUM_ID ALBUM_TITLE                                         ARTIST_ID
---------- -------------------------------------------------- ----------
        10 Joshua tree                                                10
        11 Achtung Baby                                               10
        12 Zooropa                                                    10

SQL>

Make a change:

SQL> insert into album (album_id, album_title,artist_id) values(200,'Whiplash Smile', 10);

1 row created.

SQL> commit;

Commit complete.

SQL> select album_id,album_title,artist_id from album where artist_id=10;

  ALBUM_ID ALBUM_TITLE                                         ARTIST_ID
---------- -------------------------------------------------- ----------
       200 Whiplash Smile                                             10
        10 Joshua tree                                                10
        11 Achtung Baby                                               10
        12 Zooropa                                                    10

To see the content of the table album as it was 5 minutes ago:

SQL> execute dbms_flashback.enable_at_time(sysdate-5/1440);

PL/SQL procedure successfully completed.

SQL> select album_id,album_title,artist_id from album where artist_id=10;

  ALBUM_ID ALBUM_TITLE                                         ARTIST_ID
---------- -------------------------------------------------- ----------
        10 Joshua tree                                                10
        11 Achtung Baby                                               10
        12 Zooropa                                                    10

Disable the flashback setting:

SQL> execute dbms_flashback.disable();

PL/SQL procedure successfully completed.

If you want to use flashback queries against other schemas' tables, you need object privileges to do so:

grant flashback on scott.album to jim;

Alternatively, you can grant jim the system privilege flashback any table:

connect / as sysdba
grant flashback any table to jim;

Sources: Oracle Documentation This document from Oracle Support