Home » SQL & PL/SQL » SQL & PL/SQL » SQL SELECT DOUBT ORA 9i
SQL SELECT DOUBT ORA 9i [message #241657] Wed, 30 May 2007 05:53 Go to next message
balamurugan_edp
Messages: 19
Registered: May 2007
Junior Member
I have a table named as DEPARTMENT in the user MASTERS.

I have created 3 views of MASTERS.DEPARTMENT in other 3 users
as below

USER: MASTERS TABLE: DEPARTMENT

USER: HLOG0708 VIEW: DEPARTMENT
USER: SLOG0708 VIEW: DEPARTMENT
USER: PAYROLL VIEW: DEPARTMENT

I want to know is there any way to listdown all the username which are using the view of the source table.

I know the following query will delete all the references

drop table department cascade constraints;

but first, I want to listdown all the reference tables;

Kindly help me.

Thanks
Bala
Re: SQL SELECT DOUBT ORA 9i [message #241660 is a reply to message #241657] Wed, 30 May 2007 06:10 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi ,

don't open new topics for the same question

you have already opened two. one which is being deleted by the moderators. now again a third one..
so you can continue in this one

(edited: after franks reply)

[Updated on: Wed, 30 May 2007 06:49]

Report message to a moderator

Re: SQL SELECT DOUBT ORA 9i [message #241675 is a reply to message #241660] Wed, 30 May 2007 06:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
... and removed the other one as well.

Continue in this thread please.
Re: SQL SELECT DOUBT ORA 9i [message #241677 is a reply to message #241657] Wed, 30 May 2007 06:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dba/all/user_constraints

Regards
Michel
Re: SQL SELECT DOUBT ORA 9i [message #241925 is a reply to message #241657] Thu, 31 May 2007 01:27 Go to previous messageGo to next message
balamurugan_edp
Messages: 19
Registered: May 2007
Junior Member
Is there any queries to assit my doubt?
please help me in the above question!

Thanks
Bala
Re: SQL SELECT DOUBT ORA 9i [message #241928 is a reply to message #241925] Thu, 31 May 2007 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did you try from what we said?

Regards
Michel
Re: SQL SELECT DOUBT ORA 9i [message #241932 is a reply to message #241657] Thu, 31 May 2007 02:05 Go to previous messageGo to next message
balamurugan_edp
Messages: 19
Registered: May 2007
Junior Member
sir, Thanks for ur kind reply

i am new to this; i dont know, how to workout with your idea;
kindly let me know what to do;

is there any QUERY available to clear my doubt;


Thanks
Bala
Re: SQL SELECT DOUBT ORA 9i [message #241937 is a reply to message #241932] Thu, 31 May 2007 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok, start with the beginning.

Do you know how to get the description of a table or view?
Do you know how to write a SELECT?

Regards
Michel
Re: SQL SELECT DOUBT ORA 9i [message #241940 is a reply to message #241937] Thu, 31 May 2007 02:18 Go to previous messageGo to next message
balamurugan_edp
Messages: 19
Registered: May 2007
Junior Member
Yes, i know how to write select and
description of the view/table

desc department;

select * from department;


what is dba/all/constraints?

Thanks
Bala

[Updated on: Thu, 31 May 2007 02:42]

Report message to a moderator

Re: SQL SELECT DOUBT ORA 9i [message #241945 is a reply to message #241940] Thu, 31 May 2007 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So connect as MASTER and desc user_constraints.

Check documentation: ALL_CONSTRAINTS.

That's for your question: "listdown all the reference tables" (assuming they are in the same schema).

Now for "listdown all the username which are using the view of the source table", connect as a DBA and do the same thing with DBA_DEPENDENCIES.

Regards
Michel
Re: SQL SELECT DOUBT ORA 9i [message #241964 is a reply to message #241657] Thu, 31 May 2007 04:07 Go to previous message
balamurugan_edp
Messages: 19
Registered: May 2007
Junior Member
how to use
dba/all/constraints?


sorry my page not refreshing; so that i thought that i dont have any replys from u;
now i will go through it and ask u the doubt
thanks for ur coordination
Thanks
Bala

[Updated on: Thu, 31 May 2007 04:09]

Report message to a moderator

Previous Topic: Get a field from a Record Type (merged)
Next Topic: Dynamic SQL In Stored Procedures
Goto Forum:
  


Current Time: Sat Dec 10 03:27:03 CST 2016

Total time taken to generate the page: 0.09411 seconds