sql>show user; oracle查看当前用户
select * from dba_users; 查看数据库里面所有用户,前提是你是有dba权限的帐号,如sys,system
select * from all_users; 查看你能管理的所有用户!
select * from user_users; 查看当前用户信息 !
-- 查询你 当前用户下,有哪些表
SELECT * FROM user_tables
-- 查询你 当前用户下, 可以访问哪些表 [也就是访问自己 和 其他用户的]
SELECT * FROM all_tables
-- 查询当前数据库所有的表, 需要你有 DBA 的权限
SELECT * FROM dba_tables
sql>show parameter instance_name;查看当前数据库实例
通过SQLID查SQL语句:
select * from table(dbms_xplan.display_cursor('&sql_id',null,'ADVANCED'));
--查看各表占用磁盘大小 select tablename as 表名,tablesize as 表占用磁盘空间G from ( Select a.Segment_Name as tablename,Sum(a.bytes)/1024/1024/1024 as tablesize from User_Extents a ,dsg.sync_tables b where a.segment_name = b.table_name Group By Segment_Name) order by tablesize desc;
--查看当前用户占用磁盘大小 Select Sum(a.bytes)/1024/1024/1024 as 表占用磁盘空间G from User_Extents a ,dsg.sync_tables b where a.segment_name = b.table_name;
oracle查owner下的表和数据量
select owner,table_name,NUM_ROWS from dba_tables where owner='XXXX' order by 3;
mysql查看(注意用户名大小写) select table_name,table_rows from information_schema.tables where table_schema = 'xxxx' order by table_name desc;
oracle查询实例大小oracle select sum(a.bytes) from dba_segments a where a.segment_type='TABLE'
检查表情况---数据量--
oracle:
select owner,table_name,NUM_ROWS,LAST_ANALYZED from dba_tables where owner not in ( 'SYS','SYSTEM','OUTLN','MGMT_VIEW','MDSYS','ORDSYS','EXFSYS','DMSYS','DBSNMP','WMSYS','CTXSYS','ANONYMOUS','SYSMAN','XDB','ORDPLUGINS', 'SI_INFORMTN_SCHEMA','OLAPSYS','SCOTT','ORACLE_OCM','TSMSYS','MDDATA','DIP','APEX_030200','APPQOSSYS', 'DBADM','PATROL','DBMGR','DSG','JTSEC','JTDBGRPCHANGE','JTDBGRPSUPERADM','ZBYLUSR','OBSERVER', 'MCESHI','OWBSYS','OWBSYS_AUDIT','XS$NULL','READONLY','QCOAGT','SYSBACKUP','SYSDG','SYSKM','SYSRAC','SYS$UMF','DBSFWUSER','GGSYS', 'GSMADMIN_INTERNAL','GSMCATUSER','REMOTE_SCHEDULER_AGENT','AUDSYS','GSMUSER','FLOWS_FILES','APEX_050000','OJVMSYS','LBACSYS','ORDDATA', 'APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR') order by 1,2;
mysql:
select table_name,table_rows from information_schema.tables where table_schema = '数据库名' order by table_name desc;
--ORACLE下有三个视图
-- DBA_TABLES 拥有DBA角色的用户可以查看系统中的所有表
-- USER_TABLES 登录数据库的当前用户拥有的所有表
-- ALL_TABLES 登录数据库的当前用户有权限查看的所有表
查用户:select USERNAME from dba_users;
一,查看数据库里面所有用户:
select * from dba_users;
前提是你是有dba权限bai的帐号,如sys,system。
二,查看你能管理的所有用户:
select * from all_users;
三,查看当前用户信息 :
select * from user_users;
4、查询用户可以访问的视图文本
select * from dba_varrays;
5、查看用bai户或角色所拥有的角色
select * from dba_role_privs;
select * from user_role_privs;
6、查看用户或角色系统权限(直接赋值给用户或角色的系统权限)
select * from dba_sys_privs;
select * from user_sys_privs; (查看当前用户所拥有的权限)