eygle.com   eygle.com
eygle.com  
 
Digest Net: January 2009 Archives

January 2009 Archives

=========================================================
作者: yangtingkun(http://yangtingkun.itpub.net)
发表于: 2006.08.18 17:58
分类: ORACLE
出处: http://yangtingkun.itpub.net/post/468/195510
=========================================================

Oracle的HASH分区,没有SPLIT PARTITION语言,替代的语句是ADD PARTITION。研究了一下发现ADD PARTITION语句和RANGE、LIST分区中的SPLIT PARTITION是十分相似的。


Oracle用于HASH分区的hash函数应该是唯一确定的,也就是说,给定分区个数,那么分区键值在这些分区中的分布就是固定不变的。否则的话,Oracle的HASH分区表就无法进行分区交换操作。

Oracle推荐分区数是2的幂,这样可以保证各个分区的数据分布相对均匀。其实对于分区数不是2的幂的HASH分区,可以看作是2的幂的一种变形。

首先考虑分区数是2的整数幂的情况:当Oracle的分区数从2个变为4个,Oracle并不需要将所有数据重新打乱,而是将原有的2个分区每个都一分为二。同样的道理,如果将分区数设置为8,Oracle会将原有的4个分区一分为二。

SQL> CREATE TABLE TEST_HASH2 (ID NUMBER) PARTITION BY HASH(ID) 2 (PARTITION P2_1, PARTITION P2_2);

表已创建。

SQL> CREATE TABLE TEST_HASH4 (ID NUMBER) PARTITION BY HASH(ID)
2 (PARTITION P4_1, PARTITION P4_2, PARTITION P4_3, PARTITION P4_4);

表已创建。

SQL> CREATE TABLE TEST_HASH8 (ID NUMBER) PARTITION BY HASH(ID)
2 (PARTITION P8_1, PARTITION P8_2, PARTITION P8_3, PARTITION P8_4,
3 PARTITION P8_5, PARTITION P8_6, PARTITION P8_7, PARTITION P8_8);

表已创建。

SQL> INSERT INTO TEST_HASH2 SELECT ROWNUM FROM USER_TABLES;

已创建22行。

SQL> INSERT INTO TEST_HASH4 SELECT ROWNUM FROM USER_TABLES;

已创建22行。

SQL> INSERT INTO TEST_HASH8 SELECT ROWNUM FROM USER_TABLES;

已创建22行。

SQL> COMMIT;

提交完成。

SQL> SELECT * FROM TEST_HASH2 PARTITION(P2_1);

ID
----------
2
5
6
8
11
13
18
20
21

已选择9行。

SQL> SELECT * FROM TEST_HASH4 PARTITION(P4_1);

ID
----------
6
11
13

SQL> SELECT * FROM TEST_HASH4 PARTITION(P4_3);

ID
----------
2
5
8
18
20
21

已选择6行。

SQL> SELECT * FROM TEST_HASH8 PARTITION(P8_1);

ID
----------
6
11

SQL> SELECT * FROM TEST_HASH8 PARTITION(P8_5);

ID
----------
13

SQL> SELECT * FROM TEST_HASH8 PARTITION(P8_3);

ID
----------
5
21

SQL> SELECT * FROM TEST_HASH8 PARTITION(P8_7);

ID
----------
2
8
18
20

举个形象一些的例子,Oracle的HASH分区就像是一棵大的二叉树。每个分区就相当于二叉树的一个叶节点。二叉树的第一层,只有一个根节点,对应只有1个分区的情况。二叉树的第二层,两个叶节点,对应2个分区的情况。二叉树的第三层,4个叶节点,对应4个分区的情况。二叉树的第n层,2^(n-1)个叶节点,对应2^(n-1)个分区情况。

每个分区中包含的分区键值都来自它的上层枝节点。

而对于分区数为非2的整数幂的情况,则可以看作上面的一种变形。可以看作树的最底层叶节点没有完全填满,还保留了几个上一层的叶节点。以6个分区为例,可以看成一个4层2叉树,第4层包括四个叶节点,第3层包括两个叶节点。

上面说了这么多,主要是为了说明,Oracle的HASH分区在增加分区时,最多只会影响到一个分区的数据。如果分区键值的分布恰好在新增分区中不存在,那么新增分区时,不会影响任何一个已经存在的分区。否则的话,只会影响它的上一层的枝节点分区。

Oracle首先会将本层的所有叶节点填满,然后才会增加新的一层。

Oracle在增加新的分区时,会根据HASH函数确定原有分区内的数据在两个新分区内的分布,增加完分区后,属于新分区的数据已经从源分区中转移到新分区中。

从二叉树的模型来说,增加分区的过程相当于一个节点分裂成两个叶节点的情况。左节点对应着分裂前的节点,而右节点就是新增的节点。

Oracle新增分区时,将从哪个分区中分裂出数据是可以判断出来的。Oracle增加分区和分裂数据的分区都是按照顺序进行的。

如果要增加的分区是第N个分区,大于等于N的最小2的整数幂为M,则当增加第N个分区时,这个分区的数据来源于分区N-M/2。

上面的公式过于抽象了,我们举两个具体的例子:

目前有4个分区,要增加第5个分区,大于等于5的最小整数幂是8,根据公式,5-8/2=1,第5个分区的数据来源于分区1。

目前有7个分区,要增加第8个分区,而大于等于8的最小整数幂也是8,根据公式,8-8/2=4,第8个分区的数据来源于分区4。

下面验证一下:

SQL> SELECT * FROM TEST_HASH4 PARTITION (P4_1);

ID
----------
6
11
13

SQL> ALTER TABLE TEST_HASH4 ADD PARTITION P4_5;

表已更改。

SQL> SELECT * FROM TEST_HASH4 PARTITION (P4_5);

ID
----------
13

SQL> CREATE TABLE TEST_HASH7 (ID) PARTITION BY HASH(ID)
2 (PARTITION P7_1, PARTITION P7_2, PARTITION P7_3, PARTITION P7_4,
3 PARTITION P7_5, PARTITION P7_6, PARTITION P7_7)
4 AS SELECT ROWNUM FROM USER_TABLES;

表已创建。

SQL> SELECT * FROM TEST_HASH7 PARTITION (P7_4);

ID
----------
1
3
4
7
14
15
16

已选择7行。

SQL> ALTER TABLE TEST_HASH7 ADD PARTITION P7_8;

表已更改。

SQL> SELECT * FROM TEST_HASH7 PARTITION(P7_8);

ID
----------
1
7
14
15

SQL> SELECT * FROM TEST_HASH7 PARTITION (P7_4);

ID
----------
3
4
16


分区合并操作的算法相当于增加分区的逆向操作,这里就不在描述了。

从上面的分析可以看出,HASH分区的ADD PARTITION和RANGE分区、LIST分区的SPLIT PARTITION很类似,都是从一个分区中取出一部分数据放到新增的分区中。唯一的区别在于,SPLIT操作允许用户指定操作的分区和SPLIT的位置,而ADD PARTITION则完全由Oracle来确定了。

最后想说的是,上面尝试用二叉树的方式解释分区的增加还是比较合适的,其实如果加上几副图的话,可能更容易把问题描述清楚。不过本文的实际意义并不大,在实际使用中没有什么的必要去了解新增分区数据来自哪个分区。所以,我也就不花力气再去配图了,有兴趣的可以自己在脑子中想象一下。

动机:

想在Oracle中用一条SQL语句直接进行Insert/Update的操作。

说明:

在进行SQL语句编写时,我们经常会遇到大量的同时进行Insert/Update的语句 ,也就是说当存在记录时,就更新(Update),不存在数据时,就插入(Insert)。

实战:

接下来我们有一个任务,有一个表T,有两个字段a,b,我们想在表T中做Insert/Update,如果存在,则更新T中b的值,如果不存在,则插入一条记录。在Microsoft的SQL语法中,很简单的一句判断就可以了,SQL Server中的语法如下:

if exists(select 1 from T where T.a='1001' ) update T set T.b=2 Where T.a='1001' else insert into T(a,b) values('1001',2);

以上语句表明当T表中如果存在a='1001' 的记录的话,就把b的值设为2,否则就Insert一条a='100',b=2的记录到T中。

但是接下来在Oracle中就遇到麻烦了,记得在Oracle 9i之后就有一条Merge into 的语句可以同时进行Insert 和Update的吗,Merge的语法如下:

MERGE INTO table_name alias1 
USING (
table|view|sub_query) alias2
ON (join condition) 
WHEN MATCHED THEN 
    
UPDATE table_name 
    
SET col1 = col_val1, 
        col2     
= col2_val 
WHEN NOT MATCHED THEN 
    
INSERT (column_list) VALUES (column_values); 

 

上面的语法大家应该都容易懂吧,那我们按照以上的逻辑再写一次。

MERGE INTO T T1
USING (
SELECT a,b FROM T WHERE t.a='1001') T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
  
UPDATE SET T1.b = 2
WHEN NOT MATCHED THEN 
  
INSERT (a,b) VALUES('1001',2);

以上的语句貌似很对是吧,实际上,该语句只能进行更新,而无法进行Insert,错误在哪里呢?

其实在Oracle中Merge语句原先是用来进行整表的更新用的,也就是ETL工具比较常用的语法,重点是在Using上。

用中文来解释Merge语法,就是:

在alias2中Select出来的数据,每一条都跟alias1进行 ON (join condition)的比较,如果匹配,就进行更新的操作(Update),如果不匹配,就进行插入操作(Insert)。

因此,严格意义上讲,"在一个同时存在Insert和Update语法的Merge语句中,总共Insert/Update的记录数,就是Using语句中alias2的记录数。"

以上这句话也就很好的解释了在上面写的语句为何只能进行Update,而不能进行Insert了,因为都Select不到数据,如何能进行Insert呢:)

