Tag Archives: Oracle_Basics

dbms_stats.import_table_stats不可以把统计信息导给别的表

Posted on by

今天在itpub看到一个问题 说是使用dbms_stats.import_table_stats并不会导入统计信息 然后做了一个实验如下

SQL> create table test as select * from dba_objects;

Table created.

SQL> create index idx_test on test(object_id);

Index created.

SQL> begin
dbms_stats.gather_table_stats(ownname          => ‘SCOTT’,
tabname          => ‘TEST’,
estimate_percent => 100,
degree           => 2,
cascade          => true);
end;  2    3    4    5    6    7
8  /

PL/SQL procedure successfully completed.

SQL> create table test_temp as select * from test;

Table created.

SQL> create index idx_test_temp on test_temp(object_id);

Index created.

SQL> select table_name,
num_rows,
blocks,
empty_blocks,
avg_space,
avg_row_len,
sample_size
from user_tables
where table_name in (‘TEST’, ‘TEST_TEMP’);  2    3    4    5    6    7    8    9

TABLE_NAME                         NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE
—————————— ———- ———- ———— ———-
AVG_ROW_LEN SAMPLE_SIZE
———– ———–
TEST                                    75253        1098                0           0
97          75253

TEST_TEMP

SQL> begin
— dbms_stats.drop_stat_table(ownname=>’scott’,stattab=>’stat_test_temp’);
dbms_stats.create_stat_table(ownname=>’scott’,stattab=>’stat_test_temp’);
dbms_stats.export_table_stats(ownname=>’scott’,tabname=>’test’,stattab=>’stat_test_temp’,cascade => true);
dbms_stats.delete_table_stats(ownname=>’scott’,tabname=>’test_temp’);
dbms_stats.import_table_stats(ownname=>’scott’,tabname=>’test_temp’,stattab=>’stat_test_temp’);
end;  2    3    4    5    6    7
8  /

PL/SQL procedure successfully completed.

SQL> select table_name,
num_rows,
blocks,
empty_blocks,
avg_space,
avg_row_len,
sample_size
from user_tables
where table_name in (‘TEST’, ‘TEST_TEMP’);  2    3    4    5    6    7    8    9

TABLE_NAME                         NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE
—————————— ———- ———- ———— ———-
AVG_ROW_LEN SAMPLE_SIZE
———– ———–
TEST                                    75253        1098                0           0
97          75253

TEST_TEMP

以上是你的实验

再往下看

SQL> begin
dbms_stats.drop_stat_table(ownname=>’scott’,stattab=>’stat_test_temp’);
dbms_stats.create_stat_table(ownname=>’scott’,stattab=>’stat_test_temp’);
dbms_stats.export_table_stats(ownname=>’scott’,tabname=>’test’,stattab=>’stat_test_temp’,cascade => true);
dbms_stats.delete_table_stats(ownname=>’scott’,tabname=>’test’);
—  dbms_stats.import_table_stats(ownname=>’scott’,tabname=>’test’,stattab=>’stat_test_temp’);
end;  2    3    4    5    6    7
8  /

PL/SQL procedure successfully completed.

SQL> select table_name,
num_rows,
blocks,
empty_blocks,
avg_space,
avg_row_len,
sample_size
from user_tables
where table_name in (‘TEST’, ‘TEST_TEMP’);  2    3    4    5    6    7    8    9

TABLE_NAME                         NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE
—————————— ———- ———- ———— ———-
AVG_ROW_LEN SAMPLE_SIZE
———– ———–
TEST

TEST_TEMP

SQL> begin
— dbms_stats.drop_stat_table(ownname=>’scott’,stattab=>’stat_test_temp’);
—  dbms_stats.create_stat_table(ownname=>’scott’,stattab=>’stat_test_temp’);
–dbms_stats.export_table_stats(ownname=>’scott’,tabname=>’test’,stattab=>’stat_test_temp’,cascade => true);
—  dbms_stats.delete_table_stats(ownname=>’scott’,tabname=>’test’);
dbms_stats.import_table_stats(ownname=>’scott’,tabname=>’test’,stattab=>’stat_test_temp’);
end;  2    3    4    5    6    7
8  /

PL/SQL procedure successfully completed.

SQL> select table_name,
num_rows,
blocks,
empty_blocks,
avg_space,
avg_row_len,
sample_size
from user_tables
where table_name in (‘TEST’, ‘TEST_TEMP’);  2    3    4    5    6    7    8    9

TABLE_NAME                         NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE
—————————— ———- ———- ———— ———-
AVG_ROW_LEN SAMPLE_SIZE
———– ———–
TEST                                    75253        1098                0           0
97          75253

TEST_TEMP

由此可见这个功能不是用于把一张表的统计信息给别的表 而是用于发生了进行不同统计信息的性能测试
我们再看一个实验

先备份

SQL> begin
dbms_stats.drop_stat_table(ownname=>’scott’,stattab=>’stat_test_temp’);
dbms_stats.create_stat_table(ownname=>’scott’,stattab=>’stat_test_temp’);
–dbms_stats.export_table_stats(ownname=>’scott’,tabname=>’test’,stattab=>’stat_test_temp’,cascade => true);
—  dbms_stats.delete_table_stats(ownname=>’scott’,tabname=>’test’);
–dbms_stats.import_table_stats(ownname=>’scott’,tabname=>’test’,stattab=>’stat_test_temp’);
end;  2    3    4    5    6    7
8  /

PL/SQL procedure successfully completed.

SQL> drop table test purge;

Table dropped.

SQL> create table test as select * from dba_objects;

Table created.

SQL> create index idx_test on test(object_id);

Index created.

SQL> select table_name,
num_rows,
blocks,
empty_blocks,
avg_space,
avg_row_len,
sample_size
from user_tables
where table_name in (‘TEST’, ‘TEST_TEMP’);  2    3    4    5    6    7    8    9

TABLE_NAME                         NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE
—————————— ———- ———- ———— ———-
AVG_ROW_LEN SAMPLE_SIZE
———– ———–
TEST

TEST_TEMP

SQL> begin
–dbms_stats.drop_stat_table(ownname=>’scott’,stattab=>’stat_test_temp’);
— dbms_stats.create_stat_table(ownname=>’scott’,stattab=>’stat_test_temp’);
–dbms_stats.export_table_stats(ownname=>’scott’,tabname=>’test’,stattab=>’stat_test_temp’,cascade => true);
—  dbms_stats.delete_table_stats(ownname=>’scott’,tabname=>’test’);
dbms_stats.import_table_stats(ownname=>’scott’,tabname=>’test’,stattab=>’stat_test_temp’);
end;  2    3    4    5    6    7
8  /

PL/SQL procedure successfully completed.

SQL> select table_name,
num_rows,
blocks,
empty_blocks,
avg_space,
avg_row_len,
sample_size
from user_tables
where table_name in (‘TEST’, ‘TEST_TEMP’);  2    3    4    5    6    7    8    9

TABLE_NAME                         NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE
—————————— ———- ———- ———— ———-
AVG_ROW_LEN SAMPLE_SIZE
———– ———–
TEST

TEST_TEMP

SecureFile LOBs and BasicFile LOBs

Posted on by

oracle11g推出以后有了一种新的LOBS的存储模式叫做SecureFile LOBs 与之区别 把以前使用的叫做basicFile Lobs
根据官方文档上的说法 secure是哪哪都好。提供了压缩 重复消除 加密等新的功能 但是basicFile Lobs依然是default的
secureFime的用法是通过在LOB存储子句后添加SECUREFILE关键字来创建
最简单的一种写法：
SQL> CREATE TABLE images (
2 id NUMBER,
3 i_data CLOB
4 )
5 LOB(i_data) STORE AS BASICFILE;

Table created

SQL> CREATE TABLE images2 (id NUMBER,
2 i_data CLOB
3 )
4 LOB(i_data) STORE AS SECUREFILE;

Table created.
———————————————————————————
Securefile列标明了是否为SecureFile类型的LOB
SQL> SELECT TABLE_NAME,SEGMENT_NAME,INDEX_NAME,SECUREFILE FROM DBA_LOBS WHERE TABLE_NAME like ‘IMAGES%’;

TABLE_NAME SEGMENT_NAME
—————————— ——————————
INDEX_NAME SEC
—————————— —
IMAGES SYS_LOB0000076951C00002$$
SYS_IL0000076951C00002$$ NO

IMAGES2 SYS_LOB0000076948C00002$$
SYS_IL0000076948C00002$$ YES
Securefile

使用Securefile LOB的表也是自动生成LOB segment和LOB index的。
但是此时LOB index只有在使用重复消除功能时才会使用，在其他情况下不会使用

