Data dictionaries in Oracle have static and dynamic points. The static data dictionary is not changed when the user accesses the data dictionary, but the Dynamic Data dictionary is dependent on the performance of the database running, reflecting some intrinsic information of the database running, so it is often not invariable to access such data dictionary.
Static data dictionaries consist primarily of tables and views, and it should be noted that the tables in the data dictionary are not directly accessible, but can access views in the data dictionary. The views in the static data dictionary are grouped into three categories, which are made up of three prefixes: user_*, all_*, and dba_*.
This article mainly introduces user's common view and its usage. the user's common view has user_objects information User_source All resource object information of the database user user_tables the user's Table object information user_tab_columns the user's table column information user_ Constraints the user's object constraint information User_tab_privs The current user's object permission information user_ind_columns the user's index corresponding to the table column information user_clusters all the user's cluster information user_clu_columns The user's cluster contains content information user_cluster_hash_expressions the information of the hash cluster User_user
Mainly describes the current user's information, including the current user name, account ID, account status, table space name, creation time, and so on. For example, you can return this information by executing the following command. user_objects (User object information)
Describes information about all objects owned by the current user, including tables, views, stored procedures, triggers, packages, indexes, sequences, and so on. This view is more comprehensive than the User_tables view.
Field name |
explain |
object_name |
Object Name |
object_id |
Object ID |
Created |
The time the object was created |
Last_ddl_time |
Related to an object (not necessarily a change in the structure of an object, or other correlation, such as if the object is a table, assigning the SELECT permission of that table to another user will also change the value of that field) the most recent time to execute the DDL operation |
Timestamp |
The time when an object's own structure changes, such as deleting a field, adding a field, and so on |
Object_type |
Object type |
Status |
State |
...... |
|
user_tables (User's Table object information)
It mainly describes the information of all the tables owned by the current user, including table name, table space name, cluster name and so on. This view makes clear what tables the current user can manipulate.
Field name |
explain |
table_name |
Table name |
Tablespace_name |
|
Cluster_Name |
|
Num_rows |
Number of rows in a table |
STATUS |
|
Pct_free |
|
Pct_used |
|
Ini_trans |
|
Max_trans |
|
Initial_extent |
|
...... |
|
user_tab_comments (user's table comment)
Field name |
explain |
table_name |
Table name or view name |
Table_type |
Type (Table/view) |
Comments |
Comments that have been entered for this column |
user_col_comments (User's table column information)
Field name |
explain |
table_name |
Table name or view name |
column_name |
Column Name |
Comments |
Comments that have been entered for this column |
User_sys_privs (System permission information for the current user)
User_tab_privs (object permission information for the current user)
This view primarily stores permissions information for all tables under the current user. The current user's permissions on the table make it clear what actions can be performed and which cannot be performed. user_col_privs table column permission information for the current user user_role_privs role permission information for the current user user_indexes User's index information user_ind_columns table column information for the user's index user_constraints User's object constraint information user_cons_columns table column information for user constraints
User_constraints is a view of table constraints, describing what the constraint type (Constraint_type) is, what tables (table_name) it belongs to, and if the constraint is of type R (foreign key), then R_constraint_ The Name field holds the name of the primary KEY constraint in the referenced primary table.
User_cons_columns is a view of table-constrained fields that describes which constraints are involved in the columns associated with the constraint in the table. These constraints have primary key constraints, foreign key constraints, and index constraints.
Both can be associated with (owner,constraint_name,table_name) common usage
The use of data dictionary, auxiliary Development and implementation, is extremely good ~ ~ ~
Lazy must learn, especially do data migration, batch processing data or data table large scope rectification. 1, Backup table comments
Select ' Comment on table ' | | table_name| | ' is
' ' | | comments| | '; ' from user_tab_comments where comments was not
null;
2, back up all the field notes
Select ' Comment on column ' | | table_name| | '. ' |
| column_name| | ' is ' ' | | comments| | '; '
From user_col_comments where comments are not null;
3. Query primary key/foreign key
Select C.constraint_name index_name,
decode (t.constraint_type, ' P ', 1, 0) type,
c.table_name,
c.column_ Name
from User_constraints T, user_cons_columns c
where c.constraint_name = T.constraint_name and
c.column _name not as ' sys_nc%$ '
and T.constraint_type in (' P ', ' U ');
Where the value of Constraint_type is p when the primary key, that is, the primary key, R represents referential AKA Foreign key, that is, the foreign key, u represents the unique key, that is, unique keys. 4. Convert the field to uppercase
Select ' ALTER TABLE ' | | T.table_name | | ' Rename column '
| | | column_name | | ' to ' | | Upper (column_name) | | '";'
From User_tab_columns t;
5, view the status of an object
Note that the use of upper, all the objects in the data dictionary are uppercase, and pl/sql is not case sensitive, so in the actual operation must pay attention to case matching. 6. Clob Turn blob
First, a CLOB field name +_1 field is built to replace
Select ' ALTER TABLE ' | | table_name | | |
column_name| | ' _1 blob; ' from User_tab_columns where
data_type= ' CLOB ';
Then use a conversion method to convert the data from the Clob field to a blob and save it to a new field
Select ' Update ' | | table_name| | ' Set ' | | column_name | |
' _1=clob_to_blob (' | | | column_name | | '); ' from
user_tab_columns where data_type= ' Clob ';
This is the time to remove the original Clob field.
Select ' ALTER TABLE ' | | table_name | | ' drop column
' | | column_name| | ' from User_tab_columns where
data_type= ' CLOB ';
Finally change the new field to the same name as the original field, that is, remove the _1
Select ' ALTER TABLE ' | | table_name| | ' rename column
' | | column_name| | ' to ' | | Replace (column_name, ' _1 ', ') | |
From User_tab_columns where data_type= ' BLOB ';
7, view the current user's default table space
Select Username,default_tablespace from User_users;
8, view the name contains log characters of the object
Sql>select object_name,object_id from User_objects
Subsequent use of the continuous supplement ...