成人午夜视频全免费观看高清-秋霞福利视频一区二区三区-国产精品久久久久电影小说-亚洲不卡区三一区三区一区

Oracle怎么把非分區(qū)表轉(zhuǎn)為分區(qū)表

這篇文章主要介紹“Oracle怎么把非分區(qū)表轉(zhuǎn)為分區(qū)表”，在日常操作中，相信很多人在Oracle怎么把非分區(qū)表轉(zhuǎn)為分區(qū)表問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”O(jiān)racle怎么把非分區(qū)表轉(zhuǎn)為分區(qū)表”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧！

創(chuàng)新互聯(lián)公司是一家集網(wǎng)站建設(shè),大邑縣企業(yè)網(wǎng)站建設(shè),大邑縣品牌網(wǎng)站建設(shè),網(wǎng)站定制,大邑縣網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營(yíng)銷(xiāo),網(wǎng)絡(luò)優(yōu)化,大邑縣網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競(jìng)爭(zhēng)力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專(zhuān)業(yè)、時(shí)尚、前沿，時(shí)刻以成就客戶成長(zhǎng)自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。

一：oracle 11G 在線將非分區(qū)表轉(zhuǎn)換為分區(qū)表

Online Redefinition

二：Oracle 12C 在線將非分區(qū)表轉(zhuǎn)換為分區(qū)表

ALTER TABLE table_name MODIFY table_partitioning_clauses

  [ filter_condition ]

  [ ONLINE ]

  [ UPDATE INDEXES [ ( index { local_partitioned_index | global_partitioned_index | GLOBAL }

                     [, index { local_partitioned_index | global_partitioned_index | GLOBAL } ]... )

                   ]

  ]

一：oracle 11G將非分區(qū)表轉(zhuǎn)換為分區(qū)表

在線重定義Online Redefinition

1.數(shù)據(jù)庫(kù)版本

---數(shù)據(jù)庫(kù)19C，相當(dāng)于12.2.0.3版本，也支持Online Redefinition

SQL> select banner_full from v$version;

BANNER_FULL

---------------------------------------------------------------------

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show pdbs

    CON_ID CON_NAME     OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

 2 PDB$SEED     READ ONLY  NO

 3 CJCPDB     READ WRITE NO

2.創(chuàng)建測(cè)試表插入測(cè)試數(shù)據(jù)

SQL> conn cjc/cjc@cjcpdb

SQL> create table t1(id number,adr varchar2(100),acc number);

SQL>

insert into t1 values(1,'dapuchai',10);

insert into t1 values(2,'dunhua',20);

insert into t1 values(3,'xiaopuchai',30);

insert into t1 values(4,'fuerhe',101);

insert into t1 values(5,'fuyuanjie',130);

insert into t1 values(6,'songyuanjie',125);

insert into t1 values(7,'bajiazhi',166);

insert into t1 values(8,'yaotun',105);

insert into t1 values(9,'hanconggou',256);

insert into t1 values(10,'jiangdong',270);

commit;

SQL> alter table t1 add constraint pk_t1_id primary key (id);

SQL> col adr for a15

SQL> select * from t1;

ID ADR     ACC

---------- --------------- ----------

 1 dapuchai     10

 2 dunhua     20

 3 xiaopuchai     30

 4 fuerhe    101

 5 fuyuanjie    130

 6 songyuanjie    125

 7 bajiazhi    166

 8 yaotun    105

 9 hanconggou    256

10 jiangdong    270

10 rows selected.

3.檢查下這張表是否可以在線重定義

---dbms_redefinition.cons_use_rowid

---dbms_redefinition.cons_use_pk

