preparation work for PoC procedure from oracle to MariaDB.

marrowyung
marrowyung used Ask the Experts™
on
Hi,

Before we migrate from oracle to MariaDB DB we will do PoC and for the PoC we need to prepare some information:

1) How many Tables, Views, Procedures, Functions, Packages, Triggers, Sequences, synonyms  are there need to migrate

2) how often items in 1) has been executed

3) do we need to modify SQL statements within the application?

For 1) and 2) any script helps to find out? the script should filter out the items NOT belongs to oracle system.

for 3) I have to check embedded SQL code within the application, right ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
do we need to modify SQL statements within the application?
Probably. If you're not using ANSI SQL mostly, then this "risk" is high.

For 1) and 2) any script helps to find out? the script should filter out the items NOT belongs to oracle system.
If the database related objects were put into separate tablespaces and users/schemas, it will narrow the places to look for proprietary code...
Some good starting points could be following views: dba_source, dba_dependencies, dba_tables, dba_tab_columns etc...

for 3) I have to check embedded SQL code within the application, right ?
Honestly, in the end, you'll have to review (virtually) your entire application ;-)

Some other nice links:
https://mariadb.com/kb/en/library/moving-to-mariadb-moving-from-oracle-to-mariadb/
https://mariadb.com/de/node/827
https://www.convert-in.com/docs/ora2sql/intro.htm
Oracle doesn't keep run count statistics on most objects so you'll need to rely on your institutional knowledge of the database.

Determine which schemas will need to be converted.  You won't need to convert all of them, certain not the built-in schemas, SYS%, SCOTT, DBMS%, etc.

Then run this SQL or its equivalent.

  select object_type, count(*) from all_objects where owner='{schema_name)' group by object_type;

or

  select owner, object_type, count(*) from all_objects where group by owner, object_type;

Either will show the number of tables, views, packages, procedures, triggers, etc. for each schema/owner in the database and give you an idea of how many things will need to be converted.

Kent
David FavorFractional CTO
Distinguished Expert 2018
Commented:
As Alexander said, "Honestly, in the end, you'll have to review (virtually) your entire application ;-)"

Keep in mind ORACLE SQL syntax just released (came out of beta) with latest MariaDB-10.3 + only covers a subset of ORACLE syntax.

My suggestion is this, develop a mechanical test suite to exercise database transactions.

Hopefully you're already doing this. If not, you can always use something simple like the PERL test harness system.

What's best to have is a set of mechanical tests which cover your entire database operation. All activities. Where you can verify your entire database i/o logic is working correctly.

Then just migrate your database + run your test suite against MariaDB. This will surface out any problems instantly.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

marrowyungSenior Technical architecture (Data)

Author

Commented:
Alexander,

"Honestly, in the end, you'll have to review (virtually) your entire application ;-)"

sure, that's why we need to know how much code we need to change.

any script to help me to find number of SP/view /function ?

Kent,

"Oracle doesn't keep run count statistics on most objects s"

even in 12c ? it should be have some,right?

"Determine which schemas will need to be converted.  You won't need to convert all of them, certain not the built-in schemas, SYS%, SCOTT, DBMS%, etc. "

tks. actually in oracle 10gr2 and 12c, what is the oracle default system schema not related to user?

I will try your script tomorrow.

David Favor,

"Keep in mind ORACLE SQL syntax just released (came out of beta) with latest MariaDB-10.3 + only covers a subset of ORACLE syntax."

yeah, full understood this !
 
when we do the real deployment, year 2020, this kind of thing will be greatly improve!

I really like MariaDB, easy to install and powerful enough ! but for large scale operation, MS SQL is the choice !

"My suggestion is this, develop a mechanical test suite to exercise database transactions."

you mean a lot of AUTOMATED test case? here we should simulate the whole thing and test by user one by one,

guys, if you have any way to find out how often SP/view/function, etc run, please share here.

for Oracle OLAP to MariaDB AX. what should we do and what we should check/care ?

for all above, any tools help to check it out ?
There are several default schemas that you can safely ignore.  Your system probably won't have all of them, but you won't need to convert any of these, either.

ANONYMOUS
CTXSYS
CBSNMP
EXFSYS
LBACSYS
MDSYS
MGMT_VIEW
LOAPSYS
ORDDATA
OWBSYS
ORDPLUGINS
ORDSYS
OUTLN
SI_INFORMTN_SCHEMA
SYS
SYSMAN
SYSTEM
WK_TEST
WKSYS
WKPROXY
WMSYS
XDB
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks. so what is the way to change this script from you:


select owner, object_type, count(*) from all_objects where group by owner, object_type;

to show up all schema other than these you list out before, anyway, tks.
Mark GeerlingsDatabase Administrator
Commented:
One way is to use "not in" and just list all of these schemas like this:

select owner, object_type, count(*) from all_objects
where owner not in ('ANONYMOUS','CTXSYS', ...)
group by owner, object_type;

Another way is to join to dba_users and exclude the default schemas (which likely have lower user_id values than the custom schemas you need to check) like this:

select o.owner, o.object_type, count(o.object_name)
from all_objects o, dba_users u
where o.owner not in ('ANONYMOUS','CTXSYS', ...)
and o.owner = u.username
and u.user_id between 100 and 10000
group by o.owner, o.object_type;

You may need to adjust the values 100 and 10000 to match your system.
Geert GOracle dba
Top Expert 2009

Commented:
12c ... oracle users:

select * from dba_users where oracle_maintained = 'YES'

Open in new window


ah, yes, you don't want those ... unless some idiot created objects/tables/views in system/sys
how would you find those ?
ah, yes, you don't want those ... unless some idiot created objects/tables/views in system/sys
how would you find those ?
In these cases, there will always be some collateral damage ;-)
marrowyungSenior Technical architecture (Data)

Author

Commented:
Geert G,

your query return nothing.

"ah, yes, you don't want those ... unless some idiot created objects/tables/views in system/sys "

ahaha. then .... ?

Mark Geerlings,

I am running  this:
select owner, object_type, count(*) from all_objects 
where owner not in ('ANONYMOUS','CTXSYS','CBSNMP','EXFSYS', 'LBACSYS','MDSYS',
'MGMT_VIEW', 'LOAPSYS', 'ORDDATA', 'OWBSYS', 'ORDPLUGINS,'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'WK_TEST', 'WKSYS', 
'WKPROXY', 'WMSYS', 'XDB')
group by owner, object_type;

Open in new window


but I don't know why there are error message:

ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:
Error at Line: 61 Column: 59

Open in new window

Missing quote after ORDPLUGINS
but I don't know why there are error message:

You missed a '

select owner,
       object_type,
       count(*)
  from all_objects
 where owner not in ('ANONYMOUS',
                     'CTXSYS',
                     'CBSNMP',
                     'EXFSYS',
                     'LBACSYS',
                     'MDSYS',
                     'MGMT_VIEW',
                     'LOAPSYS',
                     'ORDDATA',
                     'OWBSYS',
                     'ORDPLUGINS',
                     'ORDSYS',
                     'OUTLN',
                     'SI_INFORMTN_SCHEMA',
                     'SYS',
                     'SYSMAN',
                     'SYSTEM',
                     'WK_TEST',
                     'WKSYS',
                     'WKPROXY',
                     'WMSYS',
                     'XDB')
 group by owner,
          object_type;

