今天在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

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@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实现对日本邮政编码分类的规则.本来学长只需要做概要设计即可,详细设计和开发都是外包给越南人做,但是担心越南的童鞋方法不能够满意,索性就直接把关键的步骤都自己实现掉,想想也是为了世界和平做了不少贡献;

规则是这样的:
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

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
这个时候就只会有一行数据了

在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;

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;
/

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包提供了一个程序,使您可以创建一个错误日志表,
以便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对包、函数、过程的加密

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加密可以得到密文