## 超级实用的Oracle学习笔记 原

bigsloth

© 逻辑运算符和谓词

in和not in 有哪些职员和分析员 SELECT ename,job

FORM emp

WHERE job IN ('clerk','analyst')

between和not between 哪些雇员的工资在2000和3000之间 SELECT ename,job,sal

FORM emp

WHERE sal BETWEEN 2000 AND 3000

like,not like SELECT ename,deptno

FORM emp

WHERE ename LIKE 'S%'

is null,is not null SELECT ename,job

FORM emp

WHERE comm IS NULL

© 数据操作

VALUES

(10,'accounting','new york',to_char(SYSDATE,'YYYY/MM/DD hh24:mi:ss') )

VALUES

(10,'accounting',(SELECT deptno FROM depemp WHERE empno=10 AND rownum<=1),to_char(SYSDATE,'YYYY/MM/DD hh24:mi:ss') )

从其它表中选择插入数据 INSERT INTO emp (empno,ename,deptno)

SELECT id,name,department

FORM old_emp

WHERE department in(10,20,30,40)

join FORM emp

JOIN dept ON emp.deptno=dept.deptno

SELECT empno,ename,job,emp.deptno,dname

FORM emp,dept

WHERE emp.deptno=dept.deptno

left (outer) join FORM m_user

LEFT OUTER JOIN m_user_account ON m_user.user_cd=m_user_account.user_cd

SELECT m_user.user_cd,m_user.user_number,m_user.user_name

FORM m_user,m_user_account

WHERE m_user.user_cd=m_user_account.user_cd(+)

right (outer) join FORM m_user

RIGHT JOIN m_user_account ON m_user.user_cd=m_user_account.user_cd

SELECT m_user.user_cd,m_user.user_number,m_user.user_name

FORM m_user,m_user_account

WHERE m_user.user_cd(+)=m_user_account.user_cd

full (outer) join FROM m_user

FULL JOIN m_user_account ON m_user.user_cd=m_user_account.user_cd

SELECT m_user.user_cd,m_user.user_number,m_user.user_name

FORM m_user,m_user_account

WHERE m_user.user_cd=m_user_account.user_cd(+)

UNION

SELECT m_user.user_cd,m_user.user_number,m_user.user_name

FORM m_user,m_user_account

WHERE m_user.user_cd(+)=m_user_account.user_cd

FORM emp,salgrade

WHERE salgrade.grade=3 and emp.sal between salgrade.losal and salgrade.hisal

FORM emp

WHERE deptno= (SELECT deptno FORM emp WHERE ename='smith')

字符串拼接 SELECT recvwork_user_cd || '_GP' FROM t_receivework

使用CASE语句 SELECT

(

CASE

WHEN recvwork_grouping_cd='GP0001' THEN to_char(recvwork_user_cd || '_GP1')

WHEN recvwork_grouping_cd='GP0002' THEN to_char(recvwork_user_cd || '_GP2')

ELSE to_char(recvwork_user_cd || '_GPN')

END

) AS recvwork_user_cd

FROM t_receivework

取N1-N2行 SELECT emName,emNo FROM

( SELECT ROWNUM ROWSEQ,emName,emNo FROM cat )

WHERE ROWSEQ BETWEEN N1 AND N2

FROM t_receivework

GROUP BY recvwork_user_cd HAVING count(*)>2

使用Exists语句 SELECT * FROM t_receive

WHERE EXISTS (

SELECT * FROM t_receivework_request

WHERE t_receive.receive_cd=t_receivework_request.receive_cd

AND t_receivework_request.contact_grouping_cd='GP0001' )

union运算 返回一个查询结果中有但又不重要的行，它将基表或视图中的记录合并在一起 SELECT ename,sal FORM account WHERE sal>2000

UNION

SELECT ename,sal FORM research WHERE sal>2000

intersect运算 返回查询结果中相同的部分 SELECT job FORM account

INTERSECT

SELECT job FORM research

minus运算 返回在第一个查询结果中与第二个查询结果不相同的那部分行记录 SELECT job FORM account

MINUS

SELECT job FORM sales;

© ORACLE系统函数

length(ename) 计算字符串的长度

substr(job,1,4) 取子字符串。如果job值为hello world，则值为hell

lower 变为小写的字符串

upper 变为大写的字符串

least 取出字符串列表中按字母排序排在最前面的一个串

greatest 取出字符串列表中按字母排序排在最后的一个串

instr(字段名,'字符串')>0 是否在字符串中

month_between(sysdate,hiredate) 计算hiredate时间与系统时间之间相差的月数

next_day(hiredate,'FRIDAY') 计算hiredate日期之后的第一个星期五的日期

to_date('2001-08-01','YYYY-MM-DD')

to_char(SYSDATE,'YYYY/MM/DD hh24:mi:ss')

D 一周中的星期几

DAY 天的名字，使用空格填充到9个字符

DD 月中的第几天

DDD 年中的第几天

DY 天的简写名

IW ISO标准的年中的第几周

IYYY ISO标准的四位年份

YYYY 四位年份

YYY,YY,Y 年份的最后三位，两位，一位

HH 小时，按12小时计

HH24 小时，按24小时计

MI 分

SS 秒

MM 月

Mon 月份的简写

Month 月份的全名

W 该月的第几个星期

WW 年中的第几个星期

select sysdate - interval '7' MINUTE from dual;

select sysdate - interval '7' hour from dual;

select sysdate - interval '7' day from dual;

select sysdate - interval '7' month from dual;

select sysdate - interval '7' year from dual;

select sysdate - 8 *interval '2' hour from dual;

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;

select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') from dual;

select to_char(sysdate, 'yyyy-ddd hh:mi:ss') from dual;

select to_char(sysdate, 'yyyy-mm iw-d hh:mi:ss') from dual;

trunc(sal,0) 取sal的近似值（截断）

avg(sal) 计算sal的平均值

stddev(sal) 计算sal的平均差

sum(sal) 计算sal的总值

IF EXPR1=NULL

RETURN EXPR2

ELSE

RETURN EXPR1

switch语句 DECODE(AA,V1,R1,V2,R2...) IF AA=V1 THEN RETURN R1

IF AA=V2 THEN RETURN R2

..…

ELSE

RETURN NULL

min(comm) 最小值

max (comm) 最大值

(

SELECT A.*, rownum r

FROM

(

XXXXXXXXXXX

) A

WHERE rownum <= PageUpperBound

) B

WHERE r > PageLowerBound;

© 关于表、View和索引的操作

VARCHAR2 可变长度的字符串

NUMBER(M,N)

DATE 日期类型

ALTER TABLE 表名1 TO 表名2; ALTER TABLEdept modify dname char(20);

