oracle认证应用技术学习资料汇总.docx
《oracle认证应用技术学习资料汇总.docx》由会员分享，可在线阅读，更多相关《oracle认证应用技术学习资料汇总.docx（168页珍藏版）》请在冰豆网上搜索。
oracle认证应用技术学习资料汇总
管理Oracle约束与分区表
1．约束
作用：
约束用于确保数据库数据的完整性,在oracle数据库中,可以使用约束,触发器和应用代码（过程,函数）3种方法实现数据完整性,这3种方法中,因为约束易于维护,并且具有最好的性能,所以实现数据完整性首选约束.
分类：
约束的种类有：
notnull,unique,primarykey,foreignkey,check
Notnull确保字段值不能为空
Unique:
确保字段值唯一性
Primarykey,最常用的约束（主键约束），主键约束的列值不仅不能重复，也不能为NULL，注意一张表最多只能有一个主键约束，当定义主键约束后oracle自动建立一个以主键为关键字段的索引。
Foreignkey:
定义了主从表之间的关系，foreign要定义在从表上，但主表必须具有主键约束或唯一约束，当定义froeignkey后外部键列的数据必须在主表的主键列中存在，或者为NULL
Check:
:
用于强制表行数据必须满足的条件，如工资表，工人工资必须在2000-5000之间
约束状态
enablevalidate:
是默认,新旧数据同时满足约束规则
enablenovalidate:
旧数据可以不满足,检新插入的数据要满足约束
disablevalidate:
不允许在表上执行任何DML操作,主要用在分区表,对于主键和唯一约事,会删除相应的唯一索引,但约束状态任可用
disablenovalidate数据可不满足约束规则,对于主键和唯一约事,会删除相应的唯一索引,
约束常用语句
createtablet（inumber,vmubmernotnull）
createtablet（inumber,vmubmerunique）
createtablet（inumberconstraintpk_iprimarykey,vnumber）
createtablet2（cnumber,dnumber,constraintfk_dforeignkey（c）,referencest1（v））;
altertabletaddconstraintpk_iprimarykey（i）
altertabletmodifyinotnull;
altertabletaddconstraintt_iunique（i）[（createindexind_nameont（i））];
altertabletaddconstraintt_icheck（iin（1,2,3,4,5））;
altertabletdisablenovalidateconstrainti
altertabletenablenovalidateconstraintcheck_i
altertabletdropconstrainti;
altertabletdropprimarykeyi;
#常用的数据字典
dba_constraints
dba_cons_columns
user_cons_columns
user_constraints
简单应用
检验当为一个表建立主键索时后，这个字段是否满足约束非空，唯一性，而且自动建立一个索引，并查看当把约束状态关闭再次插入相同的记录,是否还能把把约束设为enableividate状态。
SQL>createtablet（inumberconstraintpk_iprimarykey,vnumber）;
SQL>insertintotvalues（1,2）;
SQL>insertintotvalues（3,4）;
SQL>commit;
SQL>select*fromt;
IV
-------------------------------------
12
34
现在表中有两条记录，然后给它插主键为空或相同的值
SQL>insertintotvalues（'',10）;
ERRORatline1:
ORA-01400:
cannotinsertNULLinto（"Y"."T"."I"）
SQL>insertintotvalues（1,10）;
ERRORatline1:
ORA-00001:
uniqueconstraint（Y.PK_I）violated
可以看到全部报错，此时主键不能为空或重复
查看是否建立索引
SQL>selectindex_namefromuser_indexes;
INDEX_NAME
------------------------------
PK_I
把约束关闭再次做同样的操用
SQL>altertabletdisablenovalidateconstraintpk_i;
Tablealtered.
SQL>insertintotvalues（'',10）;
1rowcreated.
SQL>insertintotvalues（1,10）;
1rowcreated.
SQL>commit;
Commitcomplete.
SQL>select*fromt;
IV
--------------------
12
34
10
110
SQL>selectindex_namefromuser_indexes;
norowsselected
可见当把约束关闭后就可以何意给表插数据了，而具索引也自动删除了。
现在激活约束
SQL>altertabletenablevalidateconstraintpk_i;
altertabletenablevalidateconstraintpk_i
ERRORatline1:
ORA-02437:
cannotvalidate（SYS.PK_I）-primarykeyviolated
因为表中主键有相同的值所以不能恢复到enablevalidate状态了
再次测试回复到enablenovalidate
SQL>altertabletenablenovalidateconstraintpk_i;
altertabletenablevalidateconstraintpk_i
ERRORatline1:
ORA-02437:
cannotvalidate（SYS.PK_I）-primarykeyviolated
也失败了,
因为表中主键有了空值和相同的值,所以恢复不到enablevalidate状态,但enablenovalidate不检查旧数据所以应该还能恢复到enablenovalidate.
要想恢复到enablenovalidate必须建立主键索引（关闭约束时自动删除的那个索引）如下:
SQL>createindexpk_iont（i）;
Indexcreated.
然后恢复到enabledisvalidate,以后再插数据不能为空,主键也不能重复了.
SQL>altertabletenablenovalidateconstraintpk_i;
Tablealtered.
SQL>insertintotvalues（1,14）;
insertintotvalues（1,14）
ERRORatline1:
ORA-00001:
uniqueconstraint（SYS.PK_I）violated
2.修正约束数据
当给一个表作主键约束时,因为已存数据不满足约束规则,会提示错误信息,些时必须对数据进行修正
要修正数据先找出不满足约束的数据
如下表,有不满足约束的数据
SQL>select*fromt;
IV
----------------------------------
12
34
1512
1510
如果一个表数据量多可通过如下方法查找
SQL>altertabletdropconstraintpk_i;
Tablealtered.
SQL>conny/123
SQL>@$ORACLE_HOME/rdbms/admin/utlexcpt.sql
Tablecreated.
SQL>altertabletaddconstraintpk_iprimarykey（i）exceptionsintoexceptions;
select*fromtwhererowidin（selectrow_idfromexceptions）
IV
----------------------------------
1512
1510
找到了重复的记录
修正
SQL>updatetseti=10wherev=12;
SQL>select*fromt;
I V
--------------------
1 2
3 4
10 12
15 10
再建主键约束
altertabletaddconstraintpk_iprimarykey（i）
Tablealtered.
成功了!
!
!
回收临时表空间ORA-03297解决
Examda提示:
临时表空间达到15G，但使用率才2%不到，回收一下
SQL>alterdatabasetempfile'd:
\oracle\oradata\ynrmdb\temp01.dbf'resize2048M;
alterdatabasetempfile'd:
\oracle\oradata\ynrmdb\temp01.dbf'resize2048M
*
ERROR位于第1行:
ORA-03297:
文件包含在请求的RESIZE值以外使用的数据
看样子，普通方法是比较麻烦了
SQL>alterdatabasetempfile'd:
\oracle\oradata\ynrmdb\temp01.dbf'drop;
Databasealtered.
SQL>altertablespacetempaddtempfile
2'd:
\oracle\oradata\ynrmdb\temp01.dbf'
3size2048Mreuseautoextendonnext100M;
Tablespacealtered.
SQL>selectd.file_name,d.file_id,d.tablespace_name,d.bytesfromdba_temp_filesd;
FILE_NAMEFILE_IDTABLESPACE_NAMEBYTES
-----------------------------------------------------------------------
D:
\ORACLE\ORADATA\YNRMDB\TEMP01.DBF1TEMP2147483648
记录oracle数据库库表变动
触发器：
CREATEORREPLACETRIGGERtr_trace_ddl
AFTERDDLONDATABASE
DECLARE
sql_textora_name_list_t;
state_sqlddl$trace.ddl_sql%TYPE;
BEGIN
FORiIN1..ora_sql_txt（sql_text）LOOP
state_sql:
=state_sql||sql_text（i）;
ENDLOOP;
INSERTINTOddl$trace（login_user,ddl_time,ip_address,audsid,
schema_user,schema_object,ddl_sql）
valueS（ora_login_user,SYSDATE,userenv（'SESSIONID'）,
sys_context（'USERENV','IP_ADDRESS'）,
ora_dict_obj_owner,ora_dict_obj_name,state_sql）;
--EXCEPTION
--WHENOTHERSTHEN
--sp_write_log（'CaptureDDLExcption:
'||SQLERRM）;
ENDtr_trace_ddl;
日志表结构：
createtableDDL$TRACE
（
LOGIN_USER VARCHAR2（10）,
DDL_TIME DATE,
IP_ADDRESS VARCHAR2（15）,
AUDSID VARCHAR2（20）,
SCHEMA_USER VARCHAR2（10）,
SCHEMA_OBJECTVARCHAR2（50）,
DDL_SQL CLOB
）
检查数据库字段命名规范与合法性的脚本
做为一名开发DBA，最重要的renweu是经常要检查开发人员或建模人员对数据库的命名规范是否严谨，下文中将主要介绍一个检查数据库数据字段命名规范与合法性的脚本，仅供大家参考。
（关键字详细信息可以查看v$reserved_words视图）
--检查不符合数据库命名规范的字段名
SelectTABLE_NAME,COLUMN_NAME,DATA_TYPE
Fromuser_tab_columns
Wherecolumn_nameIn--（SelectkeywordFromv$reserved_words）
（'ACCESS','ADD','ALL','ALTER','AND','ANY','AS','ASC','AUDIT',
'BETWEEN','BY','CHAR','CHECK','CLUSTER',
'COLUMN','COMMENT','COMPRESS','CONNECT','CREATE','CURRENT',
'DATE','DECIMAL','DEFAULT','DELETE',
'DESC','DISTINCT','DROP','ELSE','EXCLUSIVE','EXISTS','FILE',
'FLOAT','FOR','FROM','GRANT','GROUP',
'HAVING','IDENTIFIED','IMMEDIATE','IN','INCREMENT','INDEX',
'INITIAL','INSERT','INTEGER','INTERSECT',
'INTO','IS','LEVEL','LIKE','LOCK','LONG','MAXEXTENTS','MINUS',
'MLSLABEL','MODE','MODIFY','NOAUDIT',
'NOCOMPRESS','NOT','NOWAIT','NULL','NUMBER','OF','OFFLINE','ON',
'ONLINE','OPTION','OR','ORDER',
'PCTFREE','PRIOR','PRIVILEGES','PUBLIC','RAW','RENAME','RESOURCE',
'REVOKE','ROW','ROWID','ROWNUM',
'ROWS','SELECT','SESSION','SET','SHARE','SIZE','SMALLINT','START',
'SUCCESSFUL','SYNONYM','SYSDATE',
'TABLE','THEN','TO','TRIGGER','UID','UNION','UNIQUE','UPDATE',
'USER','VALIDATE','valueS','VARCHAR',
'VARCHAR2','VIEW','WHENEVER','WHERE','WITH'）
Orcolumn_nameLike'%%'
--检查数据库中相同名字不同类型的字段名
selecta.column_name,a.data_type,b.data_typeFrom
（selectdistinctcolumn_name,data_typefrom
all_tab_columnsWhereTABLE_NAMELike'T%'）a,
（selectdistinctcolumn_name,data_typefrom
all_tab_columnsWhereTABLE_NAMELike'T%'）b
wherea.column_name=b.column_nameanda.data_type<>b.data_type
--检查数据库中相同名字相同类型不同长度的字段名
selecta.column_name,a.data_type,b.data_type,
a.data_length,b.data_lengthFrom
（selectdistinctcolumn_name,data_type,
data_lengthfromall_tab_columnsWhereTABLE_NAMELike'T%'）a,
（selectdistinctcolumn_name,data_type,
data_lengthfromall_tab_columnsWhereTABLE_NAMELike'T%'）b
wherea.column_name=b.column_nameanda.data_type=b.data_typeAndA.DATA_LENGTH<>B.DATA_LENGTH
简单的oracle物化视图
物化视图是一种特殊的物理表，“物化”（Materialized）视图是相对普通视图而言的。
普通视图是虚拟表，应用的局限性大，任何对视图的查询，Oracle都实际上转换为视图SQL语句的查询。
这样对整体查询性能的提高，并没有实质上的好处。
1、物化视图的类型：
ONDEMAND、ONCOMMIT
二者的区别在于刷新方法的不同，ONDEMAND顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新（REFRESH），即更新物化视图，以保证和基表数据的一致性；而ONCOMMIT是说，一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。
2、ONDEMAND物化视图
物化视图的创建本身是很复杂和需要优化参数设置的，特别是针对大型生产数据库系统而言。
但Oracle允许以这种最简单的，类似于普通视图的方式来做，所以不可避免的会涉及到默认值问题。
也就是说Oracle给物化视图的重要定义参数的默认值处理是我们需要特别注意的。
物化视图的特点：
（1）物化视图在某种意义上说就是一个物理表（而且不仅仅是一个物理表），这通过其可以被user_tables查询出来，而得到佐证；
（2）物化视图也是一种段（segment），所以其有自己的物理存储属性；（3）物化视图会占用数据库磁盘空间，这点从user_segment的查询结果，可以得到佐证；
创建语句：
creatematerializedviewmv_nameasselect*fromtable_name
默认情况下，如果没指定刷新方法和刷新模式，则Oracle默认为FORCE和DEMAND。
物化视图的数据怎么随着基表而更新？
Oracle提供了两种方式，手工刷新和自动刷新，默认为手工刷新。
也就是说，通过我们手工的执行某个Oracle提供的系统级存储过程或包，来保证物化视图与基表数据一致性。
这是最基本的刷新办法了。
自动刷新，其实也就是Oracle会建立一个job，通过这个job来调用相同的存储过程或包，加以实现。
ONDEMAND物化视图的特性及其和ONCOMMIT物化视图的区别，即前者不刷新（手工或自动）就不更新物化视图，而后者不刷新也会更新物化视图，——只要基表发生了COMMIT。
3、ONCOMMIT物化视图
ONCOMMIT物化视图的创建，和上面创建ONDEMAND的物化视图区别不大。
因为ONDEMAND是默认的，所以ONCOMMIT物化视图，需要再增加个参数即可。
需要注意的是，无法在定义时仅指定ONCOMMIT，还得附带个参数才行。
创建ONCOMMIT物化视图：
creatematerializedviewmv_namerefreshforceoncommitasselect*fromtable_name
备注：
实际创建过程中，基表需要有主键约束，否则会报错（ORA-12014）
将MySQL数据迁移到Oracle
因为项目的原因，今晚将mysql数据库的内容尝试迁移到oracle，虽然结果失败，不过学到了不少，下次就不一定了，哈哈
因为之前项目是使用mysql数据库的，现在因为新公司要求使用oracle（公司大得很），不得不尝试将以前项目进行迁移。
经过查找相关文档，最终选定使用oraclesqldeveloper。
提示：
将Informix和DB2数据库迁移到Oracle。
有关所有其他数据库迁移，请访问OracleSQLDeveloper迁移。
下面的文档写的很好，官方的：
OracleSQLDeveloper迁移
这里详细介绍了将MicrosoftAccess、MicrosoftSQLServer和MySQL数据库迁移到
升级会员 