您好,登錄后才能下訂單哦!
本篇內(nèi)容介紹了“Oracle12.2怎么用對象數(shù)據(jù)類型來重定義表”的有關(guān)知識,在實際案例的操作過程中,不少人都會遇到這樣的困境,接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧!希望大家仔細(xì)閱讀,能夠?qū)W有所成!
Oracle 12.2使用對象數(shù)據(jù)類型來重定義表,下面的例子將重定義表將列改變?yōu)閷ο髮傩?,原始表為customer,定義如下:
SQL> create table jy.customer( 2 cid number not null, 3 name varchar2(30), 4 street varchar2(100), 5 city varchar2(30), 6 state varchar2(2), 7 zip number(5) 8 ); Table created SQL> alter table jy.customer add constraint customer_pk primary key(cid); Table altered
創(chuàng)建新的對象類型
SQL> create type jy.addr_t as object 2 ( 3 street varchar2(100), 4 city varchar2(30), 5 state varchar2(2), 6 zip number(5,0) 7 ); 8 9 / Type created
聯(lián)機(jī)重定義操作如下:
1.用要執(zhí)行聯(lián)機(jī)重定義操作的用戶登錄數(shù)據(jù)庫
SQL> conn jy/jy@jypdb Connected.
2.驗證表是否可以執(zhí)行聯(lián)機(jī)重定義,可以使用主鍵或偽主鍵來執(zhí)行驗證操作。
SQL> begin 2 dbms_redefinition.can_redef_table( 3 uname => 'jy', 4 tname =>'customer', 5 options_flag => DBMS_REDEFINITION.CONS_USE_PK); 6 end; 7 / PL/SQL procedure successfully completed
3.創(chuàng)建中間表jy.int_customer
SQL> create table jy.int_customer 2 ( 3 cid number, 4 name varchar2(30), 5 addr addr_t 6 ); Table created
4.因為customer是一個非常大的表,為了讓下一步操作啟用并行執(zhí)行以下語句:
SQL> alter session force parallel dml parallel 4; Session altered SQL> alter session force parallel query parallel 4; Session altered
5.使用主鍵來執(zhí)行重定義操作
SQL> begin 2 dbms_redefinition.start_redef_table( 3 uname => 'jy', 4 orig_table => 'customer', 5 int_table => 'int_customer', 6 col_mapping => 'cid cid, name name, 7 addr_t(street, city, state, zip) addr'); 8 end; 9 / PL/SQL procedure successfully completed
6.復(fù)制依賴對象
SQL> declare 2 num_errors pls_integer; 3 begin 4 dbms_redefinition.copy_table_dependents( 5 uname => 'jy', 6 orig_table => 'customer', 7 int_table => 'int_customer', 8 copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS, 9 copy_triggers => TRUE, 10 copy_constraints => TRUE, 11 copy_privileges => TRUE, 12 ignore_errors => FALSE, 13 num_errors => num_errors, 14 copy_statistics => TRUE); 15 end; 16 / PL/SQL procedure successfully completed
7.可選操作同步中間表
SQL> begin 2 dbms_redefinition.sync_interim_table( 3 uname => 'jy', 4 orig_table => 'customer', 5 int_table => 'int_customer'); 6 end; 7 / PL/SQL procedure successfully completed
8.完成重定義操作
SQL> begin 2 dbms_redefinition.finish_redef_table( 3 uname => 'jy', 4 orig_table => 'customer', 5 int_table => 'int_customer'); 6 end; 7 / PL/SQL procedure successfully completed
SQL> select dbms_metadata.get_ddl(object_type =>'TABLE',name =>'CUSTOMER',schema => 'JY') from dual; DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'TABLE',NAME=>'CUSTOMER',SCHEMA=>'JY') -------------------------------------------------------------------------------- CREATE TABLE "JY"."CUSTOMER" ( "CID" NUMBER NOT NULL ENABLE, "NAME" VARCHAR2(30), "ADDR" "JY"."ADDR_T" , CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("CID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TEST" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TEST" 1 row selected.
可以看到表jy.customer已經(jīng)成功能聯(lián)機(jī)重定義
9.等待任何查詢中間表的語句執(zhí)行完成后將其刪除
SQL> desc jy.customer Name Type Nullable Default Comments ---- ------------ -------- ------- -------- CID NUMBER NAME VARCHAR2(30) Y ADDR ADDR_T Y SQL> drop table jy.customer purge; Table dropped
到此重定義操作就完成了。
“Oracle12.2怎么用對象數(shù)據(jù)類型來重定義表”的內(nèi)容就介紹到這里了,感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站,小編將為大家輸出更多高質(zhì)量的實用文章!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。