oracle数据字典.docx
- 文档编号:30727272
- 上传时间:2023-08-19
- 格式:DOCX
- 页数:16
- 大小:21.50KB
oracle数据字典.docx
《oracle数据字典.docx》由会员分享,可在线阅读,更多相关《oracle数据字典.docx(16页珍藏版)》请在冰豆网上搜索。
oracle数据字典
ORACLE数据字典是ORACLE数据库的重要组成部分,它提供了诸如数据库结构、数据库对
象空间分配和数据库用户等等有关数据库的信息。
本文试图从数据库管理系统的若干基本概
念出发,具体介绍从ORACLE数据字典来了解ORACLE的方法。
不同的操作系统、不同版本的ORACLE数据字典有所差异,本文均以UNIX操作系统、ORAE
7为例加以说明。
数据库系统实例和数据库标识
ORACLE数据库系统包含两个方面,即ORACLE数据库和ORACLE实例,用户是通过ORACLE实例
来访问ORACLE数据库的。
1.数据库(DATABASE)
数据库是作为整体看待的数据集合,通常在安装ORACLE软件的最后阶段创建,用数据库名
加以标识(允许1~8个字符),数据库名在创建数据库时确认,且保存在控制文件中。
2.实例(INSTANCE,也译作例程)
实例是存取和控制数据库的软件机制。
它由系统全局区(SystemGlobalArea,即SGA)和
ORACLE进程两部分组成,和数据库名一样,实例也要唯一标识,并且在安装ORACLE软件时确认
。
我们可以采用下述方法来了解实例标识。
有的系统管理员有意无意地将数据库名和实例标识取相同的标识符,当然是可以的,有时
甚至会带来某些方便,但笔者认为,还是取不同标识符为宜。
数据库的初始化参数文件
在数据库的建立和运行中,都要阅读一个初始化参数文件,它是个文本文件,可以用一般
的编辑程序编辑。
每一个数据库至少有一个初始化参数文件,一般命名为INIT.ORA,在UNIX系统下,按照缺
省规定,其初始化参数文件名的命名原则为INIT后紧跟实例的标识,再加ORA后缀,例如,某实
例标识为TEST,其初始化参数文件名为INITTEST.ORA。
数据库的初始化参数有100多个,前面提到的DB_NAME即是其中之一。
此外,还有许多其他
参数,如
CONTROL-FILES控制文件名
ROLLBACK-SEGMENTS分配给实例的回滚段名
INIT-SQL-FILES数据库建立时执行的命令文件
PROCESSES多进程系统中最大进程数
数据库的物理结构
数据库的物理结构是面向操作系统的,它描述数据库中的数据的存储形式。
在物理上OA
CLE数据库文件包括数据文件、日志文件和控制文件。
1.数据文件(DATAFILES)
ORACLE数据库中的数据,逻辑上存放在表空间里,但物理上却是存放在数据文件里的,数
据文件有如下特点,即每一个数据文件只与一个数据库相联系,数据库文件一旦建立,就不能
改变大小,一个表空间可以包含一个或多个数据文件等。
我们可以用下述方法来了解数据库的全部数据文件。
(1)SQLDBA>SELECT*FROMDBA-DATA-FILES;结果中,列名FILE-NAME即为数据文件
名。
(2)SQLDBA>SELECT*FROMV$DATAFILE;结果中,列名NAME即为数据文件名。
(3)SQLDBA>SELECT*FROMV$DBFILE;结果中,列名NAME即为数据文件名。
2.日志文件(REDOLOGFILES)
日志文件用于记录数据库所做的全部变更,以便在系统发生故障时进行恢复。
每一个数
据库至少有两个日志文件。
3.控制文件(CONTROLFILES)
控制文件虽然是一个较小的二进制文件,但很重要。
如果控制文件一旦被破坏,则无法对
数据库进行操作。
为防止控制文件被破坏,一般一个数据库应至少有二个控制文件,且分别放
在不同的磁盘上,控制文件的名字是记录在参数CONTROL_FILES中的。
数据库的逻辑结构
数据库的逻辑结构是面向用户的,数据库的逻辑结构包含表空间、段、范围、数据块和
模式对象。
从逻辑上看数据库有以下特点。
一个数据库(DATABASE)由一个或多个表空间所组成。
一个表空间(TABLESPACE)由段组成,作为SYSTEM表空间,它由自举段、回滚段、数据段、
临时段等多个段组成,而作为非SYSTEM表空间,则因用途而异由不同段组成。
一个段(SEGMENT)由一组范围组成。
一个范围(EXTENT)由一组连续的数据块组成。
一个
数据块(DATABASEBLOCK)对应磁盘上的一个或多个物理块。
在数据库的逻辑结构中,表空间和回滚段比较重要,下面分别加以介绍。
1.表空间
每一个数据库都必须包含一个名为SYSTEM的表空间。
该表空间在创建数据库时由系统自
动创建,为了保证数据库能正常运行,SYSTEM表空间必须处于在线状态。
为了增强对数据库的控制和维护,一般一个数据库都包含多个表空间。
使用多个表空间
有许多优点,例如可以使用户数据与数据字典相分离,可以在不同的磁盘上存储不同表空间的
数据文件,从而减少I/O冲突,还可以使一些表空间在线,而使另一些表空间离线等等。
2.回滚段
每一个表空间是都由段组成。
ORACLE数据库中的段有数据段、索引段、临时段、回滚段
和自举段。
比较重要的是回滚段,它记录数据库的变更信息,以实现数据库的读一致性及恢复
工作。
在SYSTEM表空间里有一个SYSTEM回滚段,是在创建数据库时随之产生的。
如果使用多个
表空间,至少还应有一个另外的回滚段。
回滚段分专用和公用两种,要使用专用回滚段,需要
在初始化参数文件的ROLLBACK-SEGMENTS参数上写上专用回滚段的段名并且重新启动数据库
或者通过回滚段在线命令使它在线。
数据库的用户
ORACLE数据库是个多用户系统。
为了保证数据库系统的安全,ORACLE数据库管理系统
配制了良好的安全机制。
例如,每一个ORACLE数据库都有一个用户表,它记载着每一个用户的
有关信息,一旦用户进入系统,ORACLE系统会通过这张表来检查用户的合法性。
又如,ORACL系
统通过合理分配用户的权限来管理用户。
通常,ORACLE将用户分为三类,即DBA、RESOUCE和C
ONNECT三类角色,使不同的用户的权限各不相同。
在创建数据库时,系统自动建立了两个用户,即SYS和SYSTEM用户,且授给这两个用户DB权
限,由于DBA具有最高权限,建议将它们的口令及时修改,以免个别用户以DBA角色进入系统,有
意无意地给系统造成损害。
除了SYS、SYSTEM用户外,其余用户都要一一建立,建立用户的方法如下:
假设要建的用户名为RSXT,口令为RSPASS;默认的表空间为USER,临时表空间为TEMP,表空
间限额为3M,其余默认,则可用下面方法创建新用户:
SQLDBA>CREATEUSERRSXTIDENTIFIEDBYRSPASS
DEFAULTTABLESPACEUSER
TEMPORARYTABLESPACETEMP
QUOTA3MONUSER;
请注意,该用户建立后,还要及时授权,否则像上述刚刚建成的用户,则一无所为,甚至都
不能连接数据库。
对一般用户,通常授予CONNECT和RESOURCE角色权限,命令如下:
SQLDBA>GRANTCONNECT,RESOURCETORSXT;
对于一个有着许多用户的数据库系统,我们可以通过下述方法来了解数据库的用户情况
。
1.对于非DBA用户
对于非DBA用户,有两条命令,即:
(1)SQL>SELECT*FROMALL-USERS;
(2)SQL>SELECT*FROMUSER-USERS;
其中,从USER-USERS表中,可以了解到该用户的默认表空间和临时表空间等信息。
2.对于DBA用户
对于DBA用户,有三条命令,即:
(1)SQLDBA>SELECT*FROMALL-USERS;
(2)SQLDBA>SELECT*FROMUSER-USERS;
(3)SQLDBA>SELECT*FROMDBA-USERS;
其中,从DBA-USERS中,可以了解到所有用户的详细信息,因而该命令对于数据库管理员来
说是很有用的。
关于非DBA用户的信息
由于DBA用户具有最高权限,为安全起见,只有系统管理员才授予DBA权限,大多数用户均
为非DBA用户。
尽管相应的权限少了,但是从ORACLE数据字典中,还是可以得到很多关于非D
BA用户的有用信息。
我们可以采用下述方法来了解有关非DBA用户的信息。
1.SQL>SELECT*FROMUSER-USERS;
可以查看该用户的默认表空间,临时表空间和用户创建时间。
2.SQL>SELECT*FROMUSER-TABLES;
可以查看该用户创建的所有表的详细信息,由于列名较多,一屏看不了几个表。
如果仅仅想查看用户所建表的表名,可以用下面的命令:
SQL>SELECTTABLE-NAMEFROMUSER-TABLES;
3.SQL〉SELECT*FROMUSER-VIEWS;
可以查看该用户所创建的视图,包括创建视图的文本。
同样,如果仅仅想查看用户所建视图的视图名,可以用下面的命令:
SQL>SELECTVIEW-NAMEFROMUSER-VIEWS;
4.SQL>SELECT*FROMUSER-TABLESPACES;
可以查看该用户可存取的表空间的信息。
5.SQL>SELECTTABLESPACE-NAME,SUM(BYTES),SUM(BLOCKS)
FROMUSER-FREE-SPACEGROUPBYTABLESPACE-NAME;
可以查看该用户可存取的表空间的剩余空间。
6.SQL>SELECT*FROMUSER-TS-QUOTAS;
可以查看该用户的表空间的份额。
7.SQL>SELECT*FROMUSER-ROLE-PRIVS;
可以查看该用户被授予的角色。
8.SQL>SELECT*FROMUSER-SYS-PRIVS;
可以查看该用户的系统权限及能否再授予其它用户的权限。
9.SQL>SELECT*FROMUSER-TAB-PRIVS-RECD;
可以查看该用户能访问其它用户的表、视图等的对象权限。
10.SQL>SELECT*FROMUSER-TAB-PRIVS-MADE;
可以查看该用户授予其它用户的表、视图等的对象权限。
有关用户的信息还有很多,这里不再一一列举,请查看ORACLE数据字典。
ORACLE的数据字典是数据库的重要组成部分之一,它随着数据库的产生而产生,随着数据库的变化而变化,
体现为sys用户下的一些表和视图。
数据字典名称是大写的英文字符。
数据字典里存有用户信息、用户的权限信息、所有数据对象信息、表的约束条件、统计分析数据库的视图等。
我们不能手工修改数据字典里的信息。
很多时候,一般的ORACLE用户不知道如何有效地利用它。
dictionary 全部数据字典表的名称和解释,它有一个同义词dict
dict_column 全部数据字典表里字段名称和解释
如果我们想查询跟索引有关的数据字典时,可以用下面这条SQL语句:
SQL>;select*fromdictionarywhereinstr(comments,'index')>;0;
如果我们想知道user_indexes表各字段名称的详细含义,可以用下面这条SQL语句:
SQL>;selectcolumn_name,commentsfromdict_columnswheretable_name='USER_INDEXES';
依此类推,就可以轻松知道数据字典的详细名称和解释,不用查看ORACLE的其它文档资料了。
下面按类别列出一些ORACLE用户常用数据字典的查询使用方法。
一、用户
查看当前用户的缺省表空间
SQL>;selectusername,default_tablespacefromuser_users;
查看当前用户的角色
SQL>;select*fromuser_role_privs;
查看当前用户的系统权限和表级权限
SQL>;select*fromuser_sys_privs;
SQL>;select*fromuser_tab_privs;
二、表
查看用户下所有的表
SQL>;select*fromuser_tables;
查看名称包含log字符的表
SQL>;selectobject_name,object_idfromuser_objects
whereinstr(object_name,'LOG')>;0;
查看某表的创建时间
SQL>;selectobject_name,createdfromuser_objectswhereobject_name=upper('&table_name');
查看某表的大小
SQL>;selectsum(bytes)/(1024*1024)as"size(M)"fromuser_segments
wheresegment_name=upper('&table_name');
查看放在ORACLE的内存区里的表
SQL>;selecttable_name,cachefromuser_tableswhereinstr(cache,'Y')>;0;
三、索引
查看索引个数和类别
SQL>;selectindex_name,index_type,table_namefromuser_indexesorderbytable_name;
查看索引被索引的字段
SQL>;select*fromuser_ind_columnswhereindex_name=upper('&index_name');
查看索引的大小
SQL>;selectsum(bytes)/(1024*1024)as"size(M)"fromuser_segments
wheresegment_name=upper('&index_name');
四、序列号
查看序列号,last_number是当前值
SQL>;select*fromuser_sequences;
五、视图
查看视图的名称
SQL>;selectview_namefromuser_views;
查看创建视图的select语句
SQL>;setview_name,text_lengthfromuser_views;
SQL>;setlong2000;说明:
可以根据视图的text_length值设定setlong的大小
SQL>;selecttextfromuser_viewswhereview_name=upper('&view_name');
六、同义词
查看同义词的名称
SQL>;select*fromuser_synonyms;
七、约束条件
查看某表的约束条件
SQL>;selectconstraint_name,constraint_type,search_condition,r_constraint_name
fromuser_constraintswheretable_name=upper('&table_name');
SQL>;selectc.constraint_name,c.constraint_type,cc.column_name
fromuser_constraintsc,user_cons_columnscc
wherec.owner=upper('&table_owner')andc.table_name=upper('&table_name')
andc.owner=cc.ownerandc.constraint_name=cc.constraint_name
orderbycc.position;
八、存储函数和过程
查看函数和过程的状态
SQL>;selectobject_name,statusfromuser_objectswhereobject_type='FUNCTION';
SQL>;selectobject_name,statusfromuser_objectswhereobject_type='PROCEDURE';
查看函数和过程的源代码
SQL>;selecttextfromall_sourcewhereowner=userandname=upper('&plsql_name');
Oracle数据库没有提供直接修改表中列名称的功能,但在实际使用时常需要修改表的列名和列顺序,不得已有些Oracle的使用人员用重新创建一个新的具有正确列名和顺序的数据库表,再将旧表的数据转储进来,最后删除旧表并将新表重命名为旧表的方法来完成此功能。
此方法的最大问题是要求有双倍的存储空间、较大的回滚段和较长的时间,如果表中数据量较大,这项工作开销会很大。
实际上我们可以从数据字典中直接修改表列的名称和顺序。
下面是具体的实现步骤:
1.以internal用户名登录Oracle数据库,并创建一测试表。
SQL>CREATETABLESCOTT.TESTASSELECTEMPNO,ENAMEFROMSCOTT.EMP;
SQL>DESCSCOTT.TEST
NameTypeNullableDefaultComments
--------------------------
EMPNONUMBER(4)Y
ENAMEVARCHAR2(10)Y
下面我们要把SCOTT.TEST表中EMPNO和ENAME两列调换顺序,并把ENAME列更名为EMP_NAME,EMPNO改为EMP_NO。
2.查询表中列的实际存储位置或表。
SQL>SETLONG9999
由于TEXT列是LONG类型,只有“SET”之后才能完全显示。
SQL>SELECTTEXTFROMALL_VIEWSWHEREVIEW_NAME=‘USER_TAB_COLUMNS’;
数据字典视图USER_TAB_COLUMNS中存储有表列的定义信息,从该语句的查询结果可以看出,列定义信息是存储在表SYS.COL$中的,即如果修改表中列的定义,应该在SYS.COL$表中修改。
3.从数据字典视图ALL_OBJECTS中查找对象SCOTT.TEST对象ID。
SQL>SELECT*FROMALL_OBJECTSWHEREOWNER=‘SCOTT’ANDOBJECT_NAME=‘TEST’;
4.根据SCOTT.TEST对象的ID,从SYS.COL$检索出表中列的定义信息。
SQL>SELECTOBJ#,COL#,NAMEFROMSYS.COL$WHEREOBJ#=13888;
OBJ#COL#NAME
---------------------------
138881EMPNO
138882ENAME
5.使用Update语句来进行修改。
UPDATESYS.COL$SETCOL#=2,NAME=‘EMP_NO’WHEREOBJ#=13888ANDNAME=‘EMPNO’;
UPDATESYS.COL$SETCOL#=1,NAME=‘EMP_NAME’WHEREOBJ#=13888ANDNAME=‘ENAME’;
COMMIT;
6.重启数据库服务。
由于数据字典是在数据库启动时加载到SQL中的,所以修改了它之后,如果使用“SELECT*FROMSCOTT.TEST;”,会发现好像并没有修改。
因此,修改完成之后,还需要重启数据库服务。
SQL>SHUTDOWN
SQL>STARTUP
这时,再查看,就会发现修改已经成功。
SQL>SELECT*FROMSCOTT.TEST;
EMP_NAMEEMP_NO
----------------
SMITH7369
ALLEN7499
WARD7521
……
这种方法直接从数据库中进行表列定义的修改,存在一定风险,但它对于数据量特别大的表是非常有用的。
充分利用数据字典功能,往往能够完成日常很难完成的工作。
下面笔者写了一段简单的存储过程,可实现表中列的重命名。
读者可直接调用此过程来完成列的重命名:
SQL>execaltercolname(‘模式名称’,‘表名称’,‘原列名称’,‘新列名称’);
createorreplaceproceduresys.altercolname
(schmanameinvarchar2,
tabnameinvarchar2,
oldcolnameinvarchar2,
newcolnameinvarchar2)is
n_schmanamevarchar2(30);--模式名称
n_tablenamevarchar2(30);--表名称
n_oldcolnamevarchar2(30);--原来列名称
n_newcolnamevarchar2(30);--新的列名称
n_objnumnumber;
begin
n_schmaname:
=upper(schmaname);
n_tablename:
=upper(tabname);
n_oldcolname:
=upper(oldcolname);
n_newcolname:
=upper(newcolname);
SELECTOBJECT_IDINTOn_objnum
FROMALL_OBJECTS
WHEREOWNER=n_schmaname
ANDOBJECT_NAME=n_tablename;
UPDATESYS.COL$
SETNAME=n_newcolname
WHEREOBJ#=n_objnumAND
NAME=n_oldcolname;
COMMIT;
endaltercolname;
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 数据 字典
![提示](https://static.bdocx.com/images/bang_tan.gif)