oracle琛ㄥ垎鍖鸿?瑙?杞_互联网

oracle琛ㄥ垎鍖鸿?瑙?杞?

姝ゆ枃浠庝互涓嬪嚑涓?柟闈㈡潵鏁寸悊鍏充簬鍒嗗尯琛ㄧ殑姒傚康鍙婃搷浣?

聽聽聽聽聽聽聽 1.琛ㄧ┖闂村強鍒嗗尯琛ㄧ殑姒傚康

聽聽聽聽聽聽聽 2.琛ㄥ垎鍖虹殑鍏蜂綋浣滅敤

聽聽聽聽聽聽聽 3.琛ㄥ垎鍖虹殑浼樼己鐐?/div>

聽聽聽聽聽聽聽 4.琛ㄥ垎鍖虹殑鍑犵?绫诲瀷鍙婃搷浣滄柟娉?/div>

聽聽聽聽聽聽聽 5.瀵硅〃鍒嗗尯鐨勭淮鎶ゆ?ф搷浣?

(1.) 琛ㄧ┖闂村強鍒嗗尯琛ㄧ殑姒傚康

琛ㄧ┖闂达細
銆?銆?鏄?竴涓?垨澶氫釜鏁版嵁鏂囦欢鐨勯泦鍚堬紝鎵?鏈夌殑鏁版嵁瀵硅薄閮藉瓨鏀惧湪鎸囧畾鐨勮〃绌洪棿涓?紝浣嗕富瑕佸瓨鏀剧殑鏄?〃锛?鎵?浠ョО浣滆〃绌洪棿銆?/div>

聽