ALTER TABLEdept modify (loc char(12));

ALTER TABLE表名 ADD 字段名 字段名描述; ALTER TABLEdept modify (dname char(13),loc char(12));

ALTER TABLE表名 MODIFY字段名 字段名描述;

ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (字段名);

ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名);

ALTER TABLE 表名 CACHE;

ALTER TABLE 表名 NOCACHE;

AS

SELECT ename,job,sal

FORM emp

WHERE deptno=10;

create index IDX_TD50030_1 on TD50030 (shiharai_taisyou_ym,shiharai_dairiten_c,dairiten_c) tablespace USER_IDX01;

CREATE SYNONYM同义词名FOR 表名@数据库链接名;

© 事务

© 触发器、存储过程、函数、游标

CREATE OR REPLACE TRIGGER <trigger_name>

在Oracle数据库中，触发器创建后，可以从表的Tirgger中查看。在写触发器的语句中，用:OLD表示操作前的数据，用:NEW表示操作后的数据。 <BEFORE | AFTER> <ACTION> ON <table_name>

编写触发器的过程中，要注意不要漏掉个别分号。 DECLARE

<variable definitions>

BEGIN

<trigger_code>

EXCEPTION

<exception clauses>

END ;

--使能用或者不能用--

ALTER TRIGGER <trigger_name> DISABLE/ENABLE

--删除--

DROP TRIGGER <trigger_name>

--例子--

CREATE OR REPLACE TRIGGER upd_test

AFTER UPDATE OF ENAME ON EMP

FOR EACH ROW

BEGIN

INSERT INTO EMPHISTORY(empno,EOLDNAME,ENEWNAME)

VALUES (:OLD.empno,:OLD.ENAME,:NEW.ENAME);

END;

存储过程的执行和触发器和函数不一样，是要在Java中手动执行的。Java调用存储过程的方法，请见这里。 CREATE OR REPLACE PROCEDURE procedure_name

<input or output variable definitions>

AS

<variable definitions>

BEGIN

<procedure_code>

EXCEPTION

<exception clauses>

END;

--例子-------1-----

CREATE OR REPLACE PROCEDURE drop_class

( arg_student_id IN varchar2, arg_class_id IN varchar2, status OUT number )

AS

counter number ;

BEGIN

status := 0 ;

select count (*) into counter from student_schedule where student_id = arg_student_id and class_id = arg_class_id ;

IF counter = 1 THEN

delete from student_schedule where student_id = arg_student_id and class_id = arg_class_id ;

status := -1 ;

END IF;

END;

--例子-------2-----

create or replace procedure insert_user_information

(

p_user_login_name in varchar2,

p_user_password in varchar2,

p_user_name in varchar2,

p_user_telephone in varchar2,

p_user_type in number,

p_out out number

) as

v_count number;

begin

if p_user_login_name is null or p_user_password is null then

p_out:=-1; --用户名和密码不能为空，

return ;

end if;

if p_user_type is null then p_out:=-2; --用户类型不能为空

return ;

end if;

select count(*) into v_count from user_information a where .user_login_name=upper(p_user_login_name);

if v_count>0 then

p_out:=-3; --该用户名已经存在

return ;

end if;

insert into user_information

values(seq_user_information.nextval,upper(p_user_login_name), p_user_password,p_user_name,p_user_telephone,p_user_type, sysdate,sysdate);

commit;

p_out:=0; --操作成功

return ;

exception

when others then

p_out:=-4; --插入过程中出现异常

return ;

end ;

--例子-------3-----

type c_offer is ref cursor return V_offer_other%rowtype;

procedure get_other_offers(e_id in varchar2,whereClause in varchar2,rc out c_offer);

import oracle.jdbc.driver.*

.....

cstmt.registerOutParameter(3,OracleTypes.CURSOR);

cstmt.execute();

rst=(ResultSet)cstmt.getObject(3);

CREATE [OR REPLACE] FUNCTION function_name

<input or output variable definitions>

RETURN return_type {IS | AS}

BEGIN

<function_code>

EXCEPTION

<exception clauses>

END;

--例子--

CREATE OR REPLACE FUNCTION getSameName(sameEname IN varchar2)

RETURN NUMBER

IS

samecount NUMBER;

BEGIN

Select count(*) INTO samecount  FROM emp WHERE ename = sameEname;

RETURN (samecount);

END;

SELECT ename,getSameName(ename) FROM Emp

open <游标名>      例 open color_cur;    <一组命令>

end loop;

%notfound 其中：

%found 索引是建立在每条记录的值之上的；记录名不必声明；每个值对应的是记录名，列名；初始化游标指打开游标；

%rowcount 活动集合中的记录自动完成FETCH操作；退出循环，关闭游标

%isopen

fetch my_cur into my_var;

while my_cur %found loop 隐式游标是指SQL命令中用到的，没有明确定义的游标

(处理数据) insert,update,delete,select语句中不必明确定义游标

fetch my_cur into my_var; 调用格式为SQL%

exit when my_cur %rowcount=10; 存贮有关最新一条SQL命令的处理信息

end loop;

%notfound属性 隐式游标有四个属性

fetch操作没有返回记录，则取值为true SQL%FOUND

fetch操作返回一条记录，则取值为false SQL%ROWCOUNT：隐式游标包括的记录数

<游标名> %notfound delete from baseball_team where batting_avg<100;

if sql%rowcount>5 then

insert into temp values('your team needs help');

if color_cur %notfound then... end if;

close <游标名>      例  close color_cur;

© ORACLE内部变量和方法

TAB 用户创建的所有基表、视图和同义词清单 SELECT * FORM tab;

DTAB   构成数据字典的所有表

COL 用户创建的基表的所有列定义的清单

CATALOG 用户可存取的所有基表清单

describe 描述基表的结构信息 describe dept

© 权限管理

1.创建用户

CREATE USER mydbuser IDENTIFIED BY mydbuser

2.GRANT 赋于权限

CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理)

ALL ON 数据对象名, SELECT ON 数据对象名, UPDATE ON 数据对象名,

DELETE ON 数据对象名, INSERT ON 数据对象名, ALTER ON 数据对象名

GRANT CONNECT, RESOURCE TO 用户名;

GRANT SELECT ON 表名 TO 用户名;

GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2;

3.REVOKE 回收权限

REVOKE CONNECT, RESOURCE FROM 用户名;

REVOKE SELECT ON 表名 FROM 用户名;

REVOKE SELECT, INSERT, DELETE ON表名 FROM 用户名1, 用户名2;

4.查询每个用户的权限

SELECT * FROM DBA_SYS_PRIVS;

© 数据导出

1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中

