data-dictionary.docVIP

data-dictionary 下面按类别列出一些ORACLE用户常用数据字典的查询使用方法。 1、用户 查看当前用户的缺省表空间 SQLselect username,default_tablespace from user_users; 查看当前用户的角色 SQLselect * from user_role_privs; 查看当前用户的系统权限和表级权限 SQLselect * from user_sys_privs; SQLselect * from user_tab_privs; 2、表 查看用户下所有的表 SQLselect * from user_tables; 查看名称包含log字符的表 SQLselect object_name,object_id from user_objects where instr(object_name,LOG)0; 查看某表的创建时间 SQLselect object_name,created from user_objects where object_name=upper(table_name); 查看某表的大小 SQLselect sum(bytes)/(1024*1024) as size(M) from user_segments where segment_name=upper(table_name); 查看放在ORACLE的内存区里的表 SQLselect table_name,cache from user_tables where instr(cache,Y)0; 3、索引 查看索引个数和类别 SQLselect index_name,index_type,table_name from user_indexes order by table_name; 查看索引被索引的字段 SQLselect * from user_ind_columns where index_name=upper(index_name); 查看索引的大小 SQLselect sum(bytes)/(1024*1024) as size(M) from user_segments where segment_name=upper(index_name); 4、序列号 查看序列号，last_number是当前值 SQLselect * from user_sequences; 5、视图 查看视图的名称 SQLselect view_name from user_views; 查看创建视图的select语句 SQLset view_name,text_length from user_views; SQLset long 2000; 说明：可以根据视图的text_length值设定set long 的大小 SQLselect text from user_views where view_name=upper(view_name); 6、同义词 查看同义词的名称 SQLselect * from user_synonyms; 7、约束条件 查看某表的约束条件 SQLselect constraint_name, constraint_type,search_condition, r_constraint_name from user_constraints where table_name = upper(table_name); SQLselect c.constraint_name,c.constrai