Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge about "Oracle12.2 how to redefine tables with object data types". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

Oracle 12.2 uses object data types to redefine tables. The following example changes columns to object attributes. The original table is customer, defined as follows:

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 createdSQL> alter table jy.customer add constraint customer_pk primary key(cid);Table altered

Create a new object type

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

The online redefinition operation is as follows:

1. Log in to the database with the user who wants to perform the online redefine operation

SQL> conn jy/jy@jypdbConnected.

2. Validates whether a table can be redefined online, using either a primary key or a pseudo-primary key.

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. Create intermediate table 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. Because customer is a very large table, parallel execution of the following statements is enabled for the next operation:

SQL> alter session force parallel dml parallel 4;Session alteredSQL> alter session force parallel query parallel 4;Session altered

5. Use the primary key to perform a redefine operation

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. Copy Dependent Objects

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. Optional Operation Synchronization Intermediate Table

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. Complete Redefinition Operation

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 completedSQL> 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.

You can see that the table jy.customer has been successfully redefined online

9. Wait for any statements querying intermediate tables to complete and delete them

SQL> desc jy.customerName Type Nullable Default Comments---- ------------ -------- ------- --------CID NUMBERNAME VARCHAR2(30) YADDR ADDR_T YSQL> drop table jy.customer purge;Table dropped

This completes the redefine operation.

"Oracle 12.2 how to use object data types to redefine the table" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.