Home » SQL & PL/SQL » SQL & PL/SQL » how to know all system defined table names (sql)
how to know all system defined table names [message #427671] Fri, 23 October 2009 22:03 Go to next message
sivaora
Messages: 119
Registered: October 2009
Location: Hyderabad
Senior Member
Hi all,
we are using many system defined tables like user_tables,user_views,user_triggers,all_objects,user_source .....
and so many tables reqularly.

but i don't know how many system tables are defined in oracle 9i,
if any one know's please reply me.
Re: how to know all system defined table names [message #427672 is a reply to message #427671] Fri, 23 October 2009 22:15 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE 'ALL%'

post results back here
Re: how to know all system defined table names [message #427673 is a reply to message #427671] Fri, 23 October 2009 22:36 Go to previous messageGo to next message
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
Member

Hi,

DICT is synonym of DICTIONARY view , use this to find out all the Dictionary & dynamic views.

Regards

Bala
Re: how to know all system defined table names [message #427700 is a reply to message #427673] Sat, 24 October 2009 02:44 Go to previous messageGo to next message
psingh7777
Messages: 22
Registered: August 2007
Location: New Delhi
Junior Member

You can try this

Select * from all_objects
where object_type = 'TABLE'
and owner in ('SYS','SYSTEM');

Regards
Parvinder
Re: how to know all system defined table names [message #427703 is a reply to message #427700] Sat, 24 October 2009 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not DBSNMP, DIP, DMSYS, EXFSYS, MDSYS, ORDPLUGINS, ORACLE_OCM, ORDSYS, OUTLN, SYSMAN, TSMSYS, WMSYS, XDB...?

Regards
Michel
Re: how to know all system defined table names [message #427707 is a reply to message #427700] Sat, 24 October 2009 03:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
psingh7777 wrote on Sat, 24 October 2009 09:44
You can try this

Select * from all_objects
where object_type = 'TABLE'
and owner in ('SYS','SYSTEM');

Regards
Parvinder

That will not work, since the original poster is actually referring to views, not tables.
Re: how to know all system defined table names [message #427747 is a reply to message #427707] Sat, 24 October 2009 23:11 Go to previous messageGo to next message
sivaora
Messages: 119
Registered: October 2009
Location: Hyderabad
Senior Member
By using these query's i am getting only the system tables,
but i want to know all the table names like
user_views, user_tables, user_source, all_tables,all_dependencies,
all_constraints,user_triggers,all_tab_privs_made.............

Re: how to know all system defined table names [message #427748 is a reply to message #427747] Sat, 24 October 2009 23:23 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Did you read all the posts in this thread?
Did you follow them?

By
Vamsi
Re: how to know all system defined table names [message #427750 is a reply to message #427747] Sat, 24 October 2009 23:25 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
If you can't generate your own answer, how will you know when proposed solution is correct?

SELECT TABLE_NAME FROM ALL_TABLES
UNION
SELECT VIEW_NAME FROM ALL_VIEWS


Re: how to know all system defined table names [message #427752 is a reply to message #427747] Sat, 24 October 2009 23:35 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>but i don't know how many system tables are defined in oracle 9i,
>By using these query's i am getting only the system tables,
You asked for SYSTEM tables!
Re: how to know all system defined table names [message #427755 is a reply to message #427747] Sun, 25 October 2009 01:03 Go to previous message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
sivaora wrote on Sun, 25 October 2009 05:11
By using these query's i am getting only the system tables,
but i want to know all the table names like
user_views, user_tables, user_source, all_tables,all_dependencies,
all_constraints,user_triggers,all_tab_privs_made.............


Did you try to search the Oracle documentation? Its Reference book contains list of all data dictionary views (where all "tables" mentioned by you belong).
Whole documentation is available available e.g.online on http://tahiti.oracle.com/.
Previous Topic: Find Tables Creation Date & Machine Name
Next Topic: how to set the server time in sql prompt
Goto Forum:
  


Current Time: Sun Sep 25 19:41:46 CDT 2016

Total time taken to generate the page: 0.16412 seconds