Oracle regularly deletes and adds table partitions (toad, PL/SQL)

From: http://blog.csdn.net/imasmallbird/archive/2008/11/20/3340547.aspx

I completed this task today and made a summary.

 

First, create a partition table.

 

Create Table mals_nm_cpu_info_t_new
(
Host_ip varchar2 (40 byte) not null,
Host_name varchar2 (15 byte) not null,
Commit_tm varchar2 (14 byte) not null,
Sys_id varchar2 (32 byte) not null,
Cpu_frequency number (10 ),
Cpu_load number (3)
)
Partition by range (commit_tm) -- use the commit_tm field as the partition condition.
(Partition p_nm_cpu_info_20081120 values less than ('20140901 '));

 

If you have already created a table but no partitions, you can only create a partition table based on the existing table, as shown below:

 

Create Table mals_nm_cpu_info_t_new
(
Host_ip varchar2 (40 byte) not null,
Host_name varchar2 (15 byte) not null,
Commit_tm varchar2 (14 byte) not null,
Sys_id varchar2 (32 byte) not null,
Cpu_frequency number (10 ),
Cpu_load number (3)
)
Partition by range (commit_tm)
(Partition p_nm_cpu_info_20081120 values less than ('20140901 '));

Insert into mals_nm_cpu_info_t_new select * From mals_nm_cpu_info_t;

Rename mals_nm_cpu_info_t to mals_nm_cpu_info_t_old;

Rename mals_nm_cpu_info_t_new to mals_nm_cpu_info_t;

Create index nm_cpu_info_index on mals_nm_cpu_info_t (commit_tm); -- creates a local partition index. By default, it is partitioned with the current table, in a tablespace.

 

After the table is created, the partition icon is displayed next to the table in Toad.

 

Second, create a storage process for adding partitions.

 

Create or replace procedure mals_nm_add_partition_proc (
Partnum number, -- number of partitions added
Tablespacename varchar2 -- partition name
)
V_sqlexec varchar2 (2000); -- DDL statement variable
V_partdate varchar2 (20); -- Date on which the partition is created (yyyymmdd)
V_partdate1 varchar2 (20); -- Date on which the partition was created (yyyymmdd)
V_err_num number; -- ora error code
V_err_msg varchar2 (100); -- error description

V_cpu_info_max varchar2 (20); -- maximum date of the mals_nm_cpu_info_t table Partition

 

Begin
-- Query the maximum Date on which the partition of the mals_nm_cpu_info_t table has been created.
-- P_nm_cpu_info_20080221
Select max (substr (partition_name, 15, 8) into v_cpu_info_max
From user_tab_partitions
Where table_name = upper ('mals _ nm_cpu_info_t ');

For I in 1 .. partnum Loop

-- Create a mals_nm_cpu_info_t table Partition
If v_cpu_info_max <to_char (sysdate + I, 'yyyymmdd') then
V_partdate: = to_char (sysdate + I, 'yyyymmdd ');
V_partdate1: = to_char (sysdate + I + 1, 'yyyymmdd ');
V_sqlexec: = 'alter table mals_nm_cpu_info_t add partition p_nm_cpu_info _ '| v_partdate |
'Values less than (''' | v_partdate1 | '000000') tablespace '|
Tablespacename;
Dbms_output.put_line ('create a mals_nm_cpu_info_t table partition '| I |' = '| v_sqlexec );
Dbms_utility.exec_ddl_statement (v_sqlexec );
End if;

End loop;
Exception
When others then
V_err_num: = sqlcode;
V_err_msg: = substr (sqlerrm, 1,100 );
Dbms_output.put_line ('mals _ nm_add_partition_proc execution exception, error code = '| v_err_num |' error description = '| v_err_msg );
End mals_nm_add_partition_proc;

 

Again, create a storage process for deleting partitions.

 

Create or replace procedure mals_nm_drop_partition_proc (
Beforedays number -- number of partitions deleted days ago
)
As
V_sqlexec varchar2 (2000); -- DDL statement variable
V_err_num number; -- ora error code
V_err_msg varchar2 (100); -- error description

-- Query the mals_nm_cpu_info_t table partition that existed before beforedays.
Cursor cursor_cpu_info_part is
Select partition_name from user_tab_partitions
Where table_name = upper ('mals _ nm_cpu_info_t ')
And substr (partition_name, 15, 8) <to_char (sysdate-beforedays, 'yyyymmdd ')
Order by partition_name;
Record_cpu_info_oldpart cursor_cpu_info_part % rowtype;

