Link to home
Start Free TrialLog in
Avatar of marrowyung
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:

select distinct owner, name, type from dba_source where text like 'ALL_TAB_COLS';

Open in new window


2)  is there any queries that show how many object has Synonyms are using in Oracle ?
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>dba_source does NOT include view source!

lol... DUH.  Just corrected the SQL posted.  Didn't really look that close.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!
Avatar of marrowyung

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?

SELECT owner, 
       name 
FROM   all_dependencies 
WHERE  TYPE = 'VIEW' 
       AND referenced_name = 'ALL_TAB_COLUMNS' 
       AND referenced_type = 'SYNONYM' 
       AND referenced_owner = 'PUBLIC'; 

Open in new window


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

SELECT owner, 
       name 
FROM   all_dependencies 
WHERE  TYPE = 'VIEW' 
       AND referenced_name = 'ALL_TAB_COLUMNS' 
       AND referenced_type = 'SYNONYM' 
       AND referenced_owner = 'PUBLIC'; 

Open in new window


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 ?
tks all.