Common SQL (iii)

select * from dba_datapump_jobs;select job_name,state from dba_datapump_jobs; select * from dba_datapump_sessions;--  View the user currently connected to the database Select distinct username  from v$session where username is not null group by username ;--  View Current database version select * from v$version--  view all tables Select * from user_ tablesselect serial#, sid from v$session;--  Display user information (owning table space) select distinct  default_tablespace,temporary_tablespace from dba_users--  Query Oracle Connections select count (*)   from v$session;--  querying the number of concurrent connections for Oracle Select count (*)  from v$session where status = ' ACTIVE ';--  view the number of connections for different users Select username,count (username)  from v$session where  username is not null group by username;--  View all users: Select * from  all_users;--  view user or role systemsPermissions (System permissions that are directly assigned to a user or role): select * from dba_sys_privs;select * from user_sys_privs;--   View the permissions contained in the role (only the roles owned by the logged-on user) select * from role_sys_privs;--  View User object permissions: select *  from dba_tab_privs;select * from all_tab_privs;select * from user_tab _privs;--  View all Roles:select * from dba_roles;--  view the roles owned by the user or role: Select * from  dba_role_privs;select * from user_role_privs;--  See which users have SYSDBA or Sysoper system permissions (requires appropriate permissions when querying) select * from v$pwfile_users;--  view the name and size of the tablespace  select t.tablespace_name, round (SUM (bytes /  (1024 * 1024)),  0)  ts_size from dba_tablespaces t,  dba_data_files d where t.tablespace_name = d.tablespace_name group by  t.tablespace_name;--  View table Space physical file name and size  select tablespace_name, file_id, file_name ,  round (Bytes /  (1024 * 1024),  0)  total_space from dba_data_files order by  tablespace_name;--  view rollback segment name and size  select segment_name, tablespace_name, r.status,   (initial_extent / 1024)  initialextent,  (next_extent / 1024)   nextextent, max_extents, v.curext curextent from dba_rollback_segs r, v$ ROLLSTAT V WHERE R.SEGMENT_ID = V.USN (+)  order by segment_name;--   View Control files  SELECT NAME FROM v$controlfile;--  View log files  SELECT MEMBER  from v$logfile;--  View tablespace Usage  select sum (bytes)  /  (1024 * 1024)  as  free_space, tablespace_name FROM dba_free_space GROUP BY tablespace_name;  select a.tablespace_name, a.bytes total, b.bytes used, c.bytes free,   (b.bytes * 100)  / a.bytes  "% used ",  (c.bytes * 100)  / a.bytes  "% free   " FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c  where a.tablespace_name = b.tablespace_name and a.tablespace_name =  c.tablespace_name;--  viewing Database Library Objects  select owner, object_type, status, count (*)   count# from all_objects group by owner, object_type, status;--  View the database version  select version from product_component_version where substr (product,  1, 6)  =  ' Oracle ';--  view the date the database was created and how it was archived  SELECT created, log_mode,  log_mode from v$database;

This article is from the "write-free" blog, please be sure to keep this source http://7156680.blog.51cto.com/7146680/1790833

Common SQL (iii)