SQL> CREATE TABLE images2 (id NUMBER,
2 i_data CLOB
3 )
4 LOB(i_data) STORE AS SECUREFILE
5 ;
CREATE TABLE images2 (id NUMBER,
*
ERROR at line 1:
ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace “SYSTEM”
要注意，Securefile LOB只能在ASSM的表空间（自动管理的表空间）里创建，
不过既然从9i起ASSM表空间就是默认设置了，一般这里不会有多大问题。
只是要求SecureLOB所在的LOB列数据需要存放在ASSM表空间中，而包含LOB列的那个表可以不是
CREATE TABLE images2 (id NUMBER,i_data CLOB) LOB(i_data) STORE AS SECUREFILE (tablespace ucjmh);
SQL> conn / as sysdba
Connected.
SQL> CREATE TABLE images2 (id NUMBER,i_data CLOB) LOB(i_data) STORE AS SECUREFILE (tablespace ucjmh);

Table created.

————————————————————————————————–
SQL> show parameter DB_SECUREFILE

NAME TYPE VALUE
———————————— ———– ——————————
db_securefile string PERMITTED

这个值的取值范围有：
DB_SECUREFILE = { NEVER | PERMITTED | ALWAYS | IGNORE }
PERMITTED 是默认的 就是当你指定是什么的时候就是什么
FORCE 是ORA-43853的来源 意思就是不管你是否指定用SecureFile 创建的时候都是用Securefile 如果不是在ASSM的表空间 那么就报ORA-43853
ALWAYS 意思就是不管你是否指定用SecureFile 创建的时候都是用Securefile 但是如果你是非ASSM的表空间 那就是BASICFILE 如果非ASSM的时候你还显示的指定了用SecureFiLE 那也报43853
NEVER 是不管怎么样都是BasicFile 如果指定了一些加密或压缩之类的参数那么就报ORA-43853
IGNORE 是不管怎么样都是BasicFile 如果指定了一些加密或压缩之类的参数也不报错。

chunk:
在BasicFile的LOB中，Chunk的大小是一定的，最小跟DB Block的大小一样，最大为32KB
–一个chunk最多只保留一行LOB数据，也就是说，如果你设置了32K的CHUNK，但是如果LOB字段大小只有4K，也将占用32K的空间
而在SecureFile中，chunk的size是可变的，由Oracle自动动态分配，最小跟DB Block的大小一样，最大为64MB
–指定的值最好是数据库块的倍数,而且指定的值不能大于表空间区间中NEXT的值

当指定enable storage in row的时候，当lob size =4000 bytes的时候,将存储在lob段里面，其存储方式和表段存储方式完全不一样，使用的是chunk为最小单位的存储，没有行迁移和行链接的概念。
如果设置了enable storage in row 那么oracle会自动将小于4000bytes的数据存储在行内, 这是ORACLE的默认值，
对于大于4000字节的lob字段保存在lob段（同disable storage in row），在表段将保留36-84字节的控制信息。
对于disable storage in row，Oracle将lob字段分开保存在lob段中，而仅仅在行位置保留20字节的指针。
对于相当于disable storage in row的这部分（也就是单独保存在LOB段的这部分数据），UNDO仅仅是记录指针与相关lob索引改变，如果发生更新操作等DML操作，原始数据将保留在LOB段
DISABLE STORAGE IN ROW:如果DISABLE这个属性,那么lob数据会在行外存储,行内只存储该lob值得指针,而且这个属性在表
storage as(cache|nocahce)表示是否允许lob段经过buffer cache并缓存。默认是nocache，表示直接读与直接写，不经过数据库的data buffer。所以，默认情况下，对于单独保存在LOB段的这部分数据，在发生物理读的时候，是直接读，如direct path read (lob)

storage as(nocache logging |nocache nologging)，logging/nologging属性只对nocache方式生效，默认是logging，如果是nologging方式，对于 保存在行外的log部分，在update等DML操作时将不记录redo日志

PCTVERSION integer、RETENTION:都是ORACLE用来管理LOB字段镜像数据的。在LOB 数据的更新过程中,
ORACLE没有用UNDO TABLESPACE空间，而是从LOB字段所在的表空间里划分一段空间来做镜像空间的,
这个空间的大小由PCTVERSION参数控制,默认值为10,代表划分表空间的10%作为镜像空间,
每个镜像空间的单元大小由CHUNK参数指定,pctversion可以使用在manual undo mode和automatic undo mode 环境中
retention应用了automatic undo mode中的undo_retention通过时间来管理lob镜像空间.
pctversion和retention不能同时被指定.建议数据库在automatic undo mode下使用retention参数

SecureFile的COMPRESS选项在表或分区一级上开启了对LOB内容的压缩，使用关键字MEDIUM和HIGH表示压缩的等级，
如果没有指定压缩等级，就默认为MEDIUM，对LOB内容进行压缩会增加系统开销，
因此使用高等级的压缩可能会对系统性能产生不良影响，SecureFile LOB的压缩功能不影响表压缩，反之亦然
SecureFile LOB的加密功能依赖于钱夹或硬件安全模型（HSM）掌管加密密钥，钱夹设置与透明数据加密（TDE）和表空间加密描述的一样，
因此在尝试下面的例子前先完成那两个实验。SecureFile的ENCRYPT选项执行块级别的LOB内容加密，
一个可选的USING子句定义了使用哪种加密算法（3DES168, AES128, AES192, 或AES256），默认使用AES192算法，
NO SALT选项对于SecureFile加密不可用，加密是应用在每一列上的，因此它会影响所有使用LOB的分区，DECRPT选项用于明确地阻止加密
加密是不受imp/exp或表空间传输支持的，因此必须使用impdp/exddp来传输数据

BasicFile和SecureFile LOB共享了部分基础的缓存和日志选项，常见的缓存选项有：
CACHE – LOB数据被放在缓冲区中。
CACHE READES – 仅读取LOB数据过程中它放在缓冲区中，写操作时不放进去。
NOCACHE – LOB数据不放在缓冲区中，这是BasicFile和SecureFile LOB的默认值。
基本的日志选项有：
LOGGING – 创建和修改LOB时产生完全重做日志，这是默认设置。
NOLOGGING – 操作不记录在重做日志中，因此不能恢复，在首次创建和巨大的载入过程中有用。
而且，SecureFile LOB还有一个日志选项FILESYSTEM_LIKE_LOGGING，只记录元数据，在出现故障后仍然允许段的恢复。
CACHE选项意味着LOGGING，因此你不能将CACHE与NOLOGGING或FILESYSTEM_LIKE_LOGGING合在一起使用。
如果要移动表
ALTER TABLE table_name LOB (lob_item) STORE AS [lob_segment]
(
TABLESPACE tablespace_name
(STORAGE…..)
ENABLE|DISABLE STORAGE IN ROW
CHUNK integer
PCTVERSION integer
RETENTION
FREEPOOLS integer
CACHE|NOCACHE|CACHE READS
INDEX lobindexname

(TABLESPACE tablesapce_name

((STORAGE…..))
)

Oracle Varray变量

Posted on by

[oracle@ucjmh ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 7 09:01:40 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set serveroutput on

SQL> DECLARE

— declare fixed array

TYPE arry_num IS VARRAY(10) OF NUMBER;

arry_top arry_num;

v_n int;

BEGIN

— init array

arry_top := arry_num(1,2,3);

dbms_output.put_line(arry_top.LIMIT());

dbms_output.put_line(arry_top.count());

dbms_output.put_line(arry_top.FIRST());

–dbms_output.put_line(arry_top.NEXT());

— dbms_output.put_line(arry_top.PRIOR());

dbms_output.put_line(arry_top.LAST());

dbms_output.put_line(arry_top(2));

FOR I IN 1..arry_top.COUNT() LOOP

v_n:=arry_top(I);

dbms_output.put_line(v_n);

DBMS_OUTPUT.PUT_LINE(‘arry_top(‘||I||’)=’ || arry_top(I));

END LOOP;

END;

/

10

3

1

3

2

1

arry_top(1)=1

2

arry_top(2)=2

3

arry_top(3)=3

PL/SQL procedure successfully completed.

SQL>

声明和初始化VARRAY变量

你不可以直接声明一个VARRAY变量。必须先声明一个包含这个数组最大容量的类型：
1. TYPE MY_ARRAY_TYPE IS VARRAY(10) OF NUMBER;
然后，你就可以用这个类型声明VARRAY变量：
1. V MY_ARRAY_TYPE;
在你对这个数组进行操作以前，必须先初始化该数组。你既可以在声明它的时候对其初始化，也可以在声明后对其赋值。下面展示了在声明的同时对其进行初始化：
1. V MY_ARRAY_TYPE := MY_ARRAY_TYPE ();
从指定的条目处取值

把条目的数目作为下标。下标可以是返回整数值(该值小于或等于数组条目数)的任意表达式，如：
1. K := V(3); 2. I := 2; 3. L := V(I+1);
得到数组的容量

对VARRAY变量使用COUNT()方法。注意，你不需要指定圆括号，因为这个方法没有输入参数：
1. N_ENTRIES := V.COUNT();
这告诉你这个数组中正在使用的条目数。当VARRAY类型被声明的时候，其最大的容量也就被定义了。你可以用LIMIT()方法得到该容量：
1. ARRAY_CAPACITY := V.LIMIT();
遍历VARRAY中的值

你可以使用多种技术。最简单的是使用FOR循环：
1. FOR I IN 1..V.COUNT() LOOP 2. DBMS_OUTPUT.PUT_LINE(‘V(‘||I||’)=’ || V(I)); 3. END LOOP;
你也可以使用FIRST()和LAST()方法。FIRST()返回数组的第一个条目的下标(总是1)，LAST()返回数组的最后一个条目的下标(与COUNT方法相同)。
1. FOR I IN V.FIRST()..V.LAST() LOOP 2. DBMS_OUTPUT.PUT_LINE(‘V(‘||I||’)=’ || V(I)); 3. END LOOP;
你也可以使用PRIOR(n)和NEXT(n)方法，这两个方法分别返回给定条目的前一个和后一个条目的下标。例如，下面的代码用来向后遍历整个数组：
1. I := V.COUNT(); 2. WHILE I IS NOT NULL LOOP 3. DBMS_OUTPUT.PUT_LINE(‘V(‘||I||’)=’ || V(I)); 4. I := V.PRIOR(I); 5. END LOOP;
PRIOR(n)和n-1是一样的，NEXT(n)和n+1是一样的，但是PRIOR(1)和NEXT(V.COUNT())则返回NULL。
对VARRAY的扩展

使用EXTEND(k)方法。这个方法可以在VARRAY的最后追加k个新的条目。如果k没有被指定，只增加一个条目。新增的条目没有值(默认为NULL)，但是你可以对它们进行初始化。COUNT()和LAST()方法现在可以反映VARRAY新的容量。以下代码向数组中添加两个条目并对其进行初始化：
1. I := V.LAST(); 2. V.EXTEND(2); 3. V(I+1) := 5; 4. V(I+2) := 6;
注意，你对VARRAY的扩展不可以超过其最大容量(通过LIMIT()方法得到)，且在对VARRAY扩展前必须要对其进行初始化。以下代码是无效的：
1. VT MY_ARRAY_TYPE; 2. VT.EXTEND(5);
下面的代码是有效的：
1. VT MY_ARRAY_TYPE; 2. VT := MY_ARRAY_TYPE(); 3. VT.EXTEND(5);
对VARRAY的缩减

使用TRIM(k)方法。这个方法在VARRAY的尾部删除最后k个条目。当k没有被指定时，删除最后一个条目。已被删除的条目的值将丢失。COUNT()和LAST()方法反映了新的容量。下面从VARRAY中删除最后一个条目：
1. V.TRIM;
你也可以用以下方式删除数组中的所有条目：
1. V.TRIM(V.COUNT());
或者用具有效果相同的DELETE()方法。该方法删除数组中的所有条目，并把其容量设置为0(也就是说，V.COUNT()现在的返回值为0)。
1. V.DELETE()

使用SQL实现对邮政编码分类规则

Posted on by

早晨在大阪工作的学长发信息过来要想用SQL实现对日本邮政编码分类的规则.本来学长只需要做概要设计即可,详细设计和开发都是外包给越南人做,但是担心越南的童鞋方法不能够满意,索性就直接把关键的步骤都自己实现掉,想想也是为了世界和平做了不少贡献;

规则是这样的:
1.日本邮编是7位的;
2.最终的结果集只需要两列(前缀 个数);
3.第一优先级是尾数为[00]的邮政编码;
4.第二优先级是前5位数字相同的邮政编码,只展现出现次数大于10次的结果,如果不足10次的邮政编码进入下一优先级统计;
5.第三优先级是前4位数字相同的邮政编码,规则同上;
6.第四优先级是前3位数字相同的邮政编码,规则同上;
7.第五优先级是剩余其它的邮政编码;

— 测试的数据,生成20W个邮政编码;
DROP TABLE TBPOSTTEST;
CREATE TABLE TBPOSTTEST AS
SELECT LPAD(ROUND(DBMS_RANDOM.VALUE (1, 9999999)) , 7, 0) postcode FROM DUAL
CONNECT BY LEVEL <= 200000;

— 实现的sql语句;
SELECT PREFIX, COUNT FROM (
WITH
P0P0 AS (SELECT ‘____00′ AS “PREFIX”, COUNT(*) c0 FROM TBPOSTTEST WHERE SUBSTR(postcode, LENGTH(postcode)-1) = ’00’),
P1P5 AS (SELECT SUBSTR(postcode, 0, 5) cp5, COUNT(*) c5 FROM TBPOSTTEST WHERE SUBSTR(postcode, LENGTH(postcode)-1) <> ’00’ GROUP BY SUBSTR(postcode, 0, 5)),
P2P4 AS (SELECT SUBSTR(cp5, 0, 4) cp4, SUM(c5) c4 FROM P1P5 WHERE c5 < 10 GROUP BY SUBSTR(cp5, 0, 4)),
P3P3 AS (SELECT SUBSTR(cp4, 0, 3) cp3, SUM(c4) c3 FROM P2P4 WHERE c4 < 10 GROUP BY SUBSTR(cp4, 0, 3))
SELECT ‘0’ AS “PRIORITY”, PREFIX, c0 AS “COUNT” FROM P0P0
UNION ALL
SELECT ‘1’, cp5 , c5 FROM P1P5 WHERE c5 >= 10
UNION ALL
SELECT ‘2’, cp4 , c4 FROM P2P4 WHERE c4 >= 10
UNION ALL
SELECT ‘3’, cp3 , c3 FROM P3P3 WHERE c3 >= 10
UNION ALL
SELECT ‘4’, ‘OTHER’ , SUM(c3) c2 FROM P3P3 WHERE c3 < 10)
ORDER BY PRIORITY;

— 测试的DEMO, 把条件改为大于2条;
INSERT INTO TBPOSTTEST VALUES(‘123400’);
INSERT INTO TBPOSTTEST VALUES(‘123500’);
INSERT INTO TBPOSTTEST VALUES(‘123450’);
INSERT INTO TBPOSTTEST VALUES(‘123451’);
INSERT INTO TBPOSTTEST VALUES(‘123452’);
INSERT INTO TBPOSTTEST VALUES(‘123453’);
INSERT INTO TBPOSTTEST VALUES(‘123454’);
INSERT INTO TBPOSTTEST VALUES(‘123444’);
INSERT INTO TBPOSTTEST VALUES(‘123555’);
INSERT INTO TBPOSTTEST VALUES(‘123566’);
INSERT INTO TBPOSTTEST VALUES(‘124444’);
COMMIT;

SELECT PREFIX, COUNT FROM (
WITH
P0P0 AS (SELECT ‘____00′ AS “PREFIX”, COUNT(*) c0 FROM TBPOSTTEST WHERE SUBSTR(postcode, LENGTH(postcode)-1) = ’00’),
P1P5 AS (SELECT SUBSTR(postcode, 0, 5) cp5, COUNT(*) c5 FROM TBPOSTTEST WHERE SUBSTR(postcode, LENGTH(postcode)-1) <> ’00’ GROUP BY SUBSTR(postcode, 0, 5)),
P2P4 AS (SELECT SUBSTR(cp5, 0, 4) cp4, SUM(c5) c4 FROM P1P5 WHERE c5 < 2 GROUP BY SUBSTR(cp5, 0, 4)),
P3P3 AS (SELECT SUBSTR(cp4, 0, 3) cp3, SUM(c4) c3 FROM P2P4 WHERE c4 < 2 GROUP BY SUBSTR(cp4, 0, 3))
SELECT ‘0’ AS “PRIORITY”, PREFIX, c0 AS “COUNT” FROM P0P0
UNION ALL
SELECT ‘1’, cp5 , c5 FROM P1P5 WHERE c5 >= 2
UNION ALL
SELECT ‘2’, cp4 , c4 FROM P2P4 WHERE c4 >= 2
UNION ALL
SELECT ‘3’, cp3 , c3 FROM P3P3 WHERE c3 >= 2
UNION ALL
SELECT ‘4’, ‘OTHER’ , SUM(c3) c2 FROM P3P3 WHERE c3 < 2)
ORDER BY PRIORITY;

— 测试结果;
PREFIX        COUNT
—————— ———-
____00               2
12345                 5
1235                   2
OTHER                2

创建带参数的视图

Posted on by

PROMPT CREATE OR REPLACE PACKAGE pkg_session

CREATE OR REPLACE PACKAGE pkg_session IS
TYPE parm_type IS TABLE OF varchar2(1000) index BY VARCHAR2(30);
PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN VARCHAR2);
PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN NUMBER);
PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN DATE);
FUNCTION get_val(p_idx IN VARCHAR2) RETURN VARCHAR2 ;
FUNCTION get_val_number(p_idx IN VARCHAR2) RETURN NUMBER;
FUNCTION get_val_date(p_idx IN VARCHAR2) RETURN DATE;
PRAGMA RESTRICT_REFERENCES(get_val,WNDS,WNPS);
PRAGMA RESTRICT_REFERENCES(get_val_number,WNDS,WNPS);
PRAGMA RESTRICT_REFERENCES(get_val_date,WNDS,WNPS);
END pkg_session;
/

CREATE OR REPLACE PACKAGE BODY pkg_session IS
sv_parameters parm_type;
PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN VARCHAR2)
IS
BEGIN
sv_parameters(TRIM(UPPER(p_idx))) := SUBSTR(p_value,1,1000);
RETURN;
END set_val;

PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN NUMBER)
IS
BEGIN
set_val(p_idx,TO_CHAR(p_value));
RETURN;
END set_val;

PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN DATE)
IS
BEGIN
set_val(p_idx,TO_CHAR(p_value,’YYYYMMDDHH24MISS’));
RETURN;
END set_val;

