Oracle's Sqlplus Common Command (ii)

Ii. how the user can effectively use the data dictionary

Oracle's data dictionary is one of the most important parts of a database, which is generated as a result of the database, and changes as the database changes , reflecting some of the tables and views under the SYS user. The data dictionary name is an uppercase English character.
　　
Data dictionary contains user information, user's permission information, all data object information, table constraints, statistical analysis database view and so on.

We cannot manually modify the information in the data dictionary.
Most of the time, the generalOracle users do not know how to use it effectively.
Dictionary The name and interpretation of all data dictionary tables, it has a synonymDict
Dict_column All data dictionary table field names and explanations
　　
If we want to query the data dictionary associated with the index, we can use this oneSQL statements:
Sql>select * from dictionary where InStr (comments, ' index ') >0;
If we want to knowUser_indexes the detailed meaning of each field name in the table, you can use one of the followingSQL statements:
Sql>select column_name,comments from Dict_columns where table_name= ' user_indexes ';
And so on, you can easily know the detailed name and explanation of the data dictionary without looking atOther documentation for Oracle.
Below are some of the categories listed by categoryORACLEquery usage of user's common data dictionary。
　　
1, User
　　
View the default tablespace for the current user
　　 Sql>select Username,default_tablespace fromuser_users;
　　
View the role of the current user
　　 Sql>select * from User_role_privs;
　　
View system and table-level permissions for the current user
　　 Sql>select * from User_sys_privs;
　　 Sql>select * from User_tab_privs;
2, table
　　
View all the tables under the user
　　 Sql>select * from User_tables;
　　
View name containsA table of log characters
　　 Sql>select object_name,object_id from User_objects
　　 where InStr (object_name, ' LOG ') >0;
　　
To view the creation time of a table
　　 Sql>select object_name,created from User_objectswhere object_name=upper (' &table_name ');
　　
View the size of a table
　　 Sql>select sum (bytes)/(1024*1024) as "size (M)" from user_segments
　　 where Segment_name=upper (' &table_name ');
View put inTables in the memory area of Oracle
　　 Sql>select Table_name,cache from User_tables whereinstr (cache, ' Y ') >0;
3, Index
　　
View index number and category
　　 Sql>select index_name,index_type,table_name fromuser_indexes ORDER BY table_name;
View the fields indexed by the index
　　 Sql>select * from User_ind_columns where Index_name=upper (' &index_name ');
To view the size of an index
　　 Sql>select sum (bytes)/(1024*1024) as "size (M)" from user_segments
　　 where Segment_name=upper (' &index_name ');
4, serial number
　　
View serial number,Last_number is the current value
　　 Sql>select * from User_sequences;
　　
5, Views
View the name of the view
　　 Sql>select view_name from User_views;
View that creates the view.SELECT statement
　　 Sql>set view_name,text_length from User_views;
　　 Sql>set Long 2000; Description: Can be based on the view'sText_length Value SettingThe size of the set long
　　 Sql>select text from User_views whereview_name=upper (' &view_name ');
6, synonyms
View the name of a synonym
　　 Sql>select * from user_synonyms;
　　
7, binding conditions
View constraints on a table
　　 Sql>select Constraint_name,constraint_type,search_condition, R_constraint_name
　　 From User_constraints where table_name =upper (' &table_name ');
　　 sql>selectc.constraint_name,c.constraint_type,cc.column_name
from User_constraints c,user_cons_columns cc
where C.owner = Upper (' &table_owner ') andc.table_ Name = Upper (' &table_name ')
and C.owner = Cc.owner and C.constraint_name =cc.constraint_name　　
order by Cc.position;

8 , stored functions and procedures
View the status of functions and procedures
sql>select object_name,status from User_objectswhere object_type= ' FUNCTION ';　　
sql>select object_name,status from User_objectswhere object_type= ' PROCEDURE ';

View the source code of functions and procedures
sql>select text from All_source where Owner=userand name=upper (' &plsql_name ');

Sqlplus Common commands for Oracle (ii)