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 ?
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 ?
ASKER CERTIFIED SOLUTION
![Avatar of Alex [***Alex140181***]](https://filedb.experts-exchange.com/files/public/2022/11/13/8257e912-ee3c-4a93-9df0-fe9a8a1515cf.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
12c ... oracle users:
ah, yes, you don't want those ... unless some idiot created objects/tables/views in system/sys
how would you find those ?
select * from dba_users where oracle_maintained = 'YES'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/sysIn these cases, there will always be some collateral damage ;-)
how would you find those ?
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:
but I don't know why there are error message:
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;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
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;"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 ;-)
ASKER
now the result is :
the meaning should be ,e.g:
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?
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. the meaning should be ,e.g:
OR_OPSS TABLE 174is 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';
Before you run a query like this:
Please run these commands:
That will make the results much easier to see in a small window.
select owner,
object_type,
count(*)
from all_objects
where ...Please run these commands:
column owner format a30
set pagesize 999That will make the results much easier to see in a small window.
For what it's worth, the DBA_USERS.ORACLE_MAINTAINE
select * from dba_users where oracle_maintained = 'Y'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.
"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
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 ?
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;
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;ASKER
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 ?
this link: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)
https://www.convert-in.com/docs/ora2sql/intro.htm
also applied to the MariaDB case ?
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 :
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.
"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 :
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;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;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.
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.
ASKER
this one:
error shows that:
any other edition ?
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;error shows that:
Error report -
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceededany other edition ?
ASKER
so this query is for the summary page:
?
also if I add:
it will complain error:
how can I include 'Java Class' (2x word)?
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;?
also if I add:
max(decode(object_type, 'JAVA CLASS', cnt, null)) 'JAVA CLASS'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: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;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;ASKER
HI,
also by this:
I see package and package body type, are they the same ?
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;
I see package and package body type, are they the same ?
ASKER
"for the summary (2nd) to include Java classes:"
I do in this way :
"
oh this is the one to list down summary of schema and ALL object in detail ?
I got an message which show:
so can't show more than 5000 rows ?
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""
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;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. 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
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 ?
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 ?
ASKER
"https://support.oracle.com/knowledge/Middleware/2175009_1.html"
it say:
I am using SQL developer 18 already.
it say:
Upgraded from SQL Developer 3.2 which did not have the issue.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's just a running count on owner and object_type, that's all ;-)
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 ;-)
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 ?
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...
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.
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.
well, the password validation routine is stored in the sys schema ...
but you'd naturally want to rewrite that for another db type
but you'd naturally want to rewrite that for another db type
the password validation routineYou mean the default ;-) (which can be replaced...)
ASKER
by doing this again:
I see object type, there are 2 x interesting type:
1)TYPE
2) TYPE BODY
their number is different, they are not the same ?
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;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:Similar to PACKAGE and PACKAGE BODY
1)TYPE
2) TYPE BODY
their number is different, they are not the same ?
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!
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!
ASKER
hi,
"Similar to PACKAGE and PACKAGE BODY"
this type shown out diff in number, so I prefer to list them out .
"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!
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.
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?
any other object type missing ?
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;any other object type missing ?
select distinct a.object_type from dba_objects aASKER
so this will be, inside that subquery:
?
you know, I add the rest of object by referencing this on any object missing:
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?
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;
No, this
select distinct a.object_type from dba_objects aASKER
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.
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"
....
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"
....
ASKER
I use this query to check type too
:
if I remark
the SYS and SYSTEM schema will be shown, but if I un remark that, this schema will be shown too:
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:
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:
using this:
?
:
/* 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;
if I remark
and u.oracle_maintained = 'N'the SYS and SYSTEM schema will be shown, but if I un remark that, this schema will be shown too:
SYSMANis 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;
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"using this:
select distinct a.object_type from dba_objects a?
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....
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....
ASKER
"SYSMAN is also one of those internal schemas, so yes, it can also be ignored."
then I can't see why:
do not filter that out!
then I can't see why:
and u.oracle_maintained = 'N'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', ....)
In the meantime, you have to filter that (and maybe others, like APEX related) out manualy, e.g. NOT IN ('SYSMAN', ....)
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 ?
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 ?
ASKER
Kent Olsen,
what queries or URL show you that:
?
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?
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.
ASKER
so both system DB and we should not include, and CDB and PDB is the schema name of them, right ?
loool, distinct in a query with group by
what's the the reason for both in the same query ?
what's the the reason for both in the same query ?
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.
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.
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:
to make sure that none of these exists and this is all my user/application related schema ?
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
XDBto make sure that none of these exists and this is all my user/application related schema ?
ASKER
one more thing, now I am checking SQL developer and see what else was left and I see all these:
should I include any of these when scanning the schema ?
should I include any of these when scanning the schema ?
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
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!!)
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.
"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.
ASKER
how I use this script:
XML DB Repository of all schema shows empty, but from SQL developer once schema has it:
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;
XML DB Repository of all schema shows empty, but from SQL developer once schema has it:
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?!
ASKER
but I think if depends on the result from this query:
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?
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;
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???
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.
then I have to ask MariaDB on this, if they don't support,then no.
ASKER
btw, do you know if the top section of SQL developer include all object of all schema?
if it is then I just go through that part and see what is left for ALL schema, right?
if it is then I just go through that part and see what is left for ALL schema, right?
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 ?
ASKER
try now to cross check using SQL developer and this query:
on this object type: Materialized View Logs
this query also can't show that type out:
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.
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;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;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 ;-)
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 ?
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?
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.
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...
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?
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 ;-)
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
and we don't do any homework at your company either
and no we can't verify if the solution is valid
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....
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....
ASKER
tks all.
ASKER
"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 ?