Difference between revisions of "Oracle:Data Dictionary"
From mi-linux
Jump to navigationJump to search| (3 intermediate revisions by the same user not shown) | |||
| Line 23: | Line 23: | ||
!TABLE NAME!!DESCRIPTION | !TABLE NAME!!DESCRIPTION | ||
|- | |- | ||
| − | + | |USER_CONSTRAINTS||Describes information regarding the constraint on tables defined by the user. | |
|- | |- | ||
| − | + | |USER_CONS_COLUMNS||Describes information about the columns involved in constraints defined by the user. | |
|- | |- | ||
| − | + | |USER_ERRORS||Describes the errors for all triggers and procedures that the application developer has attempted to register to the database. | |
|- | |- | ||
| − | + | |USER_INDEXES||Lists the indexes that you own and those that other users have defined for tables that you own. | |
|- | |- | ||
| − | + | |USER_IND_COLUMNS||Defines the attributes involved in an index, especially useful for composite indexes. | |
|- | |- | ||
| − | + | |USER_SEQUENCES||Descriptions of all the users sequences. | |
|- | |- | ||
| − | + | |USER_SOURCE||Describes all the Procedures, Functions and Packages defined by the user, including the source code. | |
|- | |- | ||
| − | + | |USER_TABLES||Describes all the tables that you own. | |
|- | |- | ||
| − | + | |USER_TAB_COLUMNS||Describes the names and column definitions of tables that you own. | |
|- | |- | ||
| − | + | |USER_TRIGGERS||Defines all the triggers defined by the user, including the source code. | |
|- | |- | ||
| − | + | |USER_TRIGGER_COLS||Defines the columns involved for a user defined trigger. | |
|- | |- | ||
| − | + | |USER_VIEWS||Lists the subqueries that define the views you own. | |
|} | |} | ||
| Line 60: | Line 60: | ||
== Next Step == | == Next Step == | ||
| − | This completes the Miscellaneous Features section. Now return to the [[Oracle_Workbook|Workbook]]. | + | This completes the ''Miscellaneous Features'' section. Now return to the [[Oracle_Workbook|Workbook]]. |
Latest revision as of 15:49, 6 April 2016
Main Page >> Oracle and SQL >> Workbook >> Miscellaneous features >> Data Dictionary
Data Dictionary
The data dictionary describes tables, columns, indexes and other objects in the database. The dictionary is held in the form of tables that can be read like any other tables. You cannot change any data in these tables manually (i.e. Using SQL INSERT, DELETE and UPDATE statements). Oracle implicitly changes these tables based upon your actions, such as:
- Creating, deleting or updating a table
- Creating, deleting or updating a view
- Creating, deleting or updating a index
- Creating, deleting or updating a trigger
- Creating, deleting or updating a procedure, function or package
The dictionary table DICTIONARY describes the tables that make up the data dictionary.
To display the names and descriptions of dictionary tables:
SELECT * FROM DICTIONARY;
Useful dictionary tables
| TABLE NAME | DESCRIPTION |
|---|---|
| USER_CONSTRAINTS | Describes information regarding the constraint on tables defined by the user. |
| USER_CONS_COLUMNS | Describes information about the columns involved in constraints defined by the user. |
| USER_ERRORS | Describes the errors for all triggers and procedures that the application developer has attempted to register to the database. |
| USER_INDEXES | Lists the indexes that you own and those that other users have defined for tables that you own. |
| USER_IND_COLUMNS | Defines the attributes involved in an index, especially useful for composite indexes. |
| USER_SEQUENCES | Descriptions of all the users sequences. |
| USER_SOURCE | Describes all the Procedures, Functions and Packages defined by the user, including the source code. |
| USER_TABLES | Describes all the tables that you own. |
| USER_TAB_COLUMNS | Describes the names and column definitions of tables that you own. |
| USER_TRIGGERS | Defines all the triggers defined by the user, including the source code. |
| USER_TRIGGER_COLS | Defines the columns involved for a user defined trigger. |
| USER_VIEWS | Lists the subqueries that define the views you own. |
For example:
SELECT TABLE_NAME, INDEX_NAME FROM USER_INDEXES ORDER BY TABLE_NAME
This lists what indexes you have on each table.
The dictionary tables can contain a lot of information, use DESCRIBE tablename to see what columns each dictionary table has before querying them.
Next Step
This completes the Miscellaneous Features section. Now return to the Workbook.
