Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle online redefinition     - Examples of RAID levels and achieve Operational Details (Linux)

- Java executable file to read information from a database copy (Programming)

- Ubuntu 14.10 install KDE Plasma 5.2 (Linux)

- Let Ubuntu 14.04 Save screen luminance value (Linux)

- Oracle for Oracle GoldenGate to achieve a one-way synchronization DDL operations (Database)

- systemctl Command Complete Guide (Linux)

- Static member variable modified (Programming)

- Java Collections Framework interfaces map (Programming)

- DNF Command Tutorial (Linux)

- Oracle and MySQL difference between the jdbc (Database)

- How do you prevent other users from accessing your home directory in Linux (Linux)

- Apache Linux firewall reverse proxy configuration (Linux)

- Alien Magic: RPM and DEB Mutual Convert (Linux)

- Getting Started with Linux system to learn: how to get the process ID (PID) in the script (Linux)

- Oracle set the timer task JOB scheduler to execute stored procedures or PL / SQL code block (Database)

- Get basic information about Linux server script (Server)

- VMware Workstation + Ubuntu 10.04 Download the Android 2.2 source code (Linux)

- After installing Ubuntu 15.04, to do a few things (Linux)

- How to manage Vim plugin (Linux)

- Boot-Repair Tool - repair of frequent start-up problems (Linux)

 
         
  Oracle online redefinition
     
  Add Date : 2018-11-21      
         
         
         
  Before online redefinition [@ more @] Oracle9i, sorting table data through alter table XXX move [tablespace XX] carried out. If the table is very large, when the IO not fast, move the process can be quite long, it can not be regarded HA features. Therefore, HA-line maintenance, we will not use the basic move to reorganize a large table, and move the table after the need to rebuild the index. In 9i introduced dbms_redefinition. The principle of the internal process is actually using the MV mechanism, similar to the establishment of a prebuilt in the original table MV then has been incremental refresh data to the smallest difference. Oracle in a small pause, and the final point incremental synchronization, after completely synchronized with the original table new table name change, since the name change operation is only the data dictionary, so the final switching time is very short.

- Firstly, to test a foreign key constraint table test_refed

SQL> create table test_refed (id number primary key);

Table created.

SQL> insert into test_refed select rownum from dba_objects where rownum <10001;

10000 rows created.

SQL> commit;

Commit complete.

- We are ready to modify a table definition create table test, unpartitioned

SQL> create table test (id number, fid number);

Table created.

SQL> insert into test select rownum, rownum from dba_objects where rownum <1001;

1000 rows created.

SQL> commit;

Commit complete.

- Add a primary key constraint and a foreign key constraint, and the outer key index

SQL> alter table test add constraint pk_test primary key (id);

Table altered.

SQL> alter table test add constraint fk_test_refed_id foreign key (fid) references test_refed (id);

Table altered.

SQL> create index idx_test_fid on test (fid);

Index created.

- Create a trigger-based test table

SQL> create trigger tr_test
 2 before insert on test for each row
 3 begin
 4 null;
 5 end;
 6 /

Trigger created.

- Preparation: call dbms_redefinition.can_redef_table boylook.test to verify whether the online redefinition. Generally there is no primary key, etc. error.
SQL> begin
 2 dbms_redefinition.can_redef_table ( 'BOYLOOK', 'TEST');
 3 end;
 4 /

PL / SQL procedure successfully completed.

- Create a need to redefine the transition table inter_test, this is a partitioned table, the original table after all the data will be transferred to the online tables were. Note that the table than the original table test more than one field c

SQL> create table inter_test (id number, fid number, c number)
 2 partition by range (id)
 3 (partition p1 values ​​less than (400),
 4 partition p2 values ​​less than (800),
 5 partition p3 values ​​less than (maxvalue));

Table created.

SQL> alter table inter_test add constraint pk_inter_test primary key (id);

Table altered.

- Perform an online redefinition
Note: Calling this stored procedure requires create / alter / drop / lock / select any table privilege

SQL> exec dbms_redefinition.start_redef_table ( 'BOYLOOK', 'TEST', 'INTER_TEST', 'id id, fid fid, 0 c');

PL / SQL procedure successfully completed.

- Verify that the data brush past
SQL> select count (*) from inter_test;

 COUNT (*)
----------
 1000

- Continue to operate on the original table test
SQL> insert into test select rownum + 1000, rownum + 1000 from dba_objects where rownum <= 24;

24 rows created.

SQL> commit;

Commit complete.

SQL> select count (*) from test;

 COUNT (*)
----------
 1024

SQL> select count (*) from inter_test;

 COUNT (*)