exp system/manager@TEST file=d:\daochu.dmp full=y

2 将数据库中system用户与sys用户的表导出

exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)

3 将数据库中的表inner_notify、notify_staff_relat导出

exp aichannel/aichannel@TESTDB2 file= d:\data\newsmgnt.dmp tables=(inner_notify,notify_staff_relat)

4 将数据库中的表table1中的字段filed1以"00"打头的数据导出

exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\"

5 将T1.dmp的数据导入用户KQA1中

imp KQA1/KQA1@orcl FILE=T1.DMP LOG=T1IMP.LOG FULL=Y SHOW=Y

6.抽出索引创建语句

expdp xu/xu directory=dp_dir dumpfile=ind.dmp include=index

impdp xu/xu directory=dp_dir dumpfile=ind.dmp sqlfile=ind.sql

© Flash Back

1.启用闪回数据库特性

startup mount;

alter database archivelog;

# archive log start;

alter database flashback on;

alter database open;

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V\$FLASHBACK_DATABASE_LOG;

select * from V\$FLASH_RECOVERY_AREA_USAGE

2.获得当前数据库的SCN值

select dbms_flashback.get_system_change_number fscn from dual; (9I 10G)

select current_scn from v\$database;

3.闪回日志

/data5/flash_recovery_area/EYGLE/flashback

4.进行闪回操作

shutdown immediate;

startup mount;

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select * from V\$FLASHBACK_DATABASE_LOG;

flashback database to timestamp to_timestamp ('2005-03-29 17:02:28','yyyy-mm-dd hh24:mi:ss');

5.resetlogs打开数据库

shutdown immediate;

startup mount;

#alter database open resetlogs;

alter database open noresetlogs

select * from v\$log;

6.设置闪回地址，大小，过期时间

alter system set db_recovery_file_dest='/flash_recovery_area';

alter system set db_recovery_file_dest_size=3G;

alter system set db_flashback_retention_target=240

alter database flashback on;

select flashback_on from v\$database;

7.闪回能够回到的最大SCN

select oldest_flashback_scn, oldest_flashback_time from v\$flashback_database_log;

8.flashback的代价（每小时必要的IO）

select end_time,flashback_data,db_data,redo_data from v\$flashback_database_stat;

9.查看当前闪回的大小

select * from v\$sgastat where name like 'flashback%';

10.闪回到某个SCN或者Sequence

select dbms_flashback.get_system_change_number c_SCN from dual;

flashback database to scn=2728665;

flashback database to sequence=2123 thread=1;

11.两天前的数据

select * from employees as of timestamp (sysdate - 2);

12.用SCN 查询

select * from employees as of SCN ;

13.表的删除和恢复:

flashback table order_items to before drop rename to order_items_old_version;

14.使用flash table

create table test as select * from dba_users;

select count(*) from test as of scn 12742550;

select count(*) from test as of timestamp to_timestamp('21-07-09 14:20:00', 'dd-mm-yy hh24:mi:ss');

alter table test enable row movement;

flashback table test to scn 1391833;

flashback table test to timestamp to_timestamp('22-07-09 13:20:00', 'dd-mm-yy hh24:mi:ss');

15.对table的flashback

flashback table t_user to before drop;

drop table t_user purge;

drop tablespace small including contents and datafiles;

purge table t_user;

purge user_recyclebin;

purge dba_recyclebin;

16.查看Undo信息

select begin_time,end_time, undoblks, maxquerylen,ssolderrcnt,nospaceerrcnt from v\$undostat;

17.使用还原点

create restore point restore_point_01 guarantee flashback database;

drop restore point restore_point_01;

flashback database to restore point restore_point_01;

18.使用回收站

show recyclebin;

select count(*) from dba_recyclebin where owner='DROPPER';

select owner, original_name, type, droptime, can_undrop, space from dab_recyclebin;

select object_name, original_name, type from user_recyclebin;

select sum(bytes) from dba_free_space where tablespace_name='SMALL';

select segment_name, bytes from dba_segments where tablespace_name='SMALL';

© RMAN

1.连接到rman

rman target sys/necsthz

2.不备份索引表空间

configure exclude for tablespace smsafeindex;

3.自动备份控制文件

configure controlfile autobackup on;

recover database until cancel using backup controlfile;

backup as copy current controlfile;

backup as backupset current controlfile;

backup tablespace system include current controlfile;

configure controlfile autobackup on;

4.进行0级增量备份

backup incremental level 0 database;

5.做增量0的备份压缩集，不包括索引表空间

backup incremental level 0 as compressed backupset database;

6.进行1级增量备份

backup incremental level 1 differential database;

7.做增量1备份

backup incremental level 1 as compressed backupset database;

8.进行1级累计备份

backup incremental level 1 cumulative database;

9.做增量1积累备份

backup incremental level 1 cumulative as compressed backupset database;

10.用增量备份更新映像副本

①backup as copy incremental level 0 database tag db_whole_copy;

②run{

allocate channel d1 type disk;

backup incremental level 1

for recover of copy with tag db_whole_copy

database tag db_copy_upd;

recover copy of database with tag db_whole_copy;

delete backupset tag db_copy_upd;}

11.备份全库并删除旧的归档日志

backup database plus archivelog delete input;

12.备份指定表空间

backup tablespace system plus archivelog delete input;

13.备份归档日志

backup archivelog all delete input;

14.查看备份

list backup;

15.验证备份

validate backupset 6;

16.基于时间点的恢复

set DBID=1454675292

startup nomount;

restore controlfile from autobackup;

alter database mount;

restore database until time "TO_DATE('03/14/07 15:00:00','MM/DD/YY HH24:MI:SS')";

recover database until time "TO_DATE('03/14/07 15:00:00','MM/DD/YY HH24:MI:SS')";

alter database open resetlogs;

17.基于cancel恢复

recover database until cancel;

recover database until change 309121;

18.restore数据库

restore database skip tablespace smsafeindex;

19.recover数据库

recover database skip forever tablespace smsafeindex;

20.Report

report schema;列出所有的用户和配置文件

report need backup; --列出未备份

report need backup days 3; --列出3天未备份的文件

report need backup redundancy 3; --少于3个备份的所有文件

report obsolete; --列出不再需要的副本

report obsolete redundancy 2; --备份数至少为3的备份

21.查看表空间的备份情况

list backup;

list backup of database;

list backup of datafile 4;

list backup of datafile 4 summary;

list backup of tablespace users;

list backup of controlfile;

list backup of archivelog all;

list backup of archivelog from sequence 1000 until sequence 1050;

list copy;

list copy of archivelog from time='sysdate-7';

22.删除并重建索引表空间

