tb6=(
(INSTANCE=(HOST=192.168.1.129) 
(PORT=8629)
(DB_NAME=tb6) 
)
hidden=(
(INSTANCE=(HOST=192.168.1.129) 
(PORT=8630)
(DB_NAME=tb6) 
)

$TB_HOME/client/config/tbpc.cfg
$TB_HOME/client/config/tbpcb.cfg

$TB_HOME/client/epa/java/config/tbepa.cfg
(Tibero 6의 경우 : $TB_HOME/client/epa/java/config/epa.cfg)

$TBGW_HOME/oracle/config/tbgw.cfg
$tbJavaGW/tbgw.cfg(Tibero 6의 경우 : $tbJavaGW/jgw.cfg)

create database
user sys identified by tibero
character set MSWIN949 
national character set UTF16
logfile group 0 ('log001.log','log002.log') size 50M,
group 1 ('log011.log','log012.log') size 50M,
group 2 ('log021.log','log022.log') size 50M
maxdatafiles 8192
maxlogfiles 100
maxlogmembers 8
noarchivelog
datafile 'system001.dtf' size 1G autoextend on next 256M maxsize unlimited 
default tablespace USR
datafile 'usr001.dtf' size 1G autoextend on next 256M maxsize unlimited    
default temporary tablespace TEMP
tempfile 'temp001.dtf' size 1G autoextend off
undo tablespace UNDO
datafile 'undo001.dtf' size 1G autoextend off
SYSSUB datafile 'syssub001.dtf' size 1G autoextend on next 32M maxsize unlimited;

$TB_HOME/scripts/system.sh

tbimport username={sys | dba계정} password=<password> port=<port_no> sid=<SID>
file=<file name> log=<log-file name> full=y script=y ignore=y

tbimport username=sys password=pwd port=8629 sid=tb7 
file=tibero6_all.dat log=tibero7_imp.log full=y ignore=y

DECLARE
job_no number;
BEGIN
DBMS_JOB.SUBMIT(job_no,'dbms_output.put_line(''ok'');',SYSDATE,'SYSDATE+1');
END;
/

CREATE DATABASE LINK olink CONNECT TO scott IDENTIFIED BY 'tiger' USING 'oragw';

CREATE OR REPLACE LIBRARY extproc IS '/home/tibero/cepa/libextproc.so';

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "JavaExtproc" AS
public class SimpleMath {
public static int findMax(int x, int y) {
if (x >= y) return x;
else return y;
}
}
/

CREATE OR REPLACE PROCEDURE wrap_test WRAPPING
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END;
/

CREATE OR REPLACE DIRECTORY dir_ext AS '/home/tibero/t7/work/external/external'
/

tbdown immediate

tbboot -t mount

tbsql sys/tibero
SQL> alter database archivelog;

tbdown immediate
tbboot

tbsql sys/tibero
SQL> SHOW PARAM LOG_ARCHIVE_DEST
NAME                    TYPE     VALUE
----------------------- -------- --------------------------------------------
LOG_ARCHIVE_DEST        DIRNAME  /home/tibero/t7/tibero7/database/t7/archive/
​
SQL> alter system switch logfile;
System altered.
​
SQL> !ls -la /home/tibero/t7/tibero7/database/t7/archive/
total 23572
-rw-------. 1 tibero dba 24126976 Apr 4 22:40 log-t0-r0-s1.arc
​
SQL> select log_mode from v$database;
LOG_MODE
-------------- -
ARCHIVELOG

Single : jdbc:tibero:thin:@192.168.11.49:8629:tibero
TAC : "jdbc:tibero:thin:@(description=
(failover=on)(load_balance=on)
(address_list=(address=(host=192.168.11.49)(port=8629))
(address=(host=192.168.11.50)(port=8629))
)(DATABASE_NAME=tibero7))”

DRIVER={Tibero 6 ODBC Driver};SERVER=127.0.0.1;PORT=8629;UID=tibero;PWD=tmax;

DRIVER={Tibero 7 ODBC Driver};SERVER=127.0.0.1;PORT=8629;DB=tb7;UID=tibero;
PWD=tmax;

$TB_HOME/client/bin/ (해당 경로에 연결 대상 DB에 따른 Gateway 존재)

$TB_HOME/bin/psmjavac
$TB_HOME/client/bin/tbjavaepa

$TB_HOME/bin/psmjavac
$TB_HOME/client/bin/tbjavaepa

SQL> SET LINESIZE 120 
SQL> COL name FOR a30
SQL> COL value FOR a20 
SQL> SELECT name, value 
FROM sys._dd_props
WHERE name like '%CHARACTERSET%'
ORDER BY 1;

NAME                                VALUE
--------------------------------    ----------
NLS_CHARACTERSET                    UTF8
NLS_NCHAR_CHARACTERSET              UTF16

SQL> SET LINESIZE 120
SQL> COL member FOR a50
SQL> SELECT * FROM v$logfile;

GROUP#             STATUS  TYPE   MEMBER
-----------      --------  -----------------------------
0                  ONLINE /data2/tb6/tbdata/log001.log
0                  ONLINE /data2/tb6/tbdata/log002.log
1                  ONLINE /data2/tb6/tbdata/log003.log
1                  ONLINE /data2/tb6/tbdata/log004.log
2                  ONLINE /data2/tb6/tbdata/log005.log
2                  ONLINE /data2/tb6/tbdata/log006.log

SQL> SELECT group#, bytes/1024/1024 as "size(mb)", members, archived FROM v$log;

GROUP#             size(mb)   MEMBERS    ARCHIVED
-----------      --------  -----------   -----------
0                   50        2          NO
1                   50        2          NO
2                   50        2          NO

SQL> SET LINESIZE 120
SQL> COL tablespace_name FOR a20
SQL> COL file_name FOR a50
SQL> SELECT tablespace_name 
FROM dba_tablespaces
WHERE contents = 'UNDO';

TABLESPACE_NAME
--------------------
UNDO


SQL> show parameter UNDO_TABLESPACE;

NAME                  TYPE      VALUE
-----------------     --------  ---------
UNDO_TABLESPACE       STRING    UNDO

SQL> SELECT tablespace_name,
file_name,
bytes/1024/1024 "size(mb)", 
maxbytes/1024/1024 "maxsize(mb)", 
autoextensible
FROM dba_data_files
WHERE tablespace_name like 'UNDO%';

TABLESPACE_NAME  FILE_NAME                    size(mb) maxsize(mb) AUTOEXTENSIBLE
---------------- ---------------------------- -------- ----------- --------------
UNDO          /data2/tb6/tbdata/undo001.tdf   400      1024        YES

SQL> SET LINESIZE 150
SQL> COL tablespace_name FOR a30
SQL> COL file_name FOR a50
SQL> SELECT tablespace_name,
file_name, bytes/1024/1024 "size(mb)", 
autoextensible,
maxbytes/1024/1024 "max(mb)" 
FROM dba_temp_files;

TABLESPACE_NAME  FILE_NAME                      size(mb) AUTOEXTENSIBLE max(mb)
---------------- -----------------------------  -------- -------------- --------
TEMP             /data2/tb6/tbdata/temp001.tdf  400      YES            1024        

SQL> SET linesize 150
SQL> COL tablespace_name FOR a30
SQL> COL file_name FOR a50
SQL> SELECT tablespace_name, 
file_name,
bytes/1024/1024 "size(mb)", 
maxbytes/1024/1024 "maxsize(mb)", 
autoextensible
FROM dba_data_files
WHERE tablespace_name not in('SYSTEM', 'UNDO', 'USR');

TABLESPACE_NAME  FILE_NAME                    size(mb) maxsize(mb) AUTOEXTENSIBLE
---------------- ---------------------------- -------- ----------- --------------
DBTECH_TBS       /tb6/tbdata/dbtech_tbs01.dtf 50       5120        YES
DBTECH_IDX       /tb6/tbdata/dbtech_idx01.dtf 50       1024        YES

SQL> SELECT log_mode FROM v$database; 
LOG_MODE
---------------
ARCHIVELOG

SQL> SHOW PARAM ARCHIVE
NAME                    TYPE     VALUE
----------------------- -------- ------------------------------------------
LOG_ARCHIVE_DEST        DIRNAME  /data2/tb6/tibero6/database/tb4/archive/ 
LOG_ARCHIVE_FORMAT      STRING   log-t%t-r%r-s%s.arc
LOG_ARCHIVE_OVERWRITE   Y_N      NO 
LOG_ARCHIVE_RETRY_TIME  UINT32   60

SQL> alter database backup controlfile to trace 
as '/data2/tb6/tb6_export/controlfile_tb6.sql'; 
Database altered.
SQL> !cat /data2/tb6/tb6_export/controlfile_tb6.sql
CREATE CONTROLFILE REUSE DATABASE "tb6" LOGFILE
GROUP 0 (
'/data2/tb6/tbdata/log001.log', 
'/data2/tb6/tbdata/log002.log'
) SIZE 50M, 
GROUP 1 (
'/data2/tb6/tbdata/log003.log', 
'/data2/tb6/tbdata/log004.log'
) SIZE 50M,
GROUP 2 (
'/data2/tb6/tbdata/log005.log', 
'/data2/tb6/tbdata/log006.log'
) SIZE 50M
NORESETLOGS DATAFILE
'/data2/tb6/tbdata/system001.tdf', 
'/data2/tb6/tbdata/undo001.tdf', 
'/data2/tb6/tbdata/usr001.tdf', 
'/data2/tb6/tbdata/dbtech_tbs01.dtf', 
'/data2/tb6/tbdata/dbtech_idx01.dtf' 
ARCHIVELOG
MAXLOGFILES 255
MAXLOGMEMBERS 8
MAXDATAFILES 100 CHARACTER SET MSWIN949
NATIONAL CHARACTER SET UTF16
;
--ALTER DATABASE MOUNT
---- Recovery is required in MOUNT mode.
--ALTER DATABASE RECOVER AUTOMATIC;
--ALTER DATABASE OPEN ;
---- Adding Tempfiles is required in OPEN mode.
-- ALTER TABLESPACE TEMP ADD TEMPFILE '/data2/tb6/tbdata/temp001.tdf'
-- SIZE 400M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G;

SQL> SET LINESIZE 150
SQL> COL what FOR a30
SQL> COL interval FOR a30
SQL> COL username FOR a20
SQL> alter session set NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';
SQL> SELECT job as job_no, 
b.username,
what,
to_char(next_date, 'yyyy/mm/dd HH24:MI:SS') as next_date, 
interval,
broken
FROM dba_jobs a, dba_users b 
WHERE a.schema_user=b.user_id;


JOB_NO USERNAME WHAT                        NEXT_DATE           INTERVAL  BROKEN
------ -------- --------------------------- ------------------- --------- ------
1       DBTECH  dbms_output.put_line('ok');  2013/06/30 00:00:00 SYSDATE+1 N

SQL> SET LINESIZE 150
SQL> COL owner FOR a15
SQL> COL db_link FOR a20
SQL> COL username FOR a20
SQL> COL host FOR a10
SQL> COL created FOR a10
SQL> SELECT owner, db_link, username, host, created FROM dba_db_links;
OWNER           DB_LINK              USERNAME             HOST       CREATED
--------------- -------------------- -------------------- ---------- ----------
DBTECH          TBLINK                DBTECH2             tb6        2013/06/12

SQL> SET LINESIZE 120
SQL> COL owner FOR a10
SQL> COL library_name FOR a30
SQL> COL file_spec FOR a50
SQL> COL status FOR a10
SELECT owner, library_name, file_spec, status FROM dba_libraries;

SET LINESIZE 120
COL owner FOR a10
COL name FOR a30
COL source FOR a50
SELECT owner, name, source FROM dba_java_classes;

SQL> SHOW PARAM JAVA_CLASS_PATH;
NAME             TYPE     VALUE
---------------- -------- --------------------------
JAVA_CLASS_PATH  DIRNAME  /data2/tb6/tbdata/java/

SQL> SET LINESIZE 120
SQL> COL owner FOR a30
SQL> COL object_name FOR a40
SQL> SELECT owner, object_name, functionable, wrapped 
FROM dba_procedures
WHERE wrapped = 'YES';
OWNER                      OBJECT_NAME              FUNCTIONABLE WRAPPED
------------------------- ------------------------- ------------ -------
DBTECH                     WRAP_TEST                NO           YES

SQL> SET LINESIZE 120
SQL> COL name FOR a20
SQL> COL path FOR a60
SQL> SELECT * FROM dba_directories;
NAME                 PATH
-------------------- ----------------------
SAM                  /data2/tb6/DIR_SAM

SQL> SET LINESIZE 150
SQL> SET PAGES 500
SQL> COL owner FOR a20
SQL> SELECT owner, object_type, status, count(*)
FROM dba_objects
WHERE owner not in ('SYS', 'SYSCAT', 'SYSGIS', 'OUTLN')
GROUP BY owner, object_Type, status
ORDER BY owner, object_type, status;

OWNER     OBJECT_TYPE     STATUS  COUNT(*)
-------- --------------  ------  --------
DBTECH    DATABASE LINK   VALID          1
DBTECH    FUNCTION        VALID          3
DBTECH    INDEX           VALID          9
DBTECH    JAVA            VALID          1
DBTECH    LIBRARY         VALID          1
DBTECH    PROCEDURE       VALID          3
DBTECH    SEQUENCE        VALID          3
DBTECH    SYNONYM         VALID          6
DBTECH    TABLE           VALID         10
DBTECH    TRIGGER         VALID          2
DBTECH    VIEW            VALID          1
DBTECH2   FUNCTION        VALID          1
DBTECH2   INDEX           VALID          7
DBTECH2   LOB             VALID          2
DBTECH2   PACKAGE         VALID          2
DBTECH2   PACKAGE BODY    VALID          2
DBTECH2   PROCEDURE       VALID          4
DBTECH2   SEQUENCE        VALID          1
DBTECH2   TABLE           VALID          7
DBTECH2   TRIGGER         VALID          1
DBTECH2   VIEW            VALID          1
PUBLIC    SYNONYM         VALID        565
TIBERO    TABLE           VALID          1

SQL> SET LINESIZE 150
SQL> COL owner FOR a30
SQL> SELECT owner
,constraint_type
,count(constraint_name) as con_cnt
FROM dba_constraints
WHERE owner not in ('SYS', 'SYSCAT', 'SYSGIS', 'OUTLN')
GROUP BY owner, constraint_type
ORDER BY owner ,constraint_type;

OWNER     CONSTRAINT_TYPE  CON_CNT
-------- ---------------  -------
DBTECH    C                     21
DBTECH    O                      0
DBTECH    P                      7
DBTECH    R                      3
DBTECH    U                      1
DBTECH2   C                      7
DBTECH2   P                      7
DBTECH2   R                      1

SQL> SELECTcount(*) FROM v$session;

COUNT(*)
---------- -
2

BEGIN
DBMS_JOB.BROKEN(100, true);
END;
/

tbexport username={sys | dba계정} password=<password> port=<port_no> sid=<SID> 
file=<file name> log=<log-file name> full=y script=y

tbexport username=sys password=pwd port=8629 sid=tibero6
file=tibero6_all.dat log=tibero6_exp.log full=y script=y

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 <파라미터명>

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' -- 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
-- spool 파일을 통해 오류 확인

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
-- spool 파일을 통해 오류 확인

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;