marrowyung
asked on
is there any queries that show how many Oracle views/objects has referring Oracle system table and Synonyms
hi,
1) is there any queries that show how many Oracle views are referring Oracle system table like 'ALL_TAB_COLS', which only exists in Oracle but not MariaDB ?
As we migrating from Oracle to MariaDB, we'd like to know how many of Oracle view we need to fix once migrated to MariaDB ! this query doesn't work:
2) is there any queries that show how many object has Synonyms are using in Oracle ?
1) is there any queries that show how many Oracle views are referring Oracle system table like 'ALL_TAB_COLS', which only exists in Oracle but not MariaDB ?
As we migrating from Oracle to MariaDB, we'd like to know how many of Oracle view we need to fix once migrated to MariaDB ! this query doesn't work:
select distinct owner, name, type from dba_source where text like 'ALL_TAB_COLS';
2) is there any queries that show how many object has Synonyms are using in Oracle ?
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 CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I may be misreading this, but I think what the asker is looking for is a way to determine the system tables on Oracle that do not correspond to a system table on MariaDB and that ALL_TAB_COLUMNS is just one example of this. I don't think you will find a query to help you. On my Oracle database, there are 768 views that start with 'DBA_' (i.e. system views). I suspect that it is quite a bit more than exist on MariaDB and that doesn't even include those system tables that start with 'V$'. I have no access to MariaDB or its documentation, but making a list of its available system tables/views compared to a list of Oracle system may be a place to start the research, although requiring a substantial amount of time and effort. You might minimize the task by running just the Oracle queries of the system tables you currently use to see which ones need to be "fixed". Even then you may just find ones that cannot be replicated. Good luck!
ASKER
slightwv (䄆 Netminder),
"select distinct owner, view_name from all_views where upper(text_vc) like '%ALL_TAB_COLS%';"
tks.
johnsone,
"Why are you searching through code?"
we need to know how many % of this need to fix AFTER migrated to MariaDB. so I need to search that in any object.
"A reference in a view is a hard reference, easily found."
what is that mean?
you seems saying everything I am asking can be search thought all_dependencis table ?
One more thing as I only found out it yesterday in our convertion that, when a function has oracle UDT, our tools will remove that input as mariaDB do not support UDT.
3) so anyway to check how many oracle function has any UDT define ?
4) any way to check if there are any oracle ONLY type, view, system table inside, which MariaDB will not have? (this surely gives error when running it)
1) table.
2) View.
3) function.
4) package.
5) trigger
6) store procedure.
7) sequence.
"select distinct owner, view_name from all_views where upper(text_vc) like '%ALL_TAB_COLS%';"
tks.
johnsone,
"Why are you searching through code?"
we need to know how many % of this need to fix AFTER migrated to MariaDB. so I need to search that in any object.
"A reference in a view is a hard reference, easily found."
what is that mean?
SELECT owner,
name
FROM all_dependencies
WHERE TYPE = 'VIEW'
AND referenced_name = 'ALL_TAB_COLUMNS'
AND referenced_type = 'SYNONYM'
AND referenced_owner = 'PUBLIC';
you seems saying everything I am asking can be search thought all_dependencis table ?
One more thing as I only found out it yesterday in our convertion that, when a function has oracle UDT, our tools will remove that input as mariaDB do not support UDT.
3) so anyway to check how many oracle function has any UDT define ?
4) any way to check if there are any oracle ONLY type, view, system table inside, which MariaDB will not have? (this surely gives error when running it)
1) table.
2) View.
3) function.
4) package.
5) trigger
6) store procedure.
7) sequence.
after all that code converting, there is 1 main item which you won't have addressed ... behavior
mariadb does not behave in the same way as oracle db does
you can convert everything from one type of db to another type of db
and in the end find out that some behavior is totally different
you'll probably notice this after going live
it may even be that some functionality doesn't work at all
a product is designed based on a specific (or multiple) database types
it is a myth to think that you can just swap an expensive database type with a cheaper one
mariadb does not behave in the same way as oracle db does
you can convert everything from one type of db to another type of db
and in the end find out that some behavior is totally different
you'll probably notice this after going live
it may even be that some functionality doesn't work at all
a product is designed based on a specific (or multiple) database types
it is a myth to think that you can just swap an expensive database type with a cheaper one
A view cannot have dynamic SQL. Everything has to be known when it is defined. Therefore, ALL_DEPENDENCIES should give you all the references for VIEWs. No need to search through code.
Now, if the view selects from a function and the function uses dynamic SQL to access ALL_TAB_COLUMNS, then all bets are off, I don't think you could ever find that reference.
Now, if the view selects from a function and the function uses dynamic SQL to access ALL_TAB_COLUMNS, then all bets are off, I don't think you could ever find that reference.
ASKER
Geert G,
"mariadb does not behave in the same way as oracle db does"
We all understood this . That's why we will have a PoC process to target any problem we will see.
"it may even be that some functionality doesn't work at all"
we will test everything during our PoC process.
"it is a myth to think that you can just swap an expensive database type with a cheaper one"
we will also rebuild the application at the same time. No one think in this way sir.
johnsone,
"Now, if the view selects from a function and the function uses dynamic SQL to access ALL_TAB_COLUMNS, then all bets are off,"
why no need to worry about that ..?
"mariadb does not behave in the same way as oracle db does"
We all understood this . That's why we will have a PoC process to target any problem we will see.
"it may even be that some functionality doesn't work at all"
we will test everything during our PoC process.
"it is a myth to think that you can just swap an expensive database type with a cheaper one"
we will also rebuild the application at the same time. No one think in this way sir.
johnsone,
"Now, if the view selects from a function and the function uses dynamic SQL to access ALL_TAB_COLUMNS, then all bets are off,"
why no need to worry about that ..?
ASKER
johnsone,,
"A view cannot have dynamic SQL. Everything has to be known when it is defined. Therefore, ALL_DEPENDENCIES should give you all the references for VIEWs. "
you are trying to explain this, right?
but I don't get what the main point is.
this query return nothing, in our test platform, i am expecting '1'
but is this query only find SYNONYM ? and it returns nothing means we don't have SYNONYM ?
"A view cannot have dynamic SQL. Everything has to be known when it is defined. Therefore, ALL_DEPENDENCIES should give you all the references for VIEWs. "
you are trying to explain this, right?
SELECT owner,
name
FROM all_dependencies
WHERE TYPE = 'VIEW'
AND referenced_name = 'ALL_TAB_COLUMNS'
AND referenced_type = 'SYNONYM'
AND referenced_owner = 'PUBLIC';
but I don't get what the main point is.
this query return nothing, in our test platform, i am expecting '1'
but is this query only find SYNONYM ? and it returns nothing means we don't have SYNONYM ?
ASKER
tks all.
lol... DUH. Just corrected the SQL posted. Didn't really look that close.