查看數據庫和數據字典的SQL

一、用戶

     查看當前用戶的缺省表空間
     SQL>select username,default_tablespace from user_users;

查看當前用戶的角色
SQL>select * from user_role_privs;

查看當前用戶的系統權限和表級權限
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;

    二、表
    
     查看用戶下所有的表
     SQL>select * from user_tables;
        
     查看名稱包含log字符的表
     SQL>select object_name,object_id from user_objects
         where instr(object_name,''LOG'')>0;
    
     查看某表的創建時間
     SQL>select object_name,created from user_objects where object_name=upper(''&table_name'');
    
     查看某表的大小
     SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
         where segment_name=upper(''&table_name'');
    
     查看放在Oracle的內存區裡的表 
     SQL>select table_name,cache from user_tables where instr(cache,''Y'')>0;
    
    三、索引
   
     查看索引個數和類別
     SQL>select index_name,index_type,table_name from user_indexes order by table_name;
    
     查看索引被索引的字段
     SQL>select * from user_ind_columns where index_name=upper(''&index_name'');
    
     查看索引的大小
     SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
         where segment_name=upper(''&index_name'');
    
    四、序列號
   
     查看序列號，last_number是當前值
     SQL>se