Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle Database Delete Delete million or more common method of heap table data     - Compile and install LNMP under CentOS 6.5 (Server)

- Gitlab installation under CentOS 7 (Linux)

- Linux input and output redirection (Linux)

- Nginx server load balancing dual-system availability (Server)

- Go powerful development server simple example (Server)

- Java garbage collection (Programming)

- Mass data storage application of MongoDB database (Database)

- A brief description of Java 8 new features introduced syntax (Programming)

- Android Studio interface-related settings (Linux)

- Linux System Getting Started Learning: The Linux command line burn ISO or NRG image to DVD (Linux)

- Use Python automatically cleared Android Engineering excess resources (Programming)

- How to Install Cantata MPD 1.3.3 for Ubuntu and Derived Version Users (Linux)

- Mistakenly deleted redo log file group being given the lead to start the database ORA-03113 (Database)

- About phpwind 5.01-5.3 0day analysis of the article (Linux)

- VirtualBox 4.3 Can not open a new task for a virtual computer solution (Linux)

- Install Unity 8 preview version of the desktop in Ubuntu (Linux)

- CentOS ClamAV antivirus package updates (Linux)

- To configure linux transparent firewall (Linux)

- Linux static library generated Guide (Programming)

- Ubuntu uses conky add desktop control (Linux)

 
         
  Oracle Database Delete Delete million or more common method of heap table data
     
  Add Date : 2018-11-21      
         
         
         
  Demand: Oracle Database delete delete heap table ten million common history.

Remove direct impact:

1. may be due to lack of space leading to the final table undo delete to fail;

2. Possible cause undo tablespace overuse, affecting other users normal operation.

Improvement program: Each row will delete 1k submitted once. (This put a big thing split into a number of small things)

NOTE: The following methods to delete all of the records prior to 2014, for example, depending on your situation changes, to prevent misuse.

method 1

declare
    . Cursor [del_cursor] is select a *, a.rowid row_id from [table_name] a order by a.rowid;
begin
    for v_cusor in [del_cursor] loop
          if v_cusor. [time_stamp] < to_date ( '2014-01-01', 'yyyy-mm-dd') then
              delete from [table_name] where rowid = v_cusor.row_id;
          end if;
          if mod ([del_cursor]% rowcount, 1000) = 0 then
              commit;
          end if;
    end loop;
    commit;
end;

Method 1 variable Explanation:

[Del_cursor] cursor name

[Table_name] you want to delete the data table

Time Field Name [time_stamp] Your filter on table

Method 2

declare
maxrows number default 1000;
delete_ct number default 0;
begin
select count (1) / maxrows into delete_ct from [table_name] where [time_stamp] < to_date ( '2014-01-01', 'yyyy-mm-dd');
for i in 1..TRUNC (delete_ct) +1
loop
delete [table_name] where [time_stamp] < to_date ( '2014-01-01', 'yyyy-mm-dd') and rownum < = maxrows;
commit;
end loop;
end;

Method 2 variable Explanation:

[Table_name] you want to delete the data table

Time Field Name [time_stamp] Your filter on table

Note

The core idea of the two methods is a big thing to split into several small things Either way, it is recommended to test in the corresponding test environment and then consider whether you can use in the actual production.

Incidentally, such a large table should be considered whether under modified ingredients zone tables.
     
         
         
         
  More:      
 
- Install Unity 8 preview version of the desktop in Ubuntu (Linux)
- Sudo and Root Account in Ubuntu related (Linux)
- Firewall settings oracle remote connection in Linux systems (Linux)
- After Pydev installation, Eclipse does not display solutions (Linux)
- Ubuntu 14.10 / 14.04 how to install Quick Start tool Mutate 2.2 (Linux)
- Python Basics: Search Path (Programming)
- map and hash_map STL containers (Programming)
- Ubuntu 14.04 + xRDP + Xfce implement Windows Remote Desktop Connection (Linux)
- Integrated security administrator Linux accident management (Linux)
- CentOS install SystemTap-2.6 (Linux)
- Java Builder mode (Programming)
- phpinfo (): like phpinfo as a Python script (Programming)
- SQLite3 simple operation (Database)
- Linux system Passwd file detailed analysis (Linux)
- Ubuntu 14.04 configure JDK1.8.0_25, switchable version (Linux)
- Hibernate learning introductory tutorial (Programming)
- How VirtualBox and VMware virtual machine conversion (Linux)
- Android components series Activity (Programming)
- Solve ORA-01012: not logged on (Database)
- Normal start Lazarus 1.0.8 under Ubuntu (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.