讲解Oracle数据库中的数据字典及相关SQL查询用法

Oracle数据字典概述 
 
数据库是数据的集合,数据库维护和管理这用户的数据,那么这些用户数据表都存在哪里,用户的信息是怎样的,存储这些用户的数据的路径在哪里,这些信息不属于用户的信息,却是数据库维护和管理用户数据的核心,这些信息就是数据库的数据字典来维护的,数据库的数据字典就汇集了这些数据库运行所需要的基础信息叻。每个数据库都提供了各自的数据字典的方案,虽然形式不同,但是目的和作用是一样的,比如在mysql里数据字典是在information_schema 里表现的,sqlserver则是在sys这个系统schema里来展示的。
Oracle的数据字典是Oracle数据库安装之后,自动创建的一系列数据库对象。数据字典是Oracle数据库对象结构的元数据信息。熟悉和深入研究数据字典对象,可以很大程度的帮助我们了解Oracle内部机制。
Oracle字典视图包括四个层次,分别为X$内部表、基础数据字典表、数据字典视图和动态性能视图。
数据字典表和用户创建的表没有什么区别,不过数据字典表里的数据是Oracle系统存放的系统数据,而普通表存放的是用户的数据而已,为了方便的区别这些表,这些表的名字都是用"$"结尾,在我们看到的sql语句里看到"$"结尾的这些表,大家就可以想到这可能是一个数据字典表了,同样,既然如此,我们创建自己的用户表的时候就不要用“$”结尾,以免让别人误会,数据字典表是系统存放系统用户的,所以他的owner是sys,在手工用create database的命令的时候,会调用$ORACLE_HOME/rdbms/admin/sql.bsq文件,这个就会执行生成我们这些数据字典表。打开sql.bsq会发现很多数据字典几乎都以$结尾,比如col$,tab$等。
对于数据字典表,里面的数据是有数据库系统自身来维护的,所以这里虽然和普通表一样可以用DML语句来修改数据内容,但是大家最好还是不要自己来做了,因为这些表都是作用于数据库内部的,我们有时看到的604的的recursive sql这里的sql实际上很有可能就是我们的相关的数据字典表的执行内容。所以这里我们切记记住不要去修改这些表里的内容。
刚才说道了。这里的数据字典表的用户都是sys,存在在system这个表空间里,表名都用"$"结尾,为了便于用户对数据字典表的查询,这样的名字是不利于我们记忆的,所以Oracle对这些数据字典都分别建立了用户视图视图,不仅有更容易接受的名字,还隐藏了数据字典表表之间的关系,让我们字节通过视图来进行查询,简单而形象,Oracle针对这些对象的范围,分别把视图命名为DBA_XXXX, ALL_XXXX和USER_XXXX
user_对象视图:描述了当前用户schema下的对象;
all_对象视图:描述了当前用户有权限访问到的所有对象的信息;
dba_对象视图:包括了所有数据库对象的信息;
注意:在创建数据库是会运行两个脚本。先运行catalog.sql,该脚本用来创建数据库的内部字典表。然后再运行catrpoc.sql,该脚本用来创建数据库内建的存储过程、包等pl\sql对象。如果我们是使用dbca来创建数据库,则dbca会自动调用这两个脚本。否则在执行create database命令来创建时,则需要手工运行这两个脚本。    
 
数据字典SQL查询
下面按类别列出一些ORACLE用户常用数据字典的查询使用方法。
 
1、用户
查看当前用户的缺省表空间

SQL>select username,default_tablespace from user_users;

查看当前用户的角色

SQL>select * from user_role_privs;

查看当前用户的系统权限和表级权限

SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;

2、表
查看用户下所有的表

SQL>select * from user_tables;

查看名称包含log字符的表

SQL>select object_name,object_id from user_objects
where instr(object_name,'LOG')>0;

查看某表的创建时间

SQL>select object_name,created from user_objects where object_name=upper('&table_name');

查看某表的大小

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

查看放在ORACLE的内存区里的表

SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;

3、索引
查看索引个数和类别

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

查看索引被索引的字段

SQL>select * from user_ind_columns where index_name=upper('&index_name');

查看索引的大小

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

4、序列号
查看序列号,last_number是当前值

SQL>select * from user_sequences;

