oracle数据字典等常用查询操作

1、查询表操作

各个权限的表：

其中表有三个权限的视图，

select count(*) from dba_tables;

select count(*) from dba_objects where object_type='TABLE'；
select count(*) from all_tables;
select count(*) from tabs;
select count(*) from user_tables;

select count(*) from tab;

以上查询结果：

2950。3028。。。78。。。87

其中tabs是user_tables的一个同义词，Oracle 的早期版本包含了一个名为TAB 的视图。该视图的功能类似于TABS，目前仍然支持该视图，因为Oracle 的产品还要引用

查询表达小：

select table_name, num_rows, avg_row_len, num_rows * avg_row_len from user_tables t
order by num_rows * avg_row_len desc;

2、视图

select count(*) from dba_views;

select count(*) from dba_objects where object_type='VIEW'
select count(*) from all_views;
select count(*) from user_views;

select count(*) from tab where tabtype='VIEW'

3、触发器

select count(*) from dba_triggers;
select count(*) from dba_objects where object_type='TRIGGER'
select count(*) from all_triggers;
select count(*) from all_objects where object_type='TRIGGER'
select count(*) from user_triggers;
select count(*) from user_objects where object_type='TRIGGER'

4、存储过程

select count(*) from dba_procedures;
select count(*) from dba_objects where object_type='PROCEDURE';
select count(*) from all_procedures;
select count(*) from all_objects where object_type='PROCEDURE';
select count(*) from user_procedures;
select count(*) from user_objects where object_type='PROCEDURE';

5、序列

select count(*) from dba_sequences;
select count(*) from dba_objects where object_type='SEQUENCE';
select count(*) from all_sequences;
select count(*) from all_objects where object_type='SEQUENCE';
select count(*) from user_sequences;
select count(*) from user_objects where object_type='SEQUENCE';

6、控制文件路径

select  name from v$controlfile;

7、日志文件路径

select member from v$logfile;

8、当前用户表空间

select username,default_tablespace from user_users;

9、查询当前用户的角色

select * from user_role_privs

10、查询当前用户权限

系统权限和表级权限

select *  from user_sys_privs;
select *  from user_tab_privs;

11、查看当前数据库归档模式

select log_mode from v$database;//并且可以查询数据库的其他一些信息，比如ID号bdid，数据库名name

archive log list；

12、查看当前数据库有几个用户连接

select username from v$session;

13、数据文件

select name,status from v$datafile;

14、表空间信息

select tablespace_name,status,contents  from dba_tablespaces;

15、显示特权用户

select 　* from v$pwfile_users;

16、显示当前连接的用户权限信息

select * from  session_privs;

17、查询表空间使用情况

select dbf.tablespace_name,
dbf.totalspace "总量(M)",
dbf.totalblocks as 总块数,
 dfs.freespace "剩余总量(M)",
 dfs.freeblocks "剩余块数",
 (dfs.freespace / dbf.totalspace) * 100 "空闲比例"
 from (select t.tablespace_name,
  sum(t.bytes) / 1024 / 1024 totalspace,
 sum(t.blocks) totalblocks
 from dba_data_files t
  group by t.tablespace_name) dbf,
  (select tt.tablespace_name,
  sum(tt.bytes) / 1024 / 1024 freespace,
  sum(tt.blocks) freeblocks
  from dba_free_space tt
 group by tt.tablespace_name) dfs
  where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)

18、查询数据库临时表空间信息

select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;

select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;--sys用户查看

19、查询某个用户多少个连接

select sid,serial#,username,program,machine,status

　　from v$session

　　where username='SHIHUA'  ---用户名

　　AND STATUS='ACTIVE';

20，查看实例状态

select status from v$instance;
STARTED - After STARTUP NOMOUNT
MOUNTED - After STARTUP MOUNT or ALTER DATABASE CLOSE
OPEN - After STARTUP or ALTER DATABASE OPEN
OPEN MIGRATE - After ALTER DATABASE OPEN { UPGRADE | DOWNGRADE }