Oracle数据字典使用

一、数据字典
　　数据字典是oracle存放有关数据库信息的地方，几乎所有的系统信息和对象信息都可在数据字典中进行查询。数据字典是oracle数据库系统的信息核心，它是一组提供有关数据库信息的表和视图的集合，这些表和视图是只读的。它是随着数据库的建立而建立的，当数据库执行特定动作时数据字典也会自动更新。数据一览与数据字典来记录、校验和管理正在进行的操作。
oracle中、sys用户是数据字典的拥有者，数据字典保证在所有数据库的系统表空间system内，任何用户都无权更改sys模式下的模式对象或数据字典中的行。也就是说数据字典只能查询，不能手动进行修改。

　　数据字典用途
　　oracle通过存取数据字典从而比较方便地获取有关用户某事对象和存储结构等信息。当系统执行了DDL语句后，oracle会及时修改数据字典。任何用户只能以读的形式使用数据字典获取数据库信息。

数据字典存储的信息

• 数据用户的名称
• 为用户授予的权限和角色
• 模式对象的名称，如 tables,views,indexex,procedures,functions,packages,triggers等。
• 完整性约束的具体信息；
• 每个字段的默认值；
• 数据库空间的使用情况；
• 审计功能，在Oracle_Home\productdb_l\rdbms\admin目录下的文件cataudit.sql就是用于为审计创建数据字典视图的脚步。
• 对象与用户的严格管理(适用于高度机密管理);
• 其他一般数据库信息。

　　三种前缀的数据字典视图 
　　user_ ：任何用户都可以读取的视图，每个用户读取的都不一样，它只提供当前用户某事下的对象信息。如查询当前模式下的所有对象select object_name, object_type from user_objects；

　　all_ ：所有用户都可读取的用户视图，它提供与用户有关的对象信息。如查询当前用户可访问的所有对象
select owner, object_name, object_type from all_objects;
　　dba_：提供了只有数据库管理员才可读取的视图，包括所有用户视图中的对象信息。如select owner, object_name, object_type from sys.dba_objects;

二、数据字典相关查询

　　1、 查询用户

 

select username from dba_users; -- 只有管理员权限的用户才能查询

select username from all_users; -- 当前或任何用户都可使用

-- 查看当前用户的默认表空间
select username, default_tablespace from user_users;

--当前用户角色
select * from user_role_privs;

-- 当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;

 

　　 2、查询 表空间 (拥有DBA权限的用户才能查询)

 

select * from dba_data_files;
select * from dba_tablespaces; --表空间

select tablespace_name, sum(bytes), sum(blocks) from dba_free_space group by tablespace_name; --空闲表空间

select * from dba_data_files where tablespace_name='USERS'; -- 表空间对于的数据文件

select * from dba_segments where tablespace_name='USERS';
--查询用户模式对象所使用过的正在使用空间大小
select name, type, source_size, code_size from user_object_size;

 

　　3、查询数据库对象(拥有DBA权限的用户才能查询)

select * from dba_objects
select * from dba_objects where object_type = upper('package body');

select * from dba_objects where OBJECT_TYPE='TABLE' and OWNER='SCOTT'

　　可根据拥有者查询下列对象类型(object_type)

　　cluster 　　 databaselink
　　function　　 index
　　library　　 package
　　package body　　 procedure
　　sequence 　　synonym
　　table　　 trigger
　　type　　 undefined
　　view
　　4、查询表

--表使用的extent的信息。segment_type='ROLLBACK'

 

select * from dba_tables;
select extent_id, bytes from dba_extents where segment_name='CUSTOMERS' and segment_type='TABLE' order by extent_id;

--    查看回滚段的空间分配信息列信息 SELECT * FROM user_tab_columns;
select distinct table_name from user_tab_columns where column_name='ID';