SQL> exec dbms_redefinition.can_redef_table( 'CJC','T1',dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

4.建立在線重定義需要的中間表

SQL>

create table t1_temp(id number,adr varchar2(100),acc number)

partition by range(acc)(  

partition PAR01 values less than (100),  

partition PAR02 values less than (200),  

partition PAR03 values less than (300),   

partition PARMAX values less THAN (MAXVALUE)  

);

SQL> alter table t1_temp add constraint pk_t1_temp_id1 primary key (id);

5.啟動(dòng)在線重定義

SQL> exec dbms_redefinition.start_redef_table('CJC', 'T1', 'T1_TEMP');

PL/SQL procedure successfully completed.

6.檢查中間表數(shù)據(jù)

SQL> select * from t1_temp;

ID ADR     ACC

---------- --------------- ----------

 1 dapuchai     10

 2 dunhua     20

 3 xiaopuchai     30

 4 fuerhe    101

 5 fuyuanjie    130

 6 songyuanjie    125

 7 bajiazhi    166

 8 yaotun    105

 9 hanconggou    256

10 jiangdong    270

10 rows selected.

7.模擬生產(chǎn)環(huán)境數(shù)據(jù)變化

SQL> insert into t1 values(20,'yansan',208);

1 row created.

SQL> delete t1 where id=4;

1 row deleted.

SQL> update t1 set adr='dashitou' where id=7;

1 row updated.

SQL> commit;

Commit complete.

8.原表被修改，中間表并沒(méi)有更新

SQL> select * from t1;

ID ADR     ACC

---------- --------------- ----------

 1 dapuchai     10

 2 dunhua     20

 3 xiaopuchai     30

 5 fuyuanjie    130

 6 songyuanjie    125

 7 dashitou    166

 8 yaotun    105

 9 hanconggou    256

10 jiangdong    270

20 yansan    208

10 rows selected.

SQL> select * from t1_temp;

ID ADR     ACC

---------- --------------- ----------

 1 dapuchai     10

 2 dunhua     20

 3 xiaopuchai     30

 4 fuerhe    101

 5 fuyuanjie    130

 6 songyuanjie    125

 7 bajiazhi    166

 8 yaotun    105

 9 hanconggou    256

10 jiangdong    270

10 rows selected.

9.中間表同步數(shù)據(jù)

SQL> exec dbms_redefinition.sync_interim_table('CJC', 'T1', 'T1_TEMP');

PL/SQL procedure successfully completed.

查詢同步后數(shù)據(jù)：

SQL> select * from t1_temp;

ID ADR     ACC

---------- --------------- ----------

 1 dapuchai     10

 2 dunhua     20

 3 xiaopuchai     30

 5 fuyuanjie    130

 6 songyuanjie    125

 8 yaotun    105

 7 dashitou    166

 9 hanconggou    256

10 jiangdong    270

20 yansan    208

10 rows selected.

10.結(jié)束在線重定義

SQL> exec dbms_redefinition.finish_redef_table('CJC', 'T1', 'T1_TEMP');

PL/SQL procedure successfully completed.

11.驗(yàn)證數(shù)據(jù)

SQL> select * from T1;

ID ADR     ACC

---------- --------------- ----------

 1 dapuchai     10

 2 dunhua     20

 3 xiaopuchai     30

 5 fuyuanjie    130

 6 songyuanjie    125

 7 dashitou    166

 8 yaotun    105

 9 hanconggou    256

10 jiangdong    270

20 yansan    208

10 rows selected.

SQL> select * from t1_temp;

ID ADR     ACC

---------- --------------- ----------

 1 dapuchai     10

 2 dunhua     20

 3 xiaopuchai     30

 5 fuyuanjie    130

 6 songyuanjie    125

 8 yaotun    105

 7 dashitou    166

 9 hanconggou    256

10 jiangdong    270

20 yansan    208

10 rows selected.

12.查看各分區(qū)數(shù)據(jù)

SQL> col table_name for a10

SQL> col partition_name for a10

SQL> select table_name, partition_name from user_tab_partitions where table_name = 'T1';

TABLE_NAME PARTITION_

---------- ----------

T1    PAR01

T1    PAR02

T1    PAR03

T1    PARMAX

SQL> select * from T1 partition(PAR01);

ID ADR     ACC

---------- --------------- ----------

 1 dapuchai     10

 2 dunhua     20

 3 xiaopuchai    30

SQL> select * from T1 partition(PAR02);

ID ADR     ACC

---------- --------------- ----------

 5 fuyuanjie    130

 6 songyuanjie    125

 8 yaotun    105

 7 dashitou    166

SQL> select * from T1 partition(PAR03);      

ID ADR     ACC

---------- --------------- ----------

 9 hanconggou    256

10 jiangdong    270

20 yansan    208

13.檢查并刪掉中間表

SQL> drop table t1_temp purge;

Table dropped.

二：Oracle 12C 將非分區(qū)表轉(zhuǎn)換為分區(qū)表

在12C中在線將非分區(qū)表轉(zhuǎn)換為分區(qū)表要相對(duì)11G容易了許多，

只需要一條語(yǔ)句即可搞定:ALTER TABLE table_name MODIFY table_partitioning_clauses ......

1.創(chuàng)建測(cè)試表入測(cè)試數(shù)據(jù)

SQL> conn cjc/cjc@cjcpdb

---drop table t1 purge;

SQL> create table t1(id number,adr varchar2(100),acc number);

insert into t1 values(1,'dapuchai',10);

insert into t1 values(2,'dunhua',20);

insert into t1 values(3,'xiaopuchai',30);

insert into t1 values(4,'fuerhe',101);

insert into t1 values(5,'fuyuanjie',130);

insert into t1 values(6,'songyuanjie',125);

insert into t1 values(7,'bajiazhi',166);

insert into t1 values(8,'yaotun',105);

insert into t1 values(9,'hanconggou',256);

insert into t1 values(10,'jiangdong',270);

commit;

SQL> alter table t1 add constraint pk_t1_id primary key (id);

SQL> col adr for a15

SQL> select * from t1;

SQL> col table_name for a10

SQL> col partition_name for a10

SQL> select table_name, partition_name from user_tab_partitions where table_name = 'T1';

no rows selected

2.在線將非分區(qū)表轉(zhuǎn)換為分區(qū)表，索引轉(zhuǎn)換成全局索引

SQL>

alter table t1 modify

      partition by range (acc)

      ( partition PAR01 values less than (100),

        partition PAR02 values less than (200),

partition PAR03 values less than (300),

partition PARMAX values less than (MAXVALUE)

      ) online

      update indexes

  (

      pk_t1_id GLOBAL

  );

Table altered.  

3.檢查

SQL> col table_name for a10

SQL> col partition_name for a10

SQL> select table_name, partition_name from user_tab_partitions where table_name = 'T1';

TABLE_NAME PARTITION_

---------- ----------

T1    PAR01

T1    PAR02

T1    PAR03

T1    PARMAX

SQL> select * from T1 partition(PAR01);

ID ADR     ACC

---------- --------------- ----------

 1 dapuchai     10

 2 dunhua     20

 3 xiaopuchai     30

SQL> select * from T1 partition(PAR02);

ID ADR     ACC

---------- --------------- ----------

 4 fuerhe    101

 5 fuyuanjie    130

 6 songyuanjie    125

 7 bajiazhi    166

 8 yaotun    105

SQL> select * from T1 partition(PAR03);

ID ADR     ACC

---------- --------------- ----------

 9 hanconggou    256

10 jiangdong    270

SQL> col index_name for a15  

SQL> col index_type for a10

SQL> select index_name,index_type,status from user_indexes;

INDEX_NAME INDEX_TYPE STATUS

--------------- ---------- --------

PK_T1_ID NORMAL    VALID

SQL> col segment_name for a15

SQL> select segment_name,segment_type from user_segments;

SEGMENT_NAME SEGMENT_TYPE

--------------- ------------------

到此，關(guān)于“Oracle怎么把非分區(qū)表轉(zhuǎn)為分區(qū)表”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！

分享標(biāo)題：Oracle怎么把非分區(qū)表轉(zhuǎn)為分區(qū)表
分享網(wǎng)址：http://jinyejixie.com/article48/jogpep.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供Google、商城網(wǎng)站、網(wǎng)站設(shè)計(jì)、虛擬主機(jī)、、品牌網(wǎng)站制作

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)

成都定制網(wǎng)站網(wǎng)頁(yè)設(shè)計(jì)
渝中区| 潼南县| 鹤岗市| 嵊泗县| 衡阳县| 华容县| 汝南县| 濮阳市| 周宁县| 饶河县| 辽中县| 车致| 库伦旗| 平度市| 顺昌县| 望都县| 类乌齐县| 新宁县| 米泉市| 鄱阳县| 太白县| 泗阳县| 资中县| 鄂托克前旗| 商城县| 阿瓦提县| 恩施市| 岑溪市| 汉源县| 祥云县| 出国| 东丰县| 十堰市| 田林县| 日喀则市| 昌吉市| 香格里拉县| 温宿县| 玉屏| 西乡县| 曲麻莱县|