鍒嗗尯琛?細聽
褰撹〃涓?殑鏁版嵁閲忎笉鏂??澶э紝鏌ヨ?鏁版嵁鐨勯?熷害灏变細鍙樻參锛屽簲鐢ㄧ▼搴忕殑鎬ц兘灏变細涓嬮檷锛岃繖鏃跺氨搴旇?鑰冭檻瀵硅〃杩涜?鍒嗗尯銆傝〃杩涜?鍒嗗尯鍚庯紝閫昏緫涓婅〃浠嶇劧鏄?竴寮犲畬鏁寸殑琛?紝鍙?槸灏嗚〃涓?殑鏁版嵁鍦ㄧ墿鐞嗕笂瀛樻斁鍒板?涓?〃绌洪棿(鐗╃悊鏂囦欢涓?锛岃繖鏍锋煡璇㈡暟鎹?椂锛屼笉鑷充簬姣忔?閮芥壂鎻忔暣寮犺〃銆?/div>

聽

(聽2).琛ㄥ垎鍖虹殑鍏蜂綋浣滅敤

Oracle鐨勮〃鍒嗗尯鍔熻兘閫氳繃鏀瑰杽鍙??鐞嗘?с?佹?ц兘鍜屽彲鐢ㄦ?э紝浠庤?屼负鍚勫紡搴旂敤绋嬪簭甯︽潵浜嗘瀬澶х殑濂藉?銆傞?氬父锛屽垎鍖哄彲浠ヤ娇鏌愪簺鏌ヨ?浠ュ強缁存姢鎿嶄綔鐨勬?ц兘澶уぇ鎻愰珮銆傛?澶?鍒嗗尯杩樺彲浠ユ瀬澶х畝鍖栧父瑙佺殑绠＄悊浠诲姟锛屽垎鍖烘槸鏋勫缓鍗冨厗瀛楄妭鏁版嵁绯荤粺鎴栬秴楂樺彲鐢ㄦ?х郴缁熺殑鍏抽敭宸ュ叿銆?/div>

聽

鍒嗗尯鍔熻兘鑳藉?灏嗚〃銆佺储寮曟垨绱㈠紩缁勭粐琛ㄨ繘涓?姝ョ粏鍒嗕负娈碉紝杩欎簺鏁版嵁搴撳?璞＄殑娈靛彨鍋氬垎鍖恒?傛瘡涓?垎鍖烘湁鑷?繁鐨勫悕绉帮紝杩樺彲浠ラ?夋嫨鑷?繁鐨勫瓨鍌ㄧ壒鎬с?備粠鏁版嵁搴撶?鐞嗗憳鐨勮?搴︽潵鐪嬶紝涓?涓?垎鍖哄悗鐨勫?璞″叿鏈夊?涓??锛岃繖浜涙?鏃㈠彲杩涜?闆嗕綋绠＄悊锛屼篃鍙?崟鐙??鐞嗭紝杩欏氨浣挎暟鎹?簱绠＄悊鍛樺湪绠＄悊鍒嗗尯鍚庣殑瀵硅薄鏃舵湁鐩稿綋澶х殑鐏垫椿鎬с?備絾鏄?紝浠庡簲鐢ㄧ▼搴忕殑瑙掑害鏉ョ湅锛屽垎鍖哄悗鐨勮〃涓庨潪鍒嗗尯琛ㄥ畬鍏ㄧ浉鍚岋紝浣跨敤 SQL DML 鍛戒护璁块棶鍒嗗尯鍚庣殑琛ㄦ椂锛屾棤闇?浠讳綍淇?敼銆?/div>

聽

浠?涔堟椂鍊欎娇鐢ㄥ垎鍖鸿〃锛?br style="margin: 0px; padding: 0px;">1銆佽〃鐨勫ぇ灏忚秴杩?GB銆?/div>

2銆佽〃涓?寘鍚?巻鍙叉暟鎹?紝鏂扮殑鏁版嵁琚??鍔犻兘鏂扮殑鍒嗗尯涓???/div>

聽

聽(3).琛ㄥ垎鍖虹殑浼樼己鐐?/div>

琛ㄥ垎鍖烘湁浠ヤ笅浼樼偣锛?span class="Apple-converted-space">聽
1銆佹敼鍠勬煡璇㈡?ц兘锛氬?鍒嗗尯瀵硅薄鐨勬煡璇㈠彲浠ヤ粎鎼滅储鑷?繁鍏冲績鐨勫垎鍖猴紝鎻愰珮妫?绱㈤?熷害銆?/div>

2銆佸?寮哄彲鐢ㄦ?э細濡傛灉琛ㄧ殑鏌愪釜鍒嗗尯鍑虹幇鏁呴殰锛岃〃鍦ㄥ叾浠栧垎鍖虹殑鏁版嵁浠嶇劧鍙?敤锛?/div>

3銆佺淮鎶ゆ柟渚匡細濡傛灉琛ㄧ殑鏌愪釜鍒嗗尯鍑虹幇鏁呴殰锛岄渶瑕佷慨澶嶆暟鎹?紝鍙?慨澶嶈?鍒嗗尯鍗冲彲锛?/div>

4銆佸潎琛?/O锛氬彲浠ユ妸涓嶅悓鐨勫垎鍖烘槧灏勫埌纾佺洏浠ュ钩琛?/O锛屾敼鍠勬暣涓?郴缁熸?ц兘銆?/div>

聽

缂虹偣锛?span class="Apple-converted-space">聽
鍒嗗尯琛ㄧ浉鍏筹細宸茬粡瀛樺湪鐨勮〃娌℃湁鏂规硶鍙?互鐩存帴杞?寲涓哄垎鍖鸿〃銆備笉杩?Oracle 鎻愪緵浜嗗湪绾块噸瀹氫箟琛ㄧ殑鍔熻兘銆?/div>

聽

(4).琛ㄥ垎鍖虹殑鍑犵?绫诲瀷鍙婃搷浣滄柟娉?/div>

聽涓?.鑼冨洿鍒嗗尯锛?br style="margin: 0px; padding: 0px;">鑼冨洿鍒嗗尯灏嗘暟鎹?熀浜庤寖鍥存槧灏勫埌姣忎竴涓?垎鍖猴紝杩欎釜鑼冨洿鏄?綘鍦ㄥ垱寤哄垎鍖烘椂鎸囧畾鐨勫垎鍖洪敭鍐冲畾鐨勩?傝繖绉嶅垎鍖烘柟寮忔槸鏈?涓哄父鐢ㄧ殑锛屽苟涓斿垎鍖洪敭缁忓父閲囩敤鏃ユ湡銆備妇涓?緥瀛愶細浣犲彲鑳戒細灏嗛攢鍞?暟鎹?寜鐓ф湀浠借繘琛屽垎鍖恒??/div>

褰撲娇鐢ㄨ寖鍥村垎鍖烘椂锛岃?鑰冭檻浠ヤ笅鍑犱釜瑙勫垯锛?/div>

1銆佹瘡涓?涓?垎鍖洪兘蹇呴』鏈変竴涓猇ALUES LESS THEN瀛愬彞锛屽畠鎸囧畾浜嗕竴涓?笉鍖呮嫭鍦ㄨ?鍒嗗尯涓?殑涓婇檺鍊笺?傚垎鍖洪敭鐨勪换浣曞?肩瓑浜庢垨鑰呭ぇ浜庤繖涓?笂闄愬?肩殑璁板綍閮戒細琚?姞鍏ュ埌涓嬩竴涓?珮涓?浜涚殑鍒嗗尯涓???/div>

2銆佹墍鏈夊垎鍖猴紝闄や簡绗?竴涓?紝閮戒細鏈変竴涓?殣寮忕殑涓嬮檺鍊硷紝杩欎釜鍊煎氨鏄??鍒嗗尯鐨勫墠涓?涓?垎鍖虹殑涓婇檺鍊笺??/div>

3銆佸湪鏈?楂樼殑鍒嗗尯涓?紝MAXVALUE琚?畾涔夈?侻AXVALUE浠ｈ〃浜嗕竴涓?笉纭?畾鐨勫?笺?傝繖涓??奸珮浜庡叾瀹冨垎鍖轰腑鐨勪换浣曞垎鍖洪敭鐨勫?硷紝涔熷彲浠ョ悊瑙ｄ负楂樹簬浠讳綍鍒嗗尯涓?寚瀹氱殑VALUE LESS THEN鐨勫?硷紝鍚屾椂鍖呮嫭绌哄?笺??/div>

渚嬩竴锛?/div>

鍋囪?鏈変竴涓狢USTOMER琛?紝琛ㄤ腑鏈夋暟鎹?00000琛岋紝鎴戜滑灏嗘?琛ㄩ?氳繃CUSTOMER_ID杩涜?鍒嗗尯锛屾瘡涓?垎鍖哄瓨鍌?00000琛岋紝鎴戜滑灏嗘瘡涓?垎鍖轰繚瀛樺埌鍗曠嫭鐨勮〃绌洪棿涓?紝杩欐牱鏁版嵁鏂囦欢灏卞彲浠ヨ法瓒婂?涓?墿鐞嗙?鐩樸?備笅闈㈡槸鍒涘缓琛ㄥ拰鍒嗗尯鐨勪唬鐮侊紝濡備笅锛?/div>

CREATE TABLE CUSTOMER聽
(聽
聽聽聽 CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,聽
聽聽聽 FIRST_NAME聽 VARCHAR2(30) NOT NULL,聽
聽聽聽 LAST_NAME聽聽 VARCHAR2(30) NOT NULL,聽
聽聽聽 PHONE聽聽聽聽聽聽聽 VARCHAR2(15) NOT NULL,聽
聽聽聽 EMAIL聽聽聽聽聽聽聽 VARCHAR2(80),聽
聽聽聽 STATUS聽聽聽聽聽聽 CHAR(1)聽
)聽
PARTITION BY RANGE (CUSTOMER_ID)聽
(聽
聽聽聽 PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01,聽
聽聽聽 PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02聽
)

渚嬩簩锛氭寜鏃堕棿鍒掑垎

CREATE TABLE ORDER_ACTIVITIES聽
(聽
聽聽聽 ORDER_ID聽聽聽聽聽 NUMBER(7) NOT NULL,聽
聽聽聽 ORDER_DATE聽聽聽 DATE,聽
聽聽聽 TOTAL_AMOUNT NUMBER,聽
聽聽聽 CUSTOTMER_ID NUMBER(7),聽
聽聽聽 PAID聽聽聽聽聽聽聽聽聽聽 CHAR(1)聽
)聽
PARTITION BY RANGE (ORDER_DATE)聽
(

聽 PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01- MAY -2003','DD-MON-YYYY')) TABLESPACEORD_TS01,

聽 PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02,

聽 PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03

)

渚嬩笁锛歁AXVALUE

CREATE TABLE RangeTable
(聽
聽 idd聽聽 INT PRIMARY KEY ,聽
聽 iNAME VARCHAR(10),聽
聽 grade INT聽聽
)聽
PARTITION聽 BY聽 RANGE (grade)聽
(聽
聽聽聽聽聽 PARTITION聽 part1 VALUES聽 LESS聽 THEN (1000) TABLESPACE聽 Part1_tb,聽
聽聽聽聽聽 PARTITION聽 part2 VALUES聽 LESS聽 THEN (MAXVALUE) TABLESPACE聽 Part2_tb聽
);

聽

浜?鍒楄〃鍒嗗尯锛?/div>

璇ュ垎鍖虹殑鐗圭偣鏄?煇鍒楃殑鍊煎彧鏈夊嚑涓?紝鍩轰簬杩欐牱鐨勭壒鐐规垜浠?彲浠ラ噰鐢ㄥ垪琛ㄥ垎鍖恒??/div>

渚嬩竴

CREATE TABLE PROBLEM_TICKETS聽
(聽
聽聽聽 PROBLEM_ID聽聽 NUMBER(7) NOT NULL PRIMARY KEY,聽
聽聽聽 DESCRIPTION聽 VARCHAR2(2000),聽
聽聽聽 CUSTOMER_ID聽 NUMBER(7) NOT NULL,聽
聽聽聽 DATE_ENTERED DATE NOT NULL,聽
聽聽聽 STATUS聽聽聽聽聽聽 VARCHAR2(20)聽
)聽
PARTITION BY LIST (STATUS)聽
(聽
聽聽聽聽聽 PARTITION PROB_ACTIVE聽聽 VALUES ('ACTIVE') TABLESPACE PROB_TS01,聽
聽聽聽聽聽 PARTITION PROB_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02

渚嬩簩

CREATE聽 TABLE聽 ListTable
(聽
聽聽聽 id聽聽聽 INT聽 PRIMARY聽 KEY ,聽
聽聽聽 name聽 VARCHAR (20),聽
聽聽聽 area聽 VARCHAR (10)聽
)聽
PARTITION聽 BY聽 LIST (area)聽
(聽
聽聽聽 PARTITION聽 part1 VALUES ('guangdong','beijing') TABLESPACE聽 Part1_tb,聽
聽聽聽 PARTITION聽 part2 VALUES ('shanghai','nanjing')聽 TABLESPACE聽 Part2_tb聽
);

)

聽

涓?鏁ｅ垪鍒嗗尯锛?br style="margin: 0px; padding: 0px;">杩欑被鍒嗗尯鏄?湪鍒楀?间笂浣跨敤鏁ｅ垪绠楁硶锛屼互纭?畾灏嗚?鏀惧叆鍝?釜鍒嗗尯涓??傚綋鍒楃殑鍊兼病鏈夊悎閫傜殑鏉′欢鏃讹紝寤鸿?浣跨敤鏁ｅ垪鍒嗗尯銆?/div>

鏁ｅ垪鍒嗗尯涓洪?氳繃鎸囧畾鍒嗗尯缂栧彿鏉ュ潎鍖?鍒嗗竷鏁版嵁鐨勪竴绉嶅垎鍖虹被鍨嬶紝鍥犱负閫氳繃鍦↖/O璁惧?涓婅繘琛屾暎鍒楀垎鍖猴紝浣垮緱杩欎簺鍒嗗尯澶у皬涓?鑷淬??/div>

渚嬩竴锛?/div>

CREATE TABLE HASH_TABLE聽
(聽
聽 COL NUMBER(8),聽
聽 INF VARCHAR2(100)聽
)聽
PARTITION BY HASH (COL)聽
(聽
聽 PARTITION PART01 TABLESPACE HASH_TS01,聽
聽 PARTITION PART02 TABLESPACE HASH_TS02,聽
聽 PARTITION PART03 TABLESPACE HASH_TS03聽
)

绠?鍐欙細

CREATE TABLE emp
(
聽聽聽 empno NUMBER (4),
聽聽聽 ename VARCHAR2 (30),
聽聽聽 sal聽聽 NUMBER聽
)
PARTITION BY聽 HASH (empno) PARTITIONS 8
STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);

