The best way to use stored procedures in Oracle to periodically split the table. Txt

The best way to periodically use Oracle stored procedures to split tables@

Author: MF Supply: GBUinx.com (2007-02-0113: 33: 43)

A variety of data are stored in the Oracle database, and a few with the information tables will turn out to be bigger and bigger more than time. Such as logs of dating chats, logs of sending and receiving text messages, logs of production systems, logs of dynamic site publishing systems, and so forth. This information is closely associated with time. Is there a way to automatically divide these log tables into tables of historical years (for instance log200308, log200309) in accordance with time? Please have a look at how I use stored procedures to periodically divide tables.　　

1. The introduction in the challenge 　　

1. When you are new to database, you only know to make use of delete to delete the data inside the table. However, inside flash drive corrupted recovery , just after a large quantity of delete records, the physical space occupied by the table cannot be released. There's a notion of higher water level, so we can't use delete to split the table.　　

2. The strategy of renaming the table 　　

(1) 1st build a new table (for instance log_new) with the identical data structure as the original log table (if it really is log), develop constraints, indexes, and default values ​​of the specified fields; 　　

(2) Rename the table log to log_YYYYMM

The issue to be noted is that the OLTP system may possibly hinder the thriving execution in the rename as a result of DML operation. An error message seems that the ORA-00054 resource is busy. You must attempt multiple occasions to succeed.　　

( how to repair bad sectors on usb flash drive ) Rename the table log_new to log.　　

Within this way, the application doesn't have to have to be modified (the impacted time is only a number of seconds), plus the log table is truncated and split.　　

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

Second, use the stored process to split the table 　　　　　

It can be seen that inside the method of renaming the table, step (two) is actually a essential.

The following rename_table course of action will retry one hundred times recursively inside the case of lock obstruction.　　

Rename the original table for the target table stored process rename_table: 　　

createorreplaceprocedurerename_table

(source_nameinvarchar2,　

target_nameinvarchar2,　　

timesinoutnumber)

is

query_strvarchar2(4000);

source_name1varchar2(64);

target_name1varchar2(64);

cursorc1isselectsegment_namefromuser_segments　　

wheresegment_name=upper(source_name);

dummyc1%rowtype;

cursorc2isselectsegment_name

fromuser_segments　　

wheresegment_name=upper(target_name);　　

dummy2c2%rowtype;　　

begin　　

source_name1:=source_name;　　

target_name1:=target_name;　　

openc1;　　

fetchc1intodummy;　　

--ifc1%foundthen　　

--dbms_output.put_line(source_name1||‘exist!’);　　

--endif;　　

openc2;　　

fetchc2intodummy2;　　

--ifc2%notfoundthen　　

--dbms_output.put_line(target_name1||‘notexist!’);　　

--endif;　　

ifc2%notfoundandc1%foundthen　　

query_str:=‘altertable’||source_name1||‘renameto‘　　||target_name1;　　

executeimmediatequery_str;　　

dbms_output.put_line(‘renamesuccess!’);　　

endif;　　

closec1;　　

closec2;　　

exception　　

WHENOTHERSTHEN　　

instances:=times+1;　　

iftimes100then　　

--dbms_output.put_line(‘times:‘||occasions);

rename_table(source_name1,target_name1,instances);　　

else　　dbms_output.put_line(SQLERRM);　　

dbms_output.put_line(‘errorover100times,exit‘);　　

endif;　　

end;　　

/　　

Truncate the stored procedure log_history with the split log table: 　　

createorreplaceprocedurelog_history　　

is　　query_strvarchar2(32767);　　

year_monthvarchar2(eight);　　

timesnumber;　　

begin　　

selectto_char(sysdate-15,‘YYYYMMDD’)intoyear_monthfromdual;　　

occasions:=0;　　

query_str:=‘createtablelog_newpctfree10pctused80　　

asselect*fromlogwhere1=2‘;　　

executeimmediatequery_str;　　

query_str:=‘altertablelog_newaddconstraintslog_‘　　

||year_month||‘_pk　　

primarykey(id)tablespaceindxnologgingpctfree10‘;　　

executeimmediatequery_str;　　

query_str:=‘altertablelog_hismodifylogtimedefaultsysdate’;　　

executeimmediatequery_str;　　

query_str:=‘createindexlog_‘||year_month||‘_logtimeonlog(logtime)　　

tablespaceindxnologgingpctfree10‘;　　

executeimmediatequery_str;　　

rename_table(‘log‘,‘log‘||year_month,times);　　

query_str:=‘altertablelog_newrenametolog’;　　

executeimmediatequery_str;　　

end;　　

/　　

Naturally, the log table of one's operating environment may well be unique from the log table structure of my example. The constraints, indexes, and default values ​​are unique. Only a slight modification is expected.　　

Third, customers want to have createanytable method permissions (not the permissions included inside the part) 　　

Since the permissions granted by the role will be invalid when the stored procedure is executed, the user who executes log_history should have the createanytable program permission granted by the DBA separately.　　

Lastly, log_history is executed within the OS at 0:00 am on the 1st day of each and every month, as well as the stored procedure periodically divides the table.　　If there are numerous log tables to be split, imitating log_history can write numerous equivalent stored procedures to split the log tables in different projects. Then let the OS execute these stored procedures on a month-to-month, weekly, or irregular basis. The administrator only needs to verify the logs.　　

4. Other matters needing consideration 　　

When the application has a bug, it might bring about a lock that cannot be released for a lengthy time to the original log table in use. Renaming log_history are going to be unsuccessful.　　

At this time DBA can view the information dictionary: 　　

selectobject_id,session_id,locked_modefromv$locked_object;　　

selectt2.username,t2.sid,t2.serial#,t2.logon_time　　

fromv$locked_objectt1,v$sessiont2　　

wheret1.session_id=t2.sidorderbyt2.logon_time;　　

If there are actually identical columns that seem for a extended time (including login time), the lock might not be released.　　

We need to have to use the following SQL statement to kill the abnormal lock which has not been released for a long time just before executing the stored process in the split log table: 　　

altersystemkillsession‘sid,serial#‘;　　

V. Conclusion 　　

Applying the stored process described above to periodically split the log table has excellent flexibility. Historical data will not be only quick to query, but in addition easy to transfer and backup. Both Unix and Windows platforms may be applied. The significance is specially clear for smaller and medium-sized corporations with smaller server hard disk space. (http://www.fanqiang.com)