We help IT Professionals succeed at work.

how to list out all Oracle UDT and any objects contains java ?

marrowyung
marrowyung asked
on
214 Views
Last Modified: 2020-07-18
hi,

May I know how can I list down which Oracle table has user defined type ?

and how can I find out how many Oracle object defined in Java/contain Java?
Comment
Watch Question

Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
marrowyungSenior Technical architecture (Data)

Author

Commented:
it should not be able to convert to MariaDB, right? any idea
?
marrowyungSenior Technical architecture (Data)

Author

Commented:
Alex,

"May I know how can I list down which Oracle table has user defined type ?"

It list down all schema's UDT, but how can I control the schema owner?  I just want to know it for a particular schema.

""and how can I find out how many Oracle object defined in Java/contain Java?""

may I also know where it define by the result ?

johnsone,

Actually need to know the table name/resource name which contain that and I will try to take a look on each of them.

any script you can provide to provide more information like this for a schema ?
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
Then, just include the column OWNER in the SELECT list and in the GROUP BY clause ;-)
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
there is no way you could find that dependency in the database.  It doesn't exist.
It does! But you'd have to analyze and dig deep into dba_source and other objects (thru their meta data views and dbms_metadata calls)... There are some free packages and tools that might help you with that but if you want or need to do this on your own, keep in mind that it WILL BE a LOT of work...
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Not if the call comes from outside the database.  That dependency doesn't exist in the database.
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
That's right. If you're making a call from, let's say a Java EE application into Oracle, then there's no way to track that from within the database.
marrowyungSenior Technical architecture (Data)

Author

Commented:
Alex,

"If you're making a call from, let's say a Java EE application into Oracle, then there's no way to track that from within the database."

you mean is the SP or call code is store in Application itself, right ?

"There are some free packages and tools that might help you with that"

no. tools is good, any suggestion ?

"keep in mind that it WILL BE a LOT of work..."

must mean all eyeball work..

johnsone,

"It only shows hard dependencies."

is that mean only dependencies in LOCAL oracle server ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
Alex,

"then, just include the column OWNER in the SELECT list and in the GROUP BY clause ;-)"

Which one you are referring to ? Oracle object defined in Java/contain Java?

has to add dba_tables in the from <table> list ?
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
you mean is the SP or call code is store in Application itself, right ?
????

I think, I made it quite clear...
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
Which one you are referring to ? Oracle object defined in Java/contain Java?

To virtually any dba_xxx metadata view you're using! If you need to group by owner, you'll have to include it in the corresponding clauses!
marrowyungSenior Technical architecture (Data)

Author

Commented:
marrowyungSenior Technical architecture (Data)

Author

Commented:
Alex [***Alex140181***],

select a.object_type,
       count(*)
  from dba_objects a
 where a.object_type like '%JAVA%'
 group by a.object_type;

Open in new window


this is for all type of objects, right ? this is good but how about if I only want tables?
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
You can use any of these types to filter on in the where clause:
select distinct a.object_type
  from dba_objects a;

Open in new window


but how about if I only want tables
Please clarify that, I cannot follow, sorry ;-)
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Please clarify that, I cannot follow, sorry ;-)"

what i mean is I only want table type, not all type of oracle object ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
Alex [***Alex140181***],

I use this one to check tables has UDT:

select distinct a.owner,
                a.table_name
  from dba_tables a
  join dba_tab_columns b
    on (b.owner = a.owner and b.table_name = a.table_name)
  left join dba_types c
    on (c.owner = b.owner and c.type_name = b.data_type)
 where lnnvl(c.predefined = 'YES')
 order by a.owner,
          a.table_name;

Open in new window