聽

hash鍒嗗尯鏈?涓昏?鐨勬満鍒舵槸鏍规嵁hash绠楁硶鏉ヨ?绠楀叿浣撴煇鏉＄邯褰曞簲璇ユ彃鍏ュ埌鍝?釜鍒嗗尯涓?hash绠楁硶涓?渶閲嶈?鐨勬槸hash鍑芥暟锛孫racle涓??鏋滀綘瑕佷娇鐢╤ash鍒嗗尯锛屽彧闇?鎸囧畾鍒嗗尯鐨勬暟閲忓嵆鍙??傚缓璁?垎鍖虹殑鏁伴噺閲囩敤2鐨刵娆℃柟锛岃繖鏍峰彲浠ヤ娇寰楀悇涓?垎鍖洪棿鏁版嵁鍒嗗竷鏇村姞鍧囧寑銆?/div>

聽

鍥?缁勫悎鑼冨洿鏁ｅ垪鍒嗗尯
杩欑?鍒嗗尯鏄?熀浜庤寖鍥村垎鍖哄拰鍒楄〃鍒嗗尯锛岃〃棣栧厛鎸夋煇鍒楄繘琛岃寖鍥村垎鍖猴紝鐒跺悗鍐嶆寜鏌愬垪杩涜?鍒楄〃鍒嗗尯锛屽垎鍖轰箣涓?殑鍒嗗尯琚?О涓哄瓙鍒嗗尯銆?/div>

