_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › 覿蠍磯

Contents

1
2 覿
3 伎覿
4 碁煙 覿
5 覿伎 譟一
6 覿 螳豌伎 旧ろ/
7 覲牛覿
8 L0B 螳讌 貉殊 覿


1 #

る狩 8 覯伎 覿蠍磯レ 螻牛. 覿蠍磯レ 企 碁煙るゼ 螳 企ろ伎れ ロ 蠍磯レ. 覿 レ 濠啓 焔レ 給. 企ろ伎る 覿 ロ蟆 覃 蠏碁 豌襴 覯螳 譴 り鍵 覓語 焔レ 手 蟆 轟壱. 企ろ伎れ 一危 朱れ ろ襦 覿磯 る 譬蟆讌. 豈 1000伎 讌襴 A朱 蟆 谿城 蟆螻 1000伎譴 500伎襦 覦 伎 1~500伎譴 A襯 谿場 蟆 殊 螻朱ゼ 螳讌讌. 覓朱 A朱 蟆 1伎 る 1000伎 500伎 螳蟆讌襷 願 朱 蟆曙一. 豌襴 覯襯 譴譴る 蟆 譯 譴 覃貉る讀伎. 豌 覿蠍磯レ 螻 豌 螳 襾轟企. 譯 覃讌 蠍磯レ伎.

覿螳朱 覿 蟆覃 覓語 覦 觜伎 譬給. 企ろ伎れ 覿 ロ 轟 企ろ伎り 覓語螳 蠍磯 覓語螳 覦 企ろ伎れ 覲企 螻 襾語 る 蟆. 伎 蠍磯覓語 蟯襴螳 蟯襴蠍磯 れ 所讌. 覿 豕 64000螳 蟾讌 覿 朱, 覿 企 伎 碁煙るゼ 燕 碁煙る 磯 覿 給. , 覿 企 觜碁У 碁煙る 覿 給. 企 貉れ覃 碁煙れ 蠍磯 貉れ蠍 覓語 覿 レ 覓企 襷給.

覿 蟆 覿螻 覲牛覿襦 . 覿 れ 覯覿螻 伎覿襦 . 襾殊 覿覿 覲企襦 蟆給... 覓碁 覃伎殊..^^;

2 覿 #

覦襦 れ旧 れ願襦 讌. 襾殊 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  -------------> 蠍一 襷讌  蟆 覈  企ろ伎れ  k..
  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 企豪.


3 伎覿 #

覿 覯讌語 伎覿...伎覿 る狩伎 伎螻襴讀 伎 る狩伎 朱 伎蟆 覦覯. 覓朱 る狩伎 蠍一 豕企手 覿 蟆. 蠏瑚 れ襦 豕 讌 覈襯企 蟆..願 蟯襴螳 焔レ 譯手鍵 覈磯 牛伎 蟆讌. 伎 螻襴讀 伎 覿 蟆 覃 覿螳 譬 給. 覲危旧 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)
  • 襦貉 -襴曙 碁煙
    • 蟆 ろ螳 襷れ 譴
    • OLTP覓伎

れ 覿 螳豌伎 蟯襴 覦覯 覲願給. 讀, 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)

ろ 螻る 覃.

  1. orders企 full れ.(覓朱 覿 覯.)
  2. product企 full れ.(覓朱 蠍磯 轟壱 覿 覯 .)
  3. 譟一誤.

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

6 覿 螳豌伎 旧ろ/ #


exp 覈/れ table = (企覈:覿覈)
imp 覈/れ table = (企覈:覿覈)


()
exp system/manager tables=(emp, psales:p1, psales:p2)
imp system/manager file=expdat.dmp tables=(psales:p1, psales:p2)

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
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:52

讌ろ 螳豢 殊 覲企 一企 覓伎 覦蟆 譴 . 豺谿企 危伎. 蠏朱蓋朱 蟲 螻 襯. 蟲郁襯 覓企Μ 豺谿 企 讌豺讌 給. 螻 襴れ 覦蟆 蠍磯ゴ. 覲伎譴 譯 給ゃ (豺朱Υ讌觚)