Migrating Data from Oracle to openGauss/MogDB Using MTK

Supported Versions

• 10.2+
• 11.2+
• 12c+

How to

1. Download And Install

   tar -zxvf mtk_<version>_linux_<platform>.tar.gz

2. Apply the license

   cd mtk_<version>_linux_<platform>
   ./mtk license gen
   License File Not Found (default license.json)
   The License code is invalid, start applying
   ? Email: xxxx@xxxx.com
   >> please enter a valid email address
   # copy mail file `license.json` to current dir

3. Install Oracle Client

4. Oracle Create User

   sqlplus as sysdba
   create user mtk_mig identified by "password";
   grant connect,resource to mtk_mig;
   grant select any dictionary to mtk_mig;
   grant select any table to mtk_mig;
   grant select_catalog_role to mtk_mig;

5. MogDB Create Database And User

   gsql
   create database db DBCOMPATIBILITY='A' ENCODING='UTF8' LC_COLLATE='C' LC_CTYPE='C';
   \c db
   create user mtk_mig with password "mtkMigAbc123" sysadmin;

   Pay attention to the character set collation. Create a database syntax referencecreate database

6. Init project

   ./mtk init-project -s oracle -t mogdb -n ora2mg
   # will build a migration directory
   ora2mg
   ├── config
   │   └── mtk.json
   ├── data
   ├── report
   └── schema

   Edit the configuration file in advance. For details, see Configuration File

   vi ora2mg/config/mtk.json

   1. Edit source node to define the source database connection information. Configuration reference source

   2. Edit target node to define the target database connection information. Configuration reference target

      Modify the connect and type information, and the parameter is adjusted according to the operation

   3. Edit object node to define the migration object. Configuration reference object

   After editing, run config-check

   ./mtk config-check -c ora2mg/config/mtk.json

   The configuration file will output type information normally

   use config : ora2mg/config/mtk.json
   There is no error in the configuration file

7. Run

   Step	Desc	Command Desc
   ./mtk mig-tab-pre -c ora2mg/config/mtk.json	Migration table	mig-tab-pre
   ./mtk mig-tab-data -c ora2mg/config/mtk.json	Migration table data	mig-tab-data
   ./mtk mig-tab-post -c ora2mg/config/mtk.json	Migration index/cons	mig-tab-post
   ./mtk mig-tab-other -c ora2mg/config/mtk.json	Migration plsql	mig-tab-other

8. view Report

   Reports and run logs are generated in the ora2og/report directory. as follows

   [2022-01-20 11:29:25.800978]  INFO reportDir: ora2mg/report/report_20220120110125 function=GenReport line=412 file=mtk/cmd/mtk/services/cmd.go
   [2022-01-20 11:29:26.426822]  INFO the text report : ora2mg/report/report_20220120110125.txt function=PrintReport line=270 file=mtk/cmd/mtk/services/cmd.go
   [2022-01-20 11:29:26.429545]  INFO the warring report : ora2mg/report/report_20220120110125.warring function=PrintReport line=281 file=mtk/cmd/mtk/services/cmd.go
   [2022-01-20 11:29:26.430118]  INFO the error report : ora2mg/report/report_20220120110125.err file=mtk/cmd/mtk/services/cmd.go function=PrintReport line=292

   file name	description
   ora2mg/report/report_20220120110125	html report
   ora2mg/report/report_20220120110125.txt	text report
   ora2mg/report/report_20220120110125.warring	A text report that contains only warning messages
   ora2mg/report/report_20220120110125.err	A text report that contains only error messages

Performance

MTK uses Golang for development. There are the following options for connecting to the Oracle driver.

The driver performance of CGO type is lower than that of Go native language driver, because of architectural limitations. For example, the godror driver call path is Godror Driver->CGO Libraries->ODPI-C->OCI. The general performance bottleneck is CGO Libraries

efficient-fetching-of-data-from-oracle-database-in-golang Drivers based on CGO have a complex architecture, they wrap around an existing library which may also be another wrapper or technology. If we take a look at the Godror Golang driver, we’ll see that it’s a 4-level architecture driver:

MTK adds a new parameter pureDriver to support the use of go-ora driver. After enabling this parameter, MTK will automatically determine whether to use the go-ora driver when querying database table data. It will not be used in the following cases

• table exists with XML column type
• table exists with Spatial column type
• table exists with UDT column type

When using go-ora, the data of Number type will have abnormal data, and MTK will perform TO_CHAR(COL_NAME) AS COL_NAME query inside

{
  "source": {
    "type": "oracle",
    "connect": {
      "version": "",
      "host": "127.0.0.1",
      "user": "system",
      "port": 1521,
      "password": "oracle",
      "dbName": "mtk",
      "dsn": "",
      "charset": "ZHS16GBK",
      "pureDriver": true
    },
    "parameter": {
      "debugTest": false
    }
  }
}

Oracle Migrate User Privilege

The migration user must be able to access the DBA_*/ALL_* view and query the migration table permissions.

• connect
• select any dictionary
• select any table
• select_catalog_role

MTK query the DBA_* view from 2.3.0 onwards, not the query ALL_* view.