Open in new window

"ah, yes, you don't want those ... unless some idiot created objects/tables/views in system/sys "

ahaha. then .... ?

Then, you're all screwed up ;-)
marrowyungSenior Technical architecture (Data)

Author

Commented:
now the result is :

OWNER                                                                                                                            OBJECT_TYPE               COUNT(*)
-------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------
GSMADMIN_INTERNAL                                                                                                                TYPE                            45
GSMADMIN_INTERNAL                                                                                                                TYPE BODY                        3
AUDSYS                                                                                                                           TABLE                            1
AUDSYS                                                                                                                           INDEX PARTITION                  6
DBSNMP                                                                                                                           SEQUENCE                         2
DBSNMP                                                                                                                           VIEW                             7
DBSNMP                                                                                                                           PACKAGE BODY                     3
GSMADMIN_INTERNAL                                                                                                                TRIGGER                          5
GSMADMIN_INTERNAL                                                                                                                VIEW                             8
GSMADMIN_INTERNAL                                                                                                                LIBRARY                          4
DVF                                                                                                                              FUNCTION                        20

OWNER                                                                                                                            OBJECT_TYPE               COUNT(*)
-------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------
OJVMSYS                                                                                                                          JAVA DATA                        8
ORS_OPSS                                                                                                                         SEQUENCE                         4
OR_IAU                                                                                                                           SEQUENCE                         1
DEV_WLS                                                                                                                          SEQUENCE                         5
OR_MDS                                                                                                                           SEQUENCE                         1
DEV_IAU_VIEWER                                                                                                                   SYNONYM                         33
OR_IAU                                                                                                                           TYPE                             3
DEV_MDS                                                                                                                          TYPE                             2
DEV_IAU                                                                                                                          TYPE                             3
CISUAT                                                                                                                           TABLE                         2043
DEV_BIPLATFORM                                                                                                                   TABLE                          149

OWNER                                                                                                                            OBJECT_TYPE               COUNT(*)
-------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------
OR_IAU                                                                                                                           TABLE                           31
ORS_WLS                                                                                                                          TABLE                           40
ORS_STB                                                                                                                          TABLE                            2
ORS_UMS                                                                                                                          PACKAGE                          1
ORS_IAU_VIEWER                                                                                                                   VIEW                             3
ORS_IAU                                                                                                                          PACKAGE BODY                     2
ORS_MDS                                                                                                                          INDEX                           41
OR_WLS_RUNTIME                                                                                                                   INDEX                            1
DBSNMP                                                                                                                           TYPE                             8
APPQOSSYS                                                                                                                        SYNONYM                          1
REMOTE_SCHEDULER_AGENT                                                                                                           FUNCTION                         1

OWNER                                                                                                                            OBJECT_TYPE               COUNT(*)
-------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------
OJVMSYS                                                                                                                          TABLE                            6
OJVMSYS                                                                                                                          INDEX                            6
OLAPSYS                                                                                                                          VIEW                            18
DVSYS                                                                                                                            TABLE                           40
DVSYS                                                                                                                            VIEW                           113
DVSYS                                                                                                                            PROCEDURE                        3
HR                                                                                                                               SEQUENCE                         3
ORACLE_OCM                                                                                                                       JOB                              2
DEV_BIPLATFORM                                                                                                                   SEQUENCE                        19
ORS_IAU_APPEND                                                                                                                   SYNONYM                         34
ORS_OPSS                                                                                                                         TABLE                          174

OWNER                                                                                                                            OBJECT_TYPE               COUNT(*)
-------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------
CISUAT                                                                                                                           FUNCTION                         3
DEV_IAU_VIEWER                                                                                                                   VIEW                             4
OR_IAU_VIEWER                                                                                                                    VIEW                             3
DEV_MDS                                                                                                                          PACKAGE BODY                     5
ORS_WLS                                                                                                                          INDEX                           29
OR_STB                                                                                                                           INDEX                            3
CISUAT                                                                                                                           JAVA RESOURCE                    2
PUBLIC                                                                                                                           SYNONYM                      12230
DBSFWUSER                                                                                                                        TABLE                            3
GSMADMIN_INTERNAL                                                                                                                PACKAGE                          8
DBSNMP                                                                                                                           INDEX                           10

OWNER                                                                                                                            OBJECT_TYPE               COUNT(*)
-------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------
GSMADMIN_INTERNAL                                                                                                                RULE SET                         2
GSMADMIN_INTERNAL                                                                                                                PROCEDURE                        2
DVSYS                                                                                                                            SEQUENCE                        22
HR                                                                                                                               TABLE                            7
DEV_OPSS                                                                                                                         SEQUENCE                         4
ORS_IAU                                                                                                                          SYNONYM                          5
OR_MDS                                                                                                                           TYPE                             2
DEV_UMS                                                                                                                          TABLE                           32
DEV_MDS                                                                                                                          TABLE                           16
DEV_WLS_RUNTIME                                                                                                                  TABLE                            1
OR_UMS                                                                                                                           PACKAGE                          1

OWNER                                                                                                                            OBJECT_TYPE               COUNT(*)
-------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------
CISUAT                                                                                                                           VIEW                            15
OR_MDS                                                                                                                           PACKAGE BODY                     5
OR_OPSS                                                                                                                          INDEX                          520
OR_IAU                                                                                                                           INDEX                           56
DEV_MDS                                                                                                                          INDEX                           41
ORS_STB                                                                                                                          INDEX                            3
DEV_WLS                                                                                                                          TRIGGER                          5
ORACLE_OCM                                                                                                                       PACKAGE BODY                     3
AUDSYS                                                                                                                           TABLE PARTITION                  2
DBSNMP                                                                                                                           PROCEDURE                        1
REMOTE_SCHEDULER_AGENT                                                                                                           PROCEDURE                       11

OWNER                                                                                                                            OBJECT_TYPE               COUNT(*)
-------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------
GSMADMIN_INTERNAL                                                                                                                TABLE                           41
GSMADMIN_INTERNAL                                                                                                                INDEX                           41
GSMADMIN_INTERNAL                                                                                                                SEQUENCE                        16
DVF                                                                                                                              PACKAGE BODY                     1
DVSYS                                                                                                                            PACKAGE BODY                    11
HR                                                                                                                               PROCEDURE                        2
ORS_IAU                                                                                                                          SEQUENCE                         1
DEV_IAU_APPEND                                                                                                                   SYNONYM                         34
CISUAT                                                                                                                           TYPE                            14
DEV_WLS                                                                                                                          TABLE                           40
ORS_MDS                                                                                                                          TABLE                           16

OWNER                                                                                                                            OBJECT_TYPE               COUNT(*)
-------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------
OR_WLS                                                                                                                           TABLE                           40
DEV_STB                                                                                                                          TABLE                            2
CISUAT                                                                                                                           PACKAGE                        181
DEV_UMS                                                                                                                          PACKAGE                          1
CISUAT                                                                                                                           PROCEDURE                        2
OR_IAU                                                                                                                           PACKAGE BODY                     2
ORS_UMS                                                                                                                          PACKAGE BODY                     1
DEV_UMS                                                                                                                          INDEX                           52
DEV_IAU                                                                                                                          INDEX                           56
ORS_UMS                                                                                                                          INDEX                           52
DBSFWUSER                                                                                                                        INDEX                            3

