Migration

select * from v$version;

$ tbboot -C
 
Available character set list
 
 Charset name       Equivalent Oracle Charset name
 
 AR8ISO8859P6       AR8ISO8859P6
 AR8MSWIN1256       AR8MSWIN1256 
 ASCII              US7ASCII 
 CL8ISO8859P5       CL8ISO8859P5 
 CL8KOI8R           CL8KOI8R 
 CL8MSWIN1251       CL8MSWIN1251
 EE8ISO8859P2       EE8ISO8859P2
 EL8ISO8859P7       EL8ISO8859P7
 EL8MSWIN1253       EL8MSWIN1253 
 EUCKR              KO16KSC5601
 EUCTW              ZHT32EUC
 GB18030            GB18030
 GBK                ZHS16GBK
 IW8ISO8859P8       IW8ISO8859P8 
 JA16EUC            JA16EUC 
 JA16EUCTILDE       JA16EUCTILDE 
 JA16SJIS
 JA16SJISTILDE
 MSWIN949           KO16MSWIN949
 RU8PC866           RU8PC866
 SJIS               JA16SJIS 
 SJISTILDE          JA16SJISTILDE 
 TH8TISASCII        TH8TISASCII 
 UTF16              AL16UTF16
 UTF8               UTF8
 VN8VN3             VN8VN3 
 WE8ISO8859P1       WE8ISO8859P1 
 WE8ISO8859P15      WE8ISO8859P15 
 WE8ISO8859P9       WE8ISO8859P9
 WE8MSWIN1252       WE8MSWIN1252 
 ZHT16BIG5          ZHT16BIG5 
 ZHT16HKSCS         ZHT16HKSCS 
 ZHT16MSWIN950      ZHT16MSWIN950
 
Available nls_date_lang set list
 
 
 AMERICAN
 BRAZILIAN PORTUGUESE 
 JAPANESE
 KOREAN 
 RUSSIAN
 SIMPLIFIED CHINESE 
 THAI
 TRADITIONAL CHINESE
 VIETNAMESE

select username, account_status, default_tablespace, temporary_tablespace 
from dba_users order by oracle;

set linesize 120
set pagesize 100
select username, default_tablespace, temporary_tablespace
from dba_users where account_status='OPEN' order by username;

select x.a tablespace_name
    , sum(x.b)/1024 "tot_size(mb)" 
    , sum(x.c)/1024 "used_size(mb)" 
    , sum(x.c)/sum(x.b)*100 rate
from (
    select
        b.tablespace_name a
        ,sum(bytes)/1024 b
        ,0 c
    from dba_data_files b 
    group by b.tablespace_name 
    union
    select
        d.tablespace_name,
        0,
        sum(bytes)/1024
    from dba_segments d
    group by d.tablespace_name 
    )x
group by x.a;

select sum(x.b)/1024 "tot(mb)", sum(x.c)/1024 "used(mb)" 
from (
    select sum(bytes)/1024 b, 0 c 
    from dba_data_files b
    union
    select 0, sum(bytes)/1024 
    from dba_segments d
) x;

select owner, object_type, status, count(*) 
from dba_objects
where owner in (select username from dba_users where account_status='OPEN' )
group by owner, object_Type, status 
order by owner, object_type, status;

WITH mig_user AS
(
    SELECT username
    FROM dba_users
    WHERE account_status='OPEN'
    AND username NOT IN('SYS', 'SYSTEM', 'WMSYS','EXFSYS', 'XDB', 'ORDSYS','MDSYS','SYSMAN')
)
SELECT
    O.OWNER
    ,NVL(TBL.TABLE_CNT, 0) AS "TABLE_CNT"
    ,O.VIEW_CNT
    ,O.DBLINK_CNT
    ,O.SEQ_CNT
    ,O.PKG_CNT
    ,O.PKGBODY_CNT
    ,O.PROC_CNT
    ,O.FUNC_CNT
    ,O.TYPE_CNT
    ,O.TYPEBODY_CNT
    ,O.LIB_CNT
    ,TRI.TRIGGER_CNT
    ,NVL(SYN.SYN_CNT,0) AS "SYN_CNT"
    ,NVL(PUB_SYN.PUB_SYN_CNT,0) AS "PUB_SYN_CNT" 
    ,NVL(IDX.IDX_CNT, 0) AS "IDX_CNT"
    ,NVL(G.GRANT_CNT, 0) AS "GRANT_CNT" 
    ,NVL(LOB.LOB_CNT, 0) AS "LOB_CNT" 
    ,NVL(JOB.JOB_CNT, 0) AS "JOB_CNT"