CREATE TABLE SALES聽
(

PRODUCT_ID VARCHAR2(5),

SALES_DATE DATE,

SALES_COST NUMBER(10),

STATUS VARCHAR2(20)

)

PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)

(

聽聽 PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009聽
聽聽聽聽聽聽聽聽聽 (聽
聽聽聽聽聽聽聽聽聽聽聽聽聽 SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,聽
聽聽聽聽聽聽聽聽聽聽聽聽聽 SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009聽
聽聽聽聽聽聽聽聽聽 ),聽
聽聽 PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009聽
聽聽聽聽聽聽聽聽聽 (聽
聽聽聽聽聽聽聽聽聽聽聽聽聽 SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,聽
聽聽聽聽聽聽聽聽聽聽聽聽聽 SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009聽
聽聽聽聽聽聽聽聽聽 )聽
)

聽

浜?澶嶅悎鑼冨洿鏁ｅ垪鍒嗗尯锛?/div>

杩欑?鍒嗗尯鏄?熀浜庤寖鍥村垎鍖哄拰鏁ｅ垪鍒嗗尯锛岃〃棣栧厛鎸夋煇鍒楄繘琛岃寖鍥村垎鍖猴紝鐒跺悗鍐嶆寜鏌愬垪杩涜?鏁ｅ垪鍒嗗尯銆?/div>