drop tablespace smsafeindex including contents;

create tablespace smsafeindex datafile 'd:\oracle\product\10.2.0\orcl\smsafeindex01.dbf' size 1G;

23.删除不要的备份

delete obsolete; --删除不要的备份

delete obsolete redundancy 2; --删除过多的备份

delete copy of datafile 6 tag file6_extra;

24.crosscheck

crosscheck archivelog from time="to_date('2008-5-20','yyyy-mm-dd')" until time="to_date('2008-5-23','yyyy-mm-dd')";

crosscheck archivelog all; 检查控制文件和实际物理文件的差别。

delete expired archivelog all; 同步控制文件的信息和实际物理文件的信息。

25.允许删除超过90天的备份

backup datafile 7 keep until time "sysdate+90" nologs;

26.用Rman创建duplicate数据库

rman target sys/oracle@orcl auxiliary sys/oracle@aux

duplicate target database to aux;

27.TSPITR

rman target /

recover tablespace users, example until time '2006-03-31:08:00:00' auxiliary destination '/u01/app/oracle/oradata/aux';

alter tablespace users online;

alter tablespace example online;

28.脚本

create script XXX

{

backup database;

}

create global script XXX1

{

backup database;

}

run

{

execute script XXX;

}

print script XXX;

29.特定脚本

○备份到时间点

run {

shutdown immediate;

startup mount;

set until sequence 10305 thread 1;

restore database;

recover database;

alter database open resetlogs;}

○备份到磁带

run {

allocate channel 'omni_0' type 'sbt_tape'

parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ocp10g,OB2BARLIST=test1)';

allocate channel 'omni_1' type 'sbt_tape'

parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ocp10g,OB2BARLIST=test1)';

backup incremental level 0 filesperset 1

format 'test1.dbf'

database;

}

○从磁盘一道磁带

run {

allocate channel t1 type sbt_tape;

allocate channel t2 type sbt_tape;

backup copy of database delete input;

backup archivelog all delete all input;

release channel t1;

release channel t2;

allocate channel d1 type disk;

allocate channel d2 type disk;

backup as copy database;

release channel d1;

release channel d2;}

○创建备份集(469/569) 单个通道，8小时内备份，二进制压缩

run {

allocate channel d1 type disk;

backup as compressed backupset

format '/u06/ocp10g/backup/%d_%u.dbf'

duration 8:00 minimize load

filesperset 1

database;

sql 'alter system archive log current';

backup as compressed backupset

format '/u06/ocp10g/backup/%d_%u.arc'

duration 8:00 minimize time

archivelog all delete all input;}

○保留超过一周的归档日志到磁带

run {

allocate channel t1 type sbt;

backup as backupset archivelog

until time 'sysdate - 7';}

○脱机备份

run {

shutdown immediate;

startup mount;

backup as backupset database;

alter database open;}

○备份用户空间

run {

allocate channel d1 type disk

maxpiecesize 50m;

backup as compressed backupset

format 'E:\oracle\backup\%U.dbf'

tablespace users;}

○将当前的控制文件备份到一个备份集

run {allocate channel d1 type disk;

backup as backupset format 'E:\oracle\backup\%U.ctl'

current controlfile;}

© 正则表达式

(1)'^' 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。

'\$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则 \$ 也匹配 'n' 或 'r'。

'.' 匹配除换行符 n之外的任何单字符。

'?' 匹配前面的子表达式零次或一次。

'*' 匹配前面的子表达式零次或多次。

'+' 匹配前面的子表达式一次或多次。

'( )' 标记一个子表达式的开始和结束位置。

'[]' 标记一个中括号表达式。

'{m,n}' 一个精确地出现次数范围，m=<出现次数<=n，'{m}'表示出现m次，'{m,}'表示至少出现m次。

'|' 指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)\$'表示所有小写字母或数字组合成的字符串。

-2

[[:alpha:]] 任何字母。

[[:digit:]] 任何数字。

[[:alnum:]] 任何字母和数字。

[[:space:]] 任何白字符。

[[:upper:]] 任何大写字母。

[[:lower:]] 任何小写字母。

[[:punct:]] 任何标点符号。