OWNER                                                                                                                            OBJECT_TYPE               COUNT(*)
-------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------
DBSNMP                                                                                                                           SYNONYM                          1
APPQOSSYS                                                                                                                        TABLE                            5
GSMADMIN_INTERNAL                                                                                                                EVALUATION CONTEXT               1
OLAPSYS                                                                                                                          TYPE                             2
DVSYS                                                                                                                            LIBRARY                          5
DVSYS                                                                                                                            PACKAGE                         11
HR                                                                                                                               INDEX                           19
OR_WLS                                                                                                                           SEQUENCE                         5
DEV_IAU                                                                                                                          SEQUENCE                         1
OR_IAU_APPEND                                                                                                                    SYNONYM                         34
OR_IAU                                                                                                                           SYNONYM                          5

OWNER                                                                                                                            OBJECT_TYPE               COUNT(*)
-------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------
ORS_IAU                                                                                                                          TYPE                             3
OR_UMS                                                                                                                           TABLE                           32
ORS_UMS                                                                                                                          TABLE                           32
ORS_IAU                                                                                                                          TABLE                           31
OR_WLS_RUNTIME                                                                                                                   TABLE                            1
ORS_MDS                                                                                                                          PACKAGE                          5
DEV_UMS                                                                                                                          VIEW                            14
OR_UMS                                                                                                                           VIEW                            14
CISUAT                                                                                                                           PACKAGE BODY                   181
DEV_IAU                                                                                                                          PACKAGE BODY                     2
DEV_OPSS                                                                                                                         INDEX                          520

OWNER                                                                                                                            OBJECT_TYPE               COUNT(*)
-------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------
OR_WLS                                                                                                                           INDEX                           29
DEV_WLS_RUNTIME                                                                                                                  INDEX                            1
OR_WLS                                                                                                                           TRIGGER                          5
OLAPSYS                                                                                                                          SEQUENCE                         1
HR                                                                                                                               VIEW                             1
ORS_MDS                                                                                                                          SEQUENCE                         1
ORS_IAU_VIEWER                                                                                                                   SYNONYM                         33
OR_STB                                                                                                                           TABLE                            2
OR_IAU                                                                                                                           PACKAGE                          2
DEV_IAU                                                                                                                          PACKAGE                          2
CISUAT_SUP                                                                                                                       VIEW                          1933

OWNER                                                                                                                            OBJECT_TYPE               COUNT(*)
-------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------
ORS_UMS                                                                                                                          VIEW                            14
DEV_BIPLATFORM                                                                                                                   VIEW                             6
OR_UMS                                                                                                                           PACKAGE BODY                     1
DEV_UMS                                                                                                                          PACKAGE BODY                     1
CISUAT                                                                                                                           TYPE BODY                        1
ORS_OPSS                                                                                                                         INDEX                          520
DEV_BIPLATFORM                                                                                                                   INDEX                          267
DEV_WLS                                                                                                                          INDEX                           29
ORACLE_OCM                                                                                                                       PACKAGE                          3
DBSFWUSER                                                                                                                        PACKAGE                          1
GSMADMIN_INTERNAL                                                                                                                SYNONYM                          2

OWNER                                                                                                                            OBJECT_TYPE               COUNT(*)
-------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------
OJVMSYS                                                                                                                          SEQUENCE                         2
OLAPSYS                                                                                                                          TABLE                            2
OLAPSYS                                                                                                                          INDEX                            2
DVSYS                                                                                                                            FUNCTION                        30
DVSYS                                                                                                                            SYNONYM                          2
HR                                                                                                                               TRIGGER                          2
CISSYS                                                                                                                           TABLE                            1
DEV_MDS                                                                                                                          SEQUENCE                         7
DEV_IAU                                                                                                                          SYNONYM                          5
ORS_MDS                                                                                                                          TYPE                             2
DEV_OPSS                                                                                                                         TABLE                          174

OWNER                                                                                                                            OBJECT_TYPE               COUNT(*)
-------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------
DEV_IAU                                                                                                                          TABLE                           31
OR_MDS                                                                                                                           TABLE                           16
ORS_WLS_RUNTIME                                                                                                                  TABLE                            1
OR_MDS                                                                                                                           PACKAGE                          5
ORS_IAU                                                                                                                          PACKAGE                          2
ORS_MDS                                                                                                                          PACKAGE BODY                     5
OR_MDS                                                                                                                           INDEX                           41
ORS_IAU                                                                                                                          INDEX                           56
ORS_WLS_RUNTIME                                                                                                                  INDEX                            1
DEV_BIPLATFORM                                                                                                                   TRIGGER                          1
PUBLIC                                                                                                                           LOCKDOWN PROFILE                 3

OWNER                                                                                                                            OBJECT_TYPE               COUNT(*)
-------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------
DBSFWUSER                                                                                                                        PACKAGE BODY                     1
DBSNMP                                                                                                                           TABLE                           20
DBSNMP                                                                                                                           PACKAGE                          3
GSMADMIN_INTERNAL                                                                                                                PACKAGE BODY                     8
DVF                                                                                                                              PACKAGE                          1
DVSYS                                                                                                                            INDEX                           70
DVSYS                                                                                                                            TYPE                            66
CISUAT                                                                                                                           SEQUENCE                       355
ORS_WLS                                                                                                                          SEQUENCE                         5
OR_OPSS                                                                                                                          SEQUENCE                         4
OR_IAU_VIEWER                                                                                                                    SYNONYM                         33

OWNER                                                                                                                            OBJECT_TYPE               COUNT(*)
-------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------
OR_OPSS                                                                                                                          TABLE                          174
DEV_MDS                                                                                                                          PACKAGE                          5
CIS_STAGE_UAT                                                                                                                    VIEW                          1933
CISUAT                                                                                                                           INDEX                         2814
OR_UMS                                                                                                                           INDEX                           52
DEV_STB                                                                                                                          INDEX                            3
CISUAT                                                                                                                           JAVA CLASS                       7
ORS_WLS                                                                                                                          TRIGGER                          5

184 rows selected. 

Open in new window


the meaning should be ,e.g:

OR_OPSS                                                                                                                          TABLE                          174

Open in new window


is a table called OR_OPSS (but it say it is the owner). and there are 174 table  under this owner? seems not making sense. we should see how many table object under the owner OR_OPSS , which is 174 in total ,right?

so how can I see all tables under the schema owner OR_OPSS?
select count(*) from dba_tables a where a.owner = 'OR_OPSS';

Open in new window

Mark GeerlingsDatabase Administrator

Commented:
Before you run a query like this:

select owner,
       object_type,
       count(*)
  from all_objects
where ...

Open in new window


Please run these commands:
column owner format a30
set pagesize 999

Open in new window


That will make the results much easier to see in a small window.
johnsoneSenior Oracle DBA

Commented:
For what it's worth, the DBA_USERS.ORACLE_MAINTAINED column is a single character (Y/N), so you should be able to use this to find all the system accounts:
select * from dba_users where oracle_maintained = 'Y'