create table dinya_test聽
聽(聽
聽transaction_id number primary key,聽
聽item_id number(8) not null,聽
聽item_description varchar2(300),聽
聽transaction_date date聽
聽)聽
聽partition by range(transaction_date)subpartition by hash(transaction_id)聽 subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)聽
聽(聽
聽聽聽聽 partition part_01 values less than(to_date(鈥?006-01-01鈥?鈥檡yyy-mm-dd鈥?),聽
聽聽聽聽 partition part_02 values less than(to_date(鈥?010-01-01鈥?鈥檡yyy-mm-dd鈥?),聽
聽聽聽聽 partition part_03 values less than(maxvalue)聽
聽);

聽

(5).鏈夊叧琛ㄥ垎鍖虹殑涓?浜涚淮鎶ゆ?ф搷浣滐細聽
涓?銆佹坊鍔犲垎鍖?span class="Apple-converted-space">聽
浠ヤ笅浠ｇ爜缁橲ALES琛ㄦ坊鍔犱簡涓?涓狿3鍒嗗尯

ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));

娉ㄦ剰锛氫互涓婃坊鍔犵殑鍒嗗尯鐣岄檺搴旇?楂樹簬鏈?鍚庝竴涓?垎鍖虹晫闄愩??/div>

浠ヤ笅浠ｇ爜缁橲ALES琛ㄧ殑P3鍒嗗尯娣诲姞浜嗕竴涓狿3SUB1瀛愬垎鍖?/div>

ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');

浜屻?佸垹闄ゅ垎鍖?span class="Apple-converted-space">聽
浠ヤ笅浠ｇ爜鍒犻櫎浜哖3琛ㄥ垎鍖猴細

ALTER TABLE SALES DROP PARTITION P3;

鍦ㄤ互涓嬩唬鐮佸垹闄や簡P4SUB1瀛愬垎鍖猴細

ALTER TABLE SALES DROP SUBPARTITION P4SUB1;

娉ㄦ剰锛氬?鏋滃垹闄ょ殑鍒嗗尯鏄?〃涓?敮涓?鐨勫垎鍖猴紝閭ｄ箞姝ゅ垎鍖哄皢涓嶈兘琚?垹闄わ紝瑕佹兂鍒犻櫎姝ゅ垎鍖猴紝蹇呴』鍒犻櫎琛ㄣ??/div>