5、视图
查看视图的名称

SQL>select view_name from user_views;

查看创建视图的select语句

SQL>set view_name,text_length from user_views;
SQL>set long 2000;        说明:可以根据视图的text_length值设定set long 的大小
SQL>select text from user_views where view_name=upper('&view_name');

6、同义词
查看同义词的名称

SQL>select * from user_synonyms;

7、约束条件
查看某表的约束条件

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

SQL>select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')
and c.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position;

8、存储函数和过程
查看函数和过程的状态

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

查看函数和过程的源代码

SQL>select text from all_source where owner=user and name=upper('&plsql_name');
时间: 2016-03-17

Oracle存储过程游标用法分析

本文实例讲述了Oracle存储过程游标用法.分享给大家供大家参考,具体如下: 使用游标的5个步骤 1.声明一些变量用于保存select语句返回的指 2.声明游标,并指定select 语句 3.打开游标 4.从游标中获取记录 5.关闭游标 从游标中获取每一条记录可使用fetch语句.fetch语句将列的指读取到指定的变量中: 语法: fetch cursor_name into variable[, variable ...]; 例子: create or replace procedure se

oracle数据库中sql%notfound的用法详解

SQL%NOTFOUND 是一个布尔值.与最近的sql语句(update,insert,delete,select)发生交互,当最近的一条sql语句没有涉及任何行的时候,则返回true.否则返回false.这样的语句在实际应用中,是非常有用的.例如要update一行数据时,如果没有找到,就可以作相应操作.如: begin update table_name set salary = 10000 where emp_id = 10; if sql%notfound then insert into

Oracle中游标Cursor基本用法详解

查询 SELECT语句用于从数据库中查询数据,当在PL/SQL中使用SELECT语句时,要与INTO子句一起使用,查询的 返回值被赋予INTO子句中的变量,变量的声明是在DELCARE中.SELECT INTO语法如下: SELECT [DISTICT|ALL]{*|column[,column,...]} INTO (variable[,variable,...] |record) FROM {table|(sub-query)}[alias] WHERE............ PL/SQL

oracle数据库定时任务dbms_job的用法详解

一.dbms_job涉及到的知识点 1.创建job: variable jobno number; dbms_job.submit(:jobno, --job号 'your_procedure;',--执行的存储过程, ';'不能省略 next_date, --下次执行时间 'interval' --每次间隔时间,interval以天为单位 ); –系统会自动分配一个任务号jobno. 2.删除job: dbms_job.remove(jobno); 3.修改要执行的操作: job:dbms_j

Oracle minus用法详解及应用实例

Oracle minus用法 "minus"直接翻译为中文是"减"的意思,在Oracle中也是用来做减法操作的,只不过它不是传统意义上对数字的减法,而是对查询结果集的减法.A minus B就意味着将结果集A去除结果集B中所包含的所有记录后的结果,即在A中存在,而在B中不存在的记录.其算法跟Java中的Collection的removeAll()类似,即A minus B将只去除A跟B的交集部分,对于B中存在而A中不存在的记录不会做任何操作,也不会抛出异常. Ora

Oracle中的INSTR,NVL和SUBSTR函数的用法详解

Oracle中INSTR的用法: INSTR方法的格式为 INSTR(源字符串, 要查找的字符串, 从第几个字符开始, 要找到第几个匹配的序号) 返回找到的位置,如果找不到则返回0. 例如:INSTR('CORPORATE FLOOR','OR', 3, 2)中,源字符串为'CORPORATE FLOOR', 在字符串中查找'OR',从第三个字符位置开始查找"OR",取第三个字后第2个匹配项的位置. 默认查找顺序为从左到右.当起始位置为负数的时候,从右边开始查找. 所以SELECT I

Oracle开发之分析函数简介Over用法

一.Oracle分析函数简介: 在日常的生产环境中,我们接触得比较多的是OLTP系统(即Online Transaction Process),这些系统的特点是具备实时要求,或者至少说对响应的时间多长有一定的要求:其次这些系统的业务逻辑一般比较复杂,可能需要经过多次的运算.比如我们经常接触到的电子商城. 在这些系统之外,还有一种称之为OLAP的系统(即Online Aanalyse Process),这些系统一般用于系统决策使用.通常和数据仓库.数据分析.数据挖掘等概念联系在一起.这些系统的特点