Begin
Open cursor_cpu_info_part;
Loop
Fetch cursor_cpu_info_part into record_cpu_info_oldpart;
Exit when cursor_cpu_info_part % notfound;
-- Delete the partition of the mals_nm_cpu_info_t table
V_sqlexec: = 'alter table mals_nm_cpu_info_t drop partition '|
Record_cpu_info_oldpart.partition_name;
Dbms_output.put_line ('delete mals_nm_cpu_info_t table partition = '| v_sqlexec );
Dbms_utility.exec_ddl_statement (v_sqlexec );
End loop;
Close cursor_cpu_info_part;

Exception
When others then
V_err_num: = sqlcode;
V_err_msg: = substr (sqlerrm, 1,100 );
Dbms_output.put_line ('mals _ nm_drop_partition_proc execution exception, error code = '| v_err_num |' error description = '| v_err_msg );
End mals_nm_drop_partition_proc;

 

Then, create two stored procedures to add and delete partitions.

 

Execute the added stored procedure:

Create or replace procedure mals_nm_exec_add_proc
V_err_num number; -- ora error code
V_err_msg varchar2 (100); -- error description
Begin
-- 10 indicates that the partition is created for 10 days, and tablespace indicates the tablespace name.
Mals_nm_add_partition_proc (10, 'tablespace ');
Commit;
Exception
When others then
V_err_num: = sqlcode;
V_err_msg: = substr (sqlerrm, 1,100 );
Dbms_output.put_line ('mals _ nm_drop_partition_proc execution exception, error code = '| v_err_num |' error description = '| v_err_msg );
End mals_nm_exec_add_proc;

 

Execute the deleted stored procedure:

Create or replace procedure mals_nm_exec_drop_proc
V_err_num number; -- ora error code
V_err_msg varchar2 (100); -- error description
Begin
-- Delete data 3 months ago. 100 represents 100 days.
Mals_nm_drop_partition_proc (100 );
Commit;
Exception
When others then
V_err_num: = sqlcode;
V_err_msg: = substr (sqlerrm, 1,100 );
Dbms_output.put_line ('mals _ nm_drop_partition_proc execution exception, error code = '| v_err_num |' error description = '| v_err_msg );
End mals_nm_exec_drop_proc;

 

After the above four stored procedures are created, if a Red Cross exists before a stored procedure, it just shows that there is a syntax error. In this case, if it is in PL/SQL, you can enter the stored procedure and press F8 for compilation, the console displays an error message. If you are using toad, right-click the stored procedure and select "compile" to start debugging.

 

Finally, create a job to regularly execute the Stored Procedure

 

Create or replace procedure mals_nm_jobs_proc
Job1 number; -- creates a partition at every day.
Job2 number; -- delete a partition at every day

V_err_num number; -- ora error code
V_err_msg varchar2 (100); -- error description

Begin
Dbms_job.submit (job1, 'mals _ nm_exec_add_proc; ', sysdate, 'trunc (sysdate + 1) + (1*60)/(24*60 )');
Dbms_job.submit (job2, 'mals _ nm_exec_drop_proc; ', sysdate, 'trunc (sysdate + 1) + (3*60)/(24*60 )');
Commit;

Exception
When others then
V_err_num: = sqlcode;
V_err_msg: = substr (sqlerrm, 1,100 );
Dbms_output.put_line ('mals _ nm_drop_partition_proc execution exception, error code = '| v_err_num |' error description = '| v_err_msg );
End mals_nm_jobs_proc;

After this stored procedure is created, you can run the stored procedure to regularly Delete and add partitions.

 

After the stored procedure is run, if everything is normally refreshed under PL/SQL or toad, two newly created jobs will be found in the job.

 

If a new job is not found, it indicates that a stored procedure has been compiled but there are still problems in execution.

Debug the stored procedure (in PL/SQL ).

 

First, place a breakpoint (CTRL + B) when there is a problem in the stored procedure );

Next, right-click the stored procedure and choose test ";

Then, enter the required parameters for the stored procedure below the pop-up test windows window;

Click the icon in the upper-left corner of the window, or press F9 to enter the debugging window and start debugging.

 

Remember that to debug, you must have the debugging permission. Otherwise, you need to grant the permission to the Administrator.

Grant debug connect session to user

 

Logs can also be recorded to check whether our code works normally in a system test or in the current network environment. PLSQL provides an utl_file package. By defining the file_type type in the utl_file package, you can obtain a document handle, which can be used to implement common document operations.