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 automatically divide these logologations automatically divided into a historical year (such as log200308, log200309)? Take a look at the method of regularly dividing the table with stored procedures.
First, the introduction of the problem
1. I only know the data in the table with Delete when I start school database. But in the Oracle database, after a large number of Delete records, the physical space occupied by the table is not allowed, and there is a high water level, so we can't use Delete to divide the table.
2. Method for renaming a rename table
(1) First build a new table (such as log_new), build constraint, index, and specified fields of the original log list (if it is a log) data structure;
(2) Rename the table log to log_yyyymm;
The problem that the OLTP system is that the OLTP system may be successful because the DML operation is hindered, and the ORA-00054 resource is busy, and it is necessary to try many times.
(3) Rename the 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.
Second, use the storage process to divide the table
You can see the method of renaming tables, step (2) is a key. The following Rename_Table process retrys 100 times in the case where there is a locked hindrance.
Rename the stored procedure RENAME_TABLE: 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;
/
The stored procedure of the cutting log table log_history:
Create Or Replace Procedure Log_History
IS
Query_Str Varchar2 (32767);
Year_Month varchar2 (8);
Times Number;
Begin
SELECT TO_CHAR (Sysdate-15, 'YYYYMMDD') 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 continraints log_'
|| Year_Month || '_pk
Primary Key (ID) TableSpace Indx Nologging Pctfree 10 ';
Execute Immediate Query_Str;
Query_str: = 'Alter Table Log_hi 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 log list of your work environment may differ from the log table structure of me, constraints, indexes, and default values. As long as you have changed a little modification.
Third, users need Create Any Table system privileges (not permissions included in the role)
Since the permissions given by the role will be invalid when the stored procedure is executed, the user who executes log_history must have the Create Any Table system permission to DBA separately.
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 execute these stored procedures on a month, on week or irregular, and the administrator can view the log.
Fourth, other precautions
If the application has bugs, it may be unsuccessful to execute the log_history rename for the long-standing lock with the original log table.
At this time, 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 is a long-term exproportional column (including login time), it may be no lock.
We must use the following SQL statement before the stored procedure of the segmentation log table, kills the long-term lock:
Alter System Kill Session 'SID, Serial #';
Five, conclude
There is a lot of flexibility in regular segmentation log tables described above. Historical data is not only easy, transfer, and backups are easy. All available in the UNIX and Windows platforms. Sub-small companies that are small for server hard disk space is particularly obvious.