FROM (
    SELECT
        T.OWNER
        ,SUM( DBLINK_CNT ) AS "DBLINK_CNT" 
        ,SUM( VIEW_CNT ) AS "VIEW_CNT"
        ,SUM( SEQ_CNT ) AS "SEQ_CNT" 
        ,SUM( PKG_CNT ) AS "PKG_CNT"
        ,SUM( PKGBODY_CNT ) AS "PKGBODY_CNT" 
        ,SUM( PROC_CNT ) AS "PROC_CNT"
        ,SUM( FUNC_CNT ) AS "FUNC_CNT"
        ,SUM( TYPE_CNT ) AS "TYPE_CNT"
        ,SUM( TYPEBODY_CNT ) AS "TYPEBODY_CNT" 
        ,SUM( LIB_CNT ) AS "LIB_CNT"
    FROM (
            SELECT
                OWNER 
                ,CASE WHEN OBJECT_TYPE = 'DATABASE LINK' THEN COUNT(1) ELSE 0 END
AS "DBLINK_CNT"
                ,CASE WHEN OBJECT_TYPE = 'VIEW' THEN COUNT(1) ELSE 0 END AS
"VIEW_CNT"
                ,CASE WHEN OBJECT_TYPE = 'SEQUENCE' THEN COUNT(1) ELSE 0 END AS
"SEQ_CNT"
                ,CASE WHEN OBJECT_TYPE = 'PACKAGE' THEN COUNT(1) ELSE 0 END AS
"PKG_CNT"
                ,CASE WHEN OBJECT_TYPE = 'PACKAGE BODY' THEN COUNT(1) ELSE 0 END
AS "PKGBODY_CNT" 
                ,CASE WHEN OBJECT_TYPE = 'PROCEDURE' THEN COUNT(1) ELSE 0 END AS
"PROC_CNT"
                ,CASE WHEN OBJECT_TYPE = 'FUNCTION' THEN COUNT(1) ELSE 0 END AS
"FUNC_CNT"
                ,CASE WHEN OBJECT_TYPE = 'TYPE' THEN COUNT(1) ELSE 0 END AS
"TYPE_CNT"
                ,CASE WHEN OBJECT_TYPE = 'TYPE BODY' THEN COUNT(1) ELSE 0 END AS
"TYPEBODY_CNT" 
                ,CASE WHEN OBJECT_TYPE = 'LIBRARY' THEN COUNT(1) ELSE 0 END AS
"LIB_CNT"
            FROM dba_objects
            WHERE owner in (select username from mig_user) 
            GROUP BY owner, OBJECT_TYPE
            )T
        GROUP BY T.OWNER
    )O
LEFT JOIN (
    SELECT OWNER, COUNT(1) AS TABLE_CNT
    FROM DBA_TABLES
    WHERE owner in (select username from mig_user) 
    GROUP BY OWNER
) TBL ON O.OWNER = TBL.OWNER
LEFT JOIN (
    SELECT OWNER, COUNT(1) AS GRANT_CNT
    FROM DBA_TAB_PRIVS
    WHERE owner in (select username from mig_user) 
    AND TABLE_NAME NOT LIKE 'BIN$%'
    GROUP BY OWNER
) G ON O.OWNER = G.OWNER
LEFT JOIN (
    SELECT OWNER, COUNT(1) AS IDX_CNT
    FROM DBA_INDEXES
    WHERE owner in (select username from mig_user) 
    GROUP BY OWNER
) IDX ON O.OWNER = IDX.OWNER
    LEFT JOIN (
    SELECT OWNER, COUNT(1) AS LOB_CNT
    FROM DBA_LOBS
    WHERE owner in (select username from mig_user)
    GROUP BY OWNER
) LOB ON O.OWNER = LOB.OWNER
LEFT JOIN (
    SELECT SCHEMA_USER, COUNT(1) AS JOB_CNT
    FROM DBA_JOBS
    WHERE SCHEMA_USER in (select username from mig_user) 
    GROUP BY SCHEMA_USER
) JOB ON O.OWNER = JOB.SCHEMA_USER
LEFT JOIN (
    SELECT OWNER, COUNT(1) AS TRIGGER_CNT
    FROM DBA_TRIGGERS
    WHERE OWNER in (select username from mig_user)
    AND TRIGGER_NAME NOT LIKE 'BIN$%'
    GROUP BY OWNER
) TRI ON O.OWNER = TRI.OWNER
LEFT JOIN (
    SELECT OWNER, COUNT(1) AS "SYN_CNT"
    FROM DBA_SYNONYMS
    WHERE OWNER in (select username from mig_user)
    AND OWNER != 'PUBLIC'
    GROUP BY OWNER
) SYN ON O.OWNER = SYN.OWNER
LEFT JOIN (
    SELECT TABLE_OWNER as "OWNER", COUNT(1) AS "PUB_SYN_CNT" 
    FROM DBA_SYNONYMS
    WHERE TABLE_OWNER in (select username from mig_user) 
    AND OWNER = 'PUBLIC'
    GROUP BY TABLE_OWNER
) PUB_SYN ON O.OWNER = PUB_SYN.OWNER 
ORDER BY O.OWNER;