Open in new window

At least, that worked on my 12c database
marrowyungSenior Technical architecture (Data)

Author

Commented:
Alexander Eßer,

"select count(*) from dba_tables a where a.owner = 'OR_OPSS';"

so this means for each owner ,if we need to dig into more detail on how many tables that owner has, need to use that query?

guys any one single script can list out owner's and all tables of that owner?

I also try to find out all objects created by each owner:
1) Views,
2) Procedures,
3)Functions,
4) Packages,
5) Triggers,
6) Sequences,
7) synonyms  

possible using one single script ?

Mark Geerlings,

tks.
Then stick to the dba_objects view and do a SELECT owner... with count and group by objects type
Geert GOracle dba
Top Expert 2009

Commented:
ahaha. then .... ?

You can't verify when a query is wrong. Sample: my YES should have been Y
corrected by Johnsone

How will you be able to correct/verify anything else of what I would say ?
select a.owner,
       a.object_type,
       count(*)
  from dba_objects a
 group by a.owner,
          a.object_type
 order by a.owner,
          a.object_type;

Open in new window

Mark GeerlingsDatabase Administrator

Commented:
And to exclude the schemas that you don't need to worry about, you need a join to dba_users like this:
select o.owner,
       o.object_type,
       count(*)
  from dba_users u, dba_objects o
 where o.owner = u.username
 and u.oracle_maintained = 'N'
 group by o.owner,
          o.object_type
 order by o.owner,
          o.object_type;

Open in new window

marrowyungSenior Technical architecture (Data)

Author

Commented:
HI Alex,

this link:

https://www.convert-in.com/docs/ora2sql/intro.htm

also applied to the MariaDB case ?
this link:

https://www.convert-in.com/docs/ora2sql/intro.htm

also applied to the MariaDB case ?
Not directly (as you can see its headline is 'Migrate Oracle to MySQL'), this could have been my fault (as there were several links opened in my browser at that moment)
marrowyungSenior Technical architecture (Data)

Author

Commented:
Alexander Eßer,

"Then stick to the dba_objects view and do a SELECT owner... with count and group by objects type"

dba_object view you are referring to is dba_objects ?

ok now the output is :

system-information-after-imported.jpg
so this is group by user schema in major so all objects of schema will be show in one section, very good looking.

But can I also list out , for each schema, the object detail of each type, e.g. VIEW, counted 18, a list of all of them?

in order to verify the result, how can I count the total number of each of the object, no matter what schema it belongs to? I want to have a summary page.
select owner,
       max(decode(object_type, 'TABLE', cnt, null)) Tables,
       max(decode(object_type, 'VIEW', cnt, null)) Views,
       max(decode(object_type, 'TRIGGER', cnt, null)) triggers,
       max(decode(object_type, 'PACKAGE', cnt, null)) packages
       -- and so on...
  from (select owner,
               object_type,
               count(*) cnt
          from dba_objects
         group by owner,
                  object_type)
 group by owner;

Open in new window


select a.owner,
       a.object_type,
       count(*),
       cursor (select *
                 from dba_objects x
                where x.owner = a.owner
                  and x.object_type = a.object_type
                order by x.object_name) detail
  from dba_objects a
 group by a.owner,
          a.object_type
 order by a.owner,
          a.object_type;

Open in new window

marrowyungSenior Technical architecture (Data)

Author

Commented:
johnsone,

I found that I add these condition:  u.oracle_maintained = 'Y'

some schema don't show out at all and actually much less record returned.
marrowyungSenior Technical architecture (Data)

Author

Commented:
this one:

select a.owner,
       a.object_type,
       count(*),
       cursor (select *
                 from dba_objects x
                where x.owner = a.owner
                  and x.object_type = a.object_type
                order by x.object_name) detail
  from dba_objects a
 group by a.owner,
          a.object_type
 order by a.owner,
          a.object_type;

Open in new window


error shows that:

Error report -
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded

Open in new window


any other edition ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
so this query is for the summary page:

select owner,
       max(decode(object_type, 'TABLE', cnt, null)) Tables,
       max(decode(object_type, 'VIEW', cnt, null)) Views,
       max(decode(object_type, 'TRIGGER', cnt, null)) triggers,
       max(decode(object_type, 'PACKAGE', cnt, null)) packages
       -- and so on...
  from (select owner,
               object_type,
               count(*) cnt
          from dba_objects
         group by owner,
                  object_type)
 group by owner;

Open in new window


?

also if I add:

 max(decode(object_type, 'JAVA CLASS', cnt, null)) 'JAVA CLASS'

Open in new window


it will complain error:

Error at Command Line : 27 Column : 53
Error report -
SQL Error: ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"
*Cause:    
*Action:

Open in new window


how can I include 'Java Class' (2x word)?
for the summary (2nd) to include Java classes:
select owner,
       max(decode(object_type, 'TABLE', cnt, null)) Tables,
       max(decode(object_type, 'VIEW', cnt, null)) Views,
       max(decode(object_type, 'TRIGGER', cnt, null)) triggers,
       max(decode(object_type, 'PACKAGE', cnt, null)) packages,
       max(decode(object_type, 'JAVA CLASS', cnt, null)) java_classes
       -- and so on...
  from (select owner,
               object_type,
               count(*) cnt
          from dba_objects
         group by owner,
                  object_type)
 group by owner;

Open in new window

select a.owner,
       a.object_type,
       a.object_name,
       sum(count(*)) over(partition by a.owner, a.object_type order by a.object_name) running_count,
       count(*) over(partition by a.owner, a.object_type) total_per_owner_and_type
  from dba_objects a
 group by a.owner,
          a.object_type,
          a.object_name
 order by a.owner,
          a.object_type;

Open in new window

marrowyungSenior Technical architecture (Data)

Author

Commented:
HI,

also by this:

select o.owner,
       o.object_type,
       count(*)
  from dba_users u, dba_objects o
 where o.owner = u.username
 and u.oracle_maintained = 'N'
 group by o.owner,
          o.object_type
 order by o.owner,
          o.object_type;
       

Open in new window


I see package and package body type, are they the same ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
"for the summary (2nd) to include Java classes:"

I do in this way :

     max(decode(object_type, 'JAVA CLASS', cnt, null)) "JAVA CLASS",
       max(decode(object_type, 'JAVA RESOURCE', cnt, null)) "JAVA RESOURCE",
       max(decode(object_type, 'LOB', cnt, null)) "LOB"

Open in new window


"
select a.owner,
       a.object_type,
       a.object_name,
       sum(count(*)) over(partition by a.owner, a.object_type order by a.object_name) running_count,
       count(*) over(partition by a.owner, a.object_type) total_per_owner_and_type
  from dba_objects a
 group by a.owner,
          a.object_type,
          a.object_name
 order by a.owner,
          a.object_type;

Open in new window

"

oh this is the one to list down summary of schema and ALL object in detail ?

I got an message which show:

Only 5,000 rows currently supported in a script results
5,000 rows selected. 

Open in new window


