IF F_OBJECT_EXIST('table_name', 'PART' || V_ACCT_DAY, 'TABLE PARTITION', 'REPORT') THEN V_SQL := 'ALTER TABLE table_name TRUNCATE PARTITION PART' || V_ACCT_DAY; EXECUTE IMMEDIATE V_SQL; END IF; IF NOT F_OBJECT_EXIST('table_name', 'PART' || V_ACCT_DAY, 'TABLE PARTITION', 'REPORT') THEN V_SQL := 'ALTER TABLE table_name ADD PARTITION PART' || V_ACCT_DAY || ' VALUES (''' || V_ACCT_DAY || ''') TABLESPACE tablespace_name'; EXECUTE IMMEDIATE V_SQL; END IF --================================= 下面是 F_OBJECT_EXIST() CREATE OR REPLACE FUNCTION F_OBJECT_EXIST( OBJ_NAME VARCHAR2, SUBOBJ_NAME VARCHAR2, OBJ_TYPE VARCHAR2, OBJ_OWNER VARCHAR2 ) RETURN BOOLEAN IS RECORD_COUNTS NUMBER ; BEGIN IF SUBOBJ_NAME IS NULL THEN SELECT 1 INTO RECORD_COUNTS FROM DUAL WHERE EXISTS (SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME = UPPER(OBJ_NAME) AND OWNER = UPPER(OBJ_OWNER) AND OBJECT_TYPE = UPPER(OBJ_TYPE)); ELSE SELECT 1 INTO RECORD_COUNTS FROM DUAL WHERE EXISTS (SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME = UPPER(OBJ_NAME) AND SUBOBJECT_NAME = UPPER(SUBOBJ_NAME) AND OWNER = UPPER(OBJ_OWNER) AND OBJECT_TYPE = UPPER(OBJ_TYPE)); END IF; IF RECORD_COUNTS = 1 THEN RETURN TRUE; ELSE RETURN FALSE; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN FALSE;END F_OBJECT_EXIST;
'PART' || V_ACCT_DAY,
'TABLE PARTITION',
'REPORT') THEN
V_SQL := 'ALTER TABLE table_name TRUNCATE PARTITION PART' ||
V_ACCT_DAY;
EXECUTE IMMEDIATE V_SQL;
END IF;
IF NOT F_OBJECT_EXIST('table_name',
'PART' || V_ACCT_DAY,
'TABLE PARTITION',
'REPORT') THEN
V_SQL := 'ALTER TABLE table_name ADD PARTITION PART' ||
V_ACCT_DAY || ' VALUES (''' || V_ACCT_DAY ||
''') TABLESPACE tablespace_name'; EXECUTE IMMEDIATE V_SQL;
END IF
--=================================
下面是 F_OBJECT_EXIST()
CREATE OR REPLACE FUNCTION F_OBJECT_EXIST(
OBJ_NAME VARCHAR2,
SUBOBJ_NAME VARCHAR2,
OBJ_TYPE VARCHAR2,
OBJ_OWNER VARCHAR2
)
RETURN BOOLEAN IS
RECORD_COUNTS NUMBER ;
BEGIN
IF SUBOBJ_NAME IS NULL THEN
SELECT 1 INTO RECORD_COUNTS FROM DUAL
WHERE EXISTS
(SELECT * FROM ALL_OBJECTS
WHERE OBJECT_NAME = UPPER(OBJ_NAME)
AND OWNER = UPPER(OBJ_OWNER)
AND OBJECT_TYPE = UPPER(OBJ_TYPE));
ELSE
SELECT 1 INTO RECORD_COUNTS FROM DUAL
WHERE EXISTS
(SELECT * FROM ALL_OBJECTS
WHERE OBJECT_NAME = UPPER(OBJ_NAME)
AND SUBOBJECT_NAME = UPPER(SUBOBJ_NAME)
AND OWNER = UPPER(OBJ_OWNER)
AND OBJECT_TYPE = UPPER(OBJ_TYPE));
END IF; IF RECORD_COUNTS = 1 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF; EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;END F_OBJECT_EXIST;
2、分割数据可以考虑采用交换分区的方式将过期分区交换至按分割定义时间范围(例如按月)定义的表中,当然前提亦是源表所有索引皆为local索引。
[email protected] 谢谢!