【Oracle】数据字典

ORACLE数据字典

前言

    在Oracle数据库字典中，许多视图都有三个不同的实例，它们的前缀分别为"USER_"、"ALL_"及"DBA_"。

    "USER_"为前缀的数据库字典视图通常记录执行查询的帐户所拥有的对象的信息。

    "ALL_"为前缀的数据库字典视图通常记录包括执行查询的帐户所拥有的对象的信息及授权至PUBLIC的帐户用户所拥有的对象的信息。

    "DBA_"为前缀的数据库字典视图则包含所有数据库对象的信息，而不管其所有者。

    其他的字典视图中主要的是V$视图，之所以这样叫是因为他们都是以V$或GV$开头的。V$视图是基于X$虚拟视图的。V$视图是SYS用户所拥有的，在缺省状况下，只有SYS用户和拥有DBA系统权限的用户可以看到所有的视图，没有DBA权限的用户可以看到USER_和ALL_视图，但不能看到DBA_视图。与DBA_,ALL,和USER_视图中面向数据库信息相反，这些视图可视的给出了面向实例的信息。

    数据字典是Oracle存放有关数据库信息的地方，其用途是用来描述数据的。比如一个表的创建者信息，创建时间信息，所属表空间信息，用户访问权限信息等。当用户在对数据库中的数据进行操作时遇到困难就可以访问数据字典来查看详细的信息。 

    Oracle中的数据字典有静态和动态之分。静态数据字典主要是在用户访问数据字典时不会发生改变的，但动态数据字典是依赖数据库运行的性能的，反映数据库运行的一些内在信息，所以在访问这类数据字典时往往不是一成不变的。以下分别就这两类数据字典来论述。 

    select * from dict;            //查看所有数据字典

静态数据字典 

    这类数据字典主要是由表和视图组成，应该注意的是，数据字典中的表是不能直接被访问的，但是可以访问数据字典中的视图。

    静态数据字典中的视图分为三类，它们分别由三个前缀够成：user_*、all_*、 dba_*。 

user_*该视图存储了关于当前用户所拥有的对象的信息。（即所有在该用户模式下的对象）。  

all_*该试图存储了当前用户能够访问的对象的信息。（与user_*相比，all_*并不需要拥有该对象，只需要具有访问该对象的权限即可）。 

dba_*该视图存储了数据库中所有对象的信息。（前提是当前用户具有访问这些数据库的权限，一般来说必须具有管理员权限）。  

    从上面的描述可以看出，三者之间存储的数据肯定会有重叠，其实它们除了访问范围的不同以外（因为权限不一样，所以访问对象的范围不一样），其他均具有一致性。具体来说，由于数据字典视图是由SYS（系统用户）所拥有的，所以在却省情况下，只有SYS和拥有DBA系统权限的用户可以看到所有的视图。没有DBA权限的用户只能看到user_*和all_*视。如果没有被授予相关的SELECT权限的话，他们是不能看到dba_*视图的。 

 

常用

查看数据库有没有装JAVA 

select * fromdba_registry where comp_id = 'JAVAVM'; 

查看用户所有表的所有字段

select * fromuser_tab_cols;

 

查询用户底下的表大小

selectsegment_name,sum(bytes/1024/1024) from user_segments group by segment_name;

//查询用户底下的表的大小.占多少字节,sum可以不用!单位到兆M

 

根据用户名或程序名查询内容

selectowner,text from dba_source where name ='SP_A_RPT_CARD_LOAN_INFO_MIDDLE';

清空回收站

PURGErecyclebin; 

通过SID查看运行的语句

select * fromv$open_cursor where sid='XXX'

查询某个用户下有多少张表以及每张表的记录数

select owner,table_name,NUM_ROWS from  dba_tables  whereowner='PREPRODTEST' 

select TABLE_NAME,NUM_ROWS from USER_tables  where TABLE_NAME IN (SELECTUPPER(TNAME) FROM T_DESC) //把需要的表名放到一个新建的表（一个字段的表）再用这条语句查表的记录数

查看当前用户名、帐户id、帐户状态、表空间名、创建时间等

select * from user_users;  

查看当前用户拥有的所有表的信息，主要包括表名、表空间名、簇名

select * from user_tables;  

查看当前用户拥有的所有对象的信息

select object_type,status from user_objects where object_name=upper('package1');//查看当前用户拥有的所有对象的信息，

对象包括表、视图、存储过程、触发器、包、索引、序列等。该视图比user_tables视图更加全面

查看当前用户下某张表有多少个字段和类型

select * fromuser_tab_cols where table_name=upper('t_contract_product');

查看当前用户缺省的表空间

 select username,default_tablespace fromuser_users; 

查看当前用户的角色

select * fromuser_role_privs; 

查看当前用户的系统权限

select * from user_sys_privs;  

查看当前用户的表级权限

select * from user_tab_privs; 

查看当前用户对某表的权限

select * from user_tab_privs where table_name=upper('table1'); 

查看所有用户的权限

select * from dba_tab_privs;   

查看所有用户的角色  

select * from dba_role_privs;   

查看DBA权限管理下的用户

select * from dba_users;     

查看当前用户下所有的表

select * from user_tables; 

查看当前用户下名称包含log字符的表

selectobject_name,object_id from user_objects whereinstr(object_name,'LOG')>0; 

查看某表的创建时间

selectobject_name,created from user_objects where object_name=upper('&table_name'); 

查看当前用户下某表的大小

selectsum(bytes)/(1024*1024) as "size(M)" from user_segments wheresegment_name=upper('&table_name'); 