Comparison Between the MTK Type and the Oracle Type

The object type is from OBJECT_TYPE of DBA_OBJECT.

Precautions

Empty string problem

In Oracle, an empty string is NULL. In openGauss, If dbcompatibility is set to A, an empty string is NULL. If dbcompatibility is set to PG, an empty string is NOT NULL.

DBCOMPATIBILITY [ = ] compatibility_type

Specifies the type of the database to be compatible with.

Value range: A, B, C, and PG

• A indicates that MogDB is compatible with Oracle.
• B indicates that MogDB is compatible with MySQL.
• C indicates that MogDB is compatible with Teradata.
• PG indicates that MogDB is compatible with PostgreSQL.

However, C is not supported currently. Therefore, the values A, B, and PG are commonly used.

Note:

• For the compatibility type of A, the database considers an empty string as null and replace the data type DATE with TIMESTAMP(0) WITHOUT TIME ZONE.
• For the compatibility type of B, when a string is converted to an integer type, if an illegal value is entered, it will be converted to 0. However, for other compatibility types, an error will be reported.
• For the compatibility type of PG, CHAR and VARCHAR use character as the string unit. For other compatibility types, byte is used as the string unit. For example, as for UTF-8 character set, CHAR(3) can hold 3 Chinese characters in the compatibility type of PG but only one Chinese character in the other compatibility types.

To be supplemented. This document lists only part problems.

Sequence

MogDB/openGauss 3.0.0

• largeSequence=on max_value > 9223372036854775807 -> Large Sequence
• largeSequence=on max_value < 9223372036854775807 -> Sequence
• largeSequence=off -> Sequence

Before MogDB/openGauss 3.0.0, there will be a warning message if the value is greater than 9223372036854775807.