-- 查看当前用户下所有的表
select * from user_tables;
--查看名称包含log字符的表
select object_name, object_id from user_objects where instr(object_name, 'LOG') > 0;
-- 查看某表的创建时间
select object_name, created from user_objects where object_name = upper('&table_name');
-- 查看某表的大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name = upper('&table_name');
-- 查看放在oracle的内存的表
select table_name, cache from user_tables where instr(cache, 'Y') > 0;

 

　　5、查询索引

 

select * from dba_indexes; -- 索引，包括主键索引

select * from all_indexes;
select * from dba_ind_columns; -- 索引列

select i.index_name, i.uniqueness, c.column_name from user_indexes i, user_ind_columns c where i.index_name=c.index_name and i.table_name = 'PERSON'; --连接使用

-- 查看索引个数和类别
select index_name, index_type, table_name from user_indexes order by table_name;
-- 查看索引被索引的字段
select * from user_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');

 

　　6、查询序列

select * from dba_sequences;
select * from all_sequences;

查看序列号，last_number是当前值
select * from user_sequences;

　　7、查询视图

select * from dba_views;
select * from all_views;

可用目录desc all_views;来查看视图结构
其中，Text列可用于查询视图生产的脚本。

 

--查询当前用户视图的名称
select view_name from user_views;

--查询创建视图的select语句
select view_name, text_length from user_views;
set long 2000;

--可以根据视图的text_length 值设定set long的大小
select text from user_views where view_name = upper('&view_name');

 

　　8、查询聚簇

select * from dba_clusters;

　　9、查询快照

select * from dba_snapshots;

　　快照、分区应存在相对应的表空间

　　10、查询同义词

select * from dba_synonyms where table_owner='SCOTT';
select * from ALL_synonyms where table_owner='SYSTEM';

　　如果用户表可以被访问，那么同义词也可以被访问，用户表不能被访问，则同义词也不能被访问。

　　11、查询数据库链

select * from dba_db_links;

 　　12、查询触发器(12)

select * from dba_triggers;

存储过程，函数从dba_objects查找

查询文本
select text from user_source where name = 'PRO_PERSON_FINDBYID';

oracle总是将存储过程，函数放在system表空间。

　　13、查看函数和过程的状态

select object_name, status from user_objects where object_type='FUNCTION';
select object_name, status from user_objects where object_type='PROCEDURE';

--查看源代码
select * from all_source where owner='WUXX' and name=upper('&plsql_name');

　　14、查询约束

约束是和表关联的，可以在create table或alter table table_name add/drop/modify 来建立、修改、删除约束。
可以临时禁止约束，如：
alter table book_example
disable constraint book_example_l;
数据完整性约束

select constraint_name, constraint_type, table_name from dba_constraints;

　　15、查询回滚段
在所有的修改结果存入磁盘前，回滚段中保持恢复该事务所需的全部信息，必须以数据库发送的事务来相应确定其大小。（DML语句才可回滚，create, drop, truncate等DDL不能回滚）
回滚段数量=并发事务/4，但不能超过50个；是每个回滚段大小足够处理一个完整的事物;

create rollback segment r05 tablespace rbs;
create rollback segment rbs_cvt tablespace rbs storage(initial 1M next 500k);

　　16、查询作业

select job, broken, next_date, interval, what from user_jobs;
select job, broken, next_date, interval, what from dba_jobs;

--正在运行的作业
select * from dba_jobs_running;

使用包exec dbms_job.sumit(:v_num, 'a;', sysdate, 'sysdate +(10/(24*60*60))');加入作业。间隔10秒

使用包exec dbms_job.sumit(:v_num, 'a;', sysdate, 'sysdate +(10/(24*60))');加入作业。间隔11分
使用表exec dbms_job.remove(21)删除21号作业。

其他信息查询
查询优化模式对象使用过的或正在使用的空间大小

select name, type, source_size, code_size from user_object_size;

查询字段的默认值

select table_name, column_name, data_default, low_value, hight_value from dba_tab_columns;