Helene

SQL for finding oracle version: select * from v$version where banner like ‘Oracle%’;
Finding Database Character Encoding: SELECT parameter, value FROM nls_database_parameters WHERE parameter = ‘NLS_CHARACTERSET’;
Find the logged in users privileges: select * from session_privs

Parameter info about oracle tools: [app] help=y (ex. csscan help=y, exp help=y)

Function for calculating table compression ratio: (Source: http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/poess_tablecomp.html)

create or replace function compression_ratio (tabname varchar2)
return number is — sample percentage
pct number := 0.000099;
blkcnt number := 0; blkcntc number; begin
execute immediate ‘ create table TEMP$$FOR_TEST pctfree 0
as select * from ‘ || tabname || ‘ where rownum < 1';
while ((pct < 100) and (blkcnt < 1000)) loop
execute immediate 'truncate table TEMP$$FOR_TEST';
execute immediate 'insert into TEMP$$FOR_TEST select *

from ' || tabname || ' sample block (' || pct || ',10)';
execute immediate 'select
count(distinct(dbms_rowid.rowid_block_number(rowid)))
from TEMP$$FOR_TEST' into blkcnt;
pct := pct * 10;
end loop;
execute immediate 'alter table TEMP$$FOR_TEST move compress ';
execute immediate 'select
count(distinct(dbms_rowid.rowid_block_number(rowid)))
from TEMP$$FOR_TEST' into blkcntc;
execute immediate 'drop table TEMP$$FOR_TEST';
return (blkcnt/blkcntc); end;

The function can be runned with this: select eli3.compression_ratio('tablename') from dual

Character Set Migration howto
1. Prescan the database to identify issues using csscan. It generates a summary report. Ensure all errors (truncation and data loss) don’t occur.