.comment-link {margin-left:.6em;}
Marc Boizeau's blog
Thursday, January 13, 2005
  Oracle data dictionary
Hello everybody (Happy coding and merry source code for 2005! )

Today I would like to talk about Oracle data Dictionary.
Without any administration tool it's possible to retrieve information about tables,views and constraints.
To understand this article you need an Oracle client connected to a valid schema.
(SQL+ for instance)

First I suggest you to try this syntax:
select * from DICT
This query returns all the views dealing with this schema's dictionary, very useful to retrieve the exact name of a view. The dictionary contains a big amount of views, let's focus on a few one:

Imagine you want to list all the tables you can acces in a schema, you may then try the following:
SELECT * FROM ALL_TABLES

If you're not a DBA but a developer you may just be interested in your own tables. So try this one:
SELECT * FROM ALL_TABLES
WHERE OWNER ='My_name'


The same thing about views:
SELECT * FROM ALL_VIEWS
WHERE OWNER ='My_name'


Now imagine you need to know all the constraints of a table :
SELECT * FROM ALL_CONSTRAINTS
WHERE
table_name= 'My_table'


That is useful, but you may want something more fine tunned:
What are the foreign keys?:
SELECT * FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_TYPE= 'R'
and table_name= 'My_table'


What is the primary key? :
SELECT * FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_TYPE= 'P'
and table_name= 'My_table'


That's interesting but most of the time you need the name of a field. So, the following retrieves the list of all the fields included in the primary key:
SELECT COLUMN_NAME FROM ALL_CONS_COLUMNS CC, all_constraints C
where CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME
and C.TABLE_NAME= 'My_Table'
and C.CONSTRAINT_TYPE='P'


That's all folks !
Hope this helps, comments are welcome!
 
Comments: Post a Comment

Links to this post:

Create a Link



<< Home
You are a developer and work with Oracle and Microsoft technologies? Have a look!
ATOM
How to:
Use updatable views in Access
Get data in Excel from Oracle 1
Get data in Excel from Oracle 2
Draw the Mandelbrot set using C#
Use the "Grouping Sets" SQl Syntax
Use the "Rollup" SQl Syntax
Use the "Rank over" SQl Syntax

Code generation with .net
Change all querytable in an Excel File
My Wiki contributions
WIKIWIKI
Connect a .Net application to an Oracle Database 1...
Mandelbrot in C# , the sources!
Mandelbrot in C#
SQL Analytical : Rollup
Just another link today
A few Oracle and Microsoft links
ARCHIVES
October 2004 / November 2004 / December 2004 / January 2005 / February 2005 / March 2005 / April 2005 / June 2005 / July 2005 / August 2005 / September 2005 / December 2005 / February 2006 / December 2006 / March 2009 /


Powered by Blogger

mboizeau.free.fr