涓夈?佹埅鏂?垎鍖?span class="Apple-converted-space">聽
鎴?柇鏌愪釜鍒嗗尯鏄?寚鍒犻櫎鏌愪釜鍒嗗尯涓?殑鏁版嵁锛屽苟涓嶄細鍒犻櫎鍒嗗尯锛屼篃涓嶄細鍒犻櫎鍏跺畠鍒嗗尯涓?殑鏁版嵁銆傚綋琛ㄤ腑鍗充娇鍙?湁涓?涓?垎鍖烘椂锛屼篃鍙?互鎴?柇璇ュ垎鍖恒?傞?氳繃浠ヤ笅浠ｇ爜鎴?柇鍒嗗尯锛?/div>

ALTER TABLE SALES TRUNCATE PARTITION P2;

閫氳繃浠ヤ笅浠ｇ爜鎴?柇瀛愬垎鍖猴細

ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

鍥涖?佸悎骞跺垎鍖?span class="Apple-converted-space">聽
鍚堝苟鍒嗗尯鏄?皢鐩搁偦鐨勫垎鍖哄悎骞舵垚涓?涓?垎鍖猴紝缁撴灉鍒嗗尯灏嗛噰鐢ㄨ緝楂樺垎鍖虹殑鐣岄檺锛屽?煎緱娉ㄦ剰鐨勬槸锛屼笉鑳藉皢鍒嗗尯鍚堝苟鍒扮晫闄愯緝浣庣殑鍒嗗尯銆備互涓嬩唬鐮佸疄鐜颁簡P1 P2鍒嗗尯鐨勫悎骞讹細

ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;

浜斻?佹媶鍒嗗垎鍖?span class="Apple-converted-space">聽
鎷嗗垎鍒嗗尯灏嗕竴涓?垎鍖烘媶鍒嗕袱涓?柊鍒嗗尯锛屾媶鍒嗗悗鍘熸潵鍒嗗尯涓嶅啀瀛樺湪銆傛敞鎰忎笉鑳藉?HASH绫诲瀷鐨勫垎鍖鸿繘琛屾媶鍒嗐??/div>

ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);

鍏??佹帴鍚堝垎鍖?coalesca)聽
缁撳悎鍒嗗尯鏄?皢鏁ｅ垪鍒嗗尯涓?殑鏁版嵁鎺ュ悎鍒板叾瀹冨垎鍖轰腑锛屽綋鏁ｅ垪鍒嗗尯涓?殑鏁版嵁姣旇緝澶ф椂锛屽彲浠ュ?鍔犳暎鍒楀垎鍖猴紝鐒跺悗杩涜?鎺ュ悎锛屽?煎緱娉ㄦ剰鐨勬槸锛屾帴鍚堝垎鍖哄彧鑳界敤浜庢暎鍒楀垎鍖轰腑銆傞?氳繃浠ヤ笅浠ｇ爜杩涜?鎺ュ悎鍒嗗尯锛?/div>

ALTER TABLE SALES COALESCA PARTITION;

涓冦?侀噸鍛藉悕琛ㄥ垎鍖?span class="Apple-converted-space">聽
浠ヤ笅浠ｇ爜灏哖21鏇存敼涓篜2

ALTER TABLE SALES RENAME PARTITION P21 TO P2;

鍏??佺浉鍏虫煡璇?br style="margin: 0px; padding: 0px;">璺ㄥ垎鍖烘煡璇?/div>

select sum( *) from

(select count(*) cn from t_table_SS PARTITION (P200709_1)

union all

select count(*) cn from t_table_SS PARTITION (P200709_2)

);

鏌ヨ?琛ㄤ笂鏈夊?灏戝垎鍖?/div>

SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'

鏌ヨ?绱㈠紩淇℃伅

select object_name,object_type,tablespace_name,sum(value)

from v$segment_statistics

where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX'

group by object_name,object_type,tablespace_name

order by 4 desc

聽

--鏄剧ず鏁版嵁搴撴墍鏈夊垎鍖鸿〃鐨勪俊鎭?細

select * from DBA_PART_TABLES

聽

--鏄剧ず褰撳墠鐢ㄦ埛鍙??闂?殑鎵?鏈夊垎鍖鸿〃淇℃伅:

select * from ALL_PART_TABLES