接下来要改成正确的语句就容易多了,如下:

MERGE INTO T T1
USING (
SELECT '1001' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
  
UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN 
  
INSERT (a,b) VALUES(T2.a,T2.b);

查询结果,OK!

注意:

如果不懂Merge语句的原理,Merge语句是一条比较危险的语句,特别是在您只想更新一条记录的时候,因为不经意间,你可能就把整表的数据都Update了一遍.....汗!!!

我曾经犯过的一个错误如下所示,大家看出来是什么问题了吗?

MERGE INTO T T1
USING (
SELECT Count(*) cnt FROM T WHERE T.a='1001') T2
ON (T2.cnt>0)
WHEN MATCHED THEN
  
UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN 
  
INSERT (a,b) VALUES(T2.a,T2.b);
 
引自 链接

Oracle Data & Temp Files Management

| No Comments
转自网络 出处链接
 
General Information
Dependencies
file$ ts$ x$ktfbhc
dba_data_files dba_temp_files  
dba_free_space gv$dbfile  
 
Alter Data Files

Resize An Existing Datafile
ALTER DATABASE DATAFILE '<data_file_name | data_file_number>'
RESIZE <n> K|M|G|T|P|E;

Beware that you can only decrease the size of the datafile with the space that is free between highest used block of the datafile and the last block of the file. If the tablespace is fragmented, the free spaces between extents cannot be deallocated this way. Check dba_free_space for details.

ALTER DATABASE DATAFILE 'c:\oracle\oradata\orabase\tools02.tom'
RESIZE 50M;

ALTER DATABASE DATAFILE 'c:\oracle\oradata\orabase\tools03.dan'
RESIZE 50M;

Add A Datafile To An Existing Tablespace
ALTER TABLESPACE <tablespace_name>
ADD DATAFILE '<path_and_file_name>' SIZE <n>K|M|G|T|P|E;
ALTER TABLESPACE tools
ADD DATAFILE 'c:\oracle\oradata\orabase\tools02.tom' SIZE 20M;
ALTER DATABASE
CREATE DATAFILE '<path_and_file_name>' SIZE <n>K|M|G|T|P|E
AS '<tablespace_name>';
ALTER DATABASE
CREATE DATAFILE 'c:\oracle\oradata\orabase\uwdata03.dbf' SIZE 1G
AS 'UWDATA';

Move Tablespace Datafile

Can also be used to move SYSTEM, SYSAUX, and TEMP tablespace files
SHUTDOWN

STARTUP MOUNT

-- Copy the datafile to it's new location

ALTER DATABASE RENAME FILE
'<old_full_path>' TO '<new_full_path>';

-- then

ALTER DATABASE OPEN;

-- then you can safely delete the old datafile.
conn / as sysdba

shutdown immediate;

startup mount

host

$ cp /u01/oradata/tools01.dbf /u06/oradata/tools01.dbf

$ exit

alter database rename file '/u01/oradata/tools01.dbf'
to '/u06/oradata/tools01.dbf';

alter database open

host

$ rm /u01/oradata/tools.01.dbf

$ exit

Autoextend
ALTER DATABASE DATAFILE <'data_file_name' | data_file_number> 
AUTOEXTEND <OFF | ON [NEXT SIZE <n>K|M|G|T|P|E
MAXSIZE <UNLIMITED | <n>K|M|G|T|P|E>;
ALTER DATABASE DATAFILE 'u06/oradata/tools01.dbf' AUTOEXTEND OFF;

ALTER DATABASE DATAFILE 'u06/oradata/tools01.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED;

Online / Offline
ALTER DATABASE DATAFILE <'data_file_name' | data_file_number> 
<ONLINE | OFFLINE [FOR DROP]>;
ALTER DATABASE DATAFILE 'u06/oradata/tools01.dbf' OFFLINE;

ALTER DATABASE DATAFILE 'u06/oradata/tools01.dbf' ONLINE;

ALTER DATABASE DATAFILE 'u06/oradata/tools01.dbf' OFFLINE FOR DROP;
 
Alter Temp Files
Resize alter database tempfile <temp file name>
resize <integer> K|M|G|T|P|E;
ALTER DATABASE TEMPFILE 'temp01.dbf' RESIZE 100M;
Drop alter database tempfile <temp file name>
drop including datafiles;
ALTER DATABASE TEMPFILE 'temp01.dbf' DROP INCLUDING DATAFILES;
 
Drop Data File

Drop A Datafile
ALTER DATABASE DATAFILE '<file_name_or_file_number>' [offline] DROP;
set linesize 121
col file_name format a80

SELECT file_name, SUM(bytes)/1024/1024 DF_SIZE
FROM dba_data_files
GROUP BY file_name;

ALTER TABLESPACE users ADD datafile SIZE 50M;

SELECT file_name, SUM(bytes)/1024/1024 DF_SIZE
FROM dba_data_files
GROUP BY file_name;

ALTER DATABASE DATAFILE 'C:\ORACLE\PRODUCT\ORADATA\UKOUG\DATAFILE\O1_MF_USERS_35HCKNFO_.DBF'
OFFLINE DROP;

or

ALTER TABLESPACE users DROP DATAFILE 'C:\ORACLE\PRODUCT\ORADATA\UKOUG\DATAFILE\O1_MF_USERS_35HCKNFO_.DBF';
 
Data File Related Queries

Data File Information
set linesize 121
col file_name format a45
col tablespace_name format a20

SELECT file_name, tablespace_name,
       bytes/1024/1024 MB, blocks
FROM dba_data_files
UNION ALL
SELECT file_name, tablespace_name,
       bytes/1024/1024 MB, blocks
FROM dba_temp_files
ORDER BY tablespace_name, file_name;

Data File Block Sizing
-- as root created a file system with block size 1024

mkfs.ext3 -b 1024 /dev/sda3

-- mounted it

mount /dev/sda3 /mnt/test

-- and issued

iostat -d -t -x /dev/sda3

-- in another shell

dd if=/tmp/foo of=/mnt/test/foo2 bs=1024k

-- the results

Time: 08:47:05
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s
avgrq-sz
avgqu-sz await svctm %util
/dev/sda3 0.00 0.00 2.00 0.00 4.00 0.00 2.00 0.00
2.00
0.10 50.00 50.00 1.00

Time: 08:47:10
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s
avgrq-sz
avgqu-sz await svctm %util
/dev/sda3 0.00 10200.40 0.20 83.60 0.40 20568.00 0.20
10284.00 245.
45 67.92 810.50 31.03 26.00

-- do the math

wsec/s / wrqm/s = 20568.00 / 10200.40 = 2,017

-- Roughly two sectors of 512 bytes. So write were in blocks of 1K.

Uneven Datafile Usage Within A Tablespace
CREATE TABLESPACE bowie_data
DATAFILE 'c:\bowie\bowie_data01.dbf' size 10m,
'c:\bowie\bowie_data02.dbf' size 10m,
'c:\bowie\bowie_data03.dbf' size 10m
uniform size 64;

col segment_name format a30

SELECT file_id, file_name
FROM dba_data_files
WHERE tablespace_name = 'BOWIE_DATA';

CREATE TABLE one (x NUMBER) TABLESPACE bowie_data;
CREATE TABLE two (x NUMBER) TABLESPACE bowie_data;
CREATE TABLE three (x NUMBER) TABLESPACE bowie_data;
CREATE TABLE four (x NUMBER) TABLESPACE bowie_data;

Now we've create 4 tables in this tablespace. Let's see which data file they were placed in ...

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA';

Note that *all* tables have their first extent created in the *first* data file defined to the tablespace.

Now lets grow these tables and see what happens next.


ALTER TABLE one ALLOCATE EXTENT;
ALTER TABLE two ALLOCATE EXTENT;
ALTER TABLE three ALLOCATE EXTENT;
ALTER TABLE four ALLOCATE EXTENT;

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA'
ORDER BY segment_name;

... and the second extent of each table has been created in the second data file of the tablespace.

If a particular table were to keep growing ...


ALTER TABLE four ALLOCATE EXTENT;
ALTER TABLE four ALLOCATE EXTENT;

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA'
ORDER BY segment_name;

You can see how the extents get allocated to the data files in a round robin fashion. But the first extent is allocate to the first data file (providing it has sufficent space) ...

CREATE TABLE five (x NUMBER) TABLESPACE bowie_data;

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA'
ORDER BY segment_name;

Let's add a new data file. What happens now ...

ALTER TABLESPACE bowie_data
ADD DATAFILE 'c:\bowie\bowie_data04.dbf' SIZE 10M;

ALTER TABLE four ALLOCATE EXTENT;

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA'
ORDER BY segment_name;

A new extent is added to table four. And uses the new datafile.

ALTER TABLE four ALLOCATE EXTENT;

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA'
ORDER BY segment_name;

ALTER TABLE four ALLOCATE EXTENT;

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA'
ORDER BY segment_name;

... and now the new file is used. The files are still used in a round robin fashion with the new file slipping in.

Note how file 16 is the *most* used file and file 19 is the least. If I were to allocate several new tables that were only 1 or 2 extents in size, see how file 16 would be the one to be most "filled".
 

66首经典的少儿歌曲下载链接

| 6 Comments

今天CD机坏了,儿子要听音乐,无奈从网上搜索,找到了一大堆少儿歌曲。

66首,我整理了一下链接,可以通过迅雷来下载,迅雷只有4首左右不能下载,其他均可。

下载连接仅供参考,迅雷有效,歌曲名恕未一一标出:

http://yr.31133.com/Upsoft/xiaoChao.mp3
http://www.tsbaobei.com/uploadsoft/blm.mp3
http://163.26.175.3/recoder/html2/90mp3/Track15.mp3
http://218.27.88.182/xx/fujin/qifei/music/zimuge.mp3
http://www.spidersky.com/updata/congmingdeyixiu.mp3
http://www.zsxx.net/oldweb/xsyd/happy/music/mp3/08.mp3
http://syxx.eicbs.com/uploadfile/2006515113419246.mp3
http://www.wl.nm.cninfo.net/yy/gq/etgq/23.mp3
http://cimg2.163.com/culture/special/song/yifengqian.mp3
http://www.jhqsng.cn/uploadFiles/2006-09/1157846071788.mp3
http://syxx.eicbs.com/uploadfile/2006515113326927.mp3
http://www.qlfy.sdedu.net/source/song/mp3_2/2.mp3
http://www.educast.com.cn/upload/media/2005-08/10/172712316/6.mp3
http://www.1122.net.cn/child/erge/cn/07.mp3
http://218.4.152.202/yishujiaoyan/yinyue/etgq/儿童歌曲2/小星星.mp3
http://www.tsinghua.edu.cn/docsn/qhyey/ettd/song/mp3_1/8.mp3
http://218.4.44.75/oldweb/xsyd/happy/music/mp3/20.mp3
http://jyxx.zhedu.net.cn/xstd/site-bj/101/yinyue/lanjingling.mp3
http://www.dyzx.org/caolixin/dfz/xxyy1-6/2/fsj.mp3
http://baby.py.shangdu.com/rm/拔萝卜.mp3
http://www.paipaiwa.com/mp3/种太阳.mp3
http://www.mqyzedu.com/jyz/xxjs/syzy/liuxin/007.mp3
http://www.czdjy.com/etmp3/laoshininzao.mp3
http://gpsxd.hfedu.gd.cn/03xuesen/music/02/04hckl.mp3
http://gpsxd.hfedu.gd.cn/03xuesen/music/04/10waxlh.mp3
http://www.czdjy.com/etmp3/小号手之歌.mp3
http://ywxhxx.vicp.net/oblog3/rm/wabjtam.mp3
http://www.hld-toy.com/儿童歌曲-采蘑菇的小姑娘.mp3
http://www.gamasa.com.cn/old/Tb_storySong/file/小螺号.mp3
http://www.sxjy.net/xxyymp3/book3/01bz.mp3
http://www.csdyzx.cn/caolixin/dfz/xxyy1-6/2/xyz.mp3
http://www.popofun.cn/mp3/m/石头剪刀布.mp3
http://www.csdyzx.cn/caolixin/dfz/xxyy1-6/2/xhh.mp3
http://www.popofun.cn/mp3/m/我的朋友在哪里.mp3
http://www4.joyes.com/www/upload_rings/mp3/2006/08/20060821160530.mp3
http://astro.pu.ru/ira/audio/iv-f3.mp3
http://www.tfmm.net/music/admin/uploadsong/music02.mp3
http://www.hsycjy.com/kejian/音乐/少儿歌曲/%BD%A1%BF%B5%B8%E8.mp3
http://www.hsycjy.com/kejian/音乐/少儿歌曲/%CE%D2%B0%AE%CF%B4%D4%E8.mp3
http://www.baoby.com/mp3/youersong/幼儿歌曲/我的好宝宝.mp3
http://www.baoby.com/mp3/youersong/幼儿歌曲/大公鸡.mp3
http://www.baoby.com/mp3/youersong/幼儿歌曲/丢手绢.mp3
http://www.baoby.com/mp3/youersong/幼儿歌曲/三个和尚.mp3
http://www.baoby.com/mp3/youersong/幼儿歌曲/好阿姨.mp3
http://vlog.nn.cn/Video1/gaohuierge/200611662821_5617_9568141.mp3
http://www.baoby.com/mp3/youersong/幼儿歌曲/泼水歌.mp3
http://www.honglinbbs.com/attachment/Mon_0602/185_2155_176f5b0ddbbe712.mp3
http://1026.hbradio.com.cn/mp3/164.mp3
http://www11.cbern.gov.cn/rs1/kaidi/kd-xiao1/010_音乐/5.儿歌专集/小小少年.mp3
http://health.qcgd.com/images.39.net/39/pu/baby/media/sszymmh.mp3
http://kalaok.cn5566.com/album/金伽桐/金伽桐-晚安.mp3
http://www11.cbern.gov.cn/...i/kd-xiao1/010_音乐/5.儿歌专集/%C6%EF%C4%BE%C2%ED.mp3
http://www11.cbern.gov.cn/rs1/kaidi/kd-xiao1/010_音乐/5.儿歌专集/%CB%B5Hello.mp3
http://www11.cbern.gov.cn/rs1/kaidi/kd-xiao1/010_音乐/5.儿歌专集/--新年好642.mp3
http://music.cmxbb.com/mp3my/20050131/tongyao/20050205/dongzhiwu/Track06.mp3
http://hzen.hyinfo.net/ke/mp3/shengrikl.mp3
http://music.cmxbb.com/mp3my/20050131/tongyao/20050205/minzufengqing/Track03.mp3
http://music.cmxbb.com/mp3my/20050131/tongyao/20050205/dilifengsu/Track38.mp3
http://www.tongnian.com/Files/egty/跳皮筋.mp3
http://hzen.hyinfo.net/ke/mp3/mayi.mp3
http://music.cmxbb.com/mp3my/20050131/tongyao/20050205/renwulishi/Track31.mp3
http://www.popofun.cn/mp3/m/吹泡泡.MP3
http://www.zhenxingxiaoxue.com/ziyuanzhongxin/images/儿歌/买月亮.mp3
http://www.joyhomes.cn/fl/平安夜(儿歌).mp3
http://www.baoby.com/mp3/youersong/幼儿歌曲/%c1%bd%d6%bb%c0%cf%bb%a2.mp3

How to use the oracle REF CURSOR

| No Comments
原文地址: http://www.psoug.org/reference/ref_cursors.html
Oracle Ref Cursors
Version 10.2

Strongly Typed
Note: A REF CURSOR that specifies a specific return type.
Package Header CREATE OR REPLACE PACKAGE strongly_typed IS

TYPE return_cur IS REF CURSOR RETURN all_tables%ROWTYPE;
PROCEDURE child(p_return_rec OUT return_cur);
PROCEDURE parent(p_NumRecs PLS_INTEGER);

END strongly_typed;
/
Package Body CREATE OR REPLACE PACKAGE BODY strongly_typed IS
PROCEDURE child(p_return_rec OUT return_cur) IS

BEGIN
OPEN p_return_rec FOR
SELECT * FROM all_tables;
END child;
--==================================================
PROCEDURE parent (p_NumRecs PLS_INTEGER) IS
p_retcur return_cur;
at_rec all_tables%ROWTYPE;
BEGIN
child(p_retcur);

FOR i IN 1 .. p_NumRecs
LOOP
FETCH p_retcur
INTO at_rec;

dbms_output.put_line(at_rec.table_name ||
' - ' || at_rec.tablespace_name ||
' - ' || TO_CHAR(at_rec.initial_extent) ||
' - ' || TO_CHAR(at_rec.next_extent));
END LOOP;
END parent;
END strongly_typed;
/
To Run The Demo set serveroutput on

exec strongly_typed.parent(1)
exec strongly_typed.parent(8)

Weakly Typed
Note: A REF CURSOR that does not specify the return type such as SYS_REFCURSOR.
Child Procedure CREATE OR REPLACE PROCEDURE child (
p_NumRecs IN PLS_INTEGER,
p_return_cur OUT SYS_REFCURSOR)
IS

BEGIN
OPEN p_return_cur FOR
'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs ;
END child;
/
Parent Procedure CREATE OR REPLACE PROCEDURE parent (pNumRecs VARCHAR2) IS
p_retcur SYS_REFCURSOR;
at_rec all_tables%ROWTYPE;
BEGIN
child(pNumRecs, p_retcur);

FOR i IN 1 .. pNumRecs
LOOP

FETCH p_retcur
INTO at_rec;

dbms_output.put_line(at_rec.table_name ||
' - ' || at_rec.tablespace_name ||
' - ' || TO_CHAR(at_rec.initial_extent) ||
' - ' || TO_CHAR(at_rec.next_extent));
END LOOP;
END parent;
/
To Run The Demo set serveroutput on

exec parent(1)
exec parent(17)

Passing Ref Cursors
Ref Cursor Passing Demo CREATE TABLE employees (
empid NUMBER(5),
empname VARCHAR2(30));

INSERT INTO employees (empid, empname) VALUES (1, 'Dan Morgan');
INSERT INTO employees (empid, empname) VALUES (2, 'Jack Cline');
INSERT INTO employees (empid, empname) VALUES (3, 'Caleb Small');
COMMIT;
CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor SYS_REFCURSOR) IS

TYPE array_t IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;

rec_array array_t;

BEGIN
FETCH p_cursor BULK COLLECT INTO rec_array;

FOR i IN rec_array.FIRST .. rec_array.LAST
LOOP
dbms_output.put_line(rec_array(i));
END LOOP;
END pass_ref_cur;
/

set serveroutput on

DECLARE
rec_array SYS_REFCURSOR;
BEGIN
OPEN rec_array FOR
'SELECT empname FROM employees';

pass_ref_cur(rec_array);
CLOSE rec_array;
END;
/



AIX系统安装之后,默认不允许单个文件大小超过2G,对于企业应用系统来说,这个限制一般是需要去除的。

首先,取消安全方面的限制,需要修改配置文件/etc/security/limits,可以针对所有用户在default段修改,或者在指定user段来修改具体user的限制。下面是一个示例。设置成-1,表示取消大小限制。
cat /etc/security/limits

*
* Sizes are in multiples of 512 byte blocks, CPU time is in seconds
*
* fsize      - soft file size in blocks
* core       - soft core file size in blocks
* cpu        - soft per process CPU time limit in seconds
* data       - soft data segment size in blocks
* stack      - soft stack segment size in blocks
* rss        - soft real memory usage in blocks
* nofiles    - soft file descriptor limit
* fsize_hard - hard file size in blocks
* core_hard  - hard core file size in blocks
* cpu_hard   - hard per process CPU time limit in seconds
* data_hard  - hard data segment size in blocks
* stack_hard - hard stack segment size in blocks
* rss_hard   - hard real memory usage in blocks
* nofiles_hard - hard file descriptor limit
*
* The following table contains the default hard values if the
* hard values are not explicitly defined:
*
*   Attribute        Value
*   ==========    ============
*   fsize_hard    set to fsize
*   cpu_hard      set to cpu
*   core_hard         -1
*   data_hard         -1
*   stack_hard      8388608
*   rss_hard          -1
*   nofiles_hard      -1
*
* NOTE:  A value of -1 implies "unlimited"
*

default:
        fsize = -1
        core = 2097151
        cpu = -1
        data = -1
        rss = -1
        stack = -1
        nofiles = 65536
        maxuprc =1024
root:

daemon:

bin:

sys:

adm:

uucp:

guest:

nobody:

lpd:

oracle:
        fsize = -1
        core = 2097151
        cpu = -1
        data = -1
        rss = -1
        stack = -1
        nofiles = 65536

除了安全方面的限制,单个文件大小还受文件系统类型的限制,AIX下主要使用JFS和JFS2文件系统,下面是一个对比表格。可见,JFS最大只能支持64G的单个文件,有时候做大文件的归档备份时,有可能会超过这个限制的,所以建议使用JFS2文件系统。

FunctionsJFS2JFS
Fragments/Block Size512-4096 Block sizes512-4096 Frags
Maximum file system size16 Terabytes1 Terabyte
Maximum file size16 Terabytes64 GB
Number of i-nodesDynamic, limited by disk spaceFixed, set at file-system creation
Directory organizationB-treeLinear
CompressionNoYes
QuotasNoYes
Error loggingYesYes

About this Archive

This page is an archive of entries from January 2009 listed from newest to oldest.

December 2008 is the previous archive.

February 2009 is the next archive.

回到 首页 查看最近文章或者查看所有归档文章.