oracle认证应用技术学习资料汇总Word格式文档下载.docx

oracle认证应用技术学习资料汇总Word格式文档下载.docx

《oracle认证应用技术学习资料汇总Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《oracle认证应用技术学习资料汇总Word格式文档下载.docx（168页珍藏版）》请在冰豆网上搜索。

　　#常用的数据字典

　　dba_constraints

　　dba_cons_columns

　　user_cons_columns

　　user_constraints

　　简单应用

　　检验当为一个表建立主键索时后，这个字段是否满足约束非空，唯一性，而且自动建立一个索引，并查看当把约束状态关闭再次插入相同的记录,是否还能把把约束设为enableividate状态。

　　SQL>

createtablet（inumberconstraintpk_iprimarykey,vnumber）;

insertintotvalues（1,2）;

insertintotvalues（3,4）;

commit;

select*fromt;

　　IV

　　-------------------------------------

　　12

　　34

　　现在表中有两条记录，然后给它插主键为空或相同的值

insertintotvalues（'

'

10）;

　　ERRORatline1:

　　ORA-01400:

cannotinsertNULLinto（"

Y"

."

T"

I"

）

insertintotvalues（1,10）;

　　ORA-00001:

uniqueconstraint（Y.PK_I）violated

　　可以看到全部报错，此时主键不能为空或重复

　　查看是否建立索引

selectindex_namefromuser_indexes;

　　INDEX_NAME

　　------------------------------

　　PK_I

　　把约束关闭再次做同样的操用

altertabletdisablenovalidateconstraintpk_i;

　　Tablealtered.

　　1rowcreated.

　　Commitcomplete.

　　--------------------

　　10

　　110

　　norowsselected

　　可见当把约束关闭后就可以何意给表插数据了，而具索引也自动删除了。

　　现在激活约束

altertabletenablevalidateconstraintpk_i;

　　altertabletenablevalidateconstraintpk_i

　　ORA-02437:

cannotvalidate（SYS.PK_I）-primarykeyviolated

　　因为表中主键有相同的值所以不能恢复到enablevalidate状态了

　　再次测试回复到enablenovalidate

altertabletenablenovalidateconstraintpk_i;

　　也失败了,

　　因为表中主键有了空值和相同的值,所以恢复不到enablevalidate状态,但enablenovalidate不检查旧数据所以应该还能恢复到enablenovalidate.

　　要想恢复到enablenovalidate必须建立主键索引（关闭约束时自动删除的那个索引）如下:

createindexpk_iont（i）;

　　Indexcreated.

　　然后恢复到enabledisvalidate,以后再插数据不能为空,主键也不能重复了.

insertintotvalues（1,14）;

　　insertintotvalues（1,14）

uniqueconstraint（SYS.PK_I）violated

　　2.修正约束数据

　　当给一个表作主键约束时,因为已存数据不满足约束规则,会提示错误信息,些时必须对数据进行修正

　　要修正数据先找出不满足约束的数据

　　如下表,有不满足约束的数据

　　----------------------------------

　　1512

　　1510

　　如果一个表数据量多可通过如下方法查找

altertabletdropconstraintpk_i;

conny/123

@$ORACLE_HOME/rdbms/admin/utlexcpt.sql

　　Tablecreated.

altertabletaddconstraintpk_iprimarykey（i）exceptionsintoexceptions;

　　select*fromtwhererowidin（selectrow_idfromexceptions）

　　1510

　　找到了重复的记录

　　修正

updatetseti=10wherev=12;

　　I　　　　　　　V

　　1　　　　　　　2

　　3　　　　　　　4

　　10　　　　　　　12

　　15　　　　　　　10

　　再建主键约束

　　成功了!

!

回收临时表空间ORA-03297解决

Examda提示:

临时表空间达到15G，但使用率才2%不到，回收一下

alterdatabasetempfile'

d:

\oracle\oradata\ynrmdb\temp01.dbf'

resize2048M;

　　alterdatabasetempfile'

resize2048M

　　*

　　ERROR位于第1行:

　　ORA-03297:

文件包含在请求的RESIZE值以外使用的数据

　　看样子，普通方法是比较麻烦了

alterdatabasetempfile'

drop;

　　Databasealtered.

altertablespacetempaddtempfile

　　2'

　　3size2048Mreuseautoextendonnext100M;

　　Tablespacealtered.

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 

　　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,

）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'

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数据库迁移到