Oracle system table static data dictionary, common user View chart and its common usage

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 ...