set linesize 150
col owner format a30

select owner
,constraint_type
,count( constraint_name ) as "con_cnt"
from dba_constraints
where owner in (select username from dba_users where account_status='OPEN' ) 
group by owner, constraint_type
order by owner ,constraint_type;

set linesize 150
set pagesize 100
column value$ format a100

select
    name,
    value$
from
    sys.props$
where
    name like '%CHARACTERSET' 
order by
    name;

select * from _vt_nls_character_set;

show parameter <parameter name>

set linesize 150
select * from v$logfile;

select group#, bytes/1024/1024 as "size(mb)", members, archived from v$log;

show parameters undo_tablespace; set linesize 150
col tablespace_name format a40
col file_name format a50
select tablespace_name, file_name, bytes/1024/1024 "size(mb)"
,maxbytes/1024/1024 "maxsize(mb)", autoextensible 
from dba_data_files
where tablespace_name = 'UNDOTBS1' -- input the result value of show parameters undo_tablespace;

col file_name format a50
select tablespace_name, file_name, bytes/1024/1024 "size mb", autoextensible 
from dba_temp_files;

select log_mode from v$database;

set linesize 150
col owner format a15
col db_link format a20
col username format a20
col host format a50
select * from dba_db_links;

cat $ORACLE_HOME/network/admin/tnsnames.ora

set linesize 200
select job, schema_user, broken, instance from dba_jobs; 
set linesize 150
col interval format a50
select job, schema_user, next_date, broken, interval from dba_jobs;

SELECT *
FROM DBA_OBJECTS
WHERE OBJECT_TYPE LIKE '%CLUSTER%' 
AND OWNER NOT IN ('SYS');

set linesize 200
SELECT owner, table_name, column_name, data_type 
FROM DBA_TAB_COLS
WHERE DATA_TYPE LIKE '%XML%' 
AND OWNER NOT IN ('SYS');

SELECT *
FROM DBA_INDEXES
WHERE INDEX_TYPE LIKE '%BITMAP%';

$ tbsql sys/tibero


tbSQL


Connected to Tibero.


SQL> spool export_ddl.log Spooling is started.

SQL> @export_ddl.sql
-- Check for Errors Through the spool File

SQL> spool off
Spooling is stopped: export_ddl.log

$ tbsql sys/tibero


tbSQL


Connected to Tibero.


SQL> spool export_other_ddl.log Spooling is started.

SQL> @export_other_ddl.sql
-- Check for Errors Through the spool File

SQL> spool off
Spooling is stopped: export_other_ddl.log

SELECT OWNER, NAME, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE 
FROM DBA_DEPENDENCIES
WHERE TYPE='VIEW'
AND REFERENCED_TYPE NOT IN ('TABLE', 'SYNONYM') 
AND REFERENCED_NAME NOT IN ('DUAL')
AND REFERENCED_NAME NOT LIKE 'DBA_%'
AND OWNER NOT IN ('SYS', 'ORANGE','MDSYS', 'EXFSYS', 'XDB', 'SYSMAN', 'DBSNMP', 'SYSTEM' )
AND REFERENCED_OWNER NOT IN ('SYS', 'ORDSYS', 'WMSYS', 'MDSYS','EXFSYS', 'XDB',
'SYSMAN','SYSTEM','OLAPSYS', 'APEX_030200', 'CTXSYS') 
ORDER BY 1, 2;

CREATE OR REPLACE TYPE DOCUROLE_ID_OBJ IS OBJECT(DOCUROLE_ID VARCHAR2(6));
/
CREATE OR REPLACE TYPE DOCUROLE_ID_TAB IS TABLE OF DOCUROLE_ID_OBJ;
/
CREATE OR REPLACE FUNCTION USER_DOCUROLESET(PARAM IN VARCHAR2) 
RETURN DOCUROLE_ID_TAB
IS
L_DOCUROLE_ID_TAB DOCUROLE_ID_TAB := DOCUROLE_ID_TAB(); 
BEGIN
L_DOCUROLE_ID_TAB.EXTEND; 
L_DOCUROLE_ID_TAB(1).DOCUROLE_ID := 'test'; 
RETURN L_DOCUROLE_ID_TAB;
END;
/