FUNCTION get_val(p_idx IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN sv_parameters(TRIM(UPPER(p_idx)));
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END get_val;

FUNCTION get_val_number(p_idx IN VARCHAR2) RETURN NUMBER
IS
BEGIN
RETURN TO_NUMBER(get_val(p_idx));
END get_val_number;

FUNCTION get_val_date(p_idx IN VARCHAR2) RETURN DATE
IS
BEGIN
RETURN TO_DATE(get_val(p_idx),’YYYYMMDDHH24MISS’);
END get_val_date;

END pkg_session;
/

CREATE OR REPLACE VIEW VW_TEST
AS SELECT * FROM t WHERE ROWNUM<=pkg_session.get_val_number('P_ROWNUM');

— 未设参数时没有数据
SELECT * FROM VW_TEST;

— 设置参数：

EXEC pkg_session.set_val('P_ROWNUM',10);

— 以下会返回10行：
SELECT * FROM VW_TEST;

–两个条件 并且当不设置值的时候不启用
CREATE OR REPLACE VIEW VW_TEST2
AS SELECT * FROM t WHERE ROWNUM exec pkg_session.set_val(‘P_NAME’,’UCJMH’);

PL/SQL procedure successfully completed.

SQL> select * from vw_test2;

USERNAME
——————————
UCJMH
这个时候就只会有一行数据了

在给定时间间隔，求间隔内出现的记录数

Posted on by

在itpub里看到一个很有意思的sql

一个表中记录了各个地点的报警开始时间，结束时间。

现在想统计，各个地点报警次数和时长，时长=结束时间-开始时间。

要求，同一个地点，开始时间间隔超过10分钟的，这个地点要在结果中再出现一次，10分钟之内的假如有多条，统计条数和报警时长，时长=10分钟内各条时长之和。

create table T_DEMO
(
id VARCHAR2(32) not null,
place_id VARCHAR2(32),
s_time DATE,
e_time DATE
)；

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘1’, ‘1’, to_date(’21-08-2014 01:24:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 01:24:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘2’, ‘1’, to_date(’21-08-2014 01:25:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 01:25:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘3’, ‘1’, to_date(’21-08-2014 01:35:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 01:35:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘4’, ‘1’, to_date(’21-08-2014 01:39:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 01:39:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘5’, ‘1’, to_date(’21-08-2014 02:05:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:05:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘6’, ‘1’, to_date(’21-08-2014 02:06:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:06:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘7’, ‘1’, to_date(’21-08-2014 02:12:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:12:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘8’, ‘1’, to_date(’21-08-2014 02:14:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:14:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘9’, ‘1’, to_date(’21-08-2014 02:50:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:50:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’10’, ‘1’, to_date(’21-08-2014 02:52:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:52:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’11’, ‘1’, to_date(’21-08-2014 02:57:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:57:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’12’, ‘1’, to_date(’25-08-2014 09:13:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 09:13:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’13’, ‘1’, to_date(’25-08-2014 09:18:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 09:18:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’14’, ‘1’, to_date(’25-08-2014 09:30:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 09:30:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’15’, ‘1’, to_date(’25-08-2014 09:37:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 09:37:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’16’, ‘1’, to_date(’25-08-2014 09:47:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 09:47:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’17’, ‘2’, to_date(’25-08-2014 10:09:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 10:09:01′, ‘dd-mm-yyyy hh24:mi:ss’));

WITHt2AS

(SELECT  a.place_id,

a.s_time,

a.e_time,

a.id,

floor((a.s_time – to_date(‘2000-01-01′,’YYYY-MM-DD’)) * 24 * 60 / 10) last_cha

FROM t_demo a)

SELECT

place_id 地点,

SUM(e_time – s_time) * 24 * 60 * 60 “时长:秒”,

min(s_time)  开始时间，

COUNT(*) 次数

FROM t2

GROUP BY place_id, last_cha;

Oracle UTL_FILE

Posted on by

With the UTL_FILE package, PL/SQL programs can read and write operating system text files. UTL_FILE provides a restricted version of operating system stream file I/O.
FOPEN
描述：打开一个文件，基本上在我们对文件进行读写动作之前都需要先执行这个function来打开文件先。
语法：
UTL_FILE.FOPEN (  location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER DEFAULT 1024)  RETURN FILE_TYPE;
参数：location   略。
Filename  略。
open_mode  指明文件打开的模式。有如下几种：
■r –只读（文本）
■ w – 只写（本文）
■ a – 追加（文本）
■ rb – 只读（字节）
■ wb – 只写（字节）
■ ab – 追加（字节）
(注：当使用模式：a或者ab的时候，如果文件不存在，则会以write模式创建此文件)
max_linesize   指定文件文本每一行存放的最大字符数。
返回值：FOPEN返回一个接下来我们的程序将要使用到的文件的指针

FCLOSE
功能：关闭一个打开的文件。
语法：UTL_FILE.FCLOSE (file IN OUT FILE_TYPE);
参数：1. file->调用FOPEN或者FOPEN_NVCHAR返回的活动中的文件指针。
注意事项：当FCLOSE执行的时候，如果还有缓冲数据没有及时写入到文件中，
那么程序就会raise一个异常：WRITE_ERROR。可以在PUT_LINE的时候加上参数autoflush => TRUE；或者在每次PUT之后执行：FFLUSH。

FCLOSE_ALL
功能：此procedure将会关闭本次session所有打开的文件。它用来紧急情况的清理功能，例如当PL/SQL程序在EXCEPTION部分退出时。
语法：UTL_FILE.FCLOSE_ALL;
注意事项：FCLOSE_ALL不会修改所打开的文件的状态，也就是说执行了FCLOSE_ALL后，再用IS_OPEN去检测文件，结果还是打开状态，
但是之后，这些文件任然是不能去read或者write的。而FCLOSE执行后，相关的文件则完全关闭了

FCOPY
功能：此procedure复制一个文件的连续部分内容或者全部内容到一个新创建的文件。
如果参数start_line和end_line省略的话，默认地会复制整个文件。此操作会将源文件以read模式打开，将目标文件以write模式打开。
语法：
UTL_FILE.FCOPY ( src_location    IN VARCHAR2,
src_filename  IN VARCHAR2,
dest_location  IN VARCHAR2,
dest_filename IN VARCHAR2,
start_line         IN BINARY_INTEGER DEFAULT 1,
end_line          IN BINARY_INTEGER DEFAULT NULL);
参数：src_location来源文件的目录名。取值来源是视图ALL_DIRECTORIES的DIRECTORY_NAME；
src_filename  将要被复制的来源文件
dest_location 被创建的目标文件存放的目录名。
dest_filename 从来源文件创建的目标文件。
start_line  要复制的内容起始行号，默认为1，表示从第一行开始复制。
end_line 要复制的内容的终止行号，默认NULL，表示文件的末尾。
FFLUSH
描述：FFLUSH强制将缓冲的数据写入文件。因为通常待写入文件的数据都是都在缓冲存储位置。当有必要去read一个任然处于打开状态的文件时，
FFLUSH就起作用了，例如在调试程序中，可以将调试的消息及时冲到文件中，已便于我们马上就能read这些内容。
语法：
UTL_FILE.FFLUSH (file IN FILE_TYPE);

FGETATTR
描述：FGETATTR读取磁盘上的文件并返回文件的属性。
语法：UTL_FILE.FGETATTR( location IN VARCHAR2,
filename IN VARCHAR2,
fexists OUT BOOLEAN,
file_length OUT NUMBER,
block_size OUT BINARY_INTEGER);
参数：location 此处略去X个字。
filename此处略去X个字。
fexists 返回的属性1：文件是否存在
file_length 返回的属性2：文件字节长度，如果文件不存在，则返回NULL。
block_size  文件系统块的字节大小。

FGETPOS
描述：此函数返回一个文件中当前的偏移位置。
语法：
UTL_FILE.FGETPOS (file IN FILE_TYPE) RETURN PLS_INTEGER;
注意事项：如果file没有打开，则会抛出异常

FREMOVE
描述：此procedure在你有充足的权限之下，删除一个磁盘上的文件。
语法：
UTL_FILE.FREMOVE ( location IN VARCHAR2,
filename IN VARCHAR2);

FRENAME
描述：此procedure将一个存在的文件重命名，类似unix命令：mv
语法：
UTL_FILE.FRENAME ( src_location   IN VARCHAR2,
src_filename  IN VARCHAR2,
dest_location  IN VARCHAR2,
dest_filename IN VARCHAR2,
overwrite        IN BOOLEAN DEFAULT FALSE);

GET_LINE
描述：此procedure从一个打开的文件中读取一行文本，直到遇到换行符。
语法：
UTL_FILE.GET_LINE ( file     IN FILE_TYPE,
buffer OUT VARCHAR2,
len      IN PLS_INTEGER DEFAULT NULL);
参数：len 从文本中读取一次的长度，默认是null，oracle就取FOPEN时的max_linesieze。

IS_OPEN
描述：顾名思义。
语法：UTL_FILE.IS_OPEN (file IN FILE_TYPE)  RETURN BOOLEAN;
1.PUT
描述：PUT写入内容到文件中。（每写一次，不带换行符）
语法：UTL_FILE.PUT (file IN FILE_TYPE, buffer IN VARCHAR2);

PUT_LINE
描述：PUT_LINE写入内容到文件中。（每写一次，末尾都加一个换行符）
语法：
UTL_FILE.PUT_LINE ( file          IN FILE_TYPE,
buffer      IN VARCHAR2,
autoflush IN BOOLEAN DEFAULT FALSE);
PUTF
描述：    写入格式化的内容到文件中。好比C语言的printf()
语法：
UTL_FILE.PUTF ( file IN FILE_TYPE,
format IN VARCHAR2,
[arg1 IN VARCHAR2 DEFAULT NULL,
. . .
arg5 IN VARCHAR2 DEFAULT NULL]);
参数：format 包含格式化字符[\n，%s]的内容。
\n：代表一个换行符。
%s：用arg1~5的值去代替。

 

 

做一个例子如下：

CREATE OR REPLACE PROCEDURE SQL_TO_CSV
(
P_QUERY IN VARCHAR2,                        — PLSQL文
P_DIR IN VARCHAR2,                          — 导出的文件放置目录
P_FILENAME IN VARCHAR2                      — CSV名
)
IS
L_OUTPUT UTL_FILE.FILE_TYPE;
L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
L_COLUMNVALUE VARCHAR2(4000);
L_STATUS INTEGER;
L_COLCNT NUMBER := 0;
L_SEPARATOR VARCHAR2(1);
L_DESCTBL DBMS_SQL.DESC_TAB;
P_MAX_LINESIZE NUMBER := 32000;
BEGIN
–OPEN FILE
L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, ‘A’, P_MAX_LINESIZE);
–DEFINE DATE FORMAT
EXECUTE IMMEDIATE ‘ALTER SESSION SET NLS_DATE_FORMAT=”YYYY-MM-DD HH24:MI:SS”’;
–OPEN CURSOR
DBMS_SQL.PARSE( L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE );
DBMS_SQL.DESCRIBE_COLUMNS( L_THECURSOR, L_COLCNT, L_DESCTBL );
–DUMP TABLE COLUMN NAM
FOR I IN 1 .. L_COLCNT LOOP
UTL_FILE.PUT( L_OUTPUT, L_SEPARATOR || ‘”’ || L_DESCTBL(I).COL_NAME || ‘”’ );
DBMS_SQL.DEFINE_COLUMN( L_THECURSOR, I, L_COLUMNVALUE, 4000 );
L_SEPARATOR := ‘,’;
END LOOP;
UTL_FILE.NEW_LINE( L_OUTPUT );
–EXECUTE THE QUERY STATEMENT
L_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR);
–DUMP TABLE COLUMN VALUE
WHILE ( DBMS_SQL.FETCH_ROWS(L_THECURSOR) > 0 ) LOOP
L_SEPARATOR := ”;
FOR I IN 1 .. L_COLCNT LOOP
DBMS_SQL.COLUMN_VALUE( L_THECURSOR, I, L_COLUMNVALUE );
UTL_FILE.PUT( L_OUTPUT, L_SEPARATOR || ‘”’ ||
TRIM(BOTH ‘ ‘ FROM REPLACE(L_COLUMNVALUE,’”’,’””’)) || ‘”’);
L_SEPARATOR := ‘,’;
END LOOP;
UTL_FILE.NEW_LINE( L_OUTPUT );
END LOOP;
–CLOSE CURSOR
DBMS_SQL.CLOSE_CURSOR(L_THECURSOR);
–CLOSE FILE
UTL_FILE.FCLOSE( L_OUTPUT );
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/

— 创建存放文件的目录
CREATE OR REPLACE DIRECTORY MYDIR AS ‘C:\’;

create table t as select * from dba_users;

— 执行块
begin
sql_to_csv(‘select * from T ‘,’MYDIR’,’EXAMPLE2.CSV’);
dbms_lock.sleep(10) ;
sql_to_csv(‘select * from T ‘,’MYDIR’,’EXAMPLE3.CSV’);
end;
/

Limit SQL*Plus operation

Posted on by

SQL*Plus Security
限制哪些用户在SQL*Plus里能够执行哪些命令以及不能执行哪些命令

oracle是用system用户下的PRODUCT_USER_PROFILE (PUP)表来进行的限制。提供了产品级别的安全,用户级安全（SQL GRANT和REVOKE命令和用户角色）相互补充。

select * from product_user_profile;
如果这张表不存在可以使用  @?/sqlplus/admin/pupbld
表结构如下：
SQL> desc product_user_profile
Name                     Type
——————— —————
PRODUCT                 VARCHAR2(30)                  –必须包含产品的名称(SQL * Plus)。不能输入通配符或NULL
USERID                  VARCHAR2(30)                  –必须包含用户的用户名(大写)来说,你想禁用命令。为多个用户禁用命令,使用SQL通配符(%)或多个条目 HR，HR1  HR%  %
ATTRIBUTE               VARCHAR2(240)                 –必须包含的名称(大写)SQL、SQL * Plus或者PL / SQL命令禁用(例如,RUN)   如果你禁用一个角色,他可以是这个角色的字符串
SCOPE                   VARCHAR2(240)                 –空就好了
NUMERIC_VALUE           NUMBER(15,2)                  –空就好了
CHAR_VALUE              VARCHAR2(240)                 –禁用一个SQL、SQL * Plus,或PL / SQL命令是”DISABLED”  如果你禁用一个角色,它必须包含角色的名字  不可以使用通配符(%)
DATE_VALUE              DATE                          –空就好了
LONG_VALUE              LONG                          –空就好了

SQL*Plus   HR      HOST                           DISABLED
SQL*Plus   %       INSERT                         DISABLED
SQL*Plus   %       UPDATE                         DISABLED
SQL*Plus   %       DELETE                         DISABLED

insert into product_user_profile(PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE )values(‘SQL*Plus’,’HR’,’HOST’,’DISABLED’);
insert into product_user_profile(PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE )values(‘SQL*Plus’,’%’,’INSERT’,’DISABLED’);
insert into product_user_profile(PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE )values(‘SQL*Plus’,’%’,’UPDATE’,’DISABLED’);
insert into product_user_profile(PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE )values(‘SQL*Plus’,’%’,’DELETE’,’DISABLED’);
COMMIT;

–Disabling HOST disables the operating system alias for HOST, such as $ on Windows, and ! on UNIX
–不推荐禁用退出/退出。如果禁用,终止命令行会话通过发送一个EOF字符如Ctrl + D在UNIX或Ctrl + Z在Windows。终止一个Windows的GUI和文件>退出会话。否则,终止会话终止的SQL * Plus流程。
–如果禁用,使用退出/退出终止当前运行的脚本iSQL *加上也禁用。如果禁用,退出操作只要OSERROR每当SQLERROR也禁用。
–当禁用了help的时候同时也禁用了？
–禁用SQL * Plus set 命令也禁用SET CONSTRAINTS, SET ROLE and SET TRANSACTION
–禁用SQL * Plus start也禁用@ / @@
–禁用BEGIN 和 DECLARE 并不妨碍使用SQL * Plus  EXECUTE  去 运行PL / SQL。EXECUTE必须单独禁用
SQL*PLUS 可以限制的命令有：
ACCEPT   ，DEFINE ，PASSWORD ，SHUTDOWN     ，APPEND
DEL      ，PAUSE  ，SPOOL    ，ARCHIVE LOG  ，DESCRIBE
PRINT    ，START (@, @@)
ATTRIBUTE       ，DISCONNECT         ，PROMPT                          ，STARTUP
BREAK           ，EDIT               ，RECOVER                         ，STORE
BTITLE          ，EXECUTE            ，REMARK                          ，TIMING
CHANGE          ，EXIT/QUIT          ，REPFOOTER                       ，TTITLE
CLEAR           ，GET                ，REPHEADER                       ，UNDEFINE
COLUM           ，NHELP (?)          ，RUN                             ，VARIABLE
COMPUTE         ，HOST
SAVE            ，WHENEVER OSERROR
CONNECT  ，INPUT           ，SET      ，WHENEVER SQLERROR
COPY     ，LIST (;)        ，SHOW     ，XQUERY

SQL命令可以限制的有：
ALTER    ，DELETE      ， MERGE   ，  SET CONSTRAINTS
ANALYZE  ，DISASSOCIATE， NOAUDIT ，  SET ROLE
ASSOCIATE，DROP        ， PURGE   ，  SET TRANSACTION
AUDIT    ，EXPLAIN     ， RENAME  ，  TRUNCATE
CALL     ，FLASHBACK   ， REVOKE  ，  UPDATE
COMMENT  ，GRANT       ， ROLLBACK，  VALIDATE
COMMIT   ，INSERT      ， SAVEPOINT， CREATE，LOCK， SELECT

pl/sql可以限制的有
BEGIN    DECLARE
我们做个例子：

1.登录到system
conn system/oracle
2.添加禁用
INSERT INTO PRODUCT_USER_PROFILE
VALUES (‘SQL*Plus’, ‘HR’, ‘SELECT’, NULL, NULL, ‘DISABLED’, NULL, NULL);
3.登录到hr并查询
SQL> conn hr/hr
Connected.
SQL> select * from dual;
SP2-0544: Command “select” disabled in Product User Profile
SQL> ho ls
SP2-0544: Command “host” disabled in Product User Profile
4.在SYSTEM里去掉刚刚的限制
DELETE FROM PRODUCT_USER_PROFILE WHERE USERID = ‘HR’;

但是在3的时候我们如果用PL/SQL DEVELOPER或者其它的IDE工具是没有任何问题的
当然 PUP也可以在角色上进行限制：
可以使用SQL命令来创建和控制角色为你提供安全访问数据库表。通过创建一个角色,然后控制谁有权访问它,确保只有特定的用户可以访问特定的数据库特权
创建两个role
–一个不带密码
create role ROLE1;
–一个带密码
create role role2 identified by oracle;

grant create session,create table,create view to ROLE1;

grant create session,create table,create view,create procedure to role2;

create user ucjmh identified by ucjmh;

grant ROLE1,ROLE2 to ucjmh;

–修改ucjmh用户的缺省角色，只让role1在登陆的时候生效

alter user ucjmh default role role1;
select * from product_user_profile
然后insert
insert into product_user_profile(PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE )values(‘SQL*Plus’,’%’,’ROLES’,’ROLE1′);
这个时候登录：
SP2-0557: Error in disabling roles in product user profile.
Connected.

这个时候 我们再insert role2
insert into product_user_profile(PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE )values(‘SQL*Plus’,’UCJMH’,’ROLES’,’ROLE2′);

SQL> conn ucjmh/ucjmh
Connected.
SQL> select * from session_roles;

no rows selected

然后可以看出来其实在登录的时候oracle内部做了：
SET ROLE ALL EXCEPT ROLE1, ROLE2;

DBMS_ERRLOG.CREATE_ERROR_LOG记录错误日志

Posted on by

DBMS_ERRLOG包提供了一个程序,使您可以创建一个错误日志表,
以便DML操作后可以继续遇到错误而不是终止和回滚。这使您能够节省时间和系统资源。

–首先创建一张测试表 A
CREATE TABLE a (ID NUMBER PRIMARY KEY);

BEGIN
— DBMS_ERRLOG.create_error_log(dml_table_name => ‘A’);
DBMS_ERRLOG.create_error_log(dml_table_name => ‘A’,err_log_table_name => ‘A_LOG’);
END;

–这个时候去查 可以发现多出来了表ERR$_A和A_LOG
SELECT * FROM tab

–两张表的表结构完全相同
SELECT * FROM ERR$_A;
SELECT * FROM A_LOG;

ORA_ERR_NUMBER$        NUMBER           –错误记录数
ORA_ERR_MESG$          VARCHAR2(2000)   –错误信息
ORA_ERR_ROWID$         ROWID            –错误记录的rowid(仅对update或delete)
ORA_ERR_OPTYP$         VARCHAR2(2)      –操作类型(I-INSERT,U-UPDATE,D-DELETE)
ORA_ERR_TAG$           VARCHAR2(2000)   –自定义标志说明
A_ID                    VARCHAR2(4000)   –原表字段

其中前五个字段是固定的,后边字段根据原表确定.
但是原表和对应error log表的字段类型是不完全匹配的,
其转换规则如下:
NUMBER          –>         VARCHAR2(4000)
CHAR/VARCHAR2(n)    –>     VARCHAR2(4000)
NCHAR/NVARCHAR2(n)   –>      NVARCHAR2(4000)
DATE/TIMESTAMP       –>          VARCHAR2(4000)
RAW                –>         RAW(2000)
ROWID              –>           UROWID
LONG/LOB     –>     LOB

DBMS_ERRLOG.CREATE_ERROR_LOG (
dml_table_name            IN VARCHAR2,–DML的名称表基础上的错误日志表
err_log_table_name        IN VARCHAR2 := NULL,–您将创建错误日志表的名称。默认的是（最多25字符）DML表前缀的名称’ERR$_‘。例子如下:dml_table_name: ‘EMP’, err_log_table_name: ‘ERR$_EMP’
err_log_table_owner       IN VARCHAR2 := NULL,–错误日志表的所有者的名称。您可以指定dml_table_name的所有者
err_log_table_space       IN VARCHAR2 := NULL,–错误日志表将被创建的表空间。如果没有指定,默认表空间为用户拥有将使用DML错误日志表
skip_unsupported          IN BOOLEAN := FALSE);–设置为TRUE时,不支持的列类型错误日志将被跳过,而不是添加到错误日志表。设置为FALSE时,一个不受支持的列类型将导致程序终止。

INSERT INTO a VALUES(1) LOG ERRORS
–INTO A_LOG  可以指定到自己的表里 也可以不写 默认就是ERR$_的
REJECT LIMIT UNLIMITED; –当insert into多行记录时 最多可以容忍多少错误。

–当然 直接这样也是可以的
INSERT INTO a VALUES(1) LOG ERRORS

Oracle包、函数、过程的加密和解密

Posted on by

首先我们来看一下oracle对包、函数、过程的加密

oracle内部提供了两种加密方式

我们先来看第一种：

在没有内置OCI之前，ORACLE只提供WRAP操作系统工具。

我们来简单的看一个例子：

SQL> ho

[oracle@ucjmh ~]$ vi function.sql

SQL> ho cat function.sql

create or replace function dl2ml(hy in varchar)

return varchar

as

x varchar(2);

begin

select

case

when hy >=’01’ and hy<= ’05’ then 1

when hy >=’06’ and hy<= ’11’ then 2

when hy >=’13’ and hy<= ’43’ then 3

when hy >=’44’ and hy<= ’46’ then 4

when hy >=’47’ and hy<= ’50’ then 5

when hy >=’51’ and hy<= ’59’ then 6

when hy >=’60’ and hy<= ’62’ then 7

when hy  =’63’ or  hy = ’65’ then 8

when hy >=’66’ and hy<= ’67’ then 9

when hy >=’68’ and hy<= ’71’ then 10

when hy >=’72’ and hy<= ’72’ then 11

when hy >=’73’ and hy<= ’74’ then 12

when hy >=’75’ and hy<= ’78’ then 13

when hy >=’79’ and hy<= ’81’ then 14

when hy >=’82’ and hy<= ’83’ then 15

when hy >=’84’ and hy<= ’84’ then 16

when hy >=’85’ and hy<= ’87’ then 17

when hy >=’88’ and hy<= ’92’ then 18

when hy >=’93’ and hy<= ’98’ then 19

else null

end

into x from dual;

return x;

end;

/

我们首先创始一个文件 里面是一个简单的函数

然后使用操作系统级别的命令进行加密

SQL> @function.sql

Function created.

SQL> ho

[oracle@ucjmh ~]$ wrap iname=function.sql oname=function.pld

PL/SQL Wrapper: Release 11.2.0.3.0- Production on Fri May 01 00:31:44 2015

Copyright (c) 1993, 2009, Oracle.  All rights reserved.

Processing function.sql to function.pld

[oracle@ucjmh ~]$ cat function.pld

create or replace function dl2ml wrapped

a000000

369

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

8

335 199

ndEQSpIvhV202FWf2jLyxpFnjQMwg1zrr0hGyo5EUOI6GMTHyUnF4S3rRsXrfy11DmGfmkPS

tcQ9Nue/OhW9vrvTuRMesVKf0vjY+UGcxijwSrxfuyzUD1jEHovrR0cVM1dx5fbGGc3GfbTg

SQVk2HhtAZooHn1TQ80riTTKhkALI+3MzBisPE7I1t7HakJ8XEgwkBPoy+j/Luup3tbe3YVo

42UE9/37WALL6NcuuovoO6z3+lA4SokDsMd7LmhMrwBLEtXWs3DDSJGKB5yt8ZQOBOOE69Oj

iS6iaQ2OqcOiUA13rQk+tok7yrcY0NRreURk6AkbTbo1G5T2BtMv0hpeRGlTQJ71g9yC2mKn

aN8uR0U38EiQ3rCYTyTetEd3lapLv8FC/psoXEq+ww==

/

[oracle@ucjmh ~]$ exit

exit

SQL> @funtion.pld

SP2-0310: unable to open file “funtion.pld”

SQL> @function.pld

Function created.

SQL> select dl2ml(’45’) from dual;

DL2ML(’45’)

——————————————————————————–

4

SQL> select text from user_source where name=’DL2ML’;

TEXT

——————————————————————————–

function dl2ml wrapped

a000000

369

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

8

335 199

ndEQSpIvhV202FWf2jLyxpFnjQMwg1zrr0hGyo5EUOI6GMTHyUnF4S3rRsXrfy11DmGfmkPS

tcQ9Nue/OhW9vrvTuRMesVKf0vjY+UGcxijwSrxfuyzUD1jEHovrR0cVM1dx5fbGGc3GfbTg

SQVk2HhtAZooHn1TQ80riTTKhkALI+3MzBisPE7I1t7HakJ8XEgwkBPoy+j/Luup3tbe3YVo

42UE9/37WALL6NcuuovoO6z3+lA4SokDsMd7LmhMrwBLEtXWs3DDSJGKB5yt8ZQOBOOE69Oj

iS6iaQ2OqcOiUA13rQk+tok7yrcY0NRreURk6AkbTbo1G5T2BtMv0hpeRGlTQJ71g9yC2mKn

aN8uR0U38EiQ3rCYTyTetEd3lapLv8FC/psoXEq+ww==

接下来我们再来看第二种加密方式：

在ORACLE 10.2之后，ORACLE提供了内置的OCI，为用户提供代码加密，其中，DBMS_DDL.WRAP和DBMS_DDL.CREATE_WRAPPED分别提供了加密、加密并且创建功能

还是来看个例子：

DBMS_DDL.WRAP使用示例

1：文本加密

select dbms_ddl.wrap(‘create or replace procedure TESTPRO is begin null end;’) from dual;

DBMS_DDL.WRAP(‘CREATEORREPLACEPROCEDURETESTPROISBEGINNULLEND;’)

————————————————————–

create or replace procedure TESTPRO wrapped

a000000

369

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

7

25 59

8g4YR51zp6Rm0t2Q5m7K9QrNB6wwg5nnm7+fMr2ywFznUrLLveeb6qV0K7jAMv7SXqWZgQjM

uIHHLcmmpmq0vfo=

2：已经创建过的代码对象加密

首先先创建一个过程

SQL> create or replace procedure TESTPRO1 is

V_str   varchar2(100);

begin

v_str:=’This is  string.’;

dbms_output.put_line(v_str);

end TESTPRO1;

/

Procedure created.

然后 从数据库获取源码 并且加密

SQL> DECLARE

l_source  sys.DBMS_SQL.VARCHAR2A;

l_wrap    sys.DBMS_SQL.VARCHAR2A;

BEGIN

SELECT case line

when 1 then ‘Create or replace ‘||Type|| ‘ ‘||text

else TEXT

end case  bulk collect into l_source from dba_source where name=’TESTPRO1’ ORDER BY LINE;

 

l_wrap := SYS.DBMS_DDL.WRAP(ddl => l_source,

lb  => 1,

ub  => l_source.count);

FOR i IN 1 .. l_wrap.count LOOP

DBMS_OUTPUT.put_line(l_wrap(i));

END LOOP;

END;

/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17

Create or replace PROCEDURE procedure wrapped

a000000

369

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

7

ab

c6

wET87k8GVIxXUUglEZEexfhS/xAwg5nnm7+fMr2ywFwWFpeu3C4+YvJc51Kyy73nm3jDpXSL

CabWS86PyMovzQRdiu4EkRo4EXAO3rL39cAy/tLW5YQJeQEKHPH3k9im

op6VbOlYQikj/DyF

l3Fuz9aGc4SE4eaE0qlXGcbrclwd2JTJwqwKT5lofzx0pkJtxUQ=

PL/SQL procedure successfully completed.

注：

dbms_sql.varchar2a提供最大为每行32k的支持

dbms_sql.varchar2s提供每行256字节的支持

ORACLE 10.2.0.1可能会出现22921 bug.

dbms_ddl.create_wrapped同上一样，只是形成加密之后，进行创建

这是oracle提供的两种加密的方式  但是o并没有提供解密的方式 o在rdbms目录下也有大量的pld的文件 可能是不想破解吧

但是聪明的使用者已经研究出来了怎么破解  我们来看一下：

首先我们要明白 oracle是怎么加密的

Oracle加密的原理就是先对源码进行lz压缩lzstr，然后对压缩数据进行SHA-1运算得到40位的加密串shstr，然后将加密串与压缩串拼接得到shstr+lzstr，然后对拼接后的字符串进行Oracle双字符转换（转换表）。最后将转换后的字符串进行base64编码，最终得到wrap的加密串。

既然我们通过ＳＱＬ可以这样对某过程做DBMS_DDL.WRAP加密可以得到密文

那么对这部份密文的正文部份进行ＢＡＳＥ６４解码的值与未加密正文直接进行ＬＺ压缩后的值必然是一一对应的，且两个串的长度也是相等的对密文

进行ＢＡＳＥ６４解码后，将对应的密文的正文部份按字节替换成替换表中预先算出来的字节，最后直接按ＬＺ算法进行解压，

替换表正确的情况下，明文就应该出来了。通过这种假设，肯定就能得到替换表，替换表是按字

节来计算的，所以应该有二个列，其中一列代表ＢＡＳＥ６４解码后的字节值（十六进制００到ＦＦ），另一列代表替换列（ＢＡＳＥ６４列不能出现重复值是一个前提）。
ＢＡＳＥ６４编码地球人都知道，在ＯＲＡＣＬＥ中有现存的工具包进行编码和解码，我们将用到ＢＡＳＥ６４的解码，具体包是：sys.utl_encode.base64_decode。用的时候还需要另一个过程来将字符串转换为ＲＡＷ格式：sys.utl_raw.cast_to_raw

ＬＺ压缩很常见，不过懂得内部算法的人很少，ＯＲＡＣＬＥ中也有现存的工具包，我这里用的是老外的一个ＪＡＶＡ包。在

使用这个ＬＺ工具包时，涉及到一个压缩级别参数，这个等级参数不一样，压缩得到的字符串完全一不样。有人可能要问，这样搞

岂不是没法得到替换表了吗？是的，但也不完全正确。因为可供选择的等级参数有限，俺们还能从０等级开始一个一个进行测试，

看到底哪个参数是ＯＲＡＣＬＥ系统用的来ＷＲＡＰ的。嘿嘿，ＯＲＡＣＬＥ用的是“９”等级。

创建过程或包时如果没有ＣＲＥＡＴＥ部份，ＯＲＡＣＬＥ肯定要报错；同样DBMS_DDL.WRAP也不能缺少这个“create”，

否则就要报错。但对于过程或包的ＳＯＵＲＣＥ，查阅系统视图DBA_SOURCE的ＴＥＸＴ列就知道了，肯定没有ＣＲＥＡＴＥ这一句。

说到密文的正文部份，首先要看下面的例子：SQL>select dbms_ddl.wrap(‘create procedure a’) from dual;

create procedure a wrapped

a000000

354

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

7

c 38

8BgMHdmA3Qg9IbJmntlZoZQoHwcwg5nnm7+fMr2ywFxakaamb40d1Q=

这里要解释一下，加密后的代码中３５４与ＤＢ的版本有关，abcd后的７与创建的对象类型有关，也就是７为存储过程，另外的c 38有其他意义，这里就不多说了。从8BgMH开始，ＢＡＳＥ６４解码后的前二十个字节是SHA1-HASH值，所以解码后的第二十一个字节开始就是正文了
为了下一节的实践活动，嘿嘿，我们先把ＪＡＶＡ包创建好，用以进行LZ压缩与解压，如下所示（全部用ＳＹＳ用户来做）：create or replace java source named UNWRAPPER

as

import java.io.*;

import java.util.zip.*;

public class UNWRAPPER

{

public static String Inflate( byte[] src )

{

try

{

ByteArrayInputStream bis = new ByteArrayInputStream( src );

InflaterInputStream iis = new InflaterInputStream( bis );

StringBuffer sb = new StringBuffer();

for( int c = iis.read(); c != -1; c = iis.read() )

{

sb.append( (char) c );

}

return sb.toString();

} catch ( Exception e )

{

}

return null;

}

public static byte[] Deflate( String src, int quality )

{

try

{

byte[] tmp = new byte[ src.length() + 100 ];

Deflater defl = new Deflater( quality );

defl.setInput( src.getBytes( “UTF-8” ) );

defl.finish();

int cnt = defl.deflate( tmp );

byte[] res = new byte[ cnt ];

for( int i = 0; i < cnt; i++ )

res = tmp;

String hello = new String(res.toString().getBytes(“iso8859-1”), “GBK”);

return hello;

return res;

} catch ( Exception e )

{

}

return null;

}

}

/

alter java source UNWRAPPER compile

/

然后用包把JAVA声明进来：

create or replace package amosunwrapper

is

function deflate( src in varchar2 )

return raw;

function deflate( src in varchar2, quality in number )

return raw;

function inflate( src in raw )

return varchar2;

end;

/

create or replace package body amosunwrapper

is

function deflate( src in varchar2 )

return raw

is

begin

return deflate( src, 6 );

end;

function deflate( src in varchar2, quality in number )

return raw

as language java

name ‘UNWRAPPER.Deflate( java.lang.String, int ) return byte[]’;

function inflate( src in raw )

return varchar2

as language java

name ‘UNWRAPPER.Inflate( byte[] ) return java.lang.String’;

end;

/
创建好了工具，我们先来看看下面的SQL：with src AS ( select ‘PACKAGE a’ txt  from dual   ),

wrap as   ( select src.txt , dbms_ddl.wrap( ‘create ‘ || src.txt ) wrap  from src  ),

subst as  (select substr( utl_encode.base64_decode( utl_raw.cast_to_raw(rtrim( substr( wrap.wrap, instr( wrap.wrap, chr( 10 ), 1, 20 ) + 1 ), chr(10) )  ) ), 41 ) x,

amosunwrapper.deflate( wrap.txt || chr(0), 9 ) d

from wrap  )

select substr( x, r * 2 – 1, 2 )  c_base64,

substr( d, r * 2 – 1, 2 )  c_translatecode

from subst  , ( select rownum r from dual connect by rownum <= ( select length( x ) / 2 from subst ) );

结果如下：

C_BASE64 C_TRANSLATECODE

30 78

83 DA

99 0B

B8 70

F5 74

33 F6

9F 76

F5 74

BF 77

5C 55

5A 48

91 64

A6 00

A6 00

CB 0E

C4 B7

E1 02

48 6E

通过对结果的排序，没有出现同一个ＢＡＳＥ６４编码对应不同的十六进制的情况，因此我们知道了可以用这个ＳＱＬ为基础，通过用不同的ＳＯＵＲＣＥ串来产生替换表的内容。
首先建一个表来存储替换表的内容,然后写一段PLSQL块来生成替换表的内容:
CREATE TABLE SYS.IDLTRANSLATE(

C_BASE64DECODE  VARCHAR2(2) NOT NULL,

C_LZDEFLATECODE VARCHAR2(2)     NULL

)

/

declare

nCnt integer;

nLoop integer;

nSLoop integer;

nCharmax integer;

nCharmin  integer;

vChar     Varchar2(3);

cursor getchar is

with src AS ( select ‘PACKAGE ‘||vChar txt  from dual   ),

wrap as   ( select src.txt , dbms_ddl.wrap( ‘create ‘ || src.txt ) wrap  from src  ),

subst as  (select substr( utl_encode.base64_decode( utl_raw.cast_to_raw(rtrim( substr( wrap.wrap, instr( wrap.wrap, chr( 10 ), 1, 20 ) + 1 ), chr(10) )  ) ), 41 ) x,

amosunwrapper.deflate( wrap.txt || chr(0), 9 ) d

from wrap  )

select  substr( x, r * 2 – 1, 2 )  xr ,

substr( d, r * 2 – 1, 2 )  dr

from subst  , ( select rownum r from dual connect by rownum <= ( select length( x ) / 2 from subst ) );

begin

nCharmax:=97;

nCharmin:=122;

For nLoop In 97..122 Loop

For nSloop In 0..99 Loop

vChar := chr(nLoop)||to_char(nSloop);

For abc In getchar Loop

Select Count(*) Into nCnt From sys.idltranslate WHERE c_base64decode = abc.xr;

If nCnt < 1 Then

Insert INTO sys.idltranslate VALUES (abc.xr,abc.dr);

Commit;

Else

Select Count(*) Into ncnt From sys.idltranslate WHERE c_base64decode = abc.xr AND c_lzdeflatecode=abc.dr;

If nCnt < 1 Then

DBMS_OUTPUT.PUT_LINE(‘wrong orginal char:’||vchar||’         hex base64:’||abc.xr);

End If;

End If;

End Loop;

End Loop;

End Loop;

end;

运行上面这段ＳＱＬ大概会产生１百多条记录，还未达到00-FF总共256条记录的要求，建议替换

select ‘PACKAGE ‘||vChar txt  from dual   中的PACKAGE关健字为procedure或者function类似的，继续运行直到替换表中有不重复的256条记录为止。

然后接下来看一个系统包

SQL> set serveroutput on;

Declare

vWrappedtext                Varchar2(32767);

vChar                                        Varchar2(2);

vRepchar                                Varchar2(2);

vLZinflatestr                        Varchar2(32767);

nLen                Integer;

nLoop        Integer;

nCnt                Integer;

Begin

select substr( utl_encode.base64_decode( utl_raw.cast_to_raw(rtrim( substr( TEXT, instr( TEXT, chr( 10 ), 1, 20 ) + 1 ), chr(10) )  ) ), 41 ) x

Into vWrappedtext

from DBA_SOURCE

Where owner=’SYS’

And Name = ‘DBMS_MONITOR’

And Type=’PACKAGE BODY’ ;

–DBMS_OUTPUT.PUT_LINE(vWrappedtext);

nLen := Length(vWrappedtext)/2 – 1;

vLZinflatestr :=”;

For nLoop In 0..nLen Loop

vChar := Substrb(vWrappedtext,nLoop*2+1,2);

SelectSQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   Count(*) Into nCnt From SYS.IDLTRANSLATE Where C_BASE64DECODE=vChar;

If nCnt <> 1 Then

DBMS_OUTPUT.PUT_LINE(‘SUBSTATION TABLE WARNING: Count not find following char–‘||vChar);

Return;

Else

Select C_LZDEFLATECODE Into vRepchar From SYS.IDLTRANSLATE Where C_BASE64DECODE=vChar;

End If;

vLZinflatestr := vLZinflatestr || vRepchar;

–DBMS_OUTPUT.PUT_LINE(vLZinflatestr);

End Loop;

–DBMS_OUTPUT.PUT_LINE(vLZinflatestr);

DBMS_OUTPUT.PUT_LINE(amosunwrapper.inflate(vLZinflatestr));

End;

23   24   25   26   27   28   29   30   31   32   33   34   35

36  /

PACKAGE BODY dbms_monitor IS

PROCEDURE CLIENT_ID_STAT_ENABLE(CLIENT_ID IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_1”

WITH CONTEXT

PARAMETERS (CONTEXT,

CLIENT_ID   OCISTRING,

CLIENT_ID   INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE CLIENT_ID_STAT_DISABLE(CLIENT_ID IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_2”

WITH CONTEXT

PARAMETERS (CONTEXT,

CLIENT_ID     OCISTRING,

CLIENT_ID   INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE

SERV_MOD_ACT_STAT_ENABLE(SERVICE_NAME IN VARCHAR2,

MODULE_NAME  IN VARCHAR2,

ACTION_NAME  IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_3”

WITH CONTEXT

PARAMETERS (CONTEXT,

SERVICE_NAME

OCISTRING,

SERVICE_NAME    INDICATOR SB4,

MODULE_NAME     OCISTRING,

MODULE_NAME

INDICATOR SB4,

ACTION_NAME     OCISTRING,

ACTION_NAME     INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE SERV_MOD_ACT_STAT_DISABLE(SERVICE_NAME IN VARCHAR2,

MODULE_NAME  IN VARCHAR2,

ACTION_NAME  IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_4”

WITH CONTEXT

PARAMETERS (CONTEXT,

SERVICE_NAME

OCISTRING,

SERVICE_NAME    INDICATOR SB4,

MODULE_NAME     OCISTRING,

MODULE_NAME

INDICATOR SB4,

ACTION_NAME     OCISTRING,

ACTION_NAME     INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE CLIENT_ID_TRACE_ENABLE(CLIENT_ID IN VARCHAR2,

WAITS IN BOOLEAN,

BINDS IN BOOLEAN,

PLAN_STAT IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_5”

WITH CONTEXT

PARAMETERS (CONTEXT,

CLIENT_ID   OCISTRING,

CLIENT_ID     INDICATOR SB4,

WAITS   UB2,

WAITS   INDICATOR SB4,

BINDS

UB2,

BINDS   INDICATOR SB4,

PLAN_STAT   OCISTRING,

PLAN_STAT   INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE CLIENT_ID_TRACE_DISABLE(CLIENT_ID IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_6”

WITH CONTEXT

PARAMETERS (CONTEXT,

CLIENT_ID   OCISTRING,

CLIENT_ID   INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE SERV_MOD_ACT_TRACE_ENABLE(SERVICE_NAME IN VARCHAR2,

MODULE_NAME  IN VARCHAR2,

ACTION_NAME  IN VARCHAR2,

WAITS IN BOOLEAN,

BINDS IN BOOLEAN,

INSTANCE_NAME

IN VARCHAR2,

PLAN_STAT IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_7”

WITH CONTEXT

PARAMETERS

(CONTEXT,

SERVICE_NAME    OCISTRING,

SERVICE_NAME    INDICATOR SB4,

MODULE_NAME

OCISTRING,

MODULE_NAME     INDICATOR SB4,

ACTION_NAME     OCISTRING,

ACTION_NAME

INDICATOR SB4,

WAITS     UB2,

WAITS     INDICATOR SB4,

BINDS     UB2,

BINDS INDICATOR SB4,

INSTANCE_NAME   OCISTRING,

INSTANCE_NAME   INDICATOR SB4,

PLAN_STAT

OCISTRING,

PLAN_STAT     INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE

SERV_MOD_ACT_TRACE_DISABLE(SERVICE_NAME IN VARCHAR2,

MODULE_NAME  IN VARCHAR2,

ACTION_NAME  IN VARCHAR2,

INSTANCE_NAME IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_8”

WITH

CONTEXT

PARAMETERS (CONTEXT,

SERVICE_NAME    OCISTRING,

SERVICE_NAME    INDICATOR SB4,

MODULE_NAME OCISTRING,

MODULE_NAME     INDICATOR SB4,

ACTION_NAME     OCISTRING,

ACTION_NAME INDICATOR SB4,

INSTANCE_NAME   OCISTRING,

INSTANCE_NAME   INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE SESSION_TRACE_ENABLE(SESSION_ID IN BINARY_INTEGER,

SERIAL_NUM IN BINARY_INTEGER,

WAITS IN BOOLEAN,

BINDS IN BOOLEAN,

PLAN_STAT IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_9”

WITH CONTEXT

PARAMETERS (CONTEXT,

SESSION_ID    SB4,

SESSION_ID    INDICATOR

SB4,

SERIAL_NUM    SB4,

SERIAL_NUM    INDICATOR SB4,

WAITS   UB2,

WAITS

INDICATOR SB4,

BINDS   UB2,

BINDS   INDICATOR SB4,

PLAN_STAT   OCISTRING,

PLAN_STAT     INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE SESSION_TRACE_DISABLE(SESSION_ID IN BINARY_INTEGER,

SERIAL_NUM IN BINARY_INTEGER) IS

EXTERNAL

NAME “kewe_3gl_10”

WITH CONTEXT

PARAMETERS (CONTEXT,

SESSION_ID    SB4,

SESSION_ID    INDICATOR SB4,

SERIAL_NUM    SB4,

SERIAL_NUM    INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE DATABASE_TRACE_ENABLE(WAITS IN BOOLEAN,

BINDS IN BOOLEAN,

INSTANCE_NAME IN

VARCHAR2,

PLAN_STAT IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_11”

WITH CONTEXT

PARAMETERS

(CONTEXT,

WAITS     UB2,

WAITS     INDICATOR SB4,

BINDS     UB2,

BINDS INDICATOR SB4,

INSTANCE_NAME   OCISTRING,

INSTANCE_NAME   INDICATOR SB4,

PLAN_STAT

OCISTRING,

PLAN_STAT     INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

PROCEDURE DATABASE_TRACE_DISABLE(INSTANCE_NAME

IN VARCHAR2) IS

EXTERNAL

NAME “kewe_3gl_12”

WITH CONTEXT

PARAMETERS (CONTEXT,

INSTANCE_NAME   OCISTRING,

INSTANCE_NAME INDICATOR SB4)

LIBRARY DBMS_MONITOR_LIB;

END;

PL/SQL procedure success

上面直接调用不能大于4000个字节  我们写一个过程来封装一下 完善一下

set serveroutput on;create or replace procedure unwrap(o in varchar,n in varchar, t in varchar)

as

vWrappedtext                Varchar2(32767);

vtrimtext                Varchar2(32767);

vChar                                        Varchar2(2);

vRepchar                                Varchar2(2);

vLZinflatestr                        Varchar2(32767);

nLen                Integer;

nLoop        Integer;

nCnt                Integer;

type vartab is table of varchar2(2) index by varchar2(2);

mytbl vartab;

cursor getchar is select C_BASE64DECODE xr,C_LZDEFLATECODE dr from sys.idltranslate;

Begin

for i in getchar loop –sys.idltranslate表内容存到字符数组

mytbl(i.xr):=i.dr;

end loop;

vtrimtext:=”;

select count(*) into ncnt                         from DBA_SOURCE

Where owner=o

And Name = n

And Type=t ;

if ncnt >0 and ncnt <5 then

for i in 1..ncnt loop

if i=1 then

select rtrim( substr( TEXT, instr( TEXT, chr( 10 ), 1, 20 ) + 1 ), chr(10) )   –保存去掉换行的BASE64码正文

into vLZinflatestr

from DBA_SOURCE

Where owner=o

And Name = n

And Type=t and line=i;

else

select text into vLZinflatestr

from DBA_SOURCE

Where owner=o

And Name = n

And Type=t and line=i;

end if;

vtrimtext:=vtrimtext||vLZinflatestr;

end loop;

end if;

vtrimtext:=replace(vtrimtext,chr(10),”);

nLen := Length(vtrimtext)/64 ;

vWrappedtext :=”;

for i in 0..nLen  loop

if i< nLen   then

vWrappedtext:=vWrappedtext||utl_encode.base64_decode( utl_raw.cast_to_raw(substrb(vtrimtext,64*i+1 , 64 ))) ;

else

vWrappedtext:=vWrappedtext||utl_encode.base64_decode( utl_raw.cast_to_raw(substrb(vtrimtext,64*i+1  ))) ;

end if;

–DBMS_OUTPUT.PUT_LINE(vWrappedtext);

End Loop;

–vWrappedtext:=substr(vWrappedtext,41);

nLen := Length(vWrappedtext)/2 – 1;

vLZinflatestr :=”;

For nLoop In 20..nLen Loop –从第41字节开始

vChar := Substrb(vWrappedtext,nLoop*2+1,2);

/*

Select Count(*) Into nCnt From SYS.IDLTRANSLATE Where C_BASE64DECODE=vChar;

If nCnt <> 1 Then

DBMS_OUTPUT.PUT_LINE(‘SUBSTATION TABLE WARNING: Count not find following char–‘||vChar);

Return;

Else

Select C_LZDEFLATECODE Into vRepchar From SYS.IDLTRANSLATE Where C_BASE64DECODE=vChar;

End If;

*/

vLZinflatestr := vLZinflatestr || mytbl(vChar); –从字符数组匹配

–DBMS_OUTPUT.PUT_LINE(vLZinflatestr);

End Loop;

–DBMS_OUTPUT.PUT_LINE(vLZinflatestr);

DBMS_OUTPUT.PUT_LINE(amosunwrapper.inflate(vLZinflatestr));

End;

/

最后我们来一个解密一下一开始的那个函数SQL> exec unwrap(‘SYS’,’DL2ML’,’FUNCTION’);

lag()和lead()

Posted on by

LAG()和LEAD()统计函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和 LEAD有更高的效率。以下整理的LAG()和LEAD()例子：

LAG(EXPRESSION,<OFFSET>,<DEFAULT>)
SQL> select year,region,profit ,lag (profit,1) over (order by year)  as 51xit_exp from test;
YEAR REGION      PROFIT 51xit_exp
—- ——- ———- ————-
2003 West            88
2003 West            88            88
2003 Central        101            88
2003 Central        100           101
2003 East           102           100
2004 West            77           102
2004 East           103            77
2004 West            89           103

LEAD(EXPRESION,<OFFSET>,<DEFAULT>)
SQL> select year,region,profit ,lead (profit,1) over (order by year)  as next_year_exp from test;
YEAR REGION      PROFIT NEXT_YEAR_EXP
—- ——- ———- ————-
2003 West            88            88
2003 West            88           101
2003 Central        101           100
2003 Central        100           102
2003 East           102            77
2004 West            77           103
2004 East           103            89
2004 West            89

Lag函数为Lag(exp,N,defval)，defval是当该函数无值可用的情况下返回的值。Lead函数的用法类似。
Lead和Lag函数也可以使用分组，以下是使用region分组的例子：
SQL> select year,region,profit , lag (profit,1,0) over (PARTITION BY region order by year)    as 51xit_exp from test;
YEAR REGION      PROFIT 51xit_exp
—- ——- ———- ————-
2003 Central        101             0
2003 Central        100           101
2003 East           102             0
2004 East           103           102
2003 West            88             0
2003 West            88            88
2004 West            77            88
2004 West            89            77

一SQL问题解答：
问题：
CREATE   TABLE  ldy_temp_2
(
分局    VARCHAR(255),
派出所    VARCHAR(255) ,
证件类型    VARCHAR(255) ,
证件号码    VARCHAR(255) ,
姓名    VARCHAR(255) ,
性别    VARCHAR(255) ,
行政区划    VARCHAR(255) ,
旅馆名称    VARCHAR(255) ,
旅馆地址    VARCHAR(255) ,
房间号    VARCHAR(255) ,
入住时间    VARCHAR(255) ,
col012    VARCHAR(255)
);

INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES(‘1234′,’ZHANGTAO’,’A’,’20100506′);
INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES(‘1234′,’ZHANGTAO’,’A’,’20100507′);
INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES(‘1234′,’ZHANGTAO’,’B’,’20100508′)；

INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES(‘1234′,’ZHANGTAO’,’A’,’20100509′);
INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES(‘1235′,’ZZZZ’,’A’,’20100506′);
INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES(‘1235′,’ZZZZ’,’B’,’20100507′);
INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES(‘1235′,’ZZZZ’,’A’,’20100508′);
INSERT INTO LDY_TEMP_2
(证件号码,姓名,旅馆名称,入住时间)
VALUES(‘1235′,’ZZZZ’,’B’,’20100509′);

建表语句和测试数据已经给出  请问  如何查找相邻两次入住旅馆名称不同的人；也就是说 一个人的证件号码是123的话 那么这个人的信息按照入住时间排序后  相邻两条数据的旅馆名称不能一样 。

解答：
with temp_a as
(select
t.证件号码,
t.旅馆名称,
t.入住时间,
lag(t.旅馆名称) over (partition by t.证件号码 order by t.入住时间) as lagname
from ldy_temp_2 t)
select 证件号码,姓名,旅馆名称,入住时间
from ldy_temp_2 a
where a.证件号码 not in (select b.证件号码 from temp_a b where  b.旅馆名称=b.lagname)

ORACLE中工具的使用09–COPY

Posted on by

COPY工具的使用:

1. COPY是sqlplus中的命令;
2. 在sqlplus中通过help copy查看帮助;
3. 一个表中只能包含一个long型字段,在数据字典中大量使用了long型字段;但是包含long类型的表不能使用CTAS语法创建新表;此时可以使用copy命令;
4. 比如dba_tab_cols视图是基于cols$表的,包含了一个LONG类型,如果直接创建:CREATE TABLE tbcols AS SELECT * FROM dba_tab_cols;
5. 使用copy命令创建:copy from system/ORACLE@orcl to system/ORACLE@orcl CREATE TBCOLS USING SELECT * FROM dba_tab_cols;

——————————- COPY的帮助文档 ——————————-
SQL> help copy

COPY
—-

Copies data from a query to a table in the same or another
database. COPY supports CHAR, DATE, LONG, NUMBER and VARCHAR2.

COPY {FROM database | TO database | FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE} destination_table
[(column, column, column, …)] USING query

where database has the following syntax:
username[/password]@connect_identifier
——————————- COPY的帮助文档 ——————————-

ORACLE中工具的使用07–BBED

Posted on by
BBED工具的使用
  1. BBED工具介绍:
    1. Oracle BBED(Oracle Block Browser and Editor)工具是Oracle内部提供的数据块级别查看和修改的工具;通过这个工具,我们可以方便的查看Oracle块级别的存储信息,从而更好的了解Oracle Internal结构技术细节;
    2. Oracle不推荐使用此工具,它只用来给内部进行恢复处理使用,对外是不提供资料和相关文档的;所以当遇到故障时,优先采用备份恢复,最后再考虑使用此工具;
    3. 只有Linux/Unix平台下提供BBED工具,在Windows平台没有对应的版本;所以使用BBED的环境通常是命令行方式,而且BBED在Oracle内部是没有编译的,需要手工编译;
  2. Oracle10g下编译BBED(适合10g以前版本):
    1. BBED对应对象文件通常在$ORACLE_HOME/rdbms/lib目录下,所以先进入此目录:cd $ORACLE_HOME/rdbms/lib;
    2. 然后使用linux系统的make进行编译和连接,生成bbed可执行文件:make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed;                                     
    3. 为了使用方便,添加一个硬链接:link $ORACLE_HOME/rdbms/lib/bbed $ORACLE_HOME/bin/bbed;
    4. 查看此工具:which bbed;                                                   
  3. Oracle11g下编译BBED:
    1. 11g中直接编译后报错,缺少$ORACLE_HOME/rdbms/lib/ssbbded.o,$ORACLE_HOME/rdbms/lib/sbbdpt.o,$ORACLE_HOME/rdbms/mesg/bbedus.msb和$ORACLE_HOME/rdbms/mesg/bbedus.msg等文件;                                        
    2. 因为缺少必要的库文件,所以需要从10g环境中拷贝相应的文件到对应的目录; 从10g中拷贝文件到11g对应的目录中:scp oracle@singleton10g:/u01/app/oracle/product/10.2.0/db_1/rdbms/lib/ssbbded.o $ORACLE_HOME/rdbms/lib;scp oracle@singleton10g:/u01/app/oracle/product/10.2.0/db_1/rdbms/lib/sbbdpt.o $ORACLE_HOME/rdbms/lib;scp oracle@singleton10g:/u01/app/oracle/product/10.2.0/db_1/rdbms/mesg/bbedus.msb $ORACLE_HOME/rdbms/mesg;scp oracle@singleton10g:/u01/app/oracle/product/10.2.0/db_1/rdbms/mesg/bbedus.msg $ORACLE_HOME/rdbms/mesg;                          
    3. 然后进入$ORACLE_HOME/rdbms/lib目录进行编译:cd $ORACLE_HOME/rdbms/lib;make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed;                              
    4. 创建链接文件并查看:link $ORACLE_HOME/rdbms/lib/bbed $ORACLE_HOME/bin/bbed;which bbed;                                                   
  4. 使用bbed工具:
    1. 工具默认的密码是blockedit;
    2. 查看bbed工具参数:bbed help=yes;                                                              
    3. 查看bbed帮助:help all;                                     
    4. 指定数据库文件列表bbed.lst,通过SELECT file# || chr(9) || NAME || chr(9) || bytes FROM v$datafile;语句查看数据库文件信息;                   
    5. 编写bbed参数文件bbed.conf;                                                               
    6. 使用bbed:bbed parfile=bbed.conf;                                              
    7. tip:修改数据块之后,Oracle会认为它是坏块,需要重新计算checksum值,然后可以使用;
  5. BBED工具使用示例:
    1. 手动把bootstrap$表中line#=-1的记录从8.0.0.0.0修改为9.0.0.0.0导致系统无法正常启动;                                       
    2. 设置数据文件列表和参数文件,打开bbed工具;
    3. 我们知道,11g中bootstrap$位于file 1 block 520,所以使用find命令从这里开始搜索(find /c 9.0.0.0.0),最终搜索到的位置为file 1 block 521 offset 8179;
    4. 设置当前位置,并导出内容:set file 1 block 521 offset 8179;dump;
    5. 将此处的9修改为8:modify /c “8” offset 8179;                                            
    6. 修改之后,Oracle会认为该块已经损坏:verify;                                               
    7. 重新计算和应用校验位后,数据块可以恢复一致:sum apply;                                             
    8. 之后数据库可以正常启动;                                                
———————————– bbed工具参数 ———————————–
PASSWORD – Required parameter
FILENAME – Database file name
BLOCKSIZE – Database block size
LISTFILE – List file name
MODE – [browse/edit]
SPOOL – Spool to logfile [no/yes]
CMDFILE – BBED command file name
LOGFILE – BBED log file name
PARFILE – Parameter file name
BIFILE – BBED before-image file name
REVERT – Rollback changes from BIFILE [no/yes]
SILENT – Hide banner [no/yes]
HELP – Show all valid parameters [no/yes]
———————————– bbed工具参数 ———————————–
———————————– bbed的帮助文档 ———————————–
SET DBA [ dba | file#, block# ]
SET FILENAME ‘filename’
SET FILE file#
SET BLOCK [+/-]block#
SET OFFSET [ [+/-]byte offset | symbol | *symbol ]
SET BLOCKSIZE bytes
SET LIST[FILE] ‘filename’
SET WIDTH character_count
SET COUNT bytes_to_display
SET IBASE [ HEX | OCT | DEC ]
SET OBASE [ HEX | OCT | DEC ]
SET MODE  [ BROWSE | EDIT ]
SET SPOOL [ Y | N ]
SHOW [ <SET parameter> | ALL ]
INFO
MAP[/v] [ DBA | FILENAME | FILE | BLOCK ]
DUMP[/v] [ DBA | FILENAME | FILE | BLOCK | OFFSET | COUNT ]
PRINT[/x|d|u|o|c] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
EXAMINE[/Nuf] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
</Nuf>:
N – a number which specifies a repeat count.
u – a letter which specifies a unit size:
b – b1, ub1 (byte)
h – b2, ub2 (half-word)
w – b4, ub4(word)
r – Oracle table/index row
f – a letter which specifies a display format:
x – hexadecimal
d – decimal
u – unsigned decimal
o – octal
c – character (native)
n – Oracle number
t – Oracle date
i – Oracle rowid
FIND[/x|d|u|o|c] numeric/character string [ TOP | CURR ]
COPY [ DBA | FILE | FILENAME | BLOCK ] TO [ DBA | FILE | FILENAME | BLOCK ]
MODIFY[/x|d|u|o|c] numeric/character string
[ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
ASSIGN[/x|d|u|o] <target spec>=<source spec>
<target spec> : [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
<source spec> : [ value | <target spec options> ]
SUM [ DBA | FILE | FILENAME | BLOCK ] [ APPLY ]
PUSH [ DBA | FILE | FILENAME | BLOCK | OFFSET ]
POP [ALL]
REVERT [ DBA | FILE | FILENAME | BLOCK ]
UNDO
HELP [ <bbed command> | ALL ]
VERIFY [ DBA | FILE | FILENAME | BLOCK ]
CORRUPT [ DBA | FILE | FILENAME | BLOCK ]
———————————– bbed的帮助文档 ———————————–
———————————– bbed参数文件 ———————————–
BLOCKSIZE=8192
LISTFILE=bbed.lst
MODE=EDIT
PASSWORD=blockedit
LOGFILE=bbed.log
———————————– bbed参数文件 ———————————–

ORACLE中工具的使用06–ORADEBUG

Posted on by
oradebug工具的使用:

  1. oradebug主要是给oracle支持人员使用的,从8i开始,它是个sql*plus命令行工具,有sysdba的权限就可以使用,无需特别设置,主要用途为:
    1. 追踪进程,自己的或者是外部的;
    2. 确定进程写入哪个trc文件;
    3. 转储:数据文件头,内部oracle结构等;
    4. 暂时挂起进程;
    5. 确定实例使用了哪些共享内存块和信号量;
    6. 找出RAC实例使用了哪些互联地址和协议;
    7. 修改SGA中的数据结构;
  2. oradebug使用步骤:
    1. 启动sql*plus并以sysdba身份登入;
    2. 连接到一个进程;
    3. 设置一个事件或者进行诊断转储;
    4. 查看trc文件名;
    5. 与连接到的进程断开;
  3. oradebug的语法;
  4. 连接到一个进程的方法:
    1. oradebug setmypid:连接到当前的进程:
    2. oradebug setorapid pid:根据pid,连接到其它进程(v$process.pid);
    3. oradebug setospid spid:根据spid,连接到其它进程(v$process.spid);
  5. 查看共享信息内存方法:ipcs [-m|-s|-q],默认会列出共享内存,信号量,队列信息;
    1. 清除命令是ipcrm [-m|-s|-q] id;
    2. 参数:
      1. -m:列出共享内存信息;
      2. -s:列出共享信号量信息;
      3. -q:列出共享队列信息;
    3. 例子;
  6. 如果一个程序挂起,那么程序调用栈就可以显示它在调用路径中的哪一步挂起的:oradebug short_stack;                        
  7. 文件转储:
    1. 可以转储的类型:oradebug dmplist;
    2. 转储控制文件:oradebug dump controlf 10;
    3. 实现10046事件,例子;
    4. oradebug dump events的级别和范围:
      1. level 1,会话级别,对应alter session命令;
      2. level 2,进程级别,对应oradebug event命令;
      3. level 4,实例级别,对应alter system命令;
  8. oradebug对于解决问题和诊断性能是必不可少的工具;
——————————– oradebug语法 ——————————–
sqlplus / as sysdba
SQL> oradebug help
HELP           [command]                 Describe one or all commands
SETMYPID                                 Debug current process
SETOSPID       <ospid>                   Set OS pid of process to debug
SETORAPID      <orapid> [‘force’]        Set Oracle pid of process to debug
SETORAPNAME    <orapname>                Set Oracle process name to debug
SHORT_STACK                              Get abridged OS stack
CURRENT_SQL                              Get current SQL
DUMP           <dump_name> <lvl> [addr]  Invoke named dump
DUMPSGA        [bytes]                   Dump fixed SGA
DUMPLIST                                 Print a list of available dumps
EVENT          <text>                    Set trace event in process
SESSION_EVENT  <text>                    Set trace event in session
DUMPVAR        <p|s|uga> <name> [level]  Print/dump a fixed PGA/SGA/UGA variable
DUMPTYPE       <address> <type> <count>  Print/dump an address with type info
SETVAR         <p|s|uga> <name> <value>  Modify a fixed PGA/SGA/UGA variable
PEEK           <addr> <len> [level]      Print/Dump memory
POKE           <addr> <len> <value>      Modify memory
WAKEUP         <orapid>                  Wake up Oracle process
SUSPEND                                  Suspend execution
RESUME                                   Resume execution
FLUSH                                    Flush pending writes to trace file
CLOSE_TRACE                              Close trace file
TRACEFILE_NAME                           Get name of trace file
LKDEBUG                                  Invoke global enqueue service debugger
NSDBX                                    Invoke CGS name-service debugger
-G             <Inst-List | def | all>   Parallel oradebug command prefix
-R             <Inst-List | def | all>   Parallel oradebug prefix (return output
SETINST        <instance# .. | all>      Set instance list in double quotes
SGATOFILE      <SGA dump dir>         Dump SGA to file; dirname in double quotes
DMPCOWSGA      <SGA dump dir> Dump & map SGA as COW; dirname in double quotes
MAPCOWSGA      <SGA dump dir>         Map SGA as COW; dirname in double quotes
HANGANALYZE    [level] [syslevel]        Analyze system hang
FFBEGIN                                  Flash Freeze the Instance
FFDEREGISTER                             FF deregister instance from cluster
FFTERMINST                               Call exit and terminate instance
FFRESUMEINST                             Resume the flash frozen instance
FFSTATUS                                 Flash freeze status of instance
SKDSTTPCS      <ifname>  <ofname>        Helps translate PCs to names
WATCH          <address> <len> <self|exist|all|target>  Watch a region of memory
DELETE         <local|global|target> watchpoint <id>    Delete a watchpoint
SHOW           <local|global|target> watchpoints        Show  watchpoints
DIRECT_ACCESS  <set/enable/disable command | select query> Fixed table access
CORE                                     Dump core without crashing process
IPC                                      Dump ipc information
UNLIMIT                                  Unlimit the size of the trace file
PROCSTAT                                 Dump process statistics
CALL           [-t count] <func> [arg1]…[argn]  Invoke function with arguments
——————————– oradebug语法 ——————————–
——————————– 查看共享信息内存的例子 ——————————–
— 1.生成信息到trace文件;
1.操作系统层面查看信息:host ipcs -m | grep ora
2.设置进程为当前进程:oradebug setmypid
3.导出信息到trace文件:oradebug ipc
4.查看trace文件的路径:oradebug tracefile_name
— 2.查看相应的trace文件;
Processing Oradebug command ‘ipc’
Dump of unix-generic skgm context
areaflags            000000f7
realmflags           0000001f
mapsize              00000800
protectsize          00001000
lcmsize              00001000
seglen               00400000
largestsize  00000000ffffffff
smallestsize 0000000000400000
stacklimit         0xbdc6af20
stackdir                   -1
mode                      640
magic                acc01ade
Handle:              0xf66058 `/u01/app/oracle/product/11.2.0/db_1ORCL’
Dump of unix-generic realm handle `/u01/app/oracle/product/11.2.0/db_1ORCL’, flags = 00
000000
Area #0 `Fixed Size’ containing Subareas 0-0
Total size 0000000000149294 Minimum Subarea size 00000000
Area  Subarea    Shmid      Stable Addr      Actual Addr
0        0   196608 0x00000020000000 0x00000020000000
Subarea size     Segment size
000000000014a000 0000000000800000
Area #1 `Variable Size’ containing Subareas 4-4
Total size 0000000032400000 Minimum Subarea size 00400000
Area  Subarea    Shmid      Stable Addr      Actual Addr
1        4   229377 0x00000020800000 0x00000020800000
Subarea size     Segment size
0000000032400000 0000000032400000
Area #2 `Redo Buffers’ containing Subareas 1-1
Total size 00000000004e3000 Minimum Subarea size 00000000
Area  Subarea    Shmid      Stable Addr      Actual Addr
2        1   196608 0x0000002014a000 0x0000002014a000
Subarea size     Segment size
00000000004e3000 0000000000800000
Area #3 `Base Allocator Control’ containing Subareas 3-3
Total size 0000000000002000 Minimum Subarea size 00000000
Area  Subarea    Shmid      Stable Addr      Actual Addr
3        3   196608 0x000000207fe000 0x000000207fe000
Subarea size     Segment size
0000000000002000 0000000000800000
Area #4 `Slab Allocator Control’ containing Subareas 2-2
Total size 00000000001d1000 Minimum Subarea size 00000000
Area  Subarea    Shmid      Stable Addr      Actual Addr
4        2   196608 0x0000002062d000 0x0000002062d000
Subarea size     Segment size
00000000001d1000 0000000000800000
Area #5 `skgm overhead’ containing Subareas 5-5
Total size 0000000000002000 Minimum Subarea size 00000000
Area  Subarea    Shmid      Stable Addr      Actual Addr
5        5   262146 0x00000052c00000 0x00000052c00000
Subarea size     Segment size
0000000000002000 0000000000400000
Dump of Linux-specific skgm context
sharedmmu 00000001
shareddec        0
used region        0: start 0000000012000000 length 0000000000400000
used region        1: start 0000000020000000 length 0000000033000000
used region        2: start 00000000af800000 length 0000000010800000
Maximum processes:               = 1000
Number of semaphores per set:    = 125
Semaphores key overhead per set: = 4
User Semaphores per set:         = 121
Number of semaphore sets:        = 9
Semaphore identifiers:           = 9
Semaphore List=
491520
524289
557058
589827
622596
655365
688134
720903
753672
————– system semaphore information ————-
—— Shared Memory Segments ——–
key        shmid      owner      perms      bytes      nattch     status
0x00000000 196608     oracle    640        4096       0
0x00000000 229377     oracle    640        4096       0
0x671360a4 262146     oracle    640        4096       0
—— Semaphore Arrays ——–
key        semid      owner      perms      nsems
0x4c1391ac 491520     oracle    640        125
0x4c1391ad 524289     oracle    640        125
0x4c1391ae 557058     oracle    640        125
0x4c1391af 589827     oracle    640        125
0x4c1391b0 622596     oracle    640        125
0x4c1391b1 655365     oracle    640        125
0x4c1391b2 688134     oracle    640        125
0x4c1391b3 720903     oracle    640        125
0x4c1391b4 753672     oracle    640        125
—— Message Queues ——–
key        msqid      owner      perms      used-bytes   messages
——————————– 查看共享信息内存的例子 ——————————–
——————————– oradebug实现10046事件 ——————————–
— 1.设置某个进程;
session 1:
sqlplus / as sysdba
conn hr/hr
SELECT pid, spid FROM v$process WHERE addr = (SELECT paddr FROM v$session WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1));
— 29 22505;
session 2:
sqlplus / as sysdba
oradebug setorapid 29;
— 2.设置trace文件的大小:oradebug unlimit;
— 3.打开10046事件:oradebug event 10046 trace name context forever, level 12;
— 4.执行查询:SELECT COUNT(*) FROM hr.employees;(在session 1中)
— 5.查看trace文件的位置:oradebug tracefile_name;
— 6.关闭10046事件:oradebug event 10046 trace name context off;
— 6.查看trace文件,使用tkprof工具格式化;
——————————– oradebug实现10046事件 ——————————–

ORACLE中工具的使用05–TKPROF

Posted on by
tkprof工具的使用

  1. tkprof工具位于$ORACLE_HOME/bin目录下(查看命令which trcsess),用于格式化trace文件(也可以使用第三方的工具,格式化出来的更加美观和全面),从而可以非常方便的跟踪和诊断sql语句的执行效率;
  2. tkprof的语法;
    1. table:手动生成explain plan时,存储中间信息的临时表,默认为PROF$PLAN_TABLE;
    2. explain:手动生成explain时,连接数据库的用户名和密码;
    3. print:仅仅处理前integer数量的sql语句,如果我们需要生成脚本,该参数对脚本中包含的sql数量是不影响的;
    4. insert:生成脚本,该脚本会创建表,并把相关统计信息插入表,从而可以在数据库中查看;
    5. record:生成不包含递归sql的脚本文件;
    6. sys:是否包含sys用户执行的sql,大多数是递归sql;
    7. aggregate=no:如果设置为yes的话,会合并相同的sql语句,一般设置为no,分别查看每次的执行;
    8. waits:是否记录等待事件;
    9. sort:对sql语句排序的规则;
    10. 常用的语法:tkprof tracefiles outputfile sys=no aggregate=no;
  3. SQL Trace文件的内容:
    1. Parse, execute, and fetch counts:解析,执行,获取三个动作的执行次数;
    2. CPU and elapsed times:消耗的cpu时间和总时间,单位是秒;
    3. Physical reads and logical reads:物理读和逻辑读的次数;
    4. Number of rows processed:处理的记录数;
    5. Misses on the library cache:没有命中缓存的次数;
    6. Username under which each parse occurred:执行sql语句的用户;
    7. Each commit and rollback:每次的提交和回滚操作(tkprof不会处理这些信息);
    8. Wait event and bind data for each SQL statement:针对每条sql语句的等待事件和绑定变量信息;
    9. Row operations showing the actual execution plan of each SQL statement:sql语句的实际执行计划;
    10. Number of consistent reads, physical reads, physical writes, and time elapsed for each operation on a row;
  4. Sql Trace与执行计划:
    1. 在sql trace期间,如果sql语句的游标已经关闭,则在sql trace中会包含相应的执行计划,Example 1;
    2. 在sql trace期间,如果sql语句的游标没有关闭,则在sql trace中不会包含相应的执行计划;
    3. 如果之前sql语句已经执行过,则会包含执行计划;
    4. 如果在trace文件中不存在执行计划的相关信息,可以通过tkprof的explain参数来登陆数据库,并执行explain plan命令,把执行计划写入到trace文件中;
  5. 指定aggregate=yes,tkprof会汇总相同的sql语句信息,并在文件的最后汇总所有语句的相关信息;
  6. 使用insert和records参数的例子,Example 2;
  7. 注意一些陷阱:
    1. Avoiding the Argument Trap:如果在运行时不注意绑定变量的问题,很可能会陷入参数陷阱,EXPLAIN PLAN命令不会检查SQL语句中绑定变量的类型,总是认为是VARCHAR类型;所以,如果绑定变量如果实际为number或者date类型的话,tkprof会进行一个隐式转换,导致生成错误的执行计划;为了避免这种情况,需要自己执行转换;
    2. Avoiding the Read Consistency Trap:如果要查询的数据被更新了,而且没有提交,那么很可能就会进入一致性读的陷阱,因为如果有很多相同的查询的操作的话,会一直构建CR块;
    3. Avoiding the Schema Trap:当看到一个只有少量数据返回却发现扫描了非常多的块的统计是,有可能是一下原因:
      1. 表被经常的更新/删除,造成了水位线很高;
      2. 可能tkprof分析的trace文件记录的是之前没有在表上创建索引情况,而之后加上了索引;
    4. Avoiding the Time Trap:当执行一个简单的sql,但是看到elapsed time时间特别长时,可能是再等待其它的锁;
————————————— tkprof的语法 —————————————
[oracle@singleton11g ~]$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename   Use ‘schema.tablename’ with ‘explain=’ option.
explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
print=integer    List only the first ‘integer’ SQL statements.
aggregate=yes|no
insert=filename  List SQL statements and data inside INSERT statements.
sys=no           TKPROF does not list SQL statements run as user SYS.
record=filename  Record non-recursive statements found in the trace file.
waits=yes|no     Record summary for any wait events found in the trace file.
sort=option      Set of zero or more of the following sort options:
prscnt  number of times parse was called
prscpu  cpu time parsing
prsela  elapsed time parsing
prsdsk  number of disk reads during parse
prsqry  number of buffers for consistent read during parse
prscu   number of buffers for current read during parse
prsmis  number of misses in library cache during parse
execnt  number of execute was called
execpu  cpu time spent executing
exeela  elapsed time executing
exedsk  number of disk reads during execute
exeqry  number of buffers for consistent read during execute
execu   number of buffers for current read during execute
exerow  number of rows processed during execute
exemis  number of library cache misses during execute
fchcnt  number of times fetch was called
fchcpu  cpu time spent fetching
fchela  elapsed time fetching
fchdsk  number of disk reads during fetch
fchqry  number of buffers for consistent read during fetch
fchcu   number of buffers for current read during fetch
fchrow  number of rows fetched
userid  userid of user that parsed the cursor
————————————— tkprof的语法 —————————————
————————————— Example 1 —————————————
— 1.使用hr用户登录,并查看sql语句的执行计划;
sqlplus / as sysdba
conn hr/hr
EXPLAIN PLAN FOR SELECT employee_id, email, salary FROM HR.EMPLOYEES WHERE employee_id = 100;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
— 2.打开sql trace功能,然后设置标识符为E1,并执行sql语句;
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER=’E1′;
SELECT employee_id, email, salary FROM HR.EMPLOYEES WHERE employee_id = 100;
— 3.格式化trace文件,然后查看执行计划:tkprof *_E1.trc e1.out sys=no aggregate=no;
————————————— Example 1 —————————————
————————————— Example 2 —————————————
— 1.使用hr用户登录,打开sql trace功能,然后设置标识符为E2,并执行sql语句;
sqlplus / as sysdba
conn hr/hr
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER=’E2′;
SELECT USER FROM DUAL;
SELECT employee_id, email, salary FROM HR.EMPLOYEES WHERE employee_id = 200;
SELECT COUNT(*) FROM HR.DEPARTMENTS;
— 2.格式化trace文件,然后查看执行计划:tkprof *_E2.trc e2.out sys=no insert=insert.sql record=record.sql;
— 3.查看record.sql文件;
[oracle@singleton11g trace]$ less record.sql
SELECT USER FROM DUAL ;
SELECT employee_id, email, salary FROM HR.EMPLOYEES WHERE employee_id = 200 ;
SELECT COUNT(*) FROM HR.DEPARTMENTS ;
— 4.查看insert.sql文件;
REM  Edit and/or remove the following  CREATE TABLE
REM  statement as your needs dictate.
CREATE TABLE  tkprof_table
(
date_of_insert                       DATE
,cursor_num                           NUMBER
,depth                                NUMBER
,user_id                              NUMBER
,parse_cnt                            NUMBER
,parse_cpu                            NUMBER
,parse_elap                           NUMBER
,parse_disk                           NUMBER
,parse_query                          NUMBER
,parse_current                        NUMBER
,parse_miss                           NUMBER
,exe_count                            NUMBER
,exe_cpu                              NUMBER
,exe_elap                             NUMBER
,exe_disk                             NUMBER
,exe_query                            NUMBER
,exe_current                          NUMBER
,exe_miss                             NUMBER
,exe_rows                             NUMBER
,fetch_count                          NUMBER
,fetch_cpu                            NUMBER
,fetch_elap                           NUMBER
,fetch_disk                           NUMBER
,fetch_query                          NUMBER
,fetch_current                        NUMBER
,fetch_rows                           NUMBER
,ticks                                NUMBER
,sql_statement                        LONG
);
set sqlterminator off
INSERT INTO tkprof_table VALUES
(
SYSDATE, 2, 0, 84, 1, 2000, 2136, 0, 0, 0, 1
, 1, 0, 46, 0, 0, 0, 0, 0
, 2, 0, 26, 0, 0, 0, 1, 12753692
, ‘SELECT USER FROM DUAL
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 3, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 10, 0, 356, 0, 0, 0, 0, 0
, 10, 0, 448, 0, 40, 0, 10, 50095736
, ‘select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spa
re2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
and linkname is null and subname is null
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 4, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 0, 80, 0, 0, 0, 0, 0
, 2, 0, 267, 0, 8, 0, 2, 1674
, ‘select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.cluc
ols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blk
cnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.proper
ty,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.tri
gflag, 0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cacheh
it,ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj#
(+)
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 5, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 0, 118, 0, 0, 0, 0, 0
, 10, 2000, 2037, 0, 16, 0, 8, 3642
, ‘select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pct
free$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.clu
fac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1
),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.def
errable#+c.valid#,0),nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.p
ctthres$,null,null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,ist
.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,min(t
o_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from
cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+)
and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 6, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 8, 1000, 301, 0, 0, 0, 0, 0
, 17, 0, 410, 0, 34, 0, 9, 2435
, ‘select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ where obj#=:1
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 7, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 0, 200, 0, 0, 0, 0, 0
, 17, 1000, 402, 0, 6, 0, 15, 1796
, ‘select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(s
cale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,sc
ale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property,
nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ where o
bj#=:1 order by intcol#
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 8, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 10, 999, 293, 0, 0, 0, 0, 0
, 10, 0, 459, 0, 30, 0, 10, 3519
, ‘select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(li
sts,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0),NVL(
bitmapranges,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 9, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 8, 0, 199, 0, 0, 0, 0, 0
, 8, 0, 330, 0, 24, 0, 8, 2328
, ‘select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$
o where o.obj#=:1
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 10, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 1000, 149, 0, 0, 0, 0, 0
, 2, 0, 119, 0, 4, 0, 0, 1135
, ‘select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ wher
e obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#,
grantee#
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 11, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 0, 65, 0, 0, 0, 0, 0
, 5, 0, 179, 0, 6, 0, 3, 762
, ‘select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$
where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 12, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 0, 51, 0, 0, 0, 0, 0
, 9, 1000, 269, 0, 18, 0, 7, 1321
, ‘select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 from cdef$ where robj
#=:1
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 13, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 0, 80, 0, 0, 0, 0, 0
, 16, 0, 290, 0, 32, 0, 14, 2173
, ‘select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),r
owid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3 from cdef$ where obj#=:1
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 14, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 14, 0, 336, 0, 0, 0, 0, 0
, 28, 0, 499, 0, 56, 0, 14, 4305
, ‘select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 15, 1, 0, 1, 1999, 1376, 0, 0, 0, 1
, 1, 2000, 2335, 0, 0, 0, 1, 0
, 1, 0, 26, 0, 2, 0, 1, 4365
, ‘select condition from cdef$ where rowid=:1
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 16, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 3, 0, 71, 0, 0, 0, 0, 0
, 3, 0, 102, 0, 9, 0, 3, 905
, ‘select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample
_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, av
gcln from hist_head$ where obj#=:1 and intcol#=:2
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 2, 0, 84, 1, 17997, 16136, 0, 0, 0, 1
, 1, 0, 23, 0, 0, 0, 0, 0
, 2, 0, 78, 0, 2, 0, 1, 1010
, ‘SELECT employee_id, email, salary FROM HR.EMPLOYEES WHERE employee_id = 200
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 15, 0, 84, 1, 8999, 9177, 0, 0, 0, 1
, 1, 1000, 113, 0, 0, 0, 0, 0
, 2, 0, 254, 0, 1, 0, 1, 2161
, ‘SELECT COUNT(*) FROM HR.DEPARTMENTS
‘)
/
COMMIT;
set sqlterminator on
————————————— Example 2 —————————————

ORACLE中工具的使用04–TRCSESS

Posted on by
trcsess工具的使用

  1. trcsess工具位于$ORACLE_HOME/bin目录下(查看命令which trcsess),它可以把USER_DUMP_DEST目录下多个trc文件根据一定规则合并成一个trc文件,然后使用tkprof工具进行格式化;
  2. 语法:trcsess [output=<output file name >]  [session=<session ID>] [clientid=<clientid>] [service=<service name>] [action=<action name>] [module=<module name>] <trace file names>;                                                                               
  3. 可以根据session,clientid,service,action,module和trace file的文件名(支持通配符)等不同的维度来合并USER_DUMP_DEST目录下的trc文件;相关的信息都可以从v$session视图中查看的到:SELECT sid, client_identifier, service_name, module, action, sql_trace FROM v$session;同样的可以从trace文件中查看的到;(其中sid是[sid.serial#]格式)
  4. trace文件的命名规则是<ORACLE_SID>_ora_<spid>_<TRACEFILE_IDENTIFIER>.trc,默认的TRACEFILE_IDENTIFIER是空,可以通过ALTER SESSION SET TRACEFILE_IDENTIFIER='<TRACEFILE_IDENTIFIER>’命令来修改trace文件的标示符;
  5. 可以通过DBMS_SESSION.SET_IDENTIFIER(‘<TRACEFILE_IDENTIFIER>’)过程来设置client id;
  6. 该工具主要应用于共享服务器模式或者采用连接池模式,我们很难针对某用户进行跟踪的情况下;
  7. 用例:根据tracefile_identifier,service/module来合并trace文件;
———————————- 根据tracefile_identifier来合并trace文件 ———————————-
— 1.清空user_dump_dest目录下的trace文件(仅仅为了测试);
— 2.打开session 1做如下操作;
sqlplus / as sysdba
ALTER SESSION SET TRACEFILE_IDENTIFIER=’ti1′;
ALTER SESSION SET SQL_TRACE=TRUE;
SELECT ‘SESSION 1’ FROM DUAL;
— 3.打开session 2做如下操作;
sqlplus / as sysdba
ALTER SESSION SET TRACEFILE_IDENTIFIER=’ti1′;
ALTER SESSION SET SQL_TRACE=TRUE;
SELECT ‘SESSION 2’ FROM DUAL;
— 4.查看生成的trace文件;

— 5.合并trace文件;
根据service_name合并:trcsess output=ti1_service.out service=’SYS$USERS’ *_ti1.trc
根据module_name合并:trcsess output=ti1_module.out module=’sqlplus@singleton11g.snda.com (TNS V1-V3)’ *_ti1.trc
———————————- 根据tracefile_identifier来合并trace文件 ———————————-

ORACLE中工具的使用03–LogMiner

Posted on by
LogMiner工具的使用:

  1. 用来分析Oracle数据库运行过程中产生的redo logfile和archived logfile来获取对数据库操作的DML语句;
  2. 以SYS用户安装LogMiner工具:
    1. 安装DMBS_LOGMNR包:@?/rdbms/admin/dbmslm.sql;                                 
    2. 安装DMBS_LOGMNR_D包:@?/rdbms/admin/dbmslmd.sql;                                 
  3. LogMiner包的介绍:
    1. DMBS_LOGMNR包:包含了初始化LogMiner工具,打开和关闭LogMiner会话的子程序;
      1. DMBS_LOGMNR.ADD_LOGFILE(LogFileName, options):开启一个LogMiner的会话,并添加redo logfile文件到这个队列里面;
        1. LogFileName:指定要分析的redo logfile;
        2. options:打开一个新的LogMiner会话并添加一个redo logfile(DMBS_LOGMNR.NEW);添加一个redo logfile到现存的LogMiner会话(DBMS_LOGMNR.ADDFILE);
      2. DMBS_LOGMNR.REMOVE_LOGFILE(LogFileName):通过指定名称,从被分析的redo logfile队列中移除一个redo logfile;
      3. DMBS_LOGMNR.START_LOGMNR(startScn, endScn, startTime, endTime, DictFileName, options):加载LogMiner数据字典并启动LogMiner来分析redo logfiles,最后填充动态视图;
        1. startScn:LogMiner返回大于此SCN的redo记录(分析后可以查询select filename, low_scn, next_scn from v$logmnr_logs来查询每个redo logfile包含的SCN范围);
        2. endScn:LogMinfer返回小于此SCN的redo记录;
        3. startTime:默认为1988-01-01,如果指定了startScn,则忽略此参数;
        4. endTime:默认为2110-12-31,如果指定了endScn,则忽略此参数;
        5. DictFileName:,默认为空,指定包含LogMiner字典的文本文件.用来生成v$logmnr_contents视图.必须指定DBMS_LOGMNR_D.BUILD过程中的全路径;
        6. options:分析日志时的操作选项,查看帮助文档;
      4. DMBS_LOGMNR.END_LOGMNR:结束LogMiner的会话,当退出Database Session的时候会自动调用此过程;
    2. DMBS_LOGMNR_D包:用来创建LogMiner的数据字典;
      1. DMBS_LOGMNR_D.BUILD(dictionary_filename, dictionary_location, options):即导出数据库的数据字典到一个文本中,数据字典发生变化都要重新创建一次;;
        1. dictionary_filename:指定LogMiner字典的文件名;
        2. dictionary_location:指定LogMiner字典文件的路径;
        3. options:指定LogMiner字典写的位置,文本文件(STORE_IN_FLAT_FILE,默认)或者是在线日志文件(STORE_IN_REDO_LOGS);
        4. 指定文本文件,则必须设置UTL_FILE_DIR初始化参数(ALTER SYSTEM SET UTL_FILE_DIR=’\tmp’ SCOPE=spfile;然后重启数据库服务使其生效):
          1. EXECUTE dbms_logmnr_d.build(‘dictionary_filename’, ‘dictionary_location’);
          2. EXECUTE dbms_logmnr_d.build(‘dictionary_filename’, ‘dictionary_location’, options => dbms_logmnr_d.store_in_flat_file);
        5. 指定在线日志文件,不常用,影响性能:EXECUTE dbms_logmnr_d.build(options => dbms_logmnr_d.store_in_redo_logs);
      2. DBMS_LOGMNR_D.SET_TABLESPACE(new_tablespace);
        1. 默认情况下,LogMiner的表会被创建到SYSAUX表空间,使用此函数可以修改LogMiner表存放的表空间;
        2. DBMS_LOGMNR_D.SET_TABLESPACE(‘tablespace_name’);
  4. 打开数据库的附加日志:ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;                   
  5. 创建数据字典:
    1. 创建表空间:CREATE TABLESPACE LOGMINER DATAFILE ‘/u01/app/oracle/oradata/AUX/logminer01.dbf’ SIZE 100M AUTOEXTEND ON;                     
    2. 设置LogMiner表的表空间:EXEC dbms_logmnr_d.set_tablespace(‘LOGMINER‘);                                     
    3. 设置UTL_FILE_DIR参数,然后重启数据库使之生效:alter system set utl_file_dir=’/u01/app/oracle/oradata/AUX/’ scope=spfile;                      
    4. 生成数据字典:EXEC dbms_logmnr_d.build(‘dictionary.lm’, ‘/u01/app/oracle/oradata/AUX/’);                             
  6. 模拟一个人为的错误;                                                                    
  7. 打开LogMiner的session并添加日志文件,只是在当前的session有效(查询归档日志的路径:select name from v$archived_log);              
  8. 分析日志,指定生成的LogMiner数据字典:EXEC dbms_logmnr.start_logmnr(DictFileName=>’/u01/app/oracle/oradata/AUX/dictionary.lm’);                 
  9. 此时就可以查询LogMiner的视图了,可以在IDE环境下去做,可以更好的过滤想要的结果;
  10. 退出LogMiner的会话:EXEC dbms_logmnr.end_logmnr;                                      
  11. LogMiner的视图:
    1. V$LOGMNR_CONTENTS:用来存放LogMiner分析日志后的结果;
      1. SELECT scn, TIMESTAMP, log_id, operation, sql_redo, sql_undo FROM v$logmnr_contents WHERE username = ‘HR’;
      2. 分析结果仅在运行过程dbms_logmrn.start_logmnr这个会话的生命期中存在.因为所有的LogMiner存储都在PGA内存中,所有其他的进程是看不到它的,同时随着进程的结束,分析结果也随之消失;
    2. V$LOGMNR_DICTIONARY:使用的数据字典的信息;
    3. V$LOGMNR_LOGS:包含所分析的日志的信息,SELECT log_id, filename, db_name, low_scn, next_scn, low_time, high_time, status FROM v$logmnr_logs;
    4. V$LOGMNR_SESSION:包含LogMiner当前的Session信息;
  12. 注意事项:
    1. 利用LogMiner工具来分析其它数据库实例产生的redo logfiles,使用LogMiner分析其它数据库实例时的注意事项;
    2. LogMiner必须使用被分析数据库实例产生的字典文件,而不是安装LogMiner的数据库产生的字典文件,另外必须保证安装LogMiner数据库的字符集和被分析数据库的字符集相同;
    3. 被分析数据库平台必须和当前LogMiner所在数据库平台一样,也就是说如果我们要分析的文件是由运行在UNIX平台上的Oracle 8i产生的,那么也必须在一个运行在UNIX平台上的Oracle实例上运行LogMiner,而不能在其它如Microsoft NT上运行LogMiner,当然两者的硬件条件不一定要求完全一样;
    4. LogMiner日志分析工具仅能够分析Oracle 8以后的产品,不过它可以分析Oracle8的日志,对于8以前的产品,该工具也无能为力.另外,Oracle8i只能对DML操作进行分析,从Oracle9i开始不仅可以分析DML操作,而且也可以分析DDL操作;
  13. Oracle11g中EM继承了LogMiner工具(需要开启附加日志):
    1. LogMiner的路径:Avaliability->Manage->View and Manage Transactions;            
    2. 可以通过时间/SCN,查询某些表/用户下的所有的事务/DDL;                             
    3. 结果页面;                                                                         
— 模拟人为错误,并切换归档;
conn hr/hr
create table lm tablespace logminer as select * from employees;
delete from lm where employee_id < 200;
commit;
conn / as sysdba
alter system switch logfile;
— 打开LogMiner的session并添加日志文件;
EXECUTE dbms_logmnr.add_logfile(LogFileName=>’/u01/app/oracle/flash_recovery_area/AUX/archivelog/2012_09_18/o1_mf_1_3_85j72xlx_.arc’, options=>dbms_logmnr.new);
EXECUTE dbms_logmnr.add_logfile(LogFileName=>’/u01/app/oracle/flash_recovery_area/AUX/archivelog/2012_09_18/o1_mf_1_4_85j76znp_.arc’, options=>dbms_logmnr.addfile);

ORACLE中工具的使用02–DBVERIFY

Posted on by
DBVERIFY工具的使用

  1. DBVERIFY工具介绍:
    1. DBVERIFY is an external command-line utility that performs a physical data structure integrity check. It can be used on offline or online databases, as well on backup files. You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored, or as a diagnostic aid when you have encountered data corruption problems;
    2. DBVERIFY checks are limited to cache-managed blocks (that is, data blocks). Because DBVERIFY is only for use with datafiles, it will not work against control files or redo logs;
    3. 只支持镜像备份,不支持备份集验证;测试发现控制文件可以验证,但是在线日志不可以;
  2. DBVERIFY的语法:dbv key=value;
    1. USERID=Username/Password:指定用户名密码,如果检查的文件是ASM文件系统,需要提供验证信息;
    2. FILE=file_name:要检查的datafile文件名;
    3. START=start_block:要检查的文件的开始块号,默认是文件第一个块;
    4. END=end_block:要检查文件的结束的块号,默认是文件最后一个块;
    5. BLOCKSIZE=size:指定块的大小,默认是8k,如果使用的不是8k的块需要指定此参数;
    6. LOGFILE=log_file:指定日志文件,如果不指定就显示在终端;
    7. FEEDBACK=n:返回检查的进度,n个单位打一个点;
    8. PARFILE=parameter_file:指定一个参数文件;
    9. SEGMENT_ID:指定段的id(由三部分组成:tablespace_id.header_file.header_block),可以通过sql语句查询;
  3. DBVERIFY的用法:
    1. 对datafile文件进行检查;
    2. 对segment进行检查;
    3. 对备份集的检查;
    4. 对控制文件的检查;
  4. 检查datafile中的block:
    1. 这种模式下,会扫描一个datafile中的一个或者多个blocks,并生成一份检查结果;
    2. 如果datafile是ASM系统文件的话,需要提供USERID连接到这个ASM文件;
    3. 检查system表空间:dbv file=$ORACLE_BASE/oradata/PROD/system01.dbf feedback=1000;
  5. 检查segment:
    1. 在这种模式下,可以用来检查一个对象(表/索引)使用的segment的可用性,指定segment的所有链表都会被检查;
    2. 被检查的相应的对象会被加锁,如果是索引的话,则引用的表会被加锁;
    3. 需要提供sysdba的身份验证,因为需要读取segment的数据字典;
    4. 检查scott用户下emp表的segment:dbv userid=scott/tiger segment_id=4.4.2003 feedback=10;                    
  6. 验证控制控制文件:
    1. 常规验证,发现错误,因为块大小不对,修改验证语法:dbv file=$ORACLE_BASE/oradata/PROD/control01.ctl feedback=100;     
    2. 验证:dbv file=$ORACLE_BASE/oradata/PROD/control01.ctl blocksize=16384 feedback=100;
— 查询某个SEGMENT的id;
SELECT a.ts# || ‘.’ || b.header_file || ‘.’ || b.header_block
FROM v$tablespace a, dba_segments b
WHERE a.NAME = b.tablespace_name AND b.segment_name = ‘segment_name’ AND owner = ‘owner’;
— 块损坏的几种检查;
————————————————————————————–
特性                      坏块侦测类型                    能否修复损坏块
————————————————————————————————
DBVERIFY                  物理                         否
ANALYZE                   逻辑                         否
DB_BLOCK_CHECKING         逻辑                         否
DB_BLOCK_CHECKSUM         物理                         否
exp                       物理                         否
FlashBack                 逻辑                         是
DBMS_REPAIR               逻辑                         是
Block media recovery      未知                         是

ORACLE中工具的使用01–DBNEWID

Posted on by
DBNEWID工具的使用

  1. DBNEWID工具介绍:
    1. 手动创建一个数据库的拷贝时,在重建控制文件时可以指定一个新的DBNAME,但是不能修改DBID.DBID是内部唯一标示一个数据库的标示符;RMAN是使用DBID区分数据库的,所以就不能把这两个数据库注册到同一个RMAN的资源库中了;
    2. DBNEWID工具可以:
      1. 只修改DBID(无法控制修改为多少);
      2. 只修改DBNAME;
      3. 同时修改DBID和DBNAME;
    3. 如果修改了DBID,之前所有的备份,归档日志都会失效,而且打开数据库时需要指定RESETLOGS参数;
    4. 强烈建议在修改DBID之前对数据库进行备份;
    5. 使用DBNEWID工具不会影响global_name,它只能通过ALTER DATABASE语法进行修改,由DB_NAME和域组成:ALTER DATABASE RENAME GLOBAL_NAME TO db_name.db_domain;
    6. DBNEWID工具也不会修改INSTANCE_NAME,但是会修改DB_NAME,DB_UNIQUE_NAME和SERVICE_NAMES;
  2. DBNEWID的语法:nid key=value;
    1. TARGET=user/pwd@tns_name:登陆目标数据库;
    2. DBNAME=newname:指定要修改的新的数据库的名称,如果不指定此参数,则只修改DBID;
    3. SETNAME=YES/NO:默认为NO,如果指定为YES,表示值修改数据库名称;
    4. REVERT=YES/NO:默认为NO,如果为YES,表示如果失败了回退到之前的状态;
    5. LOGFILE=log_file:指定日志文件;
    6. APPEND=YES/NO:指定是追加日志还是覆盖日志;
    7. 例子:
      1. 只修改DBID:nid target=user/pwd@tns_name logfile=nid.log;
      2. 只修改DBNAME:nid target=user/pwd@tns_name dbname=newname setname=yes logfile=nid.log;
      3. 同时修改DBID和DBNAME:nid target=user/pwd@tns_name dbname=newname logfile=nid.log;
      4. 发生错误回退到之前的状态:nid target=user/pwd@tns_name revert=yes logfile=nid.log
  3. 修改DBID和DBNAME:
    1. 对数据库做一次全备;
    2. 配置好监听(静态注册数据库)和网络名,如果是在本机操作则不需要此步骤;
    3. 查看当前的DBID和DBNAME:SELECT dbid, name FROM v$database;             
    4. 正常关闭数据库,并启动到mount状态;                                       
    5. 调用nid工具,使用sysdba登陆:nid target=sys/oracle dbname=OMR,修改完成后数据库处于shutdown状态;                  
    6. 修改spfile文件中的DB_NAME参数(如果只修改DBID的话,不用这个步骤);               
    7. 重置密码文件(如果只修改DBID或者没有修改ORACLE_SID的话,不用这个步骤,因为密码文件只跟ORACLE_SID有关):
    8. 启动数据库到mount状态,然后以resetlogs方式打开数据库:ALTER DATABASE OPEN RESETLOGS;(如果只是修改了DB_NAME的话,不用这个步骤,之前的日志还可以使用)
    9. 查看新的DBID和DB_NAME;