so can't show more than 5000 rows ?
I see package and package body type, are they the same ?
A packkage consists of a specification (header, interface) and its body (the code/implementation)...
so can't show more than 5000 rows ?
You'll have to change the preferences:
https://community.oracle.com/thread/979859
https://support.oracle.com/knowledge/Middleware/2175009_1.html
marrowyungSenior Technical architecture (Data)

Author

Commented:
"A packkage consists of a specification (header, interface) and its body (the code/implementation)..."

so the number is the same also  means it is the same object but diff header?

"sum(count(*)) over(partition by a.owner, a.object_type order by a.object_name) running_count,"

the running count seems does show much information, it just increment by 1 each time, what is it for ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
"https://support.oracle.com/knowledge/Middleware/2175009_1.html"

it say:

Upgraded from SQL Developer 3.2 which did not have the issue.

Open in new window


I am using SQL developer 18 already.
You need to turn on/off some option:
https://www.thatjeffsmith.com/archive/2018/01/scripts-on-spooling-and-output-to-screen-in-oracle-sql-developer/

it just increment by 1 each time, what is it for ?
If you don't need it, delete it, skip that ;-)
It's just a running count on owner and object_type, that's all ;-)
marrowyungSenior Technical architecture (Data)

Author

Commented:
"It's just a running count on owner and object_type, that's all ;-)"

so it means it is the xth object it processed ?

"so the number is the same also  means it is the same object but diff header?"

so I will assume it is a yes, they are referring the SAME object ?
so I will assume it is a yes, they are referring the SAME object ?
The number/count of package spec and its bodies should be the same, yes.
so it means it is the xth object it processed ?
What do you mean with "processed"?! It's just a grouped counting of objects and types...
extract as example
Mark GeerlingsDatabase Administrator

Commented:
You don't need to worry about any of the SYS objects when you do an export or import.  Those objects do not get exported and they do not get imported.  In an Oracle database, Oracle creates them when the database gets created, and Oracle updates them automatically when DDL commands get executed (like: create user..., create table..., create index..., create or replace..., drop ..., alter..., etc.).  I've never used Maria DB, but that likely also has its own "data dictionary" similar to the SYS schema in Oracle.

And, you likely don't need to worry about any of the "oracle_maintained = 'Y'" schemas either, since these are also unique to Oracle.

In Oracle, package headers and package bodies are separate objects.  Usually each package header (specification) has a corresponding package body with the same name (the body contains all of the code).  But it is possible to have a package header that does not need a package body.  This can be useful for storing "global" variables that can be used by any PL\SQL object called in a particular session.  You cannot have a package body that does not have a corresponding package header.
You don't need to worry about any of the SYS objects when you do an export or import.
Correct! But you have to keep that in mind, because this Oracle system and data dictionary stuff might throw errors in the import log(s) that can be ignored.
Geert GOracle dba
Top Expert 2009

Commented:
well, the password validation routine is stored in the sys schema ...
but you'd naturally want to rewrite that for another db type
the password validation routine
You mean the default ;-) (which can be replaced...)
marrowyungSenior Technical architecture (Data)

Author

Commented:
by doing this again:

select o.owner,
       o.object_type,
       count(*)
  from dba_users u, dba_objects o
 where o.owner = u.username
 and u.oracle_maintained = 'N'
 group by o.owner,
          o.object_type
 order by o.owner,
          o.object_type;

Open in new window


I see object type, there are 2 x interesting type:
1)TYPE
2) TYPE BODY

their number is different, they are not the same ?
I see object type, there are 2 x interesting type:
1)TYPE
2) TYPE BODY

their number is different, they are not the same ?
Similar to PACKAGE and PACKAGE BODY
marrowyungSenior Technical architecture (Data)

Author

Commented:
Mark Geerlings,

tks.

"You don't need to worry about any of the SYS objects when you do an export or import.  Those objects do not get exported and they do not get imported."

I mean once imported to the TEST oracle DB, I also want to filter that out those object for reporting purpose ! so that in the report this kind of object never show up, we need to count these object ONLY need for PoC. so oracle related object MUST not shown up!
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

"Similar to PACKAGE and PACKAGE BODY"

this type shown out diff in number, so I prefer to list them out .
There are tons of sites claiming to have those lists to exclude, like https://blog.dbi-services.com/oracle-system-schemas-vs-created-users/
In the end, you have 2 options: either you just include your users/schemas or exclude everything else!
this type shown out diff in number, so I prefer to list them out .
Types always have a spec/header/definition, but they does not have to have a body.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"either you just include your users/schemas "

I want this. just want to list out what object MUST involve in the PoC migration list, so I need no Oracle related object in the final list.

so once I import the UAT oracle dmp to another Oracle should I running this query against, I still do not want any oracle system related object.

I try to find out a full list of application related only objects we can try to port to MariaDB.

so finally this can be the script to find out the summary each type of object for each schema?

column owner format a30
set pagesize 999

/*  object summary */
select owner,
       max(decode(object_type, 'TABLE', cnt, null)) Tables,
       max(decode(object_type, 'VIEW', cnt, null)) Views,
       max(decode(object_type, 'TRIGGER', cnt, null)) triggers,
       max(decode(object_type, 'PACKAGE', cnt, null)) packages,
       max(decode(object_type, 'PACKAGE BODY', cnt, null)) "PACKAGE BODY",   
       max(decode(object_type, 'FUNCTION', cnt, null)) FUNCTION,
       max(decode(object_type, 'INDEX', cnt, null)) "INDEX",
       max(decode(object_type, 'JAVA CLASS', cnt, null)) "JAVA CLASS",
       max(decode(object_type, 'JAVA RESOURCE', cnt, null)) "JAVA RESOURCE",
       max(decode(object_type, 'LOB', cnt, null)) "LOB", 
       max(decode(object_type, 'PROCEDURE', cnt, null)) "PROCEDURE",
       max(decode(object_type, 'SEQUENCE', cnt, null)) "SEQUENCE",
       max(decode(object_type, 'TYPE', cnt, null)) "TYPE",
       max(decode(object_type, 'TYPE BODY', cnt, null)) "TYPE BODY",
       max(decode(object_type, 'SYNONYM', cnt, null)) "SYNONYM",
        max(decode(object_type, 'QUEUE', cnt, null)) "QUEUE"
       /* -- and so on...*/
  from (select o.owner,
               o.object_type,
               count(*) cnt
          from dba_users u, dba_objects o
           where o.owner = u.username
           and u.oracle_maintained = 'N'
         group by owner,
                  object_type)
 group by owner;

Open in new window


any other object type missing ?
select distinct a.object_type from dba_objects a

Open in new window

marrowyungSenior Technical architecture (Data)

Author

Commented:
so this will be, inside that subquery:

select o.owner,
               distinct o.object_type,
               count(*) cnt
          from dba_users u, dba_objects o
           where o.owner = u.username
           and u.oracle_maintained = 'N'
         group by owner,
                  object_type

Open in new window


?

you know, I add the rest of object by referencing this on any object missing:

/*  object summary */
select o.owner,
       o.object_type,
       count(*)
  from dba_users u, dba_objects o
 where o.owner = u.username
 and u.oracle_maintained = 'N'
 group by o.owner,
          o.object_type
 order by o.owner,
          o.object_type;

Open in new window