[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。

-3

regexp_like(x,pattern[,match_option])

match_option可以有如下几种形式：

1.'c' 表明进行匹配时区分大小写(这也是默认选项)

2.'i' 表明在匹配时不区分大小写

3.'n' 表明允许使用匹配任何字符串的元数据，即'.'

4.'m' 将x作为一个包含多行的字符串

select * from emp where regexp_like(to_char(birthdate,’yyyy’),’^198[0-9]\$’);

select * from emp where regexp_like(ename,’^j’,'i’)

-4

regexp_instr(x,pattern[,start[,occurrence[,return_option[,match_option]]]])

1. x 待匹配的字符串

2. pattern 待匹配的模式

3. start 开始匹配的位置，如果不指定默认为1

4. occurrence 匹配的次数，如果不指定，默认为1

5. return_option 指定返回值的类型，如果该参数为0，则返回值为匹配位置的第一个字符，如果该值为非0则返回匹配值的最后一个位置

6. match_option 可以用这个参数来修改一些默认的配置设置这个值与前面所说的regexp_like函数中的match_option参数的意义是一样的

(1)select regexp_instr('i love oracle','o[[:alpha:]]{4}e\$') as r from dual;

(2)select regexp_instr('The total is \$400 for your purchase.','\$[[:digit:]]+') from dual;

\$400在字符串的开始位置

select q'(amar's web blog. It's personal.)' str from dual;

select q'[amar's web blog. It's personal.]' str from dual;

select q'Aamar's web blog. It's personal.A' str from dual;

select q'/amar's web blog. It's personal./' str from dual;

select q'Zamar's web blog. It's personal.Z' str from dual;

select q'|amar's web blog. It's personal.|' str from dual;

select q'+amar's web blog. It's personal.+' str from dual;

insert into am102(col1, col2) values (1,q'[amar's web blog. It's personal]')

insert into am102(col1, col2) values (2,q'[this is a simple string]')

insert into am102(col1, col2) values (3,q'[this is just another string]')

© 有用的SQL语句

select table_name from user_tables;       ##获取数据库的全部表

select * from all_users;                 ##查看所有用户

select name from v\$database;             ##查看当前数据库名

select * from v\$instance;                ##查看所有的数据库实例

select username,password from dba_users; ##查看当前实例中的用户和密码

select member from v\$logfile;            ##查看日志文件

select * from user_role_privs;           ##查看当前用户的角色

select username,default_tablespace from user_users; ##查看当前用户的缺省表空间

select C.column_name,C.TABLE_NAME from dba_tab_columns C where owner=''

insert into table_a (id,name,age) select b.id,b.name,b.age from table_b;

select job,next_date,next_sec,failures,broken from user_jobs;

begin

dbms_job.remove(46);--46为job号

end;

select TABLE_NAME from all_tables;

select * from all_tables;

select table_name from all_tables where table_name like ‘u’;

create user mpss

identified by "mpss12"

default tablespace TS_MPSS_DATA

temporary tablespace TEMP;

CREATE

TEMPORARY TABLESPACE "SWVIP" TEMPFILE '/app/oracle/oradata/

sworacle/SWVIP.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM

SIZE 1M

create tablespace TS_MPSS_DATA datafile '/mpss/data/ts_mpss_data.bdf ' size 1024m autoextend on ;

SELECT D.TABLESPACE_NAME "Name",

TO_CHAR(((((A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024)) /(A.BYTES / 1024 /   1024))*100,'99,990.9') "used(%)",

TO_CHAR((DECODE(F.BYTES, NULL, 0, F.BYTES) / 1024 / 1024),'999,990.9') "Free (M)"

FROM SYS.DBA_TABLESPACES D, SYS.SM\$TS_AVAIL A, SYS.SM\$TS_FREE F

WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME

AND F.TABLESPACE_NAME (+) = D.TABLESPACE_NAME;

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",

ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"

FROM

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) D,

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

UNION ALL --if have tempfile

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,

USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",

NVL(FREE_SPACE,0) "FREE_SPACE(M)"

FROM

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS

FROM DBA_TEMP_FILES

GROUP BY TABLESPACE_NAME) D,

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,

ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE

FROM V\$TEMP_SPACE_HEADER

GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ;

select tablespace_name, file_id, file_name,

round(bytes/(1024*1024),0) total_space

from dba_data_files

order by tablespace_name;

col file_name format a50

select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;

select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

select a.session_id,a.process,a.locked_mode,b.object_name,b.object_type,b.status from v\$locked_object a,dba_objects b where a.object_id=b.object_id;

select username,sid,serial# from v\$session;

alter tablespace G000 add datafile '/dev/vgbilling/rg000_lv03' SIZE 7500m;

CREATE DATABASE LINK LK_KQA CONNECT TO KQA1 IDENTIFIED BY KQA1 USING 'ORCL';

create materialized view FRAME refresh fast for update as select

ACCOMMODATION_AREA_CODE,

FACILITY_NAME,

FACILITY_NUMBER,

DEVICE_NUMBER,

FACILITY_TYPE,

COUPLER_TYPE,

FLOOR_NAME,

FLOOR_GROUP,

FLOOR_FRAME_NUMBER,

REAL_ACCOMMODATE_BIL_CODE

from k027.FRAME@k027

where ACCOMMODATION_AREA_CODE = '27205001';

sequence使用

create sequence seq_product INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE CACHE 10;

--seq_product.CURRVAL

--seq_product.NEXTVAL

CREATE TABLE CENTER_ACROSS_INFO (

ACCOMMODATION_AREA_CODE VARCHAR2(24) NOT NULL ,

FACILITY_NAME VARCHAR2(30) NOT NULL ,

FACILITY_NUMBER VARCHAR2(39) NOT NULL);

ALTER TABLE CENTER_ACROSS_INFO ADD COLUMN DEVICE_NUMBER VARCHAR(30);

ALTER TABLE CENTER_ACROSS_INFO ALTER COLUMN DEVICE_NUMBER NUMBER(2,4);

ALTER TABLE CENTER_ACROSS_INFO DROP COLUMN DEVICE_NUMBER ;

ALTER TABLE CENTER_ACROSS_INFO ADD CONSTRAINT

CENTER_ACROSS_INFO_PK PRIMARY KEY (ACCOMMODATION_AREA_CODE, FACILITY_NAME);

--DROP TABLE CENTER_ACROSS_INFO;

create tablespace DATA

datafile

'd:/DATA/data01.dbf' size 512M reuse autoextend off

extent management local uniform size 10M

segment space management auto;

alter tablespace DATA add datafile 'd:/DATA/data02.dbf' size 512M;

lsnrctl start

startup mount

alter database archivelog;

alter database open;

archive log list;

archive log all;

select log_mode from v\$database;

alter system set control_files="E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTL" scope=spfile;

startup force;

select * from v\$controlfile;

select recid,name,thread#,sequence# from v\$archived_log

create tablespace noncrit datafile 'E:\oracle\product\10.1.0\oradata\orcl\noncrit.dbf' size 2m;

create table ex203 (c1 date) tablespace noncrit;

insert into ex203 values(sysdate);

alter database datafile 7 online;

alter database datafile 3 offline;

Alter database backup control file to trace.

alter table dept enable row movement;

alter table emp enable row movement;

flashback table emp,dept to timestamp to_timestamp('21-07-09 14:20:00', 'dd-mm-yy hh24:mi:ss');

flashback table emp,dept to scn 6539425 enable triggers;

select sid,username,program from v\$session;

tnsping qnetdb

SQL>show parameter mttr

fast_start_mttr_target integer 300

show parameter recovery

show parameter db_recovery

show parameter log_archive_dest

alter system set fast_start_mttr_target=0;

select count(*) from dba_objects,dba_objects;

select cpu_time,elapsed_time,disk_reads from v\$sql where sql_text='select count(*) from dba_objects,dba_objects ';

select reason,metric_value from dba_outstanding_alerts;

create index xu.rname_idx on xu.regions(region_name) tablespace indx parallel 8;

create index xu.rname_idx on xu.regions(region_name) tablespace indx nologging;

select name, versions_startscn,versions_endscn from countries versions between scn minvalue and maxvalue;

create table countries (name varchar2(50));

insert into countries values ('hello');

insert into countries values ('tba');

update countries set name='abc' where name='tba'

alter table countries enable row movement;

flashback table countries to scn 12776941 enable triggers;

select distinct FILE_NAME, TABLESPACE_NAME from dba_data_files;

select owner,table_name,tablespace_name from all_tables where tablespace_name='USERS';

select extent_id, file_id, block_id, blocks from dba_extents where owner='XU' and segment_name='USERS';

select

a.tablespace_name "Name",

round(a.bytes_alloc/1024/1024,2) "all(M)",

round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "used(M)",

round(nvl(b.bytes_free,0)/1024/1024,2) "free(M)",

round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "free(%)"

from (select f.tablespace_name,

sum(f.bytes) bytes_alloc,

sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes

from dba_data_files f

group by tablespace_name) a,

(select f.tablespace_name,

sum(f.bytes) bytes_free

from dba_free_space f

group by tablespace_name) b

where a.tablespace_name = b.tablespace_name;

dbv file=/oracle/oradata/new_tbs.dbf blocksize=8192;

select wait_class,total_waits,time_waited from v\$system_wait_class order by time_waited;

execute dbms_workload_repository.modify_snapshot_settings(retention=>20160,interval=>20);

select recovery_estimated_ios ios, actual_redo_blks redo,target_mttr, estimated_mttr, writes_mttr from v\$instance_recovery;

select sql_id from v\$sql where sql_text='select max(c1) from t1';

select file_id,file_name,bytes,autoextensible,increment_by,maxbytes from dba_data_files;

alter database datafile 7 autoextend on;

alter database datafile '/oradata/users01.dbf' autoextend on next 10m maxsize 200m;

select tablespace_name, sum(bytes) from dba_free_space group by tablespace_name;

select object_name, reason from dba_outstanding_alerts;

alter session enable resumable timeout 60 name 'AR archive';

alter session enable resumable;

select owner,bytes from dba_segments where segment_name='TEST';

alter table test enable row movement;

alter table test shrink space;

ANALYZE TABLE table_name VALIDATE STRUCTURE CASCADE;

ANALYZE INDEX index_name VALIDATE STRUCTURE;

analyze index i1 validate structure;

select lf_rows_len, del_lf_rows_len from index_stats where name='I1';

select bytes from dba_segments where segment_name='I1';

alter index i1 shrink space;

analyze index i1 validate structure;

alter index i1 coalesce;

alter index i1 rebuild online;

alter index i1 rebuild online tablespace idx_ts;

alter database backup controlfile to trace;

alter database backup controlfile to '/oracle/app/product/10.2.0/db_1/tmp';

show parameter user_dump_dest;

shutdown immediate;

startup nomount;

CREATE CONTROLFILE REUSE DATABASE "orcl" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 454

LOGFILE

GROUP 1 (

'E:\oracle\product\10.1.0\oradata\orcl\REDO01.LOG'

) SIZE 10M,

GROUP 2 (

'E:\oracle\product\10.1.0\oradata\orcl\REDO02.LOG'

) SIZE 10M,

GROUP 3 (

'E:\oracle\product\10.1.0\oradata\orcl\REDO03.LOG'

) SIZE 10M

-- STANDBY LOGFILE

DATAFILE

'E:\oracle\product\10.1.0\oradata\orcl\SYSTEM01.DBF',

'E:\oracle\product\10.1.0\oradata\orcl\UNDOTBS01.DBF',

'E:\oracle\product\10.1.0\oradata\orcl\SYSAUX01.DBF',

'E:\oracle\product\10.1.0\oradata\orcl\USERS01.DBF',

'E:\oracle\product\10.1.0\oradata\orcl\EXAMPLE01.DBF'

CHARACTER SET WE8MSWIN1252

;

select group#,sequence#,archived,status from v\$log;

select group#,status,member from v\$logfile order by group#;

alter database clear logfile group 2;

alter database clear unarchived logfile group 2;

recover database until cancel;

select * from v\$recovery_file_dest;

select reason from dba_outstanding_alerts;

alter system switch logfile;

select group#,sequence#,members,status from v\$log;

select group#,status,member from v\$logfile;

alter system checkpoint;

ALTER DATABASE ADD LOGFILE MEMBER 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\myAddLog' TO GROUP 1

SHOW USER ;

select sys_context('userenv','ip_address') from dual;

SELECT * FROM V\$PARAMETER WHERE NAME LIKE 'proc%';

select * from v\$locked_object ;

select name from v\$database;

truncate table table_name;

ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME;

ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME

ALTER SYSTEM KILL SESSION ‘SID,SERIR#';

ORACLE产生随机函数

DBMS_RANDOM.RANDOM

SELECT * FROM USER_OBJECTS;

SELECT * FROM DBA_SEGMENTS;

SELECT * FROM USER_ERRORS;

SELECT * FROM DBA_DB_LINKS;

SELECT * FROM NLS_DATABASE_PARAMETERS;

SELECT * FROM V\$NLS_PARAMETERS;

select name,value\$ from props\$ where name like 'NLS%'

SELECT * FROM DBA_DATA_FILES;

select b.file_id 文件ID号, b.tablespace_name 表空间名, b.bytes 字节数, (b.bytes-sum(nvl(a.bytes,0))) 已使用, sum(nvl(a.bytes,0)) 剩余空间, sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order by b.file_id

select systimestamp from dual;

select 'Welcome to visit'||chr(10)||'www.CSDN.NET' from dual ;

alter session set nls_date_format='yyyymmddhh24miss';

select tablespace_name from user_tables where table_name='TEST';

create table New_table as (select * from Old_table);

select * from dba_tablespaces;

insert into table_name values (translate ('at{&}t','at{}','at'));

SELECT * FROM V\$INSTANCE;

select * from all_tables;

set timing on ;

select * from tablename;

SELECT CHAR(65) FROM DUAL;

SELECT ASCII('A') FROM DUAL;

select * from table_name where trunc(日期字段)＝to_date('2003-05-02','yyyy-mm-dd');

conn internal ;

show parameter processes ;

select add_months(sysdate,24) from dual;

SELECT CEIL(N) FROM DUAL;

SELECT FLOOR(N) FROM DUAL;

SELECT LAST_DAY(SYSDATE) FROM DUAL

SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW;

SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL; -- 年

SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL; -- 月

SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL; -- 日

SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL; -- 时

SELECT TO_CHAR(SYSDATE,'MI') FROM DUAL; -- 分

SELECT TO_CHAR(SYSDATE,'SS') FROM DUAL; -- 秒

SELECT TO_CHAR(SYSDATE,'D') FROM DUAL; -- 星期

SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL; -- 第几天

SELECT TO_CHAR(SYSDATE,'WW') FROM DUAL; -- 第几周

SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') FROM DUAL; -- 时间

to_char(1210.78, '9999.9') = '1210.8'

to_char(1210.73, '9999.9') = '1210.7'

to_char(1210.73, '9, 999.99') = '1, 210.73'

to_char(1210.73, '\$9, 999.00') = '\$1, 210.73'

to_char(21, '000099') = '000021'

to_char(sysdate, 'ss') 取当前时间秒部分 a

to_char(sysdate, 'mi') 取当前时间分钟部分

to_char(sysdate, 'HH24') 取当前时间秒小时部分

to_char(sysdate, 'DD') 取当前时间日期部分

to_char(sysdate, 'MM') 取当前时间月部分

to_char(sysdate, 'YYYY') 取当前时间年部分

to_char(sysdate, 'w') 取当前时间是一个月中的第几周(从1日开始算)

to_char(sysdate, 'ww') 取当前时间是一年中的第几周（从1.1开始算）

to_char(sysdate, 'iw') 取当前时间是一年中的第几周（按实际日历的）

to_char(sysdate, 'd') 取当前时间是一周的第几天，从星期天开始，周六结束

to_char(sysdate, 'day') 取当前日是星期几，和数据库设置的字符集有关，会输出'Tuesday'

to_char(sysdate, 'ddd') 当前日是一年中的第几天

to_char(sysdate, 'yyyy/mm/dd');

to_char(sysdate, 'Month DD, YYYY');

to_char(sysdate, 'FMMonth DD, YYYY');

to_char(sysdate, 'MON DDth, YYYY');

to_char(sysdate, 'FMMON DDth, YYYY');

to_char(sysdate, 'FMMon ddth, YYYY');

SELECT TRUNC(SYSDATE) FROM DUAL;

SELECT TO_DATE('2003/08/01') FROM DUAL;

SELECT CONVERT('要改变的字符串','US7ASCII','WE8HP') 'CONVERSION' FROM PUBS;

ALTER USER hr ACCOUNT LOCK;

ALTER USER hr ACCOUNT UNLOCK;

alter database enable block change tracking using file

'e:/oracle/backup/change_tracking.dbf';

alter system set max_dump_file_size='5m';

select internal_metric_name from v\$alert_types where internal_metric_name like '%_ps';

select reason,object_type type,object_name name from dba_outstanding_alerts;

select reason,object_type type,object_name name from dba_alert_history where object_name='SMALL';

execute dbms_server_alert.set_threshold(-

metrics_id=>dbms_server_alert.tablespace_pct_full,-

warning_operator=>dbms_server_alert.operator_ge,-

warning_value=>'50',-

critical_operator=>dbms_server_alert.operator_ge,-

critical_value=>'75',-

observation_period=>1,consecutive_occurrences=>2,-

instance_name=>null,-

object_type=>dbms_server_alert.object_type_tablespace,-

object_name=>'SMALL');

begin

for n in 1..500 loop

insert into toobig values('a row');

end loop;

commit;

end;

/

alter system set sql_trace=true;

select s.username,s.sid,p.spid from v\$session s, v\$process p where s.paddr=p.addr;

alter session set sql_trace=true;

execute dbms_monitor.session_trace_disable( -

session_id=>162,serial_num=>14);

alter session set sql_trace=false;

alter tablespace temp_ts3 add tempfile 'c:/ts2.bdf' size 100m;

alter database tempfile 'c:/ts1.bdf' offline;

alter database tempfile 'c:/ts1.bdf' drop;

create temporary tablespace temp_ts4 tempfile 'c:/ts4.bdf' size 100m;

alter database default temporary tablespace temp_ts4;

drop tablespace temp_ts3 including contents and datafiles;

select group#,sequence#,bytes,members,archived,status from v\$log;

select group#,status,member from v\$logfile order by group#;

alter database drop logfile member 'c:/redo01.log';

alter database add logfile member 'c:/redo02.log' to group 1;

alter database clear logfile group 2;

select name,status from v\$datafile where file#=7;

select online_status,error from v\$recover_file where file#=7;

alter index reg_id_pk monitoring usage;

select index_name, table_name,used from v\$object_usage;

alter index reg_id_pk nomoinitoring usage;

CREATE DIRECTORY TEST AS 'e:\data\';

GRANT READ ON DIRECTORY TEST TO mydbuser;

GRANT WRITE ON DIRECTORY TEST TO mydbuser;

CREATE TABLE oldempx (empno number,empname char(20))

ORGANIZATION EXTERNAL

(TYPE ORACLE_LOADER DEFAULT DIRECTORY TEST ACCESS PARAMETERS

(RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ','

(empno char, empname char)) LOCATION ('emp1.txt'))

parallel 5 reject limit 200;

desc employees;

1.指定实例名

export ORACLE_SID=ax

cd \$ORACLE_HOME/dbs

2.创建密码文件

orapwd file=orapwclone password=oracle

3.从原数据库拷贝spfile

create pfile from spfile;

4.创建物理文件

mkdir -p ax/(adump,bdump,cdump,udump)

mkdir /u01/app/oracle/oradata/ax

cp initorcl.ora initclone.ora

vi initclone.ora orcl -> ax

*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/ax'

*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/ax'

create spfile from pfile;

ROLLUP

create table emp_rollup as select * from dba_indexes;

select index_type, status, count(*) from emp_rollup group by index_type, status;

select index_type, status, count(*) from emp_rollup group by rollup(index_type, status);

select index_type, status, count(*) from emp_rollup group by cube(index_type, status);

timezone

SELECT DBTIMEZONE,SESSIONTIMEZONE FROM DUAL;

CREATE TABLE TIMESTAMP_TEST(TIME DATE, TIMESTP TIMESTAMP(3), TIMESTP_TZ TIMESTAMP(3) WITH TIME ZONE, TIMESTP_LTZ TIMESTAMP(3) WITH LOCAL TIME ZONE);

INSERT INTO TIMESTAMP_TEST VALUES(SYSDATE,SYSDATE,SYSDATE,SYSDATE);

SELECT * FROM TIMESTAMP_TEST;

ALTER SESSION SET TIME_ZONE='+10:00';

SELECT * FROM TIMESTAMP_TEST;

DECLARE

task_name varchar2(30);

sql_stmt clob;

BEGIN

sql_stmt := 'select /*+ full(a) use_hash(a) ' ||

' parallel(a,8) full(b) use_hash(b) parallel(b,8) ' ||

' */ a.type, sum(a.amt_paid) ' ||

' from large_table a, large_table2 b ' ||

' where a.key = b.key ' ||

' and state_id = :bnd';

task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_text => sql_stmt,

bind_list => sql_binds (anydata.ConvertNumber(32));

user_name => 'BUTERTB',

scope => 'COMPREHENSIVE',

time_limit => 45,

task_name => 'large_table_task',

description => 'Tune state totals query');

