Regular Table Partitioning Using Stored Procedures in Oracle databases

Oracle databases store a variety of data, some of which will become larger over time. Such as dating chat logs, SMS sending and receiving logs, production system logs, dynamic website publishing system logs, and so on. This information is closely related to time. Is there a way for these log tables to be automatically divided into historical tables (such as log200308 and log200309) by time? Please refer to the method for regularly splitting tables using stored procedures!

I. Problem Introduction

1. When you are a beginner, you only know to use delete to delete data in the table. However, in Oracle databases, after a large number of delete records, the physical space occupied by the table cannot be released. There is a concept of high water level, so we cannot use delete to split the table.

2. rename the table

(1) first create a new table (such as log_new) with the same data structure as the original log table (such as log), create constraints, indexes, and default values of specified fields;
(2) Rename the table log to log_YYYYMM;

It is important to note that the OLTP system may be unable to successfully rename the execution because of DML operations, there is an error message that the ORA-00054 resources are busy, you need to try multiple times to succeed.

(3) Rename the table log_new to log.

In this way, the application does not need to be modified (the affected time is only a few seconds), and the log table is truncated and split.

The above steps can be implemented using stored procedures in Oracle.

2. Use stored procedures to split tables

As you can see, step (2) is the key to renaming a table.

In the following rename_table process, 100 retries are performed recursively in case of lock obstruction.

Rename_table:

Create or replace procedure rename_table (Source_name in varchar2, Target_name in varchar2, Times in out number) Is Query_str varchar2 (4000 ); Source_name1 varchar2 (64 ); Target_name1 varchar2 (64 ); Cursor c1 is select segment_name from User_segments where segment_name = upper (source_name ); Dummy c1 % rowtype; Cursor c2 is select segment_name from User_segments where segment_name = upper (target_name ); Dummy2 c2 % rowtype; Begin Source_name1: = source_name; Target_name1: = target_name; Open c1; Fetch c1 into dummy; -- If c1 % found then -- Dbms_output.put_line (source_name1 | 'exist! '); -- End if; Open c2; Fetch c2 into dummy2; -- If c2 % notfound then -- Dbms_output.put_line (target_name1 | 'not exist! '); -- End if; If c2 % notfound and c1 % found then Query_str: = 'alter table' | source_name1 | 'rename to' | target_name1; Execute immediate query_str; Dbms_output.put_line ('rename success! '); End if; Close c1; Close c2; Exception WHEN OTHERS THEN Times: = times + 1; If times <100 then -- Dbms_output.put_line ('times: '| times ); Rename_table (source_name1, target_name1, times ); Else Dbms_output.put_line (SQLERRM ); Dbms_output.put_line ('error over 100 times, exit '); End if; End; /Truncate the Stored Procedure log_history for splitting the log table: Create or replace procedure log_history Is Query_str varchar2 (32767 ); Year_month varchar2 (8 ); Times number; Begin Select to_char (sysdate-15, 'yyymmm ') into year_month from dual; Times: = 0; Query_str: = 'create table log_new pctfree 10 pctused 80 As select * from log where 1 = 2 '; Execute immediate query_str; Query_str: = 'alter table log_new add constraints log _ '| year_month |' _ pk Primary key (id) tablespace indx nologging pctfree 10 '; Execute immediate query_str; Query_str: = 'alter table log_his modify logtime default sysdate '; Execute immediate query_str; Query_str: = 'create index log _ '| year_month |' _ logtime on log (logtime) Tablespace indx nologging pctfree 10 '; Execute immediate query_str; Rename_table ('log', 'log' | year_month, times ); Query_str: = 'alter table log_new rename to log '; Execute immediate query_str; End; /

Of course, the structure of the log table in your working environment may be different from that in my example. The constraints, indexes, and default values are different. You only need to make a slight modification.

3. the user must have the create any table system permission (not included in the role)

When executing a stored procedure, the permissions granted by the role will be invalid. Therefore, the user executing log_history must have the create any table system permissions granted by the DBA separately.

At last, log_history is scheduled to be executed at on the first day of every month in the OS, so that the stored procedure can periodically split the table.

If there are many log tables to be split, imitating log_history can write many similar stored procedures to split the log tables in different projects.

Then, let the OS execute these stored procedures on a monthly, weekly, or irregularly. The Administrator only needs to view the logs.

4. Other Precautions

If the application has a BUG, it may generate a lock that cannot be released for a long time when the original log table is used, and the rename of log_history will fail.

In this case, DBA can view the data dictionary:

select object_id,session_id,locked_mode from v$locked_object; select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;

If there are long-standing identical columns including Logon Time), there may be no released locks.

Before executing the storage process of the split log table, we should use the following SQL statement to kill the abnormal lock that has not been released for a long time:

alter system kill session 'sid,serial#';

V. Conclusion

It is flexible to use the stored procedure described above to regularly split the log table. Historical data is not only easy to query, but also easy to transfer and back up.

Both Unix and Windows platforms can be used. It is particularly significant for small and medium-sized enterprises with small server hard disk space.

Related Articles]

• Use of PB built-in Oracle Database Interfaces

• Understand Oracle database structure components from the inside

• Two basic concepts of Oracle