No, this
select distinct a.object_type from dba_objects a

Open in new window

is just a query to list all object types used in your database
marrowyungSenior Technical architecture (Data)

Author

Commented:
but my query also exclude oracle existing system related schema, right?

or you can simply change my query I post with your part, I am confused.
You would have to complete the columns from your query:

max(decode(object_type, 'TABLE', cnt, null)) Tables,
       max(decode(object_type, 'VIEW', cnt, null)) Views,
       max(decode(object_type, 'TRIGGER', cnt, null)) triggers,
       max(decode(object_type, 'PACKAGE', cnt, null)) packages,
       max(decode(object_type, 'PACKAGE BODY', cnt, null)) "PACKAGE BODY",  
       max(decode(object_type, 'FUNCTION', cnt, null)) FUNCTION,
       max(decode(object_type, 'INDEX', cnt, null)) "INDEX",
       max(decode(object_type, 'JAVA CLASS', cnt, null)) "JAVA CLASS",
       max(decode(object_type, 'JAVA RESOURCE', cnt, null)) "JAVA RESOURCE",
       max(decode(object_type, 'LOB', cnt, null)) "LOB",
       max(decode(object_type, 'PROCEDURE', cnt, null)) "PROCEDURE",
       max(decode(object_type, 'SEQUENCE', cnt, null)) "SEQUENCE",
       max(decode(object_type, 'TYPE', cnt, null)) "TYPE",
       max(decode(object_type, 'TYPE BODY', cnt, null)) "TYPE BODY",
       max(decode(object_type, 'SYNONYM', cnt, null)) "SYNONYM",
        max(decode(object_type, 'QUEUE', cnt, null)) "QUEUE"
....
marrowyungSenior Technical architecture (Data)

Author

Commented:
I use this query to check type too
:

/*  object summary */
select o.owner,
       o.object_type,
       count(*)
  from dba_users u, dba_objects o
 where o.owner = u.username
and u.oracle_maintained = 'N'
 group by o.owner,
          o.object_type
 order by o.owner,
          o.object_type;
       

Open in new window


if I remark  
and u.oracle_maintained = 'N'

Open in new window


the SYS and SYSTEM schema will be shown, but if I un remark that, this schema will be shown too:

SYSMAN

Open in new window


is this also oracle system schema that can be ignore?

"You would have to complete the columns from your query:"

as I said I reference:

/*  object summary */
select o.owner,
       o.object_type,
       count(*)
  from dba_users u, dba_objects o
 where o.owner = u.username
and u.oracle_maintained = 'N'
 group by o.owner,
          o.object_type
 order by o.owner,
          o.object_type;
       

Open in new window


to get the result of type not included by your first "max(" query, so I cover more and that's why I post my version after modifying yours !

or you mean replace the whole part here:

max(decode(object_type, 'TABLE', cnt, null)) Tables,
       max(decode(object_type, 'VIEW', cnt, null)) Views,
       max(decode(object_type, 'TRIGGER', cnt, null)) triggers,
       max(decode(object_type, 'PACKAGE', cnt, null)) packages,
       max(decode(object_type, 'PACKAGE BODY', cnt, null)) "PACKAGE BODY",   
       max(decode(object_type, 'FUNCTION', cnt, null)) FUNCTION,
       max(decode(object_type, 'INDEX', cnt, null)) "INDEX",
       max(decode(object_type, 'JAVA CLASS', cnt, null)) "JAVA CLASS",
       max(decode(object_type, 'JAVA RESOURCE', cnt, null)) "JAVA RESOURCE",
       max(decode(object_type, 'LOB', cnt, null)) "LOB", 
       max(decode(object_type, 'PROCEDURE', cnt, null)) "PROCEDURE",
       max(decode(object_type, 'SEQUENCE', cnt, null)) "SEQUENCE",
       max(decode(object_type, 'TYPE', cnt, null)) "TYPE",
       max(decode(object_type, 'TYPE BODY', cnt, null)) "TYPE BODY",
       max(decode(object_type, 'SYNONYM', cnt, null)) "SYNONYM",
        max(decode(object_type, 'QUEUE', cnt, null)) "QUEUE"

Open in new window


using this:

select distinct a.object_type from dba_objects a

Open in new window


 ?
SYSMAN is also one of those internal schemas, so yes, it can also be ignored.

Regarding the query, I suggested you complete the list with the missing object types. Since these object counts are distributed column based, you cannot just use a dynamic source here. Or you should have a look at PIVOT/UNPIVOT....
marrowyungSenior Technical architecture (Data)

Author

Commented:
"SYSMAN is also one of those internal schemas, so yes, it can also be ignored."

then I can't see why:

and u.oracle_maintained = 'N'

Open in new window


do not filter that out!
Honestly I have no clue, why SYSMAN is not in that list... Maybe some other expert here as an answer to this.
In the meantime, you have to filter that (and maybe others, like APEX related) out manualy, e.g. NOT IN ('SYSMAN', ....)
marrowyungSenior Technical architecture (Data)

Author

Commented:
"e.g. NOT IN ('SYSMAN', ....)"

tks,

from here:
https://blog.dbi-services.com/oracle-system-schemas-vs-created-users/

I know SYS and SYSTEM, any other link can tell me more what else is related to Oracle only !

from that link, what is 12c non-cdb database?

it seems DVF schema/owner belongs to system too ? and table on that link is all system schema owned by oracle 12 c ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
Kent Olsen,

what queries or URL show you that:

ANONYMOUS
CTXSYS
CBSNMP
EXFSYS
LBACSYS
MDSYS
MGMT_VIEW
LOAPSYS
ORDDATA
OWBSYS
ORDPLUGINS
ORDSYS
OUTLN
SI_INFORMTN_SCHEMA
SYS
SYSMAN
SYSTEM
WK_TEST
WKSYS
WKPROXY
WMSYS
XDB

Open in new window


?
from that link, what is 12c non-cdb database?
Since 12c you can create Container Databases and Pluggable Databases (CDB and PDB) due to the multitenant architecture. Upon creation/installation, you have the choice whether to have a CDB/PDB based database (or more) and the conventional (prior 12c) database that is not CDB based.
marrowyungSenior Technical architecture (Data)

Author

Commented:
so both system DB and we should not include, and CDB and PDB is the schema name of them, right ?
Geert GOracle dba
Top Expert 2009

Commented:
loool, distinct in a query with group by
what's the the reason for both in the same query ?
Mark GeerlingsDatabase Administrator

Commented:
No.  SYSTEM and SYS are two of the schemas that you should not include in your export or import.  (SYS will be excluded automatically anyway).  The others that you should exclude are the other Oracle-maintained schemas that we mentioned earlier.

CDB and PDB are not schemas.  These are abbreviations that Oracle introduced with Oracle12 to describe new concepts in Oracle12.

CDB = Container DataBase.  This is the outer or base-level database that can support or contain one or more PDBs.

PDB = Pluggable DataBase.  This describes a separate, removeable and transportable database that can be moved to a different CDB if necessary.  These PDBs cannot operate independently.  They depend on a CDB to provide the  background services that Oracle databases need.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"(SYS will be excluded automatically anyway). "

by the query I modified and post above, without "and u.oracle_maintained = 'N'", SYS and SYSTEM both shown out.

