資訊專欄INFORMATION COLUMN
11g環(huán)境
1. 創(chuàng)建實(shí)驗(yàn)表和實(shí)驗(yàn)數(shù)據(jù)
----------
SQL> create table test_p (id number);
Table created.
SQL> declare
i number:=1;
begin
for i in 1..3000 loop
insert into scott.test_p values(i);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.----------
SQL> create table test_p2
partition by range (id)
(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (3000),
partition p4 values less than (maxvalue)
)
as
select * from test_p;
Table created.----------
SQL> create index t_p on test_p(id);
Index created.3.2 檢查實(shí)驗(yàn)表是否可以在線重定義
----------
SQL> exec dbms_redefinition.can_redef_table( scott,test_p,dbms_redefinition.cons_use_pk);
BEGIN dbms_redefinition.can_redef_table( scott,test_p,dbms_redefinition.cons_use_pk); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "SCOTT"."TEST_P" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 143
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1635
ORA-06512: at line 1----------
SQL> alter table test_p add constraint pt_p primary key(id);
Table altered.
SQL> exec dbms_redefinition.can_redef_table( scott,test_p,dbms_redefinition.cons_use_pk);
PL/SQL procedure successfully completed.----------
SQL> create table p_temp (id number)
partition by range (id)
(
partition p1 values less than(1000),
partition p2 values less than(2000),
partition p3 values less than(3000),
partition p4 values less than(maxvalue)
);
Table created.
SQL> alter table p_temp add constraint pp_temp primary key(id);
Table altered.----------
SQL> exec dbms_redefinition.start_redef_table(scott, test_p, p_temp);
PL/SQL procedure successfully completed.----------
SQL> exec dbms_redefinition.finish_redef_table(scott, test_p, p_temp);
PL/SQL procedure successfully completed.
SQL> select table_name,partition_name from user_tab_partitions where table_name=TEST_P;
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TEST_P P1
TEST_P P2
TEST_P P3
TEST_P P412c環(huán)境下
----------
SQL> create table test_p (id number);
Table created.
SQL> declare
i number:=1;
begin
for i in 1..3000 loop
insert into scott.test_p values(i);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.----------
SQL> select table_name,partition_name from user_tab_partitions where table_name=TEST_P;
no rows selected----------
SQL> alter table test_p add constraint tp_p primary key (id);
Table altered.----------
SQL> alter table test_p modify
partition by range (id)
(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (3000),
partition p4 values less than (maxvalue)
)
update indexes (tp_p global);
Table altered.----------
SQL> select table_name,partition_name from user_tab_partitions where table_name=TEST_P;
TABLE_NAME PARTITION_NAME
------------------------------
TEST_P P1
TEST_P P2
TEST_P P3
TEST_P P4對分區(qū)表進(jìn)行擴(kuò)展的兩種情況
----------
SQL> create table test_p2 (id number)
partition by range (id)
(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (3000)
);
Table created.----------
SQL> select table_name,partition_name from user_tab_partitions where table_name=TEST_P2;
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TEST_P2 P1
TEST_P2 P2
TEST_P2 P3----------
SQL> alter table test_p2 add partition p4 values less than (4000);
Table altered.----------
SQL> select table_name,partition_name from user_tab_partitions where table_name=TEST_P2;
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TEST_P2 P1
TEST_P2 P2
TEST_P2 P3
TEST_P2 P4----------
SQL> alter table test_p add partition p5 values less than (4000);
alter table test_p add partition p5 values less than (4000)
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition此時，可以通過分割存在maxvalue的分區(qū)來實(shí)現(xiàn)對分區(qū)的增加：
----------
SQL> alter table test_p split partition p4 at (4000) into (partition p5,partition p4);
Table altered.Dblink的創(chuàng)建
SQL> conn scott/oracle
Connected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT UNLIMITED TABLESPACE NO賦予當(dāng)前用戶創(chuàng)建dblink的權(quán)限：
----------
SQL> grant create public database link to scott;
Grant succeeded.
SQL> grant create database link to scott;
Grant succeeded.----------
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT CREATE DATABASE LINK NO
SCOTT UNLIMITED TABLESPACE NO
SCOTT CREATE PUBLIC DATABASE LINK NO文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請注明本文地址：http://m.specialneedsforspecialkids.com/yun/129605.html
摘要：新晉技術(shù)專家下面是墨天輪部分新晉的技術(shù)專家。大家可以點(diǎn)擊往期閱讀墨天輪技術(shù)專家邀請函了解詳情，申請成為我們的技術(shù)專家，加入專家團(tuán)隊(duì)，與我們一起創(chuàng)建一個開放互助的數(shù)據(jù)庫技術(shù)社區(qū)。新關(guān)聯(lián)公眾號墨天輪是一個開放互助的數(shù)據(jù)庫技術(shù)社區(qū)。 引言 近期我們在DBASK小程序增加了數(shù)據(jù)庫 MongoDB、Redis、 Elasticsearch、DB2、Weblogic 等新的的專題欄目和一些新的技術(shù)...
閱讀 1353·2023-01-11 13:20
閱讀 1699·2023-01-11 13:20
閱讀 1211·2023-01-11 13:20
閱讀 1904·2023-01-11 13:20
閱讀 4161·2023-01-11 13:20
閱讀 2751·2023-01-11 13:20
閱讀 1397·2023-01-11 13:20
閱讀 3664·2023-01-11 13:20