dbms_output.put_line('Task ' || task_name ||

' has been created.');

END;

/

BEGIN

DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'large_table_task');

END;

The status of the executing task can be monitored by querying the DBA_ADVISOR_LOG view or V\$SESSION_LONGOPS:

select status from dba_advisor_log where task_name = 'large_table_task';

BEGIN

DBMS_SQLTUNE.INTERRUPT_TUNING_TASK( task_name => 'large_table_task');

END;

If you want to interrupt a tuning task that is currently executing, use the INTERRUPT_

TUNING_TASK procedure:

BEGIN

DBMS_SQLTUNE.INTERRUPT_TUNING_TASK( task_name => 'large_table_task');

END;

Task Results

set long 1000

set longchunksize 1000

set linesize 132

select dbms_sqltune.report_tuning_task('large_table_task') from dual;

Accepting a SQL Profile

DECLARE

sqlprofile_name varchar2(30);

BEGIN

sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(

task_name => 'large_table_task',

profile_name => 'large_table_profile');

END;

begin

DBMS_SQLTUNE.ALTER_SQL_PROFILE(

name => 'large_table_profile',

Atribute_name =>'STATUS',

Value => 'ENABLES');

END;

BEGIN

DBMS_SQLTUNE.DROP_SQL_PROFILE(

Name => 'large_table_profile');