but as what I said is, even we do "and u.oracle_maintained = 'N'", some other system schema STILL shown, like sysman, this is what I don't understand here.

 so I have to eyesball the schema below:

ANONYMOUS
CTXSYS
CBSNMP
EXFSYS
LBACSYS
MDSYS
MGMT_VIEW
LOAPSYS
ORDDATA
OWBSYS
ORDPLUGINS
ORDSYS
OUTLN
SI_INFORMTN_SCHEMA
SYS
SYSMAN
SYSTEM
WK_TEST
WKSYS
WKPROXY
WMSYS
XDB

Open in new window


to make sure that none of these exists and this is all my user/application related schema ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
one more thing, now I am checking SQL developer and see what else was left and I see all these:

CIS-UAT-DB-object.jpg
should I include any of these when scanning the schema ?
Geert GOracle dba
Top Expert 2009

Commented:
should you ?
why ask us ?

you need to translate features from Oracle to MariaDb
that means comparing the features you use in oracle, to features available in MariaDb

you could open each leaf and see if there is anything there ... for each schema

or check dba_objects and it's type column ... as already indicated by multiple people
should I include any of these when scanning the schema ?
It looks like none of these were used in that schema, so you may skip them for that very schema.
Those objects may exist in other (user data) schemas...
as already indicated by multiple people
... and please do us a favor: Try to read more carefully!! You're repeating similar questions over and over!! And you're kinda over-reading stuff (see CDB, PDB above: I explained it to you and then Mark did it again!!)
marrowyungSenior Technical architecture (Data)

Author

Commented:
Geert G,

"you could open each leaf and see if there is anything there ... for each schema"

if I work in this way it will takes a long time, that's why I need script scan it out.

Alexander,

"(see CDB, PDB above: I explained it to you and then Mark did it again!!)"

he just explain in the other way around and I found it is good.

 I think the best way to do is to include them and check the result, if it is all empty, then ignore it.
marrowyungSenior Technical architecture (Data)

Author

Commented:
how I use this script:

select owner,
       max(decode(object_type, 'TABLE', cnt, null)) Tables,
       max(decode(object_type, 'VIEW', cnt, null)) Views,
       max(decode(object_type, 'TRIGGER', cnt, null)) triggers,
       max(decode(object_type, 'Crossedition Triggers', cnt, null)) "Crossedition Triggers",    
       max(decode(object_type, 'PACKAGE', cnt, null)) packages,
       max(decode(object_type, 'PACKAGE BODY', cnt, null)) "PACKAGE BODY",   
       max(decode(object_type, 'FUNCTION', cnt, null)) FUNCTION,
       max(decode(object_type, 'INDEX', cnt, null)) "INDEX",
       max(decode(object_type, 'JAVA CLASS', cnt, null)) "JAVA CLASS",
       max(decode(object_type, 'JAVA RESOURCE', cnt, null)) "JAVA RESOURCE",
       max(decode(object_type, 'LOB', cnt, null)) "LOB", 
       max(decode(object_type, 'PROCEDURE', cnt, null)) "PROCEDURE",
       max(decode(object_type, 'SEQUENCE', cnt, null)) "SEQUENCE",
       max(decode(object_type, 'Materialized Views', cnt, null)) "Materialized Views",  
       max(decode(object_type, 'TYPE', cnt, null)) "TYPE",
       max(decode(object_type, 'TYPE BODY', cnt, null)) "TYPE BODY",
       max(decode(object_type, 'SYNONYM', cnt, null)) "SYNONYM",
       max(decode(object_type, 'Database Links', cnt, null)) "Database Links",
       max(decode(object_type, 'XML Schemas', cnt, null)) "XML Schemas",
       max(decode(object_type, 'XML DB Repository', cnt, null)) "XML DB Repository",              
       max(decode(object_type, 'Analytic Views', cnt, null)) "Analytic Views", 
       max(decode(object_type, 'QUEUE', cnt, null)) "QUEUE",
        max(decode(object_type, 'Queues Tables', cnt, null)) "Queues Tables"
       /* -- and so on...*/
  from (select o.owner,
               o.object_type,
               count(*) cnt
          from dba_users u, dba_objects o
           where o.owner = u.username
           and u.oracle_maintained = 'N'
           and o.owner not in('ANONYMOUS','CTXSYS','CBSNMP','EXFSYS', 'LBACSYS','MDSYS',
'MGMT_VIEW', 'LOAPSYS', 'ORDDATA', 'OWBSYS', 'ORDPLUGINS','ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'WK_TEST', 'WKSYS', 
'WKPROXY', 'WMSYS', 'XDB')
         group by owner,
                  object_type)
 group by owner;
 

Open in new window


XML DB Repository of all schema shows empty, but from SQL developer once schema has it:

CISUAT-schema-object.jpg
XML DB Repository of all schema shows empty, but from SQL developer once schema has it:
1. Do you know what that is?!
2. Do you need it for MariaDB / what is the equivalent in MariaDB for this?!
marrowyungSenior Technical architecture (Data)

Author

Commented:
but I think if depends on the result from this query:

column owner format a30
set pagesize 999

/*  object summary */
select o.owner,
       o.object_type,
       count(*)
  from dba_users u, dba_objects o
 where o.owner = u.username
and u.oracle_maintained = 'N'
and o.owner not in('ANONYMOUS','CTXSYS','CBSNMP','EXFSYS', 'LBACSYS','MDSYS',
'MGMT_VIEW', 'LOAPSYS', 'ORDDATA', 'OWBSYS', 'ORDPLUGINS','ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'WK_TEST', 'WKSYS', 
'WKPROXY', 'WMSYS', 'XDB')
 group by o.owner,
          o.object_type
 order by o.owner,
          o.object_type;
       

Open in new window


the o.object_type should already list out ALL possible object type, right?

I can't find XML DB Repository object type, so this is not an object we can migrate, right?
I can't find XML DB Repository object type, so this is not a type, right?
Again: Do you even know what that is and if you need it for migration???
marrowyungSenior Technical architecture (Data)

Author

Commented:
if I have to check clearly what it is, then https://docs.oracle.com/database/121/ADXDB/partpg5.htm#ADXDB5284/

then I have to ask MariaDB on this, if they don't support,then no.
marrowyungSenior Technical architecture (Data)

Author

Commented:
btw, do you know if the top section of SQL developer include all object of all schema?

SQL-developer-UI.jpg
if it is then I just go through that part and see what is left for ALL schema, right?
marrowyungSenior Technical architecture (Data)

Author

Commented:
one thing, I found SQL developer data dictionary of the report tab can show out sth similar, but how can I filter that out system schema from SQL developer's UI ?

SQL-developer-report-UI.jpg
marrowyungSenior Technical architecture (Data)

Author

Commented:
try now to cross check using SQL developer and this query:

