CODEWITHSUNDEEP
sqloracle-databasedata-dictionary
Gaurav Soni
Gaurav Soni

Reputation: 6336

Exploring data dictionary tables usage in Oracle?

I am fresher and just started learning about database. But one thing strikes me, being a PL/SQL I should know all the data dictionary table, rather than relying on the options given in TOAD, SQL Developer. Like explain plan, search an object, locks, search a text in database and many more which we uses in daily life .

Can anyone contribute the tables or query which we can use in daily practices,rather than just clicking the button in tool, because it's not possible that everywhere we have this GUI interface to work with.

I think this will be very helpful for the people who really want to know what is working behind what option in our buttons.

For Example: The query below is use to search the string in all the database objects 

Select * 
  FROM DBA_SOURCE 
  WHERE text LIKE '%<your text >%';

Upvotes: 1

Views: 4811

Answers (3)

Big Ed
Big Ed

Reputation: 1244

You can get a good list from the following select statement:

select table_name||': '||comments from dictionary;

That lists 838 rows. The ones you would use most are probably ALL_OBJECTS, ALL_TABLES, ALL_TAB_COLUMNS, ALL_VIEWS, ALL_SOURCE, ALL_COMMENTS, and (sometimes very important) ALL_SYNONYMS.

ALL_SOURCE is a good place to find documentation for Oracle's built-in packages, because the comments in the package specification tell you everything you need to know to use them. For example, look at DBMS_SQL.

Upvotes: 1

APC
APC

Reputation: 146239

You are right: developers (and wannabe DBAs come to that) should know the Data Dictionary, rather than relying on an IDE. A good Oracle practitioner should be able to survive with just a text editor and SQL*Plus.

There are too many views to understand them all. You just need to know that they are all covered in the documentation. Find out more.

Upvotes: 4

rlobban
rlobban

Reputation: 343

there are many different uses of the data dictionary from querying package sources, to database administration.

Burleson has a few here to get you started http://www.dba-oracle.com/concepts/data_dictionary.htm

Upvotes: 2

Related Questions