A variety of data is placed in the Oracle database, with some data sheets, more and more. Such as dating chat, SMS send and receive logs, production system logs, dynamic website publishing system logs, etc. Such information is closely related to time. Is there a way to allow these logistics to automatically divide a table of history (such as log200308, log200309)? Take a look at the method of regularly dividing tables with stored procedures. First, the problem of the problem 1. only know the data in the table with Delete when you begin a database. But in the Oracle database, after a large number of Delete records, you cannot release the physical space occupied by the table, which has a high-level concept, so we can't use delete to divide the table. 2. Methods (1) with a rename table (1). First build a new table (such as log_new), build constraint, index, and designated fields; (2). Renown Table Log to log_yyyymm; what to pay attention to is that the OLTP system may be successful because DML operations hinder renames, and the ORA-00054 resource is busy, and you need to try multiple times. (3). Renown table log_new to log. This application does not have to modify (only a few seconds from the affected time), and the log table is cut off. The above steps can be implemented in Oracle to implement them with a stored procedure. Second, use the stored procedure to segment the table to see the method of the renaming table, step (2) is a key. The following Rename_Table process retrys 100 times in the case where there is a locked hindrance.
Rename the original table to the target table stored procedure 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 Ipen 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% NOT found 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'); Endness;
/ Truncated split stored procedure log_history 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, 'YYYYMM') 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, your work environment log table may be different from the log table structure of me, constraint conditions, index, and default values. That is the same. As long as you have changed a little modification. Third, the user needs to have Create Any Table System Permissions (not the permissions included in the role) Because the permissions given by the role will be invalid when the stored procedure is executed, so the user who executes log_history must have a CREATE ANY TABLE system permission to DBA. . Finally, the log_history is performed at 0:00 in the morning of the OS, allowing the storage procedure to regularly segment the table. If there are many log tables to be divided, imitation log_history can write a lot of similar stored procedures to split log tables in different projects. Then let the OS will perform these stored procedures on a month, on week or from time to time, and the administrator can use the log. 4. Other considerations If the application has bugs, it may be unsuccessful to execute the log_history rename without the long-term lock with the original log list.