聽

--鏄剧ず褰撳墠鐢ㄦ埛鎵?鏈夊垎鍖鸿〃鐨勪俊鎭?細

select * from USER_PART_TABLES

聽

--鏄剧ず琛ㄥ垎鍖轰俊鎭?鏄剧ず鏁版嵁搴撴墍鏈夊垎鍖鸿〃鐨勮?缁嗗垎鍖轰俊鎭?細

select * from DBA_TAB_PARTITIONS

聽

--鏄剧ず褰撳墠鐢ㄦ埛鍙??闂?殑鎵?鏈夊垎鍖鸿〃鐨勮?缁嗗垎鍖轰俊鎭?細

select * from ALL_TAB_PARTITIONS

聽

--鏄剧ず褰撳墠鐢ㄦ埛鎵?鏈夊垎鍖鸿〃鐨勮?缁嗗垎鍖轰俊鎭?細

select * from USER_TAB_PARTITIONS

聽

--鏄剧ず瀛愬垎鍖轰俊鎭?鏄剧ず鏁版嵁搴撴墍鏈夌粍鍚堝垎鍖鸿〃鐨勫瓙鍒嗗尯淇℃伅锛?/div>

select * from DBA_TAB_SUBPARTITIONS

聽

--鏄剧ず褰撳墠鐢ㄦ埛鍙??闂?殑鎵?鏈夌粍鍚堝垎鍖鸿〃鐨勫瓙鍒嗗尯淇℃伅锛?/div>

select * from ALL_TAB_SUBPARTITIONS

聽

--鏄剧ず褰撳墠鐢ㄦ埛鎵?鏈夌粍鍚堝垎鍖鸿〃鐨勫瓙鍒嗗尯淇℃伅锛?/div>

select * from USER_TAB_SUBPARTITIONS

聽

--鏄剧ず鍒嗗尯鍒?鏄剧ず鏁版嵁搴撴墍鏈夊垎鍖鸿〃鐨勫垎鍖哄垪淇℃伅锛?/div>

select * from DBA_PART_KEY_COLUMNS

聽

--鏄剧ず褰撳墠鐢ㄦ埛鍙??闂?殑鎵?鏈夊垎鍖鸿〃鐨勫垎鍖哄垪淇℃伅锛?/div>

select * from ALL_PART_KEY_COLUMNS

聽

--鏄剧ず褰撳墠鐢ㄦ埛鎵?鏈夊垎鍖鸿〃鐨勫垎鍖哄垪淇℃伅锛?/div>

select * from USER_PART_KEY_COLUMNS

聽

--鏄剧ず瀛愬垎鍖哄垪鏄剧ず鏁版嵁搴撴墍鏈夊垎鍖鸿〃鐨勫瓙鍒嗗尯鍒椾俊鎭?細

select * from DBA_SUBPART_KEY_COLUMNS

聽

--鏄剧ず褰撳墠鐢ㄦ埛鍙??闂?殑鎵?鏈夊垎鍖鸿〃鐨勫瓙鍒嗗尯鍒椾俊鎭?細

select * from ALL_SUBPART_KEY_COLUMNS

聽

--鏄剧ず褰撳墠鐢ㄦ埛鎵?鏈夊垎鍖鸿〃鐨勫瓙鍒嗗尯鍒椾俊鎭?細

select * from USER_SUBPART_KEY_COLUMNS

聽

--鎬庢牱鏌ヨ?鍑簅racle鏁版嵁搴撲腑鎵?鏈夌殑鐨勫垎鍖鸿〃

select * from user_tables a where a.partitioned='YES'

聽

--鍒犻櫎涓?涓?〃鐨勬暟鎹?槸

truncate table table_name;

聽

--鍒犻櫎鍒嗗尯琛ㄤ竴涓?垎鍖虹殑鏁版嵁鏄?/div>

alter table table_name truncate partition p5;

鏈?枃鍑鸿嚜鈥?a style="margin: 0px; color: #434343; text-decoration: none; padding: 0px;" href="http://tianzt.blog.51cto.com/">澶╃煡閬撶殑鎶?鏈?崥瀹?/a>鈥?鍗氬?锛岃浆杞借?涓庝綔鑰呰仈绯?/p>

杞?嚜锛?a href="http://tianzt.blog.51cto.com/459544/171759/">http://tianzt.blog.51cto.com/459544/171759/

查看全文