MTK gets the Oracle sequence value through DBA_SEQUENCES.LAST_NUMBER+CACHE_SIZE. If the requirements are not met, please refer to [Get the last value of the sequence] (#sequence_last_number)

Type

• CREATE OR REPLACE TYPE TY_STR_SPLIT IS TABLE OF VARCHAR2 (4000)

  MogDB/openGauss 3.0.0 Support

  MTK v2.4.2 Support

Table

• IOT_OVERFLOW TABLE not supported.

• NESTED TABLE not supported

• OBJECT TYPE TABLE

  MogDB/openGauss 3.0.0 Support

  MTK v2.4.2 Support

  CREATE OR REPLACE TYPE address_obj as OBJECT(
        street VARCHAR2(20),
        city VARCHAR2(20),
        state CHAR(2),
        zip CHAR(5));
  /
  CREATE TABLE address_table OF ADDRESS_OBJ;

• Partition table

  • Hash Partition Table

  • List Partition Table

  • Range Partition Table

  • Sub Partition Table

  • System Partition table

  • Due to limitations of openGauss syntax, only part of interval partitions are supported.

    • Convert interval(NUMTOYMINTERVAL(1,'year')) --> interval(1 year)

      Oracle	openGauss
      NUMTOYMINTERVAL(1,'YEAR')	1 year
      NUMTOYMINTERVAL ( 1, 'MONTH' )	1 month
      numtodsinterval ( 1, 'day' )	1 day

    • number interval -- invalid input syntax for type interval

• The table of the materialized view does not migrate, the materialized view is migrated

Char Column Type

If character set conversion is present, note the Char type field data.

• Oracle

SQL> create table tab_char(id number, col_char char(2));

Table created.

SQL> insert into tab_char values (1,'1');

1 row created.

SQL> select id,'"'||col_char||'"' ,col_char from tab_char;

    ID '"' C
---------- --- -
    1 "1 " 1

• openGauss/MogDB

Due to the migration from the GBK character set to UTF8, the field length becomes larger

MogDB=#create table tab_char(id bigint, col_char char(3));
CREATE TABLE
MogDB=#insert into tab_char values (1,'1');
INSERT 0 1
MogDB=#select id,concat('"',col_char,'"') ,col_char from tab_char;
 id | concat | col_char
----+--------+----------
  1 | "1  "   | 1              >> There is one more space
(1 row)

Column

Conversion of Column Types Between Oracle and openGauss/MogDB

Default value of the column

• sys_guid

  SQL> SELECT sys_guid() FROM dual;
  
  SYS_GUID()
  --------------------------------
  B2CBE5EE3AB4032BE053030011AC6BD3

  CREATE or replace FUNCTION pg_catalog.sys_guid() RETURNS varchar AS $$
  BEGIN
  RETURN md5(random()::text || clock_timestamp()::text);
  END;
  $$ LANGUAGE plpgsql;

• TO_CHAR(SYSTIMESTAMP, 'TZR') from dual;

Number

• number(38,127) --> numeric(127, 127)

  When p (precision) is less than s (scale), numeric(s, s) applies. In this way, the number of digit 0 behind decimal point can be defined in Oracle.

  create table o_test3(value number(38,127));
  INSERT INTO o_test3 VALUES (00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000012345678901234567890123456890123456789012345678901234567890123456789);
  SELECT * FROM o_test3;

• number(38,-64) --> numeric(102)

  create table o_test4(value number(38,-64));
  INSERT INTO o_test4 VALUES(12345678901234567890123456789012345678901234567890123456789012345678900000000000000000000000000000000);
  INSERT INTO o_test4 VALUES(123456789012345678901234567890123456789012345678901234567890123456789000000000000000000000000000000000);
  INSERT INTO o_test4 VALUES(123456789012345678901234567890123456789012345678901234567890123456789000000000000000000000000000000001);
  SELECT * FROM o_test4;

Timestamp

• Timestamp
  • Oracle Timestamp has a maximum precision of 9, openGauss/mogdb maximum 6
• Timestamp with local time zone Oracle converts insert time to database time zone time. openGauss/mogdb uses timestamp local time zone
• Timestamp local time zone Oracle does not convert time zones. openGauss/mogdb is not supported. openGauss/mogdb Using the Timestamp local time zone loses the original client time zone information

Column Name

openGauss database System Columns.

• "CMAX"
• "CMIN"
• "CTID"
• "TABLEOID"
• "TID"
• "XMAX"
• "XMIN"

These names cannot be used as names of user-defined columns.

Constraint

• If the Oracle primary key constraint is consistent with the table name, an error will occur in openGauss. The program will automatically add _PK behind the name.
• C - Check constraint on a table
• P - Primary key
• U - Unique key
• R - Referential integrity
• V - With check option, on a view -- Not supported
• O - With read only, on a view -- Not supported
• H - Hash expression
• F - Constraint that involves a REF column
• S - Supplemental logging -- Not supported

Constraint using index leading column

• Oracle

create index idx_test_01 on test_01(id,col1,col2);
alter table test_01 add constraint pk_test_01 primary key (id) using index idx_test_01;

• MogDB

-- mtk normal
create unique index idx_test_01 on test_01(id,col1,col2);
alter table test_01 add constraint pk_test_01 primary key using index idx_test_01;
-- The above results are wrong

-- right
create unique index idx_test_01 on test_01(id,col1,col2);
create unique index idx_pk_test_01 on test_01(id);
alter table test_01 add constraint pk_test_01 primary key using index idx_pk_test_01;

-- There will be one more index than the source database

Index

• The bitmap index is not supported, which cannot be resolved currently.

View

• Problem of alias of some columns in the SELECT statement

select name name from dual      -- rewrite name as name

• Oracle TRANSLATE(xx USING NCHAR_CS) function (This problem cannot be resolved.)

  select TRANSLATE(col1 USING NCHAR_CS) from (
      select 1 as col1 from dual;
  )

同义词

Synonyms under DBLink are not migrated

SELECT OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME FROM DBA_SYNONYMS WHERE DB_LINK IS NULL;

Spatial

Migration Oracle Spatial object from Oracle database. There's some configuration directives that could be used to control the export.

• enableConvertSrid
• defaultSrid

Migrating Oracle Spatial object will use the pg_extension view to determine the database has postgis installed.

• If postgis are not installed, they will be migrated to text.
• If postgis is installed, it will be migrated to geometry.

The support is as follows:

• SDO_GTYPE
  • [2,3,4]0[0-7] Support
  • [2,3,4]0[8-9] No Support
• SDO_ETYPE
  • 1, 2, 1003, 2003, 4,1005, 2005 Support
  • [2006,1007]: Not Supprt

Other Problem

ORA-29275: partial multibyte character

• AL32UTF8 TO UTF8 Configure the parameter charAppendEmptyString under source-parameter to true

• ZHS16GBK TO UTF8

  • Method one Configure the parameter charAppendEmptyString under source-parameter to true

  • Method two Configure the parameter clientCharset under source and target under connect to GBK

    Configure the parameter igErrorData under target-parameter to true

ORA-01805 msg:possible error in date/time operation

• Query timezone_file

  1. Query oracle database server timezone_file version

     sqlplus "/ as sysdba"
     SQL> select * from v$timezone_file;
     
     FILENAME    VERSION     CON_ID
     -------------------- ---------- ----------
     timezlrg_34.dat        34    0

  2. Query oracle client timezone_file version

     genezi -v
     Client Shared Library 64-bit - 19.3.0.0.0
     
     System name:  Darwin
     Release:  19.6.0
     Version:  Darwin Kernel Version 19.6.0: Thu Jun 18 20:49:00 PDT 2020; root:xnu-6153.141.1~1/RELEASE_X86_64
     Machine:  x86_64
     
     TIMEZONE INFORMATION
     --------------------
     Operating in Instant Client mode.
     
     Small timezone file = /opt/client_19/oracore/zoneinfo/timezone_32.dat
     Large timezone file = /opt/client_19/oracore/zoneinfo/timezlrg_32.dat

  If the client version and server version are inconsistent, upgrade timezone_file.

  Server version: timezlrg_34.dat

  Client version: timezlrg_32.dat

• Solution:

  1. Install the Oracle client.

  2. Upgrade timezone_file. Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)

     for oracle Instant client
     
     # Set environment variable ORA_TZFILE.
     export ORA_TZFILE=timezlrg_34.dat
     # Copy from other machine that has the patch installed.
     cp timezlrg_34.dat $ORACLE_HOME/oracore/zoneinfo/
     genezi -v
     Client Shared Library 64-bit - 19.3.0.0.0
     
     System name: Darwin
     Release:  19.6.0
     Version:  Darwin Kernel Version 19.6.0: Thu Jun 18 20:49:00 PDT 2020; root:xnu-6153.141.1~1/RELEASE_X86_64
     Machine:  x86_64
     
     TIMEZONE INFORMATION
     --------------------
     Operating in Instant Client mode.
     
     Time zone file timezlrg_34.dat set in ORA_TZFILE environment variable will be used.
     Small timezone file = /opt/client_19/oracore/zoneinfo/timezlrg_34.dat
     Large timezone file = /opt/client_19/oracore/zoneinfo/timezlrg_34.dat

