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_*视图的。

常用

杀 job 运行的进程：

SELECT *FROM DBA_JOBS_RUNNING;

SELECT *FROM V$SESSION WHERE SID=326;

ALTERSYSTEM KILL SESSION '326,176';//通过sid 查询 SERIAL#

查看表空间详细

SELECTUPPER(F.TABLESPACE_NAME) "表空间名",

D.TOT_GROOTTE_MB "表空间大小(M)",

D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",

TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) /D.TOT_GROOTTE_MB * 100,

2),

'990.99')"使用比",

F.TOTAL_BYTES "空闲空间(M)",

F.MAX_BYTES "最大块(M)"

FROM(SELECT TABLESPACE_NAME,

ROUND(SUM(BYTES) / (1024 * 1024), 2)TOTAL_BYTES,

ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES

FROMSYS.DBA_FREE_SPACE

GROUP BYTABLESPACE_NAME) F,

(SELECTDD.TABLESPACE_NAME,

ROUND(SUM(DD.BYTES) / (1024 * 1024), 2)TOT_GROOTTE_MB

FROMSYS.DBA_DATA_FILES DD

GROUP BYDD.TABLESPACE_NAME) D

WHERED.TABLESPACE_NAME = F.TABLESPACE_NAME

ORDER BY 4DESC;

查看数据库有没有装JAVA

select *from dba_registry where comp_id = 'JAVAVM';

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

select *from user_tab_cols;

查询用户底下的表大小

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

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

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

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

清空回收站

PURGErecyclebin;

通过 SID 查看运行的语句

select *from v$open_cursor where sid='XXX'

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

selectowner,table_name,NUM_ROWS from dba_tables whereowner='PREPRODTEST'

selectTABLE_NAME,NUM_ROWS from USER_tables where TABLE_NAME IN

(SELECTUPPER(TNAME) FROM T_DESC) //把需要的表名放到一个新建的表（一个字段的表）在用这条语句查。表的记录数

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

select *from user_users;

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

select *from user_tables;

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

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

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

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

select *from user_tab_cols wheretable_name=upper('t_contract_product');

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

selectusername,default_tablespace from user_users;

查看当前用户的角色

select *from user_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 dba_temp_files;

查看临时表空间的大小和空闲

Selectround((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "totalMB",

round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used,0)) / 1024 / 1024,

2) "FreeMB",

d.file_name "Datafile name",

round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) "UsedMB",

round((f.bytes_free + f.bytes_used) / 1024, 2) "totalKB",

round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used,0)) / 1024,

2) "FreeKB",

round(nvl(p.bytes_used, 0) / 1024, 2) "Used KB",

0"Fragmentation Index"

fromSYS.V_$TEMP_SPACE_HEADER f,

DBA_TEMP_FILES d,

SYS.V_$TEMP_EXTENT_POOL p

wheref.tablespace_name(+) = d.tablespace_name

andf.file_id(+) = d.file_id

andp.file_id(+) = d.file_id;

查看表空间的最大值大小和最大使用过多少

select *from dba_data_files;

查看临时表空间 temp

select *from v$sort_segment;

查看数据库版本。启动时间等等

SELECT *FROM v$instance;

查看当前用户下所有的表

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 whereinstr(cache,'Y')>0;

查看哪张表被锁

SELECT *FROM v$session_wait t WHERE t.EVENT LIKE '%library cachepin%';

查看哪张表被锁

Select *from v$locked_object;

查看锁

selectV$SESSION.sid,v$session.SERIAL#,v$process.spid,

rtrim(object_type) object_type,rtrim(owner) || '.' ||object_name object_name,

decode(lmode, 0, 'None',

1,'Null',

2,'Row-S',

3,'Row-X',

4,'Share',

5,'S/Row-X',

6,'Exclusive', 'Unknown') LockMode,

decode(request, 0, 'None',

1,'Null',

2,'Row-S',

3,'Row-X',

4,'Share',

5,'S/Row-X',

6,'Exclusive', 'Unknown') RequestMode

,ctime,block b,

v$session.username,MACHINE,MODULE,ACTION,

decode(A.type,

'MR','Media Recovery',

'RT','RedoThread',

'UN','UserName',

'TX','Transaction',

'TM','DML',

'UL','PL/SQL User Lock',

'DX','Distributed Xaction',

'CF','Control File',

'IS','Instance State',

'FS','File Set',

'IR','Instance Recovery',

'ST','Disk Space Transaction',

'TS','Temp Segment',

'IV','Library Cache Invalida-tion',

'LS', 'LogStart or Switch',

'RW', 'RowWait',

'SQ','Sequence Number',

'TE','Extend Table',

'TT','Temp Table',

'Unknown')LockType

from(SELECT * FROM V$LOCK) A,all_objects,V$SESSION,v$process

whereA.sid > 6

andobject_name<>'OBJ$'

and A.id1= all_objects.object_id

andA.sid=v$session.sid

andv$process.addr=v$session.paddr;

查看一条语句运行时间

select *from v$session_longops

wheresofar<>totalwork;

select *from v$session_longops wheretime_remaining>0;//待完成大于0 秒

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

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

fromdba_data_files order by file_id;

查看等待的信息

select *from v$session_wait

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

select *from dba_segments where owner= 'DWUSER'

索引:

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

select *from USER_CONS_COLUMNS;

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

selectindex_name,index_type,table_name from user_indexes order bytable_name;

看下索引被索引的字段

select *from user_ind_columns whereindex_name=upper('&index_name');

查看索引的大小

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

查看所有的索引

select *from all_indexes

视图:

查看当前用户的视图

selectview_name from user_views;

同义词:

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

select *from user_synonyms;

约束条件:

查看某表的约束条件

selectconstraint_name, constraint_type,search_condition,r_constraint_name from

user_constraints where table_name =upper('&table_name');

表分析

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

确的执行计划

analyzetable tablename compute statistics for all indexedcolumns;//索引分析

execdbms_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;

userenv() 函数

selectuserenv('language') from dual; --字符集

shutdownimmediate

startupmount

altersystem enable restricted session;

altersystem set job_queue_processes=0;

altersystem set aq_tm_processes=0;

alterdatabase open;

alterdatabase character set AL32UTF8;

shutdownimmediate ;

startup;

selectuserenv('isdba') from dual; --是否DBA

selectuserenv('sessionid') from dual; --sessionid

selectuserenv('TERMINAL') from dual; --客户端名字

selectuserenv('INSTANCE') from dual; --实例数

SYS_CONTEXT() 函数

selectSYS_CONTEXT('USERENV','CURRENT_SCHEMAID') from dual;--当前模式ID

selectSYS_CONTEXT('USERENV','CURRENT_USER') from dual;--当前用户

selectSYS_CONTEXT('USERENV','DB_NAME') from dual; --数据库

selectSYS_CONTEXT('USERENV','HOST') from dual; --主机

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。