Our several tables in a database, a partition every day, but we need to clear data by date, now with "alter table table name truncate partition partition number update the indexes" statement manually removed, tired, want to go to check the partition number of date required, write into, again to perform a detailed, have a great god can help write just table name and delete date, according to the partition number directly clear data statement, thank you very much!!!!!!
Is simplified, the only input to remove the table name, commencement date, can perform "alter table table name truncate partition partition number update the indexes" statement, to go, do not need to each table to find the partition number, thank you very much for the great god help!!

CodePudding user response:

Do you encapsulate into procedure

CodePudding user response:

You can try to define a job, time to delete procedure

CodePudding user response:

But how can I just to date need to delete the partition number, from the view out slowly

CodePudding user response:

Due to the landlord didn't give partition name naming conventions, it is assumed that as: P + yyyymmdd, such as: P20171225
Create a stored procedure, please make your own test code,

 
The create or replace procedure p_partition_truncate 
(
Table_name in varchar2, - table name 
Dtime varchar2 - start date in 
) 
Is 
V_sql varchar2 (1000); 
The begin 
V_sql:='alter table' | | table_name | | 'truncate partition' | | 'P' | | dtime | | 'update the indexes'; 
The exception 
The when others then 
Raise; 
end; 

CodePudding user response:

The stored procedure to modify the:

 
The create or replace procedure p_partition_truncate 
(
Table_name in varchar2, - table name 
Dtime varchar2 - start date in 
) 
Is 
V_sql varchar2 (1000); 
The begin 
V_sql:='alter table' | | table_name | | 'truncate partition' | | 'P' | | dtime | | 'update the indexes'; 
The execute immediate v_sql; 
The exception 
The when others then 
Raise; 
end; 

CodePudding user response:

This task to do more appropriate, the stored procedure, the shell call execution

CodePudding user response:

dba_tab_partitions

CodePudding user response:

 CREATE OR REPLACE PROCEDURE P_PARTITION_TRUNCATE 
(
TABLE_NAME IN VARCHAR2, - table name 
DTIME IN VARCHAR2, - start date 
TIME_COL_NAME VARCHAR2 - date IN column name 
) IS 
V_SQL VARCHAR2 (1000); 
V_ROWID ROWID. 
V_SUBOBJECT_NAME USER_OBJECTS. SUBOBJECT_NAME % TYPE; 
The BEGIN 
V_SQL:='select Max (rowid) from' | | TABLE_NAME | | 'where' | | 
TIME_COL_NAME | | '=' ' '| | DTIME | |' ' ' '; 
The EXECUTE IMMEDIATE V_SQL 
INTO V_ROWID; 
The SELECT MAX (SUBOBJECT_NAME) 
INTO V_SUBOBJECT_NAME 
The FROM USER_OBJECTS T 
WHERE T.D ATA_OBJECT_ID IN (SELECT DBMS_ROWID. ROWID_OBJECT (V_ROWID) 
The FROM DUAL); 
V_SQL:='alter table' | | TABLE_NAME | | 'truncate partition' | | 
V_SUBOBJECT_NAME | | 'update the indexes'; 
-- DBMS_OUTPUT. PUT_LINE (V_SQL); 
The EXECUTE IMMEDIATE V_SQL; 
The EXCEPTION 
The WHEN OTHERS THEN 
RAISE; 
END; 

Don't know the name of the partition, partition, is identified by the rowid belong to code with date is a string type, if it is other types, to change slightly