Segmentation fault on some requests handling ROWID

https://github.com/oracle/python-cx_Oracle/issues/315

Try setting the environment variable ORA_OCI_NO_OPTIMIZED_FETCH to the value 1 before you run the query to avoid the issue

乱码问题

1. Chinese garbled characters in the stored procedure.

   The MTK report shows garbled characters. Basically, non-UTF8 encoded data appears in the AL32UTF8 database. Such as GBK encoding

   -- Use the Oracle Dump function to determine
   -- Query the corresponding SQL text from DBA_SOURCE
   select line,text,dump(text,1016),dump(CONVERT(text, 'AL32UTF8','ZHS16GBK'),1016) from dba_source where name='xxx';
   
   /*
       -- example
       SELECT
           dump(CONVERT('Request system response', 'ZHS16GBK'),1016), -- Convert normal UTF8 encoded data to GBK
           dump(CONVERT(CONVERT('Request system response', 'ZHS16GBK'),'AL32UTF8','ZHS16GBK'),1016) -- normal UTF8 encoded data is converted to GBK and then converted to UTF8
       FROM dual;
   */

2. invalid byte sequence for encoding

   This problem is generally caused by garbled characters in the source database. You can configure the parameter igErrorData to true, skip the error data first, and then process the error data.

   Find the error data file indicated in the report. Refer to the following operations

   This CSV will be imported normally through DBeaver/Mogeaver because of the Decoder

   gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:29:12 commit 0 last mr release)
   Non-SSL connection (SSL connection is recommended when requiring high-security)
   Type "help" for help.
   
   -- Use CTAS to create a temporary table. Note that the following indications are modified to real indications
   omm=# create table copy_table as select * from XXX.TABLE where 1=0;
   INSERT 0 0
   omm=# \COPY TABLE FROM 'xxx_table_0.csv' WITH csv header DELIMITER ',' encoding 'UTF8';
   ERROR: invalid byte sequence for encoding "UTF8": 0xe7 0x31 0x31
   CONTEXT: COPY TABLE, line 18750

   Find the problematic line according to the error message, go to the source database to query the data, and try to repair if you find garbled characters.

   Oracle

   select dump(col_name,1016) from table_name t WHERE t.col_name='xxx';

   openGauss/MogDB/PostgreSQL

   select upper(encode(col_name::bytea, 'hex')) from table_name t WHERE t.col_name='xxx'

Migrating US7ASCII

The Oracle Client NLS_LANG is set to the same as the database character set and will not perform transcoding operations. Therefore, the encoding of Chinese characters depends on the encoding generated by the client application

Check the encoding of Chinese in the database. Query tables with Chinese data. For example

SELECT col_name,dump(col_name,1016) FROM tab_ascii
COL2    DUMP_NAME
-----   ------------------------------------------------------------------------------------------
中国人   Typ=1 Len=9 CharacterSet=US7ASCII: e4,b8,ad,e5,9b,bd,e4,ba,ba

Open the website View Chinese Character Encoding and enter Chinese characters to view the encoding. Compare it with the results of the database DUMP query. For example, the above is UTF8 encoding

Configure clientCharset in the MTK configuration file. The reference is as follows

{
  "source": {
    "type": "oracle",
    "connect": {
      "clientCharset": "ASCII",
    }
  },
  "target": {
    "type": "opengauss",
    "connect": {
      "clientCharset": "UTF8"
    }
  }
}

Part query SQL statement

Sequence

11G

SELECT SEQUENCE_OWNER,SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY,
 ORDER_FLAG,
 CYCLE_FLAG,
 CACHE_SIZE,
 LAST_NUMBER,
 LAST_NUMBER + CACHE_SIZE AS STARTVAL
FROM
 DBA_SEQUENCES S
WHERE 1=1

12C+

SELECT SEQUENCE_OWNER,SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY,
 ORDER_FLAG,
 CYCLE_FLAG,
 CACHE_SIZE,
 LAST_NUMBER,
 LAST_NUMBER + CACHE_SIZE AS STARTVAL
FROM
 DBA_SEQUENCES S
WHERE
 NOT EXISTS (
 SELECT
  1
 FROM
  DBA_TAB_IDENTITY_COLS C
 WHERE
  S.SEQUENCE_OWNER = C.OWNER
 AND S.SEQUENCE_NAME = C.SEQUENCE_NAME ) AND SEQUENCE_OWNER='ORA_MTK'