select owner,
       max(decode(object_type, 'TABLE', cnt, null)) Tables,
       max(decode(object_type, 'VIEW', cnt, null)) Views,
       max(decode(object_type, 'TRIGGER', cnt, null)) triggers,
       max(decode(object_type, 'Crossedition Triggers', cnt, null)) "Crossedition Triggers",    
       max(decode(object_type, 'PACKAGE', cnt, null)) packages,
       max(decode(object_type, 'PACKAGE BODY', cnt, null)) "PACKAGE BODY",   
       max(decode(object_type, 'FUNCTION', cnt, null)) FUNCTION,
       max(decode(object_type, 'INDEX', cnt, null)) "INDEX",
       max(decode(object_type, 'JAVA CLASS', cnt, null)) "JAVA CLASS",
       max(decode(object_type, 'JAVA RESOURCE', cnt, null)) "JAVA RESOURCE",
       max(decode(object_type, 'LOB', cnt, null)) "LOB", 
       max(decode(object_type, 'PROCEDURE', cnt, null)) "PROCEDURE",
       max(decode(object_type, 'SEQUENCE', cnt, null)) "SEQUENCE",
       max(decode(object_type, 'Materialized Views', cnt, null)) "Materialized Views",  
        max(decode(object_type, 'Materialized View Logs', cnt, null)) "Materialized View Logs", 
       max(decode(object_type, 'TYPE', cnt, null)) "TYPE",
       max(decode(object_type, 'TYPE BODY', cnt, null)) "TYPE BODY",
       max(decode(object_type, 'SYNONYM', cnt, null)) "SYNONYM",
       max(decode(object_type, 'Database Links', cnt, null)) "Database Links",
       max(decode(object_type, 'XML Schemas', cnt, null)) "XML Schemas",
       max(decode(object_type, 'XML DB Repository', cnt, null)) "XML DB Repository",              
       max(decode(object_type, 'Analytic Views', cnt, null)) "Analytic Views", 
       max(decode(object_type, 'QUEUE', cnt, null)) "QUEUE",
        max(decode(object_type, 'Queues Tables', cnt, null)) "Queues Tables"
       /* -- and so on...*/
  from (select o.owner,
               o.object_type,
               count(*) cnt
          from dba_users u, dba_objects o
           where o.owner = u.username
           and u.oracle_maintained = 'N'
           and o.owner not in('ANONYMOUS','CTXSYS','CBSNMP','EXFSYS', 'LBACSYS','MDSYS',
'MGMT_VIEW', 'LOAPSYS', 'ORDDATA', 'OWBSYS', 'ORDPLUGINS','ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'WK_TEST', 'WKSYS', 
'WKPROXY', 'WMSYS', 'XDB')
         group by owner,
                  object_type)
 group by owner;

Open in new window


on this object type: Materialized View Logs

this query also can't show that type out:

select o.owner,
       o.object_type,
       count(*)
  from dba_users u, dba_objects o
 where o.owner = u.username
and u.oracle_maintained = 'N'
and o.object_type= 'Materialized View Logs' 
and o.owner not in('ANONYMOUS','CTXSYS','CBSNMP','EXFSYS', 'LBACSYS','MDSYS',
'MGMT_VIEW', 'LOAPSYS', 'ORDDATA', 'OWBSYS', 'ORDPLUGINS','ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'WK_TEST', 'WKSYS', 
'WKPROXY', 'WMSYS', 'XDB')
 group by o.owner,
          o.object_type
 order by o.owner,
          o.object_type;

Open in new window


from SQL developer for one schema is shows out, but not by that query on THAT schema, anyone can imagine why ?

I think that one is a log only and we shoudn't consider that on mariaDB.

the point is , even from UI, the object distribution tab do not show that out too.  I think I need to ignore all this kind of weird thing.
Without getting into too much detail for you, I suggest you skip Materialized View Logs ;-)
marrowyungSenior Technical architecture (Data)

Author

Commented:
I believe so , as I keep click by mouse one by one and see it! (we go back to stone age !) not much MView, and it seems only oracle related schema has it.

BTW, I am contracting with Oracle SQL developer project manager and see how to get the most out of that tools.

the script we built here show diff result than SQL developer.

btw, really no way to know how frequently the objects reference and execute ?
not much MView, and it seems only oracle related schema has it.
MViews may also be used in other schemas, they are NOT just Oracle internal objects!

btw, really no way to know how frequently the objects reference and execute ?
Without explicit auditing (thru trigger and/or built-in auditing) -> no!

the script we built here show diff result than SQL developer.
What's the difference?
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Without explicit auditing (thru trigger and/or built-in auditing) -> no!"

ok, do you know if import will carry this information for me if source oracle has it on ?

"What's the difference?"

can't show here! I don't want to type so much ! e..g table number.
can't show here! I don't want to type so much ! e..g table number.
Seriously?!? Then we're not able to help you with that!

ok, do you know if import will carry this information for me if source oracle has it on ?
I would say NO...
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Seriously?!? Then we're not able to help you with that!"

LOL, will specify one topic if I found it is necessary to ask ! the script above already can help out 90-95%, except there are object inside oracle default schema as you said! but we will PoC that.

my point is, any uncertainty will relies on PoC.

and query we discussed should really conclude everything, agree ? I can see that already check that query already select from dba_objects, should be all that, right?

"I would say NO..."

then I can't do anything for it. what if I send a query for the control team to run again the export source, any query can check the usage statistic from the export source?
any query can check the usage statistic from the export source?
Same here: if auditing is active on that level, yes (or if there are triggers on tables for "manual" auditing)...

But let's go back to your initial questions:
1) How many Tables, Views, Procedures, Functions, Packages, Triggers, Sequences, synonyms  are there need to migrate

2) how often items in 1) has been executed

3) do we need to modify SQL statements within the application?

For 1) and 2) any script helps to find out? the script should filter out the items NOT belongs to oracle system.

for 3) I have to check embedded SQL code within the application, right ?

ad 1: we gave you some good starting points and base queries using dba_objects etc...
ad 2: depends on auditing, but I suppose, you won't be able to determine...
ad 3: YES + YES on the "embedded SQL code within the application"

Now, I'm speaking only for me (but I guess, most of the other experts here will agree):
I will NOT do the whole work for you! It's up to you to complete e.g. the queries etc. I will NOT write that entire query so that it suits your needs. IMHO, that's not the way, EE works: we will not do any homework ;-)
Geert GOracle dba
Top Expert 2009

Commented:
we will not do any homework ;-)
and we don't do any homework at your company either

and no we can't verify if the solution is valid
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Seriously?!? Then we're not able to help you with that!"

e.g. query above do not have table partition object type but from SQL developer show we have !

also from SQL developer there is an object types: Evaluation Context, which script above doesn't show at all.

the act here is combine information and try diff idea and experience myself,  I still need to work out a lot other solution instead of asking you both to remote login to my terminal to see what is going on, I have to do it myself.  here is to get the right direction.

I am also working with other SQL developer tools guys to see how much I can get from SQL developer!

People there send me links to read step by steps instruction.

"and no we can't verify if the solution is valid"

when I take both script and SQL developer together I see the diff and I verify it myself.

"ok, do you know if import will carry this information for me if source oracle has it on ?
I would say NO..."

audit trial will, has an option for export!

'INCLUDE=AUDIT_TRAILS'

this can make me import with new export DMP again! and again.

if I need you guys to remote login to it I will pay any of you .....

"ad 2: depends on auditing, but I suppose, you won't be able to determine...
"

I will try....
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks all.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial