Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle online redefinition     - Getting Started with Linux system to learn: how to install USB webcams come in raspberry (Linux)

- Perl loop (Programming)

- Linux + Apache + PHP + Oracle based environment to build (Server)

- To remove those IP is prohibited Fail2ban on CentOS 6/7 (Server)

- Nginx server security configuration (Server)

- Change CentOS 7 NIC name eno16777736 to eth0 (Linux)

- GCC and gfortran write MEX program (Matlab2012a) under Ubuntu 14.04 (Programming)

- Security Knowledge: How to hide a backdoor PHP file tips (Linux)

- configuration ssh without password under Linux (Linux)

- Oracle TAF Analysis (Database)

- Linux command Detailed chpasswd bulk edit user password (Linux)

- Ease of use "Explain Shell" script to understand Shell command (Linux)

- Cobbler remotely install CentOS system (Linux)

- Linux crontab (Linux)

- Linux ls command (Linux)

- Python common data type summary (Programming)

- Linux system monitoring tool set cpu (Linux)

- socket busy poll of Linux kernel 3.11 to avoid sleep switch (Linux)

- OpenSSH version smooth upgrade method (Linux)

- SSH mutual trust configuration (Server)

 
         
  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:      
 
- CentOS6.5 setup Oracle 11gR2 startup (Database)
- Hyper-V virtual hard disk how to copy files to and attached to the virtual machine (Linux)
- Nagios (centreon) monitoring LVS (Server)
- C language - Traverse pci device (Programming)
- To install Docker under CentOS7 (Linux)
- How to generate Linux, random password encryption or decryption (Linux)
- Some security configuration of Linux systems (Linux)
- Linux configuration Samba server (Server)
- CentOS / Debian configuration Gitlab 7.1x to build self Git repository (Linux)
- Understanding the Linux load average on other UNIX-Like systems (Linux)
- Use Mop monitor stock prices at the Linux command line (Linux)
- How to manage start-up applications in Ubuntu (Linux)
- CentOS set up FTP server (Server)
- Install mono offline on CentOS (Server)
- Taught you how to install Ubuntu Linux (Linux)
- Ubuntu upgrade to Linux Kernel 4.2.3 Kernel (Linux)
- Clojure programming languages: take full advantage of the Clojure plug-in Eclipse (Programming)
- Linux Beginner Guide: Installing packages on Ubuntu and Fedora (Linux)
- Qt shared memory interprocess communication (Programming)
- Ubuntu 64-bit installation Adobe Reader 9.5.5 (Linux)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.