查看当前用户放在oracle内存的表

selecttable_name,cache from user_tables where instr(cache,'Y')>0; 

查看一条语句运行时间

select * fromv$session_longops

where sofar<>totalwork;

select * fromv$session_longops where time_remaining>0;//待完成大于0秒

查看表空间数据文件的路径

selecttablespace_name,file_id,bytes/1024/1024/1024,file_name 

   from dba_data_files order by file_id; 

查看等待的信息

select * fromv$session_wait

查看用户、表、表空间等相关信息

select * from dba_segments  where owner= 'DWUSER'

 

索引:

查看表的有无主键外键和主键外键的列名

select * from  USER_CONS_COLUMNS; 

查看当前用户下索引个数和类别

select index_name,index_type,table_name from user_indexes order by table_name; 

看下索引被索引的字段

select * fromuser_ind_columns where index_name=upper('&index_name'); 

查看索引的大小  

select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&index_name'); 

查看所有的索引

select * from all_indexes

视图:

查看当前用户的视图

select view_name from user_views; 

 

同义词:

查看当前用户的同义词名称

select * from user_synonyms;   

约束条件:

查看某表的约束条件

select constraint_name, constraint_type,search_condition, r_constraint_name from user_constraints where table_name = upper('&table_name'); 

表分析

analyze tabletablename compute statistics for table;//表分析，分析是为了让ORACLE优化器了解表或者索引的信息以执行正确的执行计划  

analyze tabletablename compute statistics  for allindexed columns;//索引分析

exec dbms_stats.gather_schema_stats(ownname => 'username',degree=> 4 –4个Cpu并行

分析某个用户下的所有表

begin

  DBMS_STATS.gather_schema_stats(ownname =>'dmuser', cascade => true);

end;  //分析某个用户下的所有表

 

动态数据字典 

Oracle包含了一些潜在的由系统管理员如SYS维护的表和视图，由于当数据库运行的时候它们会不断进行更新，所以称它们为动态数据字典（或者是动态性能视图）。这些视图提供了关于内存和磁盘的运行情况，所以我们只能对其进行只读访问而不能修改它们。

 

查看最近执行的SQL

select * from v$sql;

查看某个用户的详细信息.(归档日志是否打开等)

select * from V$database; 

查看数据库中锁定的数据库对象以及访问这些对象的会话对象（session对象）

select * from v$access;    

列出当前会话的详细信息

select * from v$session;  

描述当前数据库下的活动的实例的信息

select * from v$active_instances;  

列出当前会话的属性信息

select * from v$context; 

ORACLE主要的系统表和数据字典视图

索引：

1.系统表

2.数据字典视图

 

--------------------------------------------------------------------------------

 

1.系统表

ORACLE数据库的系统参数都存储在数据库中，可以通过SQLPLUS，以用户SYSYTEM进行查询。几个重要的表或者视图如下：

v$controlfile：控制文件的信息；

v$datafile：数据文件的信息；

v$log：日志文件的信息；

v$process：处理器的信息；

v$session：会话信息；

v$transaction：事务信息；

v$resource：资源信息；

v$sga：系统全局区的信息。

  

上面的视图名中的‘v$’,只是视图名字中的字符。类似于上面的视图或表还有很多，位于：$ORACLE_HOME/RDBMS/ADMIN/CATALOG.SQL文件中。

这些视图或表可以在SQLPLUS中用SELECT语句进行查询。

 

--------------------------------------------------------------------------------

 

2.数据字典视图

表和列

DBA_TABLES、ALL_TABLES和USER_TABLES显示了有关数据库表的一般信息。

DBA_TAB_COLUMNS、ALL_TAB_COLUMNS和USER_TAB_COLUMNS显示了每个数据库表的列的信息。

注意：DBA_OBJECTS、ALL_OBJECTS和USER_OBJECTS显示了模式对象的信息，包括表。

 

完整性约束

DBA_CONSTRAINTS、ALL_CONSTRAINTS和USER_CONSTRAINTS显示有关约束的一般信息。 

DBA_CONS_COLUMNS、ALL_CONS_COLUMNS和USER_CONS_COLUMNS显示有关列的相关约束的一般信息。

 

视图

DBA_VIEWS、ALL_VIEWS和USER_VIEWS。 

注意：DBA_OBJECTS、ALL_OBJECTS和USER_OBJECTS显示了模式对象的信息，包括视图。

 

序列

DBA_SEQUENCES、ALL_SEQUENCES和USER_SEQUENCES。

注意：DBA_OBJECTS、ALL_OBJECTS和USER_OBJECTS显示了模式对象的信息，包括序列。

 

同义词

DBA_SYNONYMS、ALL_SYNONYMS和USER_SYNONYMS。

注意：DBA_OBJECTS、ALL_OBJECTS和USER_OBJECTS显示了模式对象的信息，包括同义词。

 

索引

DBA_INDEXS、ALL_INDEXS、USER_INDEXS、DBA_IND_COLUMNS、ALL_IND_COLUMNS和USER_IND_COLUMNS。

 

用户

DBA_USERS。

 

角色

DBA_ROLES。

 

表空间定额

DBA_TS_QUOTAS。

 

配置表

DBA_PROFILES。

 

表空间

DBA_TABLESPACES。

 

数据文件

DBA_DATA_FILES。

 

段

DBA_SEGMENTS、USER_SEGMENT。

 

回滚段

DBA_ROLLBACK_SEGS、V$ROLLNAME、V$ROLLSTAT。