CREATE OR REPLACE PACKAGE "PK_TYPES" AS
TYPE DOCUROLE_ID_OBJ IS RECORD(DOCUROLE_ID VARCHAR2(6)); 
TYPE DOCUROLE_ID_TAB IS TABLE OF DOCUROLE_ID_OBJ;
END;
/
CREATE OR REPLACE FUNCTION USER_DOCUROLESET(PARAM IN VARCHAR2) 
RETURN PK_TYPES.DOCUROLE_ID_TAB
IS
L_DOCUROLE_ID_TAB PK_TYPES.DOCUROLE_ID_TAB := PK_TYPES.DOCUROLE_ID_TAB(); 
N INTEGER := 0;
BEGIN 
L_DOCUROLE_ID_TAB.EXTEND;
L_DOCUROLE_ID_TAB(1).DOCUROLE_ID := 'test'; 
RETURN L_DOCUROLE_ID_TAB;
END;
/

alter trigger trigger_name disable;

col owner format a20 
set pages 500
select owner, object_type, status, count(*) 
from dba_objects
group by owner, object_Type, status
order by owner, object_type, status;

set linesize 120
col object_name format A30 
col owner format a20
select owner, object_name, object_type, status 
from dba_objects
where status='INVALID';

$ tbsql sys/tibero

tbSQL

Connected to Tibero.

SQL> exec utl_recomp.recomp_serial(schema=>'MIGRATION_USER');

PSM completed.

select * from view_name where rownum < 1;

alter function function_name compile; 
alter procedure procedure_name compile;

show errors

alter index index_name rebuild;

set linesize 150
column owner format a30

select
    owner,
    count(table_name) as "table_cnt"
from
    dba_tables 
where
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by
    owner 
order by
    owner;

set linesize 150
column owner format a30

select
    owner,
    count(view_name) as "view_cnt"
from
    dba_views 
where
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by
    owner 
order by
    owner;

set linesize 150 
column owner format a30

select
    owner,
    count(1) as "synonym_cnt" 
from
    dba_synonyms
where
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1') 
group by
    owner
order by owner;

set linesize 150
column sequence_owner format a30 

select
    sequence_owner,
    count( sequence_name ) as "sequence_cnt"
from
    dba_sequences
where
    sequence_owner not in
('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1') 
group by
    sequence_owner
order by
    sequence_owner;

set linesize 150
column owner format a30

select
    owner,
    count( name ) as "package_cnt"
from
    dba_source 
where
    type = 'PACKAGE' and
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by
    owner 
order by
    owner;

set linesize 150
column owner format a30


select
    owner,
    count( name ) as "package_body_cnt"
from
    dba_source 
where
    type = 'PACKAGE BODY' and
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by
    owner 
order by
    owner;

set linesize 150
column owner format a30

select
    owner,
    count( name ) as "function_cnt"
from
    dba_source 
where
    type = 'FUNCTION' and
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1') 
group by
    owner 
order by
    owner;

set linesize 150
column owner format a30


select
    owner,
    count( name ) as "procuedure_cnt"
from
    dba_source 
where
    type = 'PROCEDURE' and
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by
    owner 
order by
    owner;

set linesize 150
column owner format a30


select
    owner,
    count( index_name ) as "index_cnt"
from
    dba_indexes 
where
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by
    owner 
order by
    owner;    

set linesize 150
column owner format a30


select
    owner, 
    constraint_type,
    count( constraint_name ) as "constraint_cnt"
from
    dba_constraints 
where
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by
    owner, constraint_type 
order by
    owner ,constraint_type;

set linesize 150 
column col format a30

select
    owner,
    count(1) as "grant_cnt"
from
    dba_tab_privs 
where
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by
    owner 
order by
    owner;

set linesize 150
column owner format a30


select
    owner,
    count(trigger_name) as "trigger_cnt"
from
    dba_triggers
where
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by
    owner 
order by
    owner;

select 'tbl1' as table_name, count(*) as oracle_count, count(*) 
- (select count(*) from tbl1 where rownum = 1) as diff from tbl1@ora;

select * from tbl1@ora minus 
select * from tbl1;

String jdbc_url = "jdbc:oracle:thin:@127.0.0.1:1521:ORCL"; 
String user = "oracle_username";
String passwd = "oracle_passwd"; 
Class.forName("oracle.jdbc.driver.OracleDriver");

String jdbc_url = "jdbc:tibero:thin:@127.0.0.1:8629:tibero"; 
String user = "tibero_username";
String passwd = "tibero_passwd";
Class.forName("com.tmax.tibero.jdbc.TbDriver");

DRIVER={Microsoft ODBC for Oracle}; 
server=(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)) 
(CONNECT_DATA=(SERVICE_NAME=utf8)));UID=scott;PWD=tiger;

DRIVER={Tibero 7 ODBC Driver};SERVER=127.0.0.1;PORT=8629
;DB=t7;UID=dbtech;PWD=dbtech;

Provider=msdaora;Data Source=MyDB;User Id=myUsername
;Password=myPassword;

Provider=tbprov.Tbprov.6;Data Source=MyDB;User ID=dbtech;Password=dbtech
;Updatable Cursor=True;OLE DB Services=-2