----------
 1000

- Perform table synchronization
Note: This step is not necessary, but for the large table in the middle run incremental synchronization helps reduce switching time.

SQL> exec dbms_redefinition.sync_interim_table ( 'BOYLOOK', 'TEST', 'INTER_TEST');

PL / SQL procedure successfully completed.

- We found that data synchronization over
SQL> select count (*) from inter_test;

 COUNT (*)
----------
 1024

- Test of the original table constraints, indexes, triggers emigres
Note: This is best to check the authorization. Check test prior permission, and give the same privileges to the middle of the table inter_test. Otherwise permission of the original table will not be transferred to the new table
SQL> alter table inter_test add constraint fk_inter_refed_id foreign key (fid) references test_refed (id);

Table altered.

SQL> create index idx_inter_test_fid on inter_test (fid);

Index created.

SQL> create or replace trigger tr_inter_test
 2 before insert on inter_test for each row
 3 begin
 4 null;
 5 end;
 6 /

Trigger created.

- Perform complete redefinition process. Oracle completed the test table to table inter_test-for staff, but all the constraints, indexes or triggers to maintain the original name or the name of the table above

SQL> exec dbms_redefinition.finish_redef_table ( 'BOYLOOK', 'TEST', 'INTER_TEST');

PL / SQL procedure successfully completed.

SQL> desc test;
Name Null? Type
----------------------------------------- ---------- ---------------------------
ID NOT NULL NUMBER
FID NUMBER
C NUMBER

SQL> desc inter_test;
Name Null? Type
----------------------------------------- ---------- ---------------------------
ID NOT NULL NUMBER
FID NUMBER

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

TABLE_NAME PARTITION_NAME
------------------------------ -------------------- ----------
TEST P1
TEST P2
TEST P3

SQL> select table_name, constraint_name, status from user_constraints where table_name in ( 'TEST', 'INTER_TEST');

TABLE_NAME CONSTRAINT_NAME STATUS
------------------------------ -------------------- ---------- --------
INTER_TEST PK_TEST ENABLED
INTER_TEST FK_TEST_REFED_ID DISABLED
TEST PK_INTER_TEST ENABLED
TEST FK_INTER_REFED_ID ENABLED

SQL> select table_name, index_name from user_indexes where table_name in ( 'TEST', 'INTER_TEST');

TABLE_NAME INDEX_NAME
------------------------------ -------------------- ----------
TEST PK_INTER_TEST
TEST IDX_INTER_TEST_FID
INTER_TEST PK_TEST
INTER_TEST IDX_TEST_FID

SQL> select table_name, trigger_name from user_triggers where table_name in ( 'TEST', 'INTER_TEST');

TABLE_NAME TRIGGER_NAME
------------------------------ -------------------- ----------
TEST TR_INTER_TEST
INTER_TEST TR_TEST

SQL> select * from test where rownum <= 10;

 ID FID C
---------- ---------- ----------
 110
 220
 330
 440
 550
 660
 770
 880
 990
 10100

10 rows selected.

- Remove the transition table, and call it a day. In this case also can consider modifying indexes, constraints, triggers consistent with the original name

SQL> drop table inter_test cascade constraints purge;

Table dropped.
     
         
         
         
  More:      
 
- Linux, set and view environment variables (Linux)
- Android Unzip the ZIP / GZIP data (based on the InflaterInputStream implementation) (Programming)
- Linux install Samba file sharing server (Server)
- How to manage start-up applications in Ubuntu (Linux)
- Python Dir find a folder several files (Programming)
- Help you make Git Bisect (Linux)
- Linux System Getting Started Learning: Join cron job in Linux (Linux)
- CentOS 6.5 installation and simple configuration Nginx (Server)
- Python variable type (Programming)
- Django 1.8 TEMPLATE_DIRS configuration and STATICFILES_DIRS (Server)
- Ubuntu 14.04 installed VirtualBox 4.3 appears vboxdrv: Unknown symbol mcount (Linux)
- MySQL flip-flop (Database)
- Access.log Apache access log analysis and how to import it into MySQL (Server)
- Shell Scripting Basics (Linux)
- Linux / Unix desktops interesting: Christmas tree on the terminal (Linux)
- Manual cleaning Oracle audit records (Database)
- Node.js v4.0.0 installation configuration on Ubuntu 14.04 / 15.04 (Linux)
- 29 practical examples Linux system / network administrator of nmap (Linux)
- phpinfo (): like phpinfo as a Python script (Programming)
- Linux system monitoring, top command of the diagnostic tool Detailed (Linux)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.