Sequence Last_Number

declare
  v_sequence_owner varchar2(100) := 'ORA_MTK'; -- sequence owner
  v_start_with number;
  v_ddl varchar2(4000);
  v_setval varchar2(4000);
begin
  for x in (select sequence_owner,
                   sequence_name,
                   min_value,
                   max_value,
                   increment_by,
                   case when cache_size= 0 then 1 else cache_size end cache_size,
                   cycle_flag,
                   last_number --Oracle 最后值是最后一次cache值
                   from dba_sequences where sequence_owner = upper(v_sequence_owner)) loop
  -- Querying through xx.nextval can guarantee that it is the last value under a single node, but cannot guarantee that it is the last value under the rac node. Because it is memory data
  execute immediate 'select ' || x.sequence_owner || '.' || x.sequence_name ||'.nextval from dual' into v_start_with;
  --  v_ddl:= case when x.max_value>9223372036854775807 then 'create large sequence ' else 'create sequence ' end
  --         || lower(x.sequence_owner) || '.' || lower(x.sequence_name) || ' minvalue ' || x.min_value || ' maxvalue '
  --         || x.max_value || ' increment by ' || x.increment_by || ' start with ' || v_start_with || ' cache '
  --         || x.cache_size || case when x.cycle_flag='N' then ' nocycle' else ' cycle' end ||';' ;
  -- dbms_output.put_line(v_ddl);
 v_setval :='SELECT setval('||''''||x.sequence_owner || '.' || x.sequence_name||''''||', '||v_start_with||');';
 dbms_output.put_line(v_setval);
 end loop;
end;

Type

SELECT
 o.object_type,
 s.owner,
 s.name,
 s.line,
 s.text,
 t.typecode,
 o.timestamp,
 h.lvl
FROM
 DBA_SOURCE s,
 DBA_TYPES t,
 DBA_OBJECTS o,
 (
 SELECT
  NAME,
  MAX(LEVEL) lvl
 FROM
  (
  SELECT
   *
  FROM
   DBA_DEPENDENCIES
  WHERE
   TYPE = 'TYPE'
   AND owner = 'ORA_MTK')
 CONNECT BY
  nocycle referenced_name = PRIOR name
 GROUP BY
  name) h
WHERE
 s.type = 'TYPE'
 AND o.object_type = 'TYPE'
 AND s.owner = 'ORA_MTK'
 AND h.name = s.name
 AND s.owner = t.owner
 AND s.name = t.type_name
 AND s.owner = o.owner
 AND s.name = o.object_name
 AND o.status = 'VALID'
UNION ALL
SELECT
 o.object_type,
 s.owner,
 s.name,
 s.line,
 s.text,
 t.typecode,
 o.timestamp,
 h.lvl
FROM
 DBA_SOURCE s,
 DBA_TYPES t,
 DBA_OBJECTS o,
 (
 SELECT
  NAME,
  MAX(LEVEL) lvl
 FROM
  (
  SELECT
   *
  FROM
   DBA_DEPENDENCIES
  WHERE
   TYPE = 'TYPE'
   AND owner = 'ORA_MTK')
 CONNECT BY
  nocycle referenced_name = PRIOR name
 GROUP BY
  name) h
WHERE
 s.type = 'TYPE BODY'
 AND o.object_type = 'TYPE BODY'
 AND s.owner = 'ORA_MTK'
 AND h.name = s.name
 AND s.owner = t.owner
 AND s.name = t.type_name
 AND s.owner = o.owner
 AND s.name = o.object_name
 AND o.status = 'VALID'
ORDER BY
 lvl,
 OWNER,
 NAME,
 object_type,
 LINE

Queue

SELECT
 Q.OWNER,
 NAME,
 QUEUE_TYPE,
 Q.QUEUE_TABLE,
 QT.OBJECT_TYPE
FROM
 DBA_QUEUES Q,
 DBA_QUEUE_TABLES QT
WHERE
 Q.QUEUE_TABLE = QT.QUEUE_TABLE
 AND Q.OWNER = QT.OWNER AND

Table

SELECT A.OWNER,A.TABLE_NAME,A.TABLESPACE_NAME,A.CLUSTER_NAME,A.IOT_NAME,A.LOGGING,A.PARTITIONED,A.IOT_TYPE,
  A.TEMPORARY,
  A.SECONDARY,
  A.NESTED,
  A.COMPRESSION,
  A.COMPRESS_FOR,
  B.COMMENTS,
  A.DURATION ,
  A.NUM_ROWS ,
  A.BLOCKS,
  A.AVG_ROW_LEN,
  A.TABLE_TYPE,
  A.TABLE_TYPE_OWNER
 FROM
 DBA_ALL_TABLES A,
 DBA_OBJECTS O,
 DBA_TAB_COMMENTS B
 WHERE NOT EXISTS (SELECT 1 FROM DBA_SNAPSHOTS S WHERE S.OWNER = A.OWNER AND S.TABLE_NAME=A.TABLE_NAME)
 AND A.OWNER=B.OWNER(+)
 AND A.TABLE_NAME=B.TABLE_NAME(+)
 AND A.OWNER=O.OWNER
 AND A.TABLE_NAME=O.OBJECT_NAME
 AND O.OBJECT_TYPE='TABLE'

