る狩 8 覯伎 覿蠍磯レ 螻牛. 覿蠍磯レ 企 碁煙るゼ 螳 企ろ伎れ ロ 蠍磯レ. 覿 レ 濠啓 焔レ 給. 企ろ伎る 覿 ロ蟆 覃 蠏碁 豌襴 覯螳 譴 り鍵 覓語 焔レ 手 蟆 轟壱. 企ろ伎れ 一危 朱れ ろ襦 覿磯 る 譬蟆讌. 豈 1000伎 讌襴 A朱 蟆 谿城 蟆螻 1000伎譴 500伎襦 覦 伎 1~500伎譴 A襯 谿場 蟆 殊 螻朱ゼ 螳讌讌. 覓朱 A朱 蟆 1伎 る 1000伎 500伎 螳蟆讌襷 願 朱 蟆曙一. 豌襴 覯襯 譴譴る 蟆 譯 譴 覃貉る讀伎. 豌 覿蠍磯レ 螻 豌 螳 襾轟企. 譯 覃讌 蠍磯レ伎.
覿螳朱 覿 蟆覃 覓語 覦 觜伎 譬給. 企ろ伎れ 覿 ロ 轟 企ろ伎り 覓語螳 蠍磯 覓語螳 覦 企ろ伎れ 覲企 螻 襾語 る 蟆. 伎 蠍磯覓語 蟯襴螳 蟯襴蠍磯 れ 所讌. 覿 豕 64000螳 蟾讌 覿 朱, 覿 企 伎 碁煙るゼ 燕 碁煙る 磯 覿 給. , 覿 企 觜碁У 碁煙る 覿 給. 企 貉れ覃 碁煙れ 蠍磯 貉れ蠍 覓語 覿 レ 覓企 襷給.
覿 蟆 覿螻 覲牛覿襦 . 覿 れ 覯覿螻 伎覿襦 . 襾殊 覿覿 覲企襦 蟆給... 覓碁 覃伎殊..^^;
覦襦 れ旧 れ願襦 讌. 襾殊 5螳 覿 襦 蟆一 螻 螳螳 覿 れ願 企ろ伎るゼ 燕. 覓朱 5螳襦 覿蠍磯 る 企 蠍一 蟆讌.
SQL> connect system/manager
郁屋給.
SQL> create tablespace data_199901
2 datafile 'K:\oracle_tablespace\data_199901.dbf' size 500k;
企 焔給.
SQL> create tablespace data_199902
2 datafile 'K:\oracle_tablespace\data_199902.dbf' size 500k;
企 焔給.
SQL> create tablespace data_199903
2 datafile 'K:\oracle_tablespace\data_199903.dbf' size 500k;
企 焔給.
SQL> create tablespace data_199904
2 datafile 'K:\oracle_tablespace\data_199904.dbf' size 500k;
企 焔給.
SQL> create tablespace data_maxvalues -------------> 蠍一 襷讌 蟆 覈 企ろ伎れ ｋ..
2 datafile 'K:\oracle_tablespace\data_maxvalues.dbf' size 500k;
企 焔給.
SQL> create table psales(
2 year char(4),
3 mon char(2),
4 dd char(2),
5 empno char(3),
6 count number(5),
7 amount number(8))
8 partition by range (year, mon) ---------------> 蠍一 覿 蠍一 貉殊 覈 譴.
9 (partition p1 values less than ('1999', '01')
10 tablespace data_199902,
11 partition p2 values less than ('1999', '02')
12 tablespace data_199903,
13 partition p3 values less than ('1999', '03')
14 tablespace data_199904,
15 partition p4 values less than ('1999','04')
16 tablespace data_199905,
17 partition p5 values less than (maxvalue, maxvalue) ------------> 覿 蠍一 覯企 蟆 企ろ伎れ ロ...覦讌..
18 tablespace data_maxvalues);
企 焔給.
SQL> insert into psales values ('1999', '01', '21', '111', 1111, 12345678);
1 螳 襷れ伎給.
SQL> insert into psales values ('1999', '02', '1', '222', 22222, 12345678);
1 螳 襷れ伎給.
SQL> insert into psales values ('1999', '03', '30', '333', 33333, 12345678);
1 螳 襷れ伎給.
SQL> insert into psales values ('1999', '04', '22', '444', 44444, 12345678);
1 螳 襷れ伎給.
SQL> insert into psales values ('1999', '12', '31', '012', 01212, 45678901);
1 螳 襷れ伎給.
SQL> select table_name, partition_name, partition_position, tablespace_name -----------> 燕 覯 覿 一危 蟆...
2 from user_tab_partitions
3 where table_name = 'PSALES';
TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ -------------------
PSALES P1 1 DATA_199901
PSALES P2 2 DATA_199902
PSALES P3 3 DATA_199903
PSALES P4 4 DATA_199904
PSALES P5 5 DATA_MAXVALUES
覿 8.x 覯 螳煙 覿 貉殊 一危襯 覲蟆渚 給. 蠏碁 8i 覯 覲蟆曙 螳ロ....一危襯 覲蟆渚 alter table 覓語 襾殊 伎 . れ 蠏 .
SQL> alter table psales enable row movement; ---------------> 覿れ 一危襯 覲蟆渚 蟆 企 覲蟆渚.
企 覲蟆暑給.
SQL> update psales
2 set year = '2001'
3 where empno = '012';
1 螳煙給.
SQL> select * from psales;
YEAR MO DD EMP COUNT AMOUNT
---- -- -- --- ---------- ----------
1999 01 21 111 1111 12345678
1999 02 1 222 22222 12345678
1999 03 30 333 33333 12345678
1999 04 22 444 44444 12345678
2001 12 31 012 1212 45678901 ------------> 覿り '1999' '2001'襦 覲蟆暑..
SQL> create table gsales
2 (no number(5),
3 person varchar2(30),
4 amount number(8),
5 week number(2))
6 partition by range (week)
7 (partition p1 values less than (4),
8 partition p2 values less than (8),
9 partition p3 values less than (12));
企 焔給.
SQL> create index gsales_gidx on gsales (week, no)
2 global
3 partition by range (week)
4 (partition g_p1 values less than (8),
5 partition g_p2 values less than (maxvalue));
碁煙り 焔給.
れ 覯覿 螻ろ伎 れ..
- 覿る 豕 16螳蟾讌
- long, long raw 一危 覿蠍磯レ .
- values less than 覿 蠍一螳 螳覲企 螳 ロ蟆 .
- 覿 蠍一 貉殊 螳 values less than maxvalue 企豪.
覿 覯讌語 伎覿...伎覿 る狩伎 伎螻襴讀 伎 る狩伎 朱 伎蟆 覦覯. 覓朱 る狩伎 蠍一 豕企手 覿 蟆. 蠏瑚 れ襦 豕 讌 覈襯企 蟆..願 蟯襴螳 焔レ 譯手鍵 覈磯 牛伎 蟆讌. 伎 螻襴讀 伎 覿 蟆 覃 覿螳 譬 給. 覲危旧 2,4,8, 16, 32.......... 襦 覿 蟆. 伎覿 覓碁 ..覃伎殊 谿瑚蟲. 襯 覲願給.
SQL> create tablespace tbs1
2 datafile 'k:\oracle_tablespace\tbs1.dbf' size 500k;
企 焔給.
SQL> create tablespace tbs2
2 datafile 'k:\oracle_tablespace\tbs2.dbf' size 500k;
企 焔給.
SQL> create tablespace tbs3
2 datafile 'k:\oracle_tablespace\tbs3.dbf' size 500k;
企 焔給.
SQL> create table product1( -----------> 伎覿 覦覯1
2 id number(3),
3 name varchar2(30)
4 )
5 partition by hash(id) partitions 8
6 store in (tbs1, tbs2, tbs3);
企 焔給.
SQL> create table product2( -------------> 伎覿 覦覯2
2 id number(3),
3 name varchar2(30)
4 )
5 partition by hash(id)
6 (partition p1 tablespace tbs1,
7 partition p2 tablespace tbs2,
8 partition p3 tablespace tbs3);
企 焔給.
朱 SQL覓(select, insert, update, delete) 伎 覿 企 れ螻 螳 覿覈 覈 譴.
SQL> select * from psales partition(p5);
YEAR MO DD EMP COUNT AMOUNT
---- -- -- --- ---------- ----------
1999 04 22 444 44444 12345678
2001 12 31 012 1212 45678901
SQL> insert into product2 values (123, 'yasi');
1 螳 襷れ伎給.
SQL> select * from product2 partition(p2);
貊螳 給.
SQL> select * from product2 partition(p1);
貊螳 給.
SQL> select * from product2 partition(p3);
ID NAME
---------- ------------------------------
123 yasi
蟯 襭.
- DBA_PART_TABLES
- DBA_TAB_PARTITIONS
- DBA_PART_KEY_COLUMNS
- DBA_TABLES
- DBA_OBJECTS
- DBA_SEGMENTS
覿 覦覯1 8螳 覿 蟲譟磯ゼ 螳蟆螻, 覿 覦覯2 る狩伎 豕 覿 蟲譟磯ゼ 襷..
4 碁煙 覿 #
覯覿譴...碁煙るゼ 覿 覦覯 伎 危ｴ覲願給. る狩伎 8.0.X 覯覿 4螳讌 譬襯 覿 碁煙るゼ 螻牛. 蟲譟一 豸°願骸 覿 蠍一朱 .
蟲譟一 豸°
- 蠍襦覯 碁煙 : 企螻 碁煙れ 覿蟲譟郁 襦 るジ 碁煙
- 襦貉 碁煙 : 企螻 碁煙れ 覿蟲譟郁 襦 螳 碁煙
覿 蠍一
- 襴曙る 碁煙 : 碁煙るゼ 燕 蠍一朱 貉殊 覿る 碁煙
- -襴曙る 碁煙 : 企螻 碁煙れ 覿蟲譟郁 襦 螳 碁煙
蠏碁 燕 覲願給. 襾殊 碁煙るゼ 企ろ伎るゼ 燕 燕 企 碁煙るゼ 燕蟆給.
SQL> create tablespace idx_t1
2 datafile 'K:\oracle_tablespace\idx_t1.dbf' size 500k;
企 焔給.
SQL> create tablespace idx_t2
2 datafile 'K:\oracle_tablespace\idx_t2.dbf' size 500k;
企 焔給.
SQL> create tablespace idx_t3
2 datafile 'K:\oracle_tablespace\idx_t3.dbf' size 500k;
企 焔給.
SQL> create tablespace idx_t4
2 datafile 'K:\oracle_tablespace\idx_t4.dbf' size 500k;
企 焔給.
SQL> create tablespace idx_t5
2 datafile 'K:\oracle_tablespace\idx_t5.dbf' size 500k;
企 焔給.
SQL> create index idx_psales_year_mon -----------------------> psales 企 碁煙 ...
2 on psales(year, mon)
3 global
4 partition by range (year, mon)
5 (partition idx_p1 values less than ('1999', '02') tablespace idx_t1,
6 partition idx_p2 values less than ('1999', '03') tablespace idx_t2,
7 partition idx_p3 values less than ('1999', '04') tablespace idx_t3,
8 partition idx_p4 values less than ('1999', '05') tablespace idx_t4,
9 partition idx_p5 values less than (maxvalue, maxvalue) tablespace idx_t5);
碁煙り 焔給.
SQL> drop index idx_psales_year_mon;
碁煙り 給.
SQL> create index idx_psales_year_mon
2 on psales(year, mon)
3 local
4 (partition p1 tablespace idx_t1, ------> psales 企 一 覈螻 碁煙 一 企ろ伎れ 覿 蟲譟郁 螳..
5 partition p2 tablespace idx_t2,
6 partition p3 tablespace idx_t3,
7 partition p4 tablespace idx_t4,
8 partition p5 tablespace idx_t5);
碁煙り 焔給.
れ 蠍覯 襴曙 碁煙 覿 覦覯 伎 危ｴ覲願給. 蠍襦覯 襴曙 碁煙る 企 覿蟲譟一 碁煙 覿蟲譟郁 襦 るゴ讌. 碁煙 煙 蠍一 貉殊 覿る 碁煙るゼ 覩誤.
SQL> create table gpsales
2 (no number(5),
3 person varchar2(30),
4 amount number(8),
5 week number(2))
6 partition by range (week)
7 (partition p1 values less than (4),
8 partition p2 values less than (8),
9 partition p3 values less than (12));
企 焔給.
SQL> create index gpsales_idx
2 on gpsales(week, no)
3 global
4 partition by range (week)
5 (partition g_p1 values less than(8),
6 partition g_p2 values less than(maxvalue));
碁煙り 焔給.
れ 襦貉 襴曙 碁煙れ 伎 危ｴ覺. 襦貉 襴曙 碁煙る 企螻 碁煙り 螳 蟲譟磯ゼ 螳讌螻 朱 碁煙 煙 蠍一企 貉殊 覿る 碁煙 覿 覦.
SQL> create table lssales
2 (no number(5),
3 person varchar2(30),
4 amount number(8),
5 week number(2))
6 partition by range (week)
7 (partition p1 values less than (4),
8 partition p2 values less than (8));
企 焔給.
SQL> create index lssales_idx on lssales(week, no)
2 local
3 (partition g_p1 tablespace idx_t1,
4 partition g_p2 tablespace idx_t2);
碁煙り 焔給.
れ 碁煙 襦襷旧...
- 襦貉 襴曙 碁煙
- 蠍覯 襴曙 碁煙
- 貉殊 覿 蠍一 讌 螻, 狩 碁煙 蟆曙
- 蟆一 覓(DSS)
- 襦貉 -襴曙 碁煙
れ 覿 螳豌伎 蟯襴 覦覯 覲願給. 讀, alter table覓語 伎伎 覿 螳豌企ゼ 蟯襴. 覓碁 覃伎殊 谿瑚蟲.
SQL> alter table psales
2 drop partition p1; --------> psales 企 p1 一 ..
企 覲蟆暑給.
SQL> alter index idx_psales_year_mon
2 drop partition p1;
alter index idx_psales_year_mon --------> 磯Μ psales 企 蠍襦覯 碁煙るゼ drop 企...讀, 企 覦 蠍襦覯 碁煙れ襷 蟆..
*
1 る:
ORA-14076: DROP/SPLIT PARTITION LOCAL 碁煙 覿朱 給
SQL> alter table psales
2 rename partition p2 to g2; -------------> 企 覿 企 覲蟆暑逢覯
企 覲蟆暑給.
SQL> alter index idx_psales_year_mon
2 rename partition p2 to g2; ---------------> 碁煙れ 覿 企 覲蟆暑逢覯
碁煙り 覲蟆暑給.
SQL> alter table gsales add partition p4 -------------> 企 襦 覯豢螳
2 values less than (16) tablespace data_199903;
企 覲蟆暑給.
SQL> alter table product1 add partition; ----------> 伎覿 企 襦 覯 豢螳
企 覲蟆暑給.
SQL> alter table product2 add partition p4 tablespace tbs3; ----------> 伎覿 企 襦 覯 豢螳
企 覲蟆暑給.
SQL> alter table psales
2 merge partitions g2, p3 into partition p2_3; -----------> 覿 g2 p3襯 一朱 豎 p2_3襦 襷..
企 覲蟆暑給.
SQL> alter table product1 coalesce partition; -----------> 覿 螳螳 螳企 蟆螳 觜讌 ...讌襦 襷れ 譯朱 覓語..
企 覲蟆暑給.
SQL> alter table gsales
2 modify partition p3 storage (maxextents 50); ---------> 一 覲蟆
企 覲蟆暑給.
SQL> alter table psales
2 modify partition p4 unusable local indexes; ------------> 襦貉 碁煙るゼ 讌 覈詩蟆 ..
企 覲蟆暑給.
################################################################################
# unusable 碁煙る alter session set skip_unusable_indexes = true; 覓語レ #
# ろ伎殊襷 insert, update, delete覓語 ろ 企. #
################################################################################
SQL> alter index idx_psales_year_mon
2 rebuild partition p5 tablespace tbs3; ---------------> 碁煙れ
碁煙り 覲蟆暑給.
SQL> select table_name, partition_name, partition_position, tablespace_name
2 from user_tab_partitions
3 where table_name = 'PSALES';
TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ -------------------
PSALES P4 2 DATA_199904
PSALES P5 3 DATA_MAXVALUES
PSALES P2_3 1 SYSTEM
SQL> alter table sales
2 merge partitions p1, p2 into partition p12;
企 覲蟆暑給.
SQL> alter table sales split partition p12
2 at ('1999', '02') ---------------> 覿蠍一 覲願 覓 郁企..讀, 1999 02 蟾讌 覲企ゼ ロ 1999 02 蠍一朱 蠍磯覓語 ..
3 into (partition p21 tablespace tbs2,
4 partition p22 tablespace tbs3);
alter table sales split partition p12
*
1 る:
ORA-14080: 覿 讌 覦企 磯 伎 給
SQL> alter table sales split partition p12
2 at ('1999', '01')
3 into (partition p21 tablespace tbs2,
4 partition p22 tablespace tbs3);
企 覲蟆暑給.
5 覿伎 譟一 #
覿 伎 譟一碁逢 譟一碁 企 覿 る 覿 襷 曙企れ 蟆 覦 襷. 螳讌 覦 給.
- 豌 覿 伎 譟一 : 譟一誤 企 覈 覿 蟆曙一 譟一
- 覿覿 覿 伎 譟一 : 譟一誤 企 朱螳 覿 螻 朱 覿 譟一 覦覯
SQL> alter session set nls_date_format = 'DD-MON-YYYY';
語 覲蟆暑給.
SQL> create table orders
2 (ordid number,
3 orderdate date,
4 prductid number,
5 quant number)
6 partition by range (orderdate)
7 subpartition by hash(prductid) subpartitions 8
8 store in (tbs1, tbs2, tbs3)
9 (partition q1 values less than ('01-APR-1999'),
10 partition q2 values less than ('02-APR-1999'),
11 partition q3 values less than ('03-APR-1999'),
12 partition q4 values less than ('04-APR-1999'));
create table orders
*
1 る:
ORA-01658: TBS3 企ろ伎れ 瑚係襾狩語 INITIAL 燕 給 --------->企ろ伎 蠍磯ゼ れ ..
SQL> alter tablespace tbs3
2 add datafile 'k:\oracle_tablespace/tbs3_2.dbf' size 500k;
企 覲蟆暑給.
SQL> create table orders
2 (ordid number,
3 orderdate date,
4 prductid number,
5 quant number)
6 partition by range (orderdate)
7 subpartition by hash(prductid) subpartitions 8
8 store in (tbs1, tbs2, tbs3)
9 (partition q1 values less than ('01-APR-1999'),
10 partition q2 values less than ('02-APR-1999'),
11 partition q3 values less than ('03-APR-1999'),
12 partition q4 values less than ('04-APR-1999'));
create table orders
*
1 る:
ORA-01658: TBS2 企ろ伎れ 瑚係襾狩語 INITIAL 燕 給
SQL> alter tablespace tbs2
2 add datafile 'k:\oracle_tablespace/tbs2_2.dbf' size 500k;
企 覲蟆暑給.
SQL> alter tablespace tbs1
2 add datafile 'k:\oracle_tablespace/tbs1_2.dbf' size 500k;
企 覲蟆暑給.
SQL> create table orders
2 (ordid number,
3 orderdate date,
4 prductid number,
5 quant number)
6 partition by range (orderdate)
7 subpartition by hash(prductid) subpartitions 8
8 store in (tbs1, tbs2, tbs3)
9 (partition q1 values less than ('01-APR-1999'),
10 partition q2 values less than ('02-APR-1999'),
11 partition q3 values less than ('03-APR-1999'),
12 partition q4 values less than ('04-APR-1999'));
create table orders
*
1 る:
ORA-01658: TBS2 企ろ伎れ 瑚係襾狩語 INITIAL 燕 給
SQL> alter tablespace tbs2
2 add datafile 'k:\oracle_tablespace/tbs2_3.dbf' size 1M;
企 覲蟆暑給.
SQL> create table orders
2 (ordid number,
3 orderdate date,
4 prductid number,
5 quant number)
6 partition by range (orderdate)
7 subpartition by hash(prductid) subpartitions 8
8 store in (tbs1, tbs2, tbs3)
9 (partition q1 values less than ('01-APR-1999'),
10 partition q2 values less than ('02-APR-1999'),
11 partition q3 values less than ('03-APR-1999'),
12 partition q4 values less than ('04-APR-1999'));
create table orders
*
1 る:
ORA-01658: TBS1 企ろ伎れ 瑚係襾狩語 INITIAL 燕 給 ---------------> ＜; 讌讀...
SQL> alter tablespace tbs1
2 add datafile 'k:\oracle_tablespace/tbs1_3.dbf' size 1M;
企 覲蟆暑給.
SQL> create table orders
2 (ordid number,
3 orderdate date,
4 prductid number,
5 quant number)
6 partition by range (orderdate)
7 subpartition by hash(prductid) subpartitions 8
8 store in (tbs1, tbs2, tbs3)
9 (partition q1 values less than ('01-APR-1999'),
10 partition q2 values less than ('02-APR-1999'),
11 partition q3 values less than ('03-APR-1999'),
12 partition q4 values less than ('04-APR-1999'));
create table orders
*
1 る:
ORA-01658: TBS3 企ろ伎れ 瑚係襾狩語 INITIAL 燕 給
SQL> alter tablespace tbs3
2 add datafile 'k:\oracle_tablespace/tbs3_3.dbf' size 1M;
企 覲蟆暑給.
SQL> create table orders
2 (ordid number,
3 orderdate date,
4 productid number,
5 quant number)
6 partition by range (orderdate)
7 subpartition by hash(productid) subpartitions 8
8 store in (tbs1, tbs2, tbs3)
9 (partition q1 values less than ('01-APR-1999'),
10 partition q2 values less than ('02-APR-1999'),
11 partition q3 values less than ('03-APR-1999'),
12 partition q4 values less than ('04-APR-1999'));
企 焔給.
SQL> create table product
2 (productid number,
3 name varchar2(60))
4 partition by hash (productid) partitions 8
5 store in (tbs1, tbs2, tbs3);
企 焔給.
SQL> @E:\oracle\ora90\rdbms\admin\utlxplan.sql
企 焔給.
SQL> set autotrace on
SQL> select * from orders o, product p
2 where o.productid = p.productid; ----------------> 豌 覿 伎 譟一...
貊螳 給.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=93)
1 0 PARTITION HASH (ALL) -----------> 豌企ゼ 詩 ALL
2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=93)
3 2 PARTITION RANGE (ALL)
4 3 TABLE ACCESS (FULL) OF 'ORDERS' (Cost=1 Card=1 Bytes=48)
5 2 TABLE ACCESS (FULL) OF 'PRODUCT' (Cost=1 Card=328 Bytes=14760)
ろ 螻る 覃.
- orders企 full れ.(覓朱 覿 覯.)
- product企 full れ.(覓朱 蠍磯 轟壱 覿 覯 .)
- 譟一誤.
Statistics
----------------------------------------------------------
122 recursive calls
96 db block gets
12 consistent gets
0 physical reads
0 redo size
501 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
7 覲牛覿 #
覲牛覿 危ｴ覺る 覯覿螻 伎覿 蟆壱 襯 襷. 2螳讌 覿覦覯 レ 覈 襷れ 螻殊企朱 譬 企給れ..~
覦襦 襦 れ願讌.
襾殊 覲 覯覿 螻 蠏 覯覿 伎 れ 伎覿 .
SQL> alter session set nls_date_format = 'dd-mon-yyyy';
語 覲蟆暑給.
SQL> create table orders
2 (ordid number,
3 orderdate date,
4 productid number,
5 quant number)
6 partition by range (orderdate)
7 subpartition by hash(productid) subpartitions 4
8 store in (tbs1, tbs2, tbs3)
9 (partition q1 values less than ('01-APR-1999'),
10 partition q2 values less than ('02-APR-1999'),
11 partition q3 values less than ('03-APR-1999'),
12 partition q4 values less than ('04-APR-1999'));
企 焔給.
SQL> select table_name,partition_name,subpartition_count, partition_position, tablespace_name
2 from user_tab_partitions
3 where table_name = 'ORDERS';
TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------ ---------------
ORDERS Q1 4 1 USERS
ORDERS Q2 4 2 USERS
ORDERS Q3 4 3 USERS
ORDERS Q4 4 4 USERS
SQL> select rownum,partition_name, segment_type, tablespace_name
2 from dba_segments
3 where owner = 'YASI' and segment_name = 'ORDERS';
ROWNUM PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
---------- ------------------------------ ------------------ ------------------------------
1 SYS_SUBP369 TABLE SUBPARTITION TBS1
2 SYS_SUBP370 TABLE SUBPARTITION TBS2
3 SYS_SUBP371 TABLE SUBPARTITION TBS3
4 SYS_SUBP372 TABLE SUBPARTITION TBS1
5 SYS_SUBP373 TABLE SUBPARTITION TBS1
6 SYS_SUBP374 TABLE SUBPARTITION TBS2
7 SYS_SUBP375 TABLE SUBPARTITION TBS3
8 SYS_SUBP376 TABLE SUBPARTITION TBS1
9 SYS_SUBP377 TABLE SUBPARTITION TBS1
10 SYS_SUBP378 TABLE SUBPARTITION TBS2
11 SYS_SUBP379 TABLE SUBPARTITION TBS3
12 SYS_SUBP380 TABLE SUBPARTITION TBS1
13 SYS_SUBP381 TABLE SUBPARTITION TBS1
14 SYS_SUBP382 TABLE SUBPARTITION TBS2
15 SYS_SUBP383 TABLE SUBPARTITION TBS3
16 SYS_SUBP384 TABLE SUBPARTITION TBS1
16 螳 給.
讀, 覯覿 蠍一 覈磯 企 覿 蠍磯 ロ る狩伎 伎 螻襴讀 譟危伎 螳螳 企ろ伎れ ロる 詞. 蠏碁磯 豈 襾殊 覯覿 4螳 燕 企ろ伎る tbs1, tbs2, tbs3 企蟆 3螳襯 . 豐12螳襦 企 覿 給る手 . 襦 危願 螳 覿覿. 螳 覲願鍵 一危 譟壱 覲 蟆郁骸 20螳襦 覿蟇磯. 螳 一危 覈 覿 ??? 覈郁企. 蟲.. 譬 覲旧″ 覲牛覿 企.
SQL> alter session set nls_date_format = 'dd-mon-yyyy';
語 覲蟆暑給.
SQL> create table order3
2 (ordid number,
3 orderdate date,
4 productid number,
5 quantity number)
6 partition by range(orderdate)
7 subpartition by hash(productid) subpartitions 8
8 store in (tbs1, tbs2, tbs3)
9 (partition q1 values less than ('01-JAN-1999')
10 (subpartition q1_1 tablespace tbs1,
11 subpartition q1_2 tablespace tbs2,
12 subpartition q1_3 tablespace tbs3),
13 partition q2 values less than ('01-FEB-1999')
14 (subpartition q2_1 tablespace tbs1,
15 subpartition q2_2 tablespace tbs2,
16 subpartition q2_3 tablespace tbs3),
17 partition q3 values less than ('01-MAR-1999')
18 (subpartition q3_1 tablespace tbs1,
19 subpartition q3_2 tablespace tbs2,
20 subpartition q3_3 tablespace tbs3),
21 partition q4 values less than (maxvalue)
22 );
企 焔給.
SQL> select table_name,partition_name,subpartition_count, partition_position, tablespace_name
2 from user_tab_partitions
3 where table_name = 'ORDER3';
TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------ ---------------
ORDER3 Q1 3 1 USERS
ORDER3 Q2 3 2 USERS
ORDER3 Q3 3 3 USERS
ORDER3 Q4 8 4 USERS
4 螳 給.
SQL> select rownum,partition_name, segment_type, tablespace_name
2 from dba_segments
3 where owner = 'YASI' and segment_name = 'ORDER3';
ROWNUM PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
---------- ------------------------------ ------------------ ------------------------------
1 Q1_2 TABLE SUBPARTITION TBS2
2 Q1_3 TABLE SUBPARTITION TBS3
3 Q2_1 TABLE SUBPARTITION TBS1
4 Q2_2 TABLE SUBPARTITION TBS2
5 Q2_3 TABLE SUBPARTITION TBS3
6 Q3_1 TABLE SUBPARTITION TBS1
7 Q3_2 TABLE SUBPARTITION TBS2
8 Q3_3 TABLE SUBPARTITION TBS3
9 SYS_SUBP297 TABLE SUBPARTITION TBS1
10 SYS_SUBP298 TABLE SUBPARTITION TBS2
11 SYS_SUBP299 TABLE SUBPARTITION TBS3
12 SYS_SUBP300 TABLE SUBPARTITION TBS1
13 SYS_SUBP301 TABLE SUBPARTITION TBS2
14 SYS_SUBP302 TABLE SUBPARTITION TBS3
15 SYS_SUBP303 TABLE SUBPARTITION TBS1
16 SYS_SUBP304 TABLE SUBPARTITION TBS2
17 Q1_1 TABLE SUBPARTITION TBS1
17 螳 給.
SQL> select table_name, partition_name, subpartition_name, tablespace_name
2 from dba_tab_subpartitions
3 where table_owner = 'YASI';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ -------
ORDER3 Q1 Q1_1 TBS1
ORDER3 Q1 Q1_2 TBS2
ORDER3 Q1 Q1_3 TBS3
ORDER3 Q2 Q2_1 TBS1
ORDER3 Q2 Q2_2 TBS2
ORDER3 Q2 Q2_3 TBS3
ORDER3 Q3 Q3_1 TBS1
ORDER3 Q3 Q3_2 TBS2
ORDER3 Q3 Q3_3 TBS3
ORDER3 Q4 SYS_SUBP393 TBS1
ORDER3 Q4 SYS_SUBP394 TBS2
ORDER3 Q4 SYS_SUBP395 TBS3
ORDER3 Q4 SYS_SUBP396 TBS1
ORDER3 Q4 SYS_SUBP397 TBS2
ORDER3 Q4 SYS_SUBP398 TBS3
ORDER3 Q4 SYS_SUBP399 TBS1
ORDER3 Q4 SYS_SUBP400 TBS2
17 螳 給.
豈 螳 3螳 覯 覿螻 8螳 伎覿襦 襾殊 覿螻 q2, q3 れ 3螳 伎 覿襦 3 * 8 + 3 + 3 = 30螳 覿 焔り るれ. 伎. 螳 一危 譟壱 企蓋蟆郁骸 yasi 襾殊 users 企ろ伎るゼ 4螳襦 覯覿 螻 給.
蠏碁Μ螻 襾語 觚一朱 17螳螳 給. 蠏碁蟾 21螳襦 覿伎 蟆伎. users 企ろ伎る 覈讌 譟壱螳 り規 伎蟆 螳れ 覈襯願讌襷 願 轟壱 蟆. 螳 yasi 蠍磯蓋 企ろ伎るゼ users襦 れ蟇磯. り襴暑. 豈 朱Μ朱 る 企朱 覈郁 螻磯 企至 蟆語 覈郁企. 螳 襯 譟郁 伎 企蓋 蟆郁骸.
SQL> create tablespace data1
2 datafile 'k:\oracle_tablespace\data1_1.dbf' size 5M;
企 焔給.
SQL> create tablespace data2
2 datafile 'k:\oracle_tablespace\data2_1.dbf' size 5M;
企 焔給.
SQL> create tablespace data3
2 datafile 'k:\oracle_tablespace\data3_1.dbf' size 5M;
企 焔給.
SQL> create tablespace data4
2 datafile 'k:\oracle_tablespace\dat4_1.dbf' size 5M;
企 焔給.
SQL> create table order2
2 (ordid number,
3 orderdate date,
4 productid number,
5 quantity number)
6 partition by range(orderdate)
7 subpartition by hash(productid) subpartitions 8
8 store in (tbs1, tbs2, tbs3)
9 (partition q1 values less than ('01-JAN-1999') tablespace data1
10 (subpartition q1_1 tablespace tbs1,
11 subpartition q1_2 tablespace tbs2,
12 subpartition q1_3 tablespace tbs3),
13 partition q2 values less than ('01-FEB-1999') tablespace data2
14 (subpartition q2_1 tablespace tbs1,
15 subpartition q2_2 tablespace tbs2,
16 subpartition q2_3 tablespace tbs3),
17 partition q3 values less than ('01-MAR-1999') tablespace data3
18 (subpartition q3_1 tablespace tbs1,
19 subpartition q3_2 tablespace tbs2,
20 subpartition q3_3 tablespace tbs3),
21 partition q4 values less than (maxvalue) tablespace data4
22 );
企 焔給.
SQL> select table_name,partition_name,subpartition_count, partition_position, tablespace_name
2 from user_tab_partitions
3 where table_name = 'ORDER2';
TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------ ---------------
ORDER2 Q1 3 1 DATA1
ORDER2 Q2 3 2 DATA2
ORDER2 Q3 3 3 DATA3
ORDER2 Q4 8 4 DATA4
4 螳 給.
SQL> select rownum,partition_name, segment_type, tablespace_name
2 from dba_segments
3 where owner = 'YASI' and segment_name = 'ORDER2';
ROWNUM PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
---------- ------------------------------ ------------------ ------------------------------
1 Q1_1 TABLE SUBPARTITION TBS1
2 Q1_2 TABLE SUBPARTITION TBS2
3 Q1_3 TABLE SUBPARTITION TBS3
4 Q2_1 TABLE SUBPARTITION TBS1
5 Q2_2 TABLE SUBPARTITION TBS2
6 Q2_3 TABLE SUBPARTITION TBS3
7 Q3_1 TABLE SUBPARTITION TBS1
8 Q3_2 TABLE SUBPARTITION TBS2
9 Q3_3 TABLE SUBPARTITION TBS3
10 SYS_SUBP289 TABLE SUBPARTITION DATA4
11 SYS_SUBP290 TABLE SUBPARTITION DATA4
12 SYS_SUBP291 TABLE SUBPARTITION DATA4
13 SYS_SUBP292 TABLE SUBPARTITION DATA4
14 SYS_SUBP293 TABLE SUBPARTITION DATA4
15 SYS_SUBP294 TABLE SUBPARTITION DATA4
16 SYS_SUBP295 TABLE SUBPARTITION DATA4
17 SYS_SUBP296 TABLE SUBPARTITION DATA4
17 螳 給.
蠏碁 17 + 4 = 21 螳 覿瑚朱 覲伎. 一 蟆語... ＜; 煙 蟲 . 轟 覓語襯 覲伎 覿 譴 螻螻蟇磯 覃 蟆 覿 蟆 譬 れ殊覃 螳蟆給. 覓企 蟆 覃殊 れ 蟆企. 蟯 襭 れ螻 螳給.
* DBA_TAB_SUBPARTITIONS
* DBA_IND_SUBPARTITIONS
* DBA_SUBPART_KEY_COLUMNS
* DBA_TAB_PARTITIONS
* DBA_IND_PARTITIONS
* DBA_PART_TABLES
* DBA_PART_INDEXES
* DBA_OBJECTS
* DBA_SEGMENTS
れ 覲牛覿 覦覯.
SQL> insert into order3 subpartition(q2_3)
2 values (1, '12-JAN-1999', 143, 12000);
1 螳 襷れ伎給.
SQL> select * from order3 subpartition(q2_3);
ORDID ORDERDATE PRODUCTID QUANTITY
---------- ----------- ---------- ----------
1 12-jan-1999 143 12000
SQL> update order3 subpartition(q2_3)
2 set quantity = 13000;
1 螳煙給.
SQL> delete order3
2 subpartition(q2_3);
1 給.
8 L0B 螳讌 貉殊 覿 #
LOB 螳讌 企 覿 曙給.
SQL> create table ord_lob
2 (orderdate date,
3 pic BLOB)
4 LOB(pic) store as (tablespace data1) --------------> LOB 磯 覿伎 ロ..
5 partition by range(orderdate)
6 (partition p1 values less than ('01-JAN-1999')
7 tablespace data2,
8 partition p2 values less than ('01-FEB-1999')
9 tablespace data3);
企 焔給.
SQL> alter table ord_lob ---------------> 襦 一 豢螳..
2 add partition p3
3 values less than (maxvalue)
4 lob (pic) store as (tablespace tbs3);
企 覲蟆暑給.
SQL> alter table ord_lob merge partitions p1, p2 into partition p5 -----------> 一 p1螻 一 p2襯 豺 蟆..
2 lob(pic) store as (tablespace tbs3);
企 覲蟆暑給.
~ 伎襦 覿 襷豺手 . る狩伎 覿 蠍磯レ /豢レ 譴譯朱 覃(?) る狩伎 覃豺企讀. 覿蠍磯レ 一危危一 覓伎 り . 覓朱 螳煙, , レ 蟇一 譟壱覓伎願鍵 覓語企手 讌襷 るジ 一危覯伎れ る 覓企 觜襯 焔レ 殊 覃豺企讀企手 螳. 覿譟燕 覿覿 蟆. 覿譟燕 蟆れ れ 豪語 豈, 覃伎殊 谿瑚蠍 覦.
覲願 螳 螻襷給. -- 螻給譴 2011-05-01 11:38:25