2022年oracle基本查看包括表,用户,索引,视图,表空间,约束等 .pdf

查看当前用户每个表占用空间的大小：Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name 查看每个表空间占用空间的大小：Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name 查看Oracle 当前用户下的信息（用户，表视图，索引，表空间，同义词，存储过程函数，约束条件）0、表空间SQLselect username,default_tablespace from user_users;查看当前用户的角色SQLselect*from user_role_privs;查看表空间select*from dba_tablespaces 查看表空间文件路径select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;查看用户和默认表空间的关系select username,default_tablespace from dba_users;查看当前用户的系统权限和表级权限SQLselect*from user_sys_privs;SQLselect*from user_tab_privs;名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 5 页 -查看用户下所有的表SQLselect*from user_tables;1、用户查看当前用户的缺省表空间SQLselect username,default_tablespace from user_users;查看当前用户的角色SQLselect*from user_role_privs;查看当前用户的系统权限和表级权限SQLselect*from user_sys_privs;SQLselect*from user_tab_privs;显示当前会话所具有的权限SQLselect*from session_privs;显示指定用户所具有的系统权限SQLselect*from dba_sys_privs where grantee=GAME;2、表查看用户下所有的表SQLselect*from user_tables;SELECT*FROM ALL_TABLES;查看名称包含log字符的表SQLselect object_name,object_id from user_objects 名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 5 页 -where instr(object_name,LOG)0;查看名称包含log字符的字段SQLselect*from all_col_comments where column_name=log Or column_name=LOG;查看某表的创建时间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);名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 5 页 -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;SELECT*FROM ALL_SYSNONYMS;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.constraint_type,cc.column_name from user_constraints c,user_cons_columns cc where c.owner=upper(&table_owner)and c.table_name=upper(&table_name)and c.owner=cc.owner and c.constraint_name=cc.constraint_name order by cc.position;名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 5 页 -8、存储函数和过程查看函数和过程的状态SQLselect object_name,status from user_objects where object_type=FUNCTION;SQLselect object_name,status from user_objects where object_type=PROCEDURE;查看函数和过程的源代码SQLselect text from all_source where owner=user and name=upper(&plsql_name);select name from v$datafile 名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 5 页 -