Oracle开发之分析函数总结

这一篇是对前面所有关于分析函数的文章的总结: 一.统计方面: 复制代码 代码如下: Sum() Over ([Partition by ] [Order by ])    Sum() Over ([Partition by ] [Order by ]       Rows Between  Preceding And  Following)        Sum() Over ([Partition by ] [Order by ]      Rows Between  Preceding An

Oracle开发之分析函数(Rank, Dense_rank, row_number)

一.使用rownum为记录排名: 在前面一篇<Oracle开发之分析函数简介Over>,我们认识了分析函数的基本应用,现在我们再来考虑下面几个问题: ①对所有客户按订单总额进行排名 ②按区域和客户订单总额进行排名 ③找出订单总额排名前13位的客户 ④找出订单总额最高.最低的客户 ⑤找出订单总额排名前25%的客户 按照前面第一篇文章的思路,我们只能做到对各个分组的数据进行统计,如果需要排名的话那么只需要简单地加上rownum不就行了吗?事实情况是否如此想象般简单,我们来实践一下. [1]测试环境

Oracle开发之分析函数(Top/Bottom N、First/Last、NTile)

一.带空值的排列: 在前面<Oracle开发之分析函数(Rank.Dense_rank.row_number)>一文中,我们已经知道了如何为一批记录进行全排列.分组排列.假如被排列的数据中含有空值呢? 复制代码 代码如下: SQL> select region_id, customer_id,          sum(customer_sales) cust_sales,          sum(sum(customer_sales)) over(partition by regio

Oracle开发之窗口函数

一.窗口函数简介: 到目前为止,我们所学习的分析函数在计算/统计一段时间内的数据时特别有用,但是假如计算/统计需要随着遍历记录集的每一条记录而进行呢?举些例子来说: ①列出每月的订单总额以及全年的订单总额 ②列出每月的订单总额以及截至到当前月的订单总额 ③列出上个月.当月.下一月的订单总额以及全年的订单总额 ④列出每天的营业额及一周来的总营业额 ⑤列出每天的营业额及一周来每天的平均营业额 仔细回顾一下前面我们介绍到的分析函数,我们会发现这些需求和前面有一些不同:前面我们介绍的分析函数用于计算/统

Oracle开发之报表函数

一.回顾一下前面<Oracle开发之窗口函数>中关于全统计一节,我们使用了Oracle提供的: 复制代码 代码如下: sum(sum(tot_sales)) over (order by month rows between unbounded preceding and unbounded following) 来统计全年的订单总额,这个函数会在记录集形成的过程中,每检索一条记录就执行一次,它总共执行了12次.这是非常费时的.实际上我们还有更简便的方法: 复制代码 代码如下: SQL>

oracle逻辑运算符与其优先级简介

Oracle的逻辑运算符也是用在SQL语句中必不可少的因素,一共有三个 逻辑运算符 意义 and 双值运算符,如果左右两个条件都为真,则得到的值就为真 or 双值运算符,只要左右两个条件有一个为真,则得到的值就为真 not 单指运算符,如果原条件为真,则得到真,如果元条件为假,反之如果原条件为假,则结果为真 Oracle的逻辑运算符也是用在SQL语下面使一些例子: Select * from emp where sal > 2000 and job = 'SALESMAN'; 寻找那些工资高于2

java 查询oracle数据库所有表DatabaseMetaData的用法(详解)

一 . 得到这个对象的实例 Connection con ; con = DriverManager.getConnection(url,userName,password); DatabaseMetaData dbmd = con.getMetaData(); 二. 方法getTables的用法 原型: ResultSet DatabaseMetaData.getTables(String catalog,String schema,String tableName,String []type

oracle跨库查询dblink的用法实例详解

本文实例讲述了oracle跨库查询dblink的用法.分享给大家供大家参考,具体如下: 1.创建之前的工作 在创建dblink之前,首先要查看用户是否有相应的权限.针对特定的用户,使用 sqlplus user/pwd登录后,执行如下语句: 复制代码 代码如下: select * from user_sys_privs t where t.privilege like upper('%link%'); 在sys用户下,显示结果为: SYS CREATE DATABASE LINK NO SYS