END;

BEGIN

DBMS_SQLTUNE.CREATE_SQLSET(

Sqlset_name => 'load_proc_set',

Description => 'SQL used in load procedure');

END;

DECLARE

sql_cursor DBMS_SQLTUNE.SQLSET_CURSOR;

begin_snap number := 1; /* beginning snapshot id

end_snap number := 5; /* end snapshot id */

BEGIN

open sql_cursor for

select value(p)

from table (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(begin_snap, end_snap) p;

DBMS_SQLTUNE.LOAD_SQLSET(

sqlset_name => 'load_proc_set',

populate_cursor => sql_cursor);

END;

/

SQL Loader工具

○ sqlldr scott/tiger control=loader.ctl

○ loader.ctl 如下:

load data

infile 'c:\data\mydata.csv'

into table emp

fields terminated by "," optionally enclosed by '"'

(empno, empname, sal, deptno )

○ mydata.csv 如下:

10001,"Scott Tiger", 1000, 40

10002,"Frank Naude", 500, 20

BEGIN

DBMS_SCHEDULER.CREATE_PROGRAM(

program_name => 'CALC_STATS2',

program_action => 'HR.UPDATE_HR_SCHEMA_STATS',

program_type => 'STORED_PROCEDURE',

enabled => TRUE);

END;

/

BEGIN

DBMS_SCHEDULER.CREATE_SCHEDULE(

schedule_name => 'stats_schedule',

start_date => SYSTIMESTAMP,

end_date => SYSTIMESTAMP + 30,

repeat_interval =>

'FREQ=HOURLY;INTERVAL=1',

comments => 'Every hour');

END;

/

BEGIN

DBMS_SCHEDULER.CREATE_JOB(

job_name=>'HR.DO_BACKUP',

job_type => 'EXECUTABLE',

job_action => '/home/usr/dba/rman/nightly_incr.sh',

start_date=> SYSDATE,

repeat_interval=>'FREQ=DAILY;BYHOUR=23',

/* next night at 11:00 PM */

comments => 'Nightly incremental backups');

END;

/

BEGIN

DBMS_SCHEDULER.CREATE_JOB(

job_name=>'ADMIN.PERFORM_DATA_LOAD',

job_type => 'EXECUTABLE',

job_action => '/home/usr/dba/rman/report_failure.sh',

start_date => SYSTIMESTAMP,

event_condition => 'tab.user_data.object_owner =

''HR'' and tab.user_data.object_name = ''DATA.TXT''

and tab.user_data.event_type = ''FILE_ARRIVAL''

and tab.user_data.event_timestamp < 9 ',

queue_spec => 'HR.LOAD_JOB_EVENT_Q');

END;

DBMS_SCHEDULER.SET_ATTRIBUTE('hr.do_backup', 'raise_events', DBMS_SCHEDULER.JOB_FAILED);

SELECT job_name, status, error#, run_duration FROM USER_SCHEDULER_JOB_RUN_DETAILS;

Disk Group

1.创建

CREATE DISKGROUP dgroupA NORMAL REDUNDANCY

FAILGROUP controller1 DISK

'/devices/A1' NAME diskA1 SIZE 120G FORCE,

'/devices/A2',

'/devices/A3'

FAILGROUP controller2 DISK

'/devices/B1',

'/devices/B2',

'/devices/B3';

2.删除

DROP DISKGROUP dgroupA INCLUDING CONTENTS;

3.修改

ALTER DISKGROUP dgroupA ADD DISK

'/dev/rdsk/c0t4d0s2' NAME A5,

'/dev/rdsk/c0t5d0s2' NAME A6,

'/dev/rdsk/c0t6d0s2' NAME A7,

'/dev/rdsk/c0t7d0s2' NAME A8;

ALTER DISKGROUP dgroupA ADD DISK '/devices/A*';

ALTER DISKGROUP dgroupA DROP DISK A5;

© 著作权归作者所有

### bigsloth

ORACLE学习笔记（二）​

DB Administration Tools Oracle Universal Installer（OUI） ：OUI是用来安装、升级和删除Oracle软件。 Oracle Database Configuration Assistance（DBCA） ：DBCA是一个图形界面的程序，可...

coolio
2014/10/19
0
0
Windows8/Silverlight/WPF/WP7/HTML5周学习导读(1月28日-2月3日)

Windows8/Silverlight/WPF/WP7/HTML5周学习导读(1月28日-2月3日) 本周Windows 8开发学习资源更新 本周Silverlight学习资源更新 本周Windows Phone开发学习资源更新 本周WPF学习资源推荐 本周...

2018/06/29
0
0
2017年读了几千块钱的书，但是我只推荐这几本

2017年是我自从娘胎里出来读书最多的一年，过去很多年加起来（除了教科书）都没有今年一年多。 最近很多伙伴总是问我能不能推荐一些好书，刚好利用这个机会，立马出现在我脑海里的，印象深刻...

2017/12/17
0
0
AngularJS学习笔记 目录

Asktao
2016/07/30
151
0
【静默】Oracle各类响应文件何在？

【静默】Oracle各类响应文件何在？ --root用户下执行： find -name *.rsp / 1、创建数据库的响应文件：\$ORACLE_HOME/assistants/dbca/dbca.rsp 例如：/u01/app/oracle/product/11.2.0/dbhome...

2018/08/07
0
0

jdk8-64

https://pan.baidu.com/s/1sunIF-dBeyDKjFEpuYFyTQ 密码：jhuj

20分钟前
1
0
CentOS 7 网络设置及静态IP配置

calmsnow
23分钟前
0
0

25分钟前
0
0
LIst的逆向遍历

public class list_demo { public static void main(String[] args) {// TODO Auto-generated method stub List list=new ArrayList<>(); list.add("a"); list.add("b");......

3
0
MySQL插入性能优化

MySQL插入性能优化 标签： 博客 [TOC] 可以从如下几个方面优化MySQL的插入性能。 代码优化 values 多个 即拼接成一个insert values sql, 例如 INSERT INTO MyTable ( Column1, Column2, Co...

13
0