Difference between revisions of "Oracle:Data Dictionary"
From mi-linux
Jump to navigationJump to search (Created page with "Main Page >> Oracle and SQL >> Workbook >> Miscellaneous features >> Data Dictionary == Data Dictionary == The data dictio...") |
|||
| (5 intermediate revisions by the same user not shown) | |||
| Line 20: | Line 20: | ||
== Useful dictionary tables == | == Useful dictionary tables == | ||
| − | TABLE NAME DESCRIPTION | + | {| class="wikitable" |
| − | USER_CONSTRAINTS Describes information regarding the constraint on tables defined by the user. | + | !TABLE NAME!!DESCRIPTION |
| − | 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_CONSTRAINTS||Describes information regarding the constraint on tables defined by the user. |
| − | 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_CONS_COLUMNS||Describes information about the columns involved in constraints defined by the user. |
| − | 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_ERRORS||Describes the errors for all triggers and procedures that the application developer has attempted to register to the database. |
| − | USER_TABLES Describes all the tables that you own. | + | |- |
| − | USER_TAB_COLUMNS Describes the names and column definitions of tables that you own. | + | |USER_INDEXES||Lists the indexes that you own and those that other users have defined for 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_IND_COLUMNS||Defines the attributes involved in an index, especially useful for composite indexes. |
| − | USER_VIEWS Lists the subqueries that define the views you own. | + | |- |
| + | |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: | For example: | ||
| Line 42: | Line 56: | ||
The dictionary tables can contain a lot of information, use DESCRIBE tablename to see what columns each dictionary table has before querying them. | 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 == | == Next Step == | ||
| − | This completes the Miscellaneous Features section. | + | 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.
