Avatar of marrowyung
marrowyung
 asked on

preparation work for PoC procedure from oracle to MariaDB.

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 ?
Oracle DatabaseMySQL ServerSQL

Avatar of undefined
Last Comment
marrowyung

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Alex [***Alex140181***]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Kent Olsen

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
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 ?
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
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.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Geert G

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 ?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Alex [***Alex140181***]

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 ;-)
marrowyung

ASKER
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

Kent Olsen

Missing quote after ORDPLUGINS
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Alex [***Alex140181***]

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

Alex [***Alex140181***]

"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 ;-)
marrowyung

ASKER
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?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Alex [***Alex140181***]

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

Open in new window

Mark Geerlings

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.
johnsone

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
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.
Alex [***Alex140181***]

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

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 ?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Alex [***Alex140181***]

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 Geerlings

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

marrowyung

ASKER
HI Alex,

this link:

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

also applied to the MariaDB case ?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Alex [***Alex140181***]

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)
marrowyung

ASKER
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.
Alex [***Alex140181***]

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
marrowyung

ASKER
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.
marrowyung

ASKER
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 ?
marrowyung

ASKER
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)?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Alex [***Alex140181***]

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

Alex [***Alex140181***]

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

marrowyung

ASKER
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 ?
Your help has saved me hundreds of hours of internet surfing.
fblack61
marrowyung

ASKER
"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 ?
Alex [***Alex140181***]

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)...
Alex [***Alex140181***]

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
"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 ?
marrowyung

ASKER
"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.
Alex [***Alex140181***]

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 ;-)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
marrowyung

ASKER
"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 ?
Alex [***Alex140181***]

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.
Alex [***Alex140181***]

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Mark Geerlings

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.
Alex [***Alex140181***]

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 G

well, the password validation routine is stored in the sys schema ...
but you'd naturally want to rewrite that for another db type
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Alex [***Alex140181***]

the password validation routine
You mean the default ;-) (which can be replaced...)
marrowyung

ASKER
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 ?
Alex [***Alex140181***]

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
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!
marrowyung

ASKER
hi,

"Similar to PACKAGE and PACKAGE BODY"

this type shown out diff in number, so I prefer to list them out .
Alex [***Alex140181***]

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!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Alex [***Alex140181***]

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.
marrowyung

ASKER
"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 ?
Alex [***Alex140181***]

select distinct a.object_type from dba_objects a

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
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

Alex [***Alex140181***]

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
marrowyung

ASKER
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Alex [***Alex140181***]

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"
....
marrowyung

ASKER
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


 ?
Alex [***Alex140181***]

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....
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
"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!
Alex [***Alex140181***]

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', ....)
marrowyung

ASKER
"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 ?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
marrowyung

ASKER
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


?
Alex [***Alex140181***]

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.
marrowyung

ASKER
so both system DB and we should not include, and CDB and PDB is the schema name of them, right ?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Geert G

loool, distinct in a query with group by
what's the the reason for both in the same query ?
Mark Geerlings

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.
marrowyung

ASKER
"(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 ?
Your help has saved me hundreds of hours of internet surfing.
fblack61
marrowyung

ASKER
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 G

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
Alex [***Alex140181***]

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...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Alex [***Alex140181***]

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!!)
marrowyung

ASKER
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.
marrowyung

ASKER
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Alex [***Alex140181***]

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?!
marrowyung

ASKER
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?
Alex [***Alex140181***]

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???
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
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.
marrowyung

ASKER
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?
marrowyung

ASKER
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
marrowyung

ASKER
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.
Alex [***Alex140181***]

Without getting into too much detail for you, I suggest you skip Materialized View Logs ;-)
marrowyung

ASKER
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 ?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Alex [***Alex140181***]

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?
marrowyung

ASKER
"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.
Alex [***Alex140181***]

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...
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
marrowyung

ASKER
"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?
Alex [***Alex140181***]

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 G

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
"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....
marrowyung

ASKER
tks all.