Column

11G

SELECT
 /*+ RULE */
 C.OWNER,
 C.TABLE_NAME,
 C.COLUMN_NAME,
 COLUMN_ID,
 DATA_DEFAULT,
 DATA_TYPE,
 DATA_TYPE_OWNER,
 DATA_LENGTH,
 CHAR_LENGTH,
 DATA_PRECISION,
 DATA_SCALE,
 CHAR_USED,
 HIDDEN_COLUMN,
 VIRTUAL_COLUMN,
 NULLABLE,
 NULL COMMENTS,
 NULL AS IDENTITY_COLUMN
FROM
 DBA_TAB_COLS C
WHERE 1=1 AND HIDDEN_COLUMN='NO'  AND UPPER(C.OWNER) = 'ORA_MTK' AND (UPPER(C.TABLE_NAME) = 'T3' OR UPPER(C.TABLE_NAME) = 'T2') ORDER BY c.OWNER,c.TABLE_NAME,c.COLUMN_ID

12C

SELECT
 /*+ RULE */
 C.OWNER,
 C.TABLE_NAME,
 C.COLUMN_NAME,
 COLUMN_ID,
 DATA_DEFAULT,
 DATA_TYPE,
 DATA_TYPE_OWNER,
 DATA_LENGTH,
 CHAR_LENGTH,
 DATA_PRECISION,
 DATA_SCALE,
 CHAR_USED,
 HIDDEN_COLUMN,
 VIRTUAL_COLUMN,
 NULLABLE,
 NULL COMMENTS,
 C.IDENTITY_COLUMN -- 12.1
FROM
 DBA_TAB_COLS C
WHERE 1=1 AND HIDDEN_COLUMN='NO'  AND UPPER(C.OWNER) = 'ORA_MTK' AND (UPPER(C.TABLE_NAME) = 'T3' OR UPPER(C.TABLE_NAME) = 'T2') ORDER BY c.OWNER,c.TABLE_NAME,c.COLUMN_ID

Column Comment

SELECT c.owner, c.table_name, c.column_name, comments FROM DBA_COL_COMMENTS c WHERE c.comments IS NOT null

Column Identity

12C+

SELECT
 C.OWNER,
 C.TABLE_NAME,
 C.COLUMN_NAME,
 C.SEQUENCE_NAME,
 C.GENERATION_TYPE,
 S.MIN_VALUE,
 S.MAX_VALUE,
 S.INCREMENT_BY,
 S.ORDER_FLAG,
 S.CYCLE_FLAG,
 S.CACHE_SIZE,
 S.LAST_NUMBER,
 S.LAST_NUMBER + S.CACHE_SIZE AS STARTVAL
FROM
 DBA_TAB_IDENTITY_COLS C,
 DBA_SEQUENCES S
WHERE
 S.SEQUENCE_OWNER = C.OWNER
 AND S.SEQUENCE_NAME = C.SEQUENCE_NAME

CONSTRAINT

Constraint Basic Info

SELECT  C.OWNER,
 C.CONSTRAINT_NAME,
 C.CONSTRAINT_TYPE,
 C.TABLE_NAME,
 C.SEARCH_CONDITION,
 C.R_OWNER,
 C.R_CONSTRAINT_NAME,
 C.DELETE_RULE,
 C.STATUS,
 C.DEFERRABLE,
 C.DEFERRED,
 C.VALIDATED,
 C.INDEX_OWNER,
 C.INDEX_NAME
FROM
 DBA_CONSTRAINTS C
WHERE
 1 = 1 AND UPPER(C.OWNER) = 'ORA_MTK' AND (UPPER(C.TABLE_NAME) = 'T3' OR UPPER(C.TABLE_NAME) = 'T2')
UNION ALL
 SELECT
 R.OWNER,
 R.CONSTRAINT_NAME,
 R.CONSTRAINT_TYPE,
 R.TABLE_NAME,
 NULL AS SEARCH_CONDITION,
 R.R_OWNER,
 R.R_CONSTRAINT_NAME,
 R.DELETE_RULE,
 R.STATUS,
 R.DEFERRABLE,
 R.DEFERRED,
 R.VALIDATED,
 R.INDEX_OWNER,
 R.INDEX_NAME
FROM
 DBA_CONSTRAINTS C
 LEFT JOIN DBA_CONSTRAINTS R ON C.R_OWNER = R.OWNER AND C.R_CONSTRAINT_NAME = R.CONSTRAINT_NAME
WHERE
 1 = 1
 AND C.CONSTRAINT_TYPE='R'
 AND UPPER(C.OWNER) = 'ORA_MTK' AND (UPPER(C.TABLE_NAME) = 'T3' OR UPPER(C.TABLE_NAME) = 'T2')

Constraint Column

SELECT
 COLS.OWNER,
 COLS.CONSTRAINT_NAME,
 COLS.TABLE_NAME,
 COLS.COLUMN_NAME,
 COLS.POSITION ,
 TC.NULLABLE
FROM
 DBA_CONS_COLUMNS COLS ,
 DBA_TAB_COLS TC
WHERE
 COLS.TABLE_NAME = TC.TABLE_NAME
 AND COLS.OWNER = TC.OWNER
 AND COLS.COLUMN_NAME = TC.COLUMN_NAME AND UPPER(COLS.OWNER) = 'ORA_MTK' AND (UPPER(COLS.TABLE_NAME) = 'T3' OR UPPER(COLS.TABLE_NAME) = 'T2')
UNION
SELECT DISTINCT COLS.OWNER,
 COLS.CONSTRAINT_NAME,
 COLS.TABLE_NAME,
 COLS.COLUMN_NAME,
 COLS.POSITION ,
 TC.NULLABLE
FROM
 DBA_CONSTRAINTS CONS,
 DBA_CONSTRAINTS CONS_R,
 DBA_CONS_COLUMNS COLS,
 DBA_TAB_COLS TC
WHERE
 1 = 1
 AND CONS.R_OWNER = CONS_R.OWNER
 AND CONS.R_CONSTRAINT_NAME = CONS_R.CONSTRAINT_NAME
 AND CONS_R.TABLE_NAME = COLS.TABLE_NAME
 AND CONS_R.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME
 AND CONS_R.OWNER = COLS.OWNER
 AND CONS_R.TABLE_NAME = TC.TABLE_NAME
 AND CONS_R.OWNER = TC.OWNER
 AND COLS.OWNER = TC.OWNER
 AND COLS.TABLE_NAME = TC.TABLE_NAME
 AND COLS.COLUMN_NAME = TC.COLUMN_NAME
 AND CONS.CONSTRAINT_TYPE ='R'
 AND UPPER(COLS.OWNER) = 'ORA_MTK' AND (UPPER(COLS.TABLE_NAME) = 'T3' OR UPPER(COLS.TABLE_NAME) = 'T2')
 ORDER BY OWNER,TABLE_NAME,CONSTRAINT_NAME,POSITION

Constraint Index

SELECT
 S.INDEX_NAME,
 S.TABLE_NAME,
 S.UNIQUENESS,
 S.INDEX_TYPE,
 S.TABLE_OWNER,
 S.PARTITIONED,
 S.TABLESPACE_NAME,
 Q.QUEUE_TABLE,
 S.JOIN_INDEX
FROM
 DBA_INDEXES S
LEFT OUTER JOIN DBA_QUEUE_TABLES Q ON
 Q.QUEUE_TABLE = S.TABLE_NAME
 AND Q.OWNER = S.TABLE_OWNER
WHERE EXISTS (
 SELECT
  AC.INDEX_NAME
 FROM
  DBA_CONSTRAINTS AC
 WHERE
   AC.CONSTRAINT_TYPE IN ('P', 'U')
  AND AC.TABLE_NAME = S.TABLE_NAME
  AND AC.OWNER = S.TABLE_OWNER
  AND AC.INDEX_NAME = S.INDEX_NAME
 )
 AND S.TEMPORARY = 'N'
 AND (FUNCIDX_STATUS IS NULL
 OR FUNCIDX_STATUS != 'DISABLED')
 AND UPPER(S.TABLE_OWNER) = 'ORA_MTK' AND UPPER(S.TABLE_NAME) = 'PROMOTIONS_VAR3'

Constraint Index Column

SELECT IC.INDEX_OWNER,IC.INDEX_NAME,IC.COLUMN_NAME,IE.COLUMN_EXPRESSION,IC.COLUMN_POSITION,IC.TABLE_NAME,IC.TABLE_OWNER,IC.DESCEND FROM DBA_IND_COLUMNS IC, DBA_IND_EXPRESSIONS IE WHERE IC.INDEX_OWNER = IE.INDEX_OWNER(+) AND IC.INDEX_NAME = IE.INDEX_NAME(+) AND IC.COLUMN_POSITION=IE.COLUMN_POSITION(+)
AND UPPER(IC.TABLE_OWNER) = 'ORA_MTK' AND UPPER(IC.TABLE_NAME) = 'PROMOTIONS_VAR3'
ORDER BY IC.TABLE_OWNER,IC.TABLE_NAME,IC.INDEX_NAME,IC.COLUMN_POSITION

Index Column

Index Basic Info

SELECT
 S.INDEX_NAME,
 S.TABLE_NAME,
 S.UNIQUENESS,
 S.INDEX_TYPE,
 S.TABLE_OWNER,
 S.PARTITIONED,
 S.TABLESPACE_NAME,
 Q.QUEUE_TABLE,
 S.JOIN_INDEX
FROM
 DBA_INDEXES S
LEFT OUTER JOIN DBA_QUEUE_TABLES Q ON
 Q.QUEUE_TABLE = S.TABLE_NAME
 AND Q.OWNER = S.TABLE_OWNER
WHERE NOT EXISTS (
 SELECT
  AC.INDEX_NAME
 FROM
  DBA_CONSTRAINTS AC
 WHERE
   AC.CONSTRAINT_TYPE IN ('P', 'U')
  AND AC.TABLE_NAME = S.TABLE_NAME
  AND AC.OWNER = S.TABLE_OWNER
  AND AC.INDEX_NAME = S.INDEX_NAME
 )
 AND S.TEMPORARY = 'N'
 AND (FUNCIDX_STATUS IS NULL
 OR FUNCIDX_STATUS != 'DISABLED')
 AND UPPER(S.TABLE_OWNER) = 'ORA_MTK' AND UPPER(S.TABLE_NAME) = 'PROMOTIONS_VAR3'

Index Column

SELECT IC.INDEX_OWNER,IC.INDEX_NAME,IC.COLUMN_NAME,IE.COLUMN_EXPRESSION,IC.COLUMN_POSITION,IC.TABLE_NAME,IC.TABLE_OWNER,IC.DESCEND FROM DBA_IND_COLUMNS IC, DBA_IND_EXPRESSIONS IE WHERE IC.INDEX_OWNER = IE.INDEX_OWNER(+) AND IC.INDEX_NAME = IE.INDEX_NAME(+) AND IC.COLUMN_POSITION=IE.COLUMN_POSITION(+)
AND UPPER(IC.TABLE_OWNER) = 'ORA_MTK' AND UPPER(IC.TABLE_NAME) = 'PROMOTIONS_VAR3'
ORDER BY IC.TABLE_OWNER,IC.TABLE_NAME,IC.INDEX_NAME,IC.COLUMN_POSITION

DBLink

SELECT OWNER, DB_LINK, USERNAME,HOST FROM DBA_DB_LINKS WHERE 1=1

View

SELECT V.OWNER,V.VIEW_NAME,V.TEXT,V.TEXT_VC ,O.STATUS
FROM DBA_VIEWS V,DBA_OBJECTS O
WHERE V.OWNER = O.OWNER AND V.VIEW_NAME = O.OBJECT_NAME

Materialized View

SELECT
 MV.OWNER,
 MV.MVIEW_NAME,
 MV.BUILD_MODE,
 MV.REFRESH_METHOD,
 MV.REFRESH_MODE,
 MV.REWRITE_ENABLED,
 MV.QUERY,
 O.STATUS,
 R.INTERVAL
FROM
 DBA_MVIEWS MV,
 DBA_OBJECTS O,
 DBA_REFRESH R
WHERE
 O.OBJECT_TYPE = 'MATERIALIZED VIEW'
 AND MV.OWNER = O.OWNER
 AND MV.MVIEW_NAME = O.OBJECT_NAME
 AND O.STATUS = 'VALID'
 AND MV.OWNER = R.ROWNER(+)
 AND MV.MVIEW_NAME = R.RNAME(+) AND UPPER(MV.OWNER) = 'ORA_MTK' AND (UPPER(MV.MVIEW_NAME) = 'T3' OR UPPER(MV.MVIEW_NAME) = 'T2')

Function

SELECT
 S.NAME,
 S.LINE,
 S.TEXT
FROM
 DBA_SOURCE S,
 DBA_OBJECTS O
WHERE
 1=1
 AND S.TYPE = 'FUNCTION'
 AND S.OWNER = O.OWNER
 AND S.TYPE = O.OBJECT_TYPE
 AND S.NAME = O.OBJECT_NAME

Procedure

SELECT
 S.NAME,
 S.LINE,
 S.TEXT
FROM
 DBA_SOURCE S,
 DBA_OBJECTS O
WHERE
 1=1
 AND S.TYPE = 'PROCEDURE'
 AND S.OWNER = O.OWNER
 AND S.TYPE = O.OBJECT_TYPE
 AND S.NAME = O.OBJECT_NAME

Package

SELECT
 S.NAME,
 S.TYPE,
 S.LINE,
 S.TEXT
FROM
 DBA_SOURCE S,
 DBA_OBJECTS O
WHERE
 1=1
 AND (S.TYPE = 'PACKAGE' OR S.TYPE = 'PACKAGE BODY')
 AND S.OWNER = O.OWNER
 AND S.TYPE = O.OBJECT_TYPE
 AND S.NAME = O.OBJECT_NAME

Trigger

SELECT
 T.TRIGGER_NAME,
 T.OWNER,
 T.TABLE_OWNER,
 T.TRIGGER_TYPE,
 T.TRIGGERING_EVENT,
 T.TABLE_NAME,
 T.TRIGGER_BODY,
 T.WHEN_CLAUSE,
 T.DESCRIPTION,
 T.STATUS,
 T.BASE_OBJECT_TYPE,
 T.ACTION_TYPE
FROM
 DBA_TRIGGERS T,
 DBA_OBJECTS O
WHERE
 O.OBJECT_NAME = T.TRIGGER_NAME
 AND O.OWNER = T.TABLE_OWNER
 -- AND o.status = 'VALID'
 AND O.OBJECT_TYPE = 'TRIGGER'

Synonym

SELECT OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME FROM DBA_SYNONYMS WHERE DB_LINK IS NULL