this is one of the table shown up in the result and I don't see any UDT here, please advice.

  CREATE TABLE "YOAP_PANEL_RECOM_CRT_STAT_J1" 
   (	"I_CREATED_BY_USR" VARCHAR2(25 CHAR), 
	"I_CREATED_BY_WPASS" NUMBER(10,0), 
	"I_CREATE_DT" DATE, 
	"I_CRT_CASE_NO" VARCHAR2(30 CHAR), 
	"I_CRT_DISPOSAL" VARCHAR2(5 CHAR), 
	"I_LST_UPD_BY_USR" VARCHAR2(25 CHAR), 
	"I_LST_UPD_BY_WPASS" NUMBER(10,0), 
	"I_LST_UPD_DT" DATE, 
	"I_PANEL_RECOM" VARCHAR2(5 CHAR), 
	"I_REC_ID" NUMBER(10,0), 
	"I_REP_MTH" DATE, 
	"JID" NUMBER(10,0) NOT NULL ENABLE, 
	"TID" NUMBER(10,0) NOT NULL ENABLE, 
	"JNL_TYPE" VARCHAR2(1 CHAR) NOT NULL ENABLE, 
	"TXN_TIMESTAMP" TIMESTAMP (6) NOT NULL ENABLE, 
	 CONSTRAINT "YOAP_PANEL_RECOM_CRT_STA_J1$PK" PRIMARY KEY ("JID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 

Open in new window

Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
Use this:
select distinct a.owner,
                a.table_name,
                b.column_name
  from dba_tables a
  join dba_tab_columns b
    on (b.owner = a.owner and b.table_name = a.table_name)
  join dba_types c
    on (c.type_name = b.data_type)
 where c.predefined = 'NO'
 order by a.owner,
          a.table_name;

Open in new window

marrowyungSenior Technical architecture (Data)

Author

Commented:
tks.

but what is it for :

select distinct a.owner,
                a.table_name
  from dba_tables a
  join dba_tab_columns b
    on (b.owner = a.owner and b.table_name = a.table_name)
  left join dba_types c
    on (c.owner = b.owner and c.type_name = b.data_type)
 where lnnvl(c.predefined = 'YES')
 order by a.owner,
          a.table_name;

Open in new window


this condition is diff:

lnnvl(c.predefined = 'YES')
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
lnnvl(c.predefined = 'YES')  returns true in where clause when c.predefined is either NULL or is NOT equal 'YES'

Nevertheless, I just corrected my SQL from my first comment, so...
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks.

as this one is the first query you shown me before

select distinct a.owner,
                a.table_name
  from dba_tables a
  join dba_tab_columns b
    on (b.owner = a.owner and b.table_name = a.table_name)
  left join dba_types c
    on (c.owner = b.owner and c.type_name = b.data_type)
 where lnnvl(c.predefined = 'YES')
 order by a.owner,
          a.table_name;

Open in new window


and I think it search for ALL objects has UDT ?

so any query will search it for all oracle objects?

please help on this.
https://www.experts-exchange.com/questions/29142557/is-there-any-queries-that-show-how-many-Oracle-views-objects-has-referring-Oracle-system-table-and-Synonyms.html?headerLink=workspace_open_questions
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
and I think it search for ALL objects has UDT ?
As I said: I corrected it! Please forget about the very first one!

so any query will search it for all oracle objects?
Maybe this one helps:
select distinct a.owner,
                a.object_name,
                a.object_type
  from dba_objects a
  join dba_types c
    on (c.type_name = a.object_type)
 where c.predefined = 'NO'
   and c.type_name <> 'JOB'
 order by a.owner,
          a.object_name,
          a.object_type;

Open in new window

marrowyungSenior Technical architecture (Data)

Author

Commented:
when I run the second one:

select distinct a.owner,
                a.table_name,
                b.column_name
  from dba_tables a
  join dba_tab_columns b
    on (b.owner = a.owner and b.table_name = a.table_name)
  join dba_types c
    on (c.type_name = b.data_type)
 where c.predefined = 'NO'
 order by a.owner,
          a.table_name;

Open in new window


it return this:

Error report -
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Open in new window


what should I do/alter ?


and this one:

select distinct a.owner,
                a.object_name,
                a.object_type
  from dba_objects a
  join dba_types c
    on (c.type_name = a.object_type)
 where c.predefined = 'NO'
   and c.type_name <> 'JOB'
 order by a.owner,
          a.object_name,
          a.object_type;

Open in new window


return no result, any filter we should change to make more result comes out ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

now I use this one to check which table has UDT.


select distinct a.owner,
                a.table_name,
                b.column_name
  from dba_tables a
  join dba_tab_columns b
    on (b.owner = a.owner and b.table_name = a.table_name)
  join dba_types c
    on (c.type_name = b.data_type)
 where c.predefined = 'NO' and a.owner= ' AAAB'
 order by a.owner,
          a.table_name;

Open in new window


is that right I should filter out table I only want, in this case the schema is 'AAAB'  ?
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
is that right I should filter out table I only want, in this case the schema is 'AAAB'  ?
This is up to you! It's not something, we EE members can decide!

ORA-01652
Maybe, you have to extend the TEMP tablespace...
http://www.dba-oracle.com/sf_ora_01652_unable_to_extend_temp_segment_by_string_in_tablespace_string.htm
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

"d a.owner= ' AAAB'"

I am not sure if I should use a.owner or b.owner. result is not consistent to what I can verify.
marrowyungSenior Technical architecture (Data)

Author

Commented:
sir,

I found this one:

select distinct a.owner,
                a.table_name,
                b.column_name
  from dba_tables a
  join dba_tab_columns b
    on (b.owner = a.owner and b.table_name = a.table_name)
  join dba_types c
    on (c.type_name = b.data_type)
 where c.predefined = 'NO'
 order by a.owner,
          a.table_name;

Open in new window


returns information of all objects but not just table !

it also show function as table too.
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

the second one:

select distinct a.owner,
                a.table_name,
                b.column_name
  from dba_tables a
  join dba_tab_columns b
    on (b.owner = a.owner and b.table_name = a.table_name)
  join dba_types c
    on (c.type_name = b.data_type)
 where c.predefined = 'NO'
 order by a.owner,
          a.table_name;

Open in new window


I tried to add the schema condition like :

select distinct a.owner,
                a.table_name,
                b.column_name
  from dba_tables a
  join dba_tab_columns b
    on (b.owner = a.owner and b.table_name = a.table_name)
  join dba_types c
    on (c.type_name = b.data_type)
 where c.predefined = 'NO' and a.owner='<schema>'
 order by a.owner,
          a.table_name;

Open in new window


this one return even no record even the schema I want exists in the result set from your second query, which i post here too.
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
marrowyungSenior Technical architecture (Data)

Author

Commented:
" and the desired owner can be found in the results from the 1st one!"

it can !

so why second one can't filter that out  ?
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

is this query good ?

select distinct a.owner,
                a.table_name,
                b.column_name
  from dba_tables a
  join dba_tab_columns b
    on (b.owner = a.owner and b.table_name = a.table_name)
  join dba_types c
    on (c.type_name = b.data_type)
 where c.predefined = 'NO' and b.owner= 'DEV_UMS' and c.type_name <> 'JOB'
 order by a.owner,
          a.table_name;
 

Open in new window


I add owner as the filter and object MUST not an oracle job ?

and how to filter out SYS related objects ?

and b.owner <> 'SYS%'

Open in new window


?
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
and b.owner <> 'SYS%'

Open in new window

This will not work! It must read
and b.owner not like 'SYS%'

Open in new window

marrowyungSenior Technical architecture (Data)

Author

Commented:
tks.

this is the hints I like .
marrowyungSenior Technical architecture (Data)

Author

Commented:
I found the one to search java do not returns objects name containing java, is it possible ?

this is the output from the 2 x queries:

OBJECT_TYPE               COUNT(*)
----------------------- ----------
JAVA DATA                      413
JAVA RESOURCE                 1733
JAVA CLASS                   38209
JAVA SOURCE                      2


REFERENCED_TYPE       COUNT(*)
------------------- ----------
JAVA CLASS               83378

Open in new window


which one is about table ?
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
marrowyungSenior Technical architecture (Data)

Author

Commented:
"What if I name a table "JAVA_TEST"?! Do you really want this to occur in your result set?! I doubt that!"


yes, so that I can check that table in the result set one by one and see what it is, how much java content it is.

e..g if I wan to check how many function has java code, then what should I do ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
actually I can get the same result set as the query you gave by:

select distinct owner, object_name, object_type from dba_objects where object_type LIKE '%JAVA%' and owner = 'CISUAT';

Open in new window


however, object name containing java code is not easy I know.

e.g. how many SP and function contain java code?

our migration tools can't handle java conversion to MariaDB, that's why we'd like to know how many /percentage of these and we measure workload to convert/rewrite it in mariaDB format or in application tier.

hope you understand.
Software Developer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Technically not possible!"

good statement,

this mean has to eyeball that.

this one return no record, seems lucky?

select *
  from user_source a
 where upper(a.text) like upper('%language%java%') escape '\';

Open in new window

Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
this one return no record, seems lucky?
For the user/schema executing that statement: yes!
For other users: ?

this mean has to eyeball that.
Good luck :-) It is NOT possible!!
however, object name containing java code is not easy I know.
Again: an object name can never contain java code!!
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Again: an object name can never contain java code!!"

from oracle SQL developer what I can see is, from java console I see this:

java console
so if it is, then usually why someone store java code inside Oracle DB?  what is it for ?
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
then usually why someone store java code inside Oracle DB?  what is it for ?
Seriously?!
Maybe to execute Java inside the database?!

Btw: I will not comment any further until you re-open this one!
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Btw: I will not comment any further until you re-open this one!"

I am sorry that I think this ticket will auto reopen if I input comment . how ...  ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
administrator please re open this question .
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

a helper said I can unclick the use this as the solution tab to reopen this ticket.
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

but seems now ticket already reopened ?


just untick the "yes, this is my solution?" ?
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.