Home » SQL & PL/SQL » SQL & PL/SQL » Truncate given partition (Oracle 11g, AIX 5.3)
| Truncate given partition [message #588061] |
Thu, 20 June 2013 11:42  |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Hi,
How to truncate the partition based on the given input. I've below table partitioned on dt field. How can i truncate the whole partition which contain the date 2013/04/30.
CREATE TABLE TST_SUMMARY
(
CUST_ID NUMBER(38) NOT NULL ,
DT DATE NOT NULL ,
ACCT_FLAG VARCHAR2(1),
PHONE_NUM NUMBER(15)
)
partition by range (dt)
(
partition SYS_01 values less than (to_date('2013-04-01','yyyy-mm-dd')),
partition SYS_12 values less than (to_date('2013-05-01','yyyy-mm-dd')),
partition SYS_24 values less than (to_date('2013-06-01','yyyy-mm-dd')),
partition SYS_36 values less than (to_date('2013-07-01','yyyy-mm-dd'))
);
INSERT INTO tst_summary values (1,to_date('20130423','YYYYMMDD'), 'Y', 1234567890);
INSERT INTO tst_summary values (2,to_date('20130412','YYYYMMDD'), 'Y', 5689742365);
INSERT INTO tst_summary values (1,to_date('20130502','YYYYMMDD'), 'N', 1234567890);
INSERT INTO tst_summary values (3,to_date('20130515','YYYYMMDD'), 'Y', 6987523158);
commit;
Appreciate your help!
Thanks
Sri
|
|
|
|
|
|
|
|
| Re: Truncate given partition [message #588118 is a reply to message #588061] |
Fri, 21 June 2013 02:07  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
You need to use ALTER TABLE ... TRUNCATE PARTITION statement for achieving the task. Truncating a partition is similar to dropping a partition, except that the partition is emptied of its data, but not physically dropped.
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
Connected as Lalit
SQL> CREATE TABLE TST_SUMMARY
2 (
3 CUST_ID NUMBER(38) NOT NULL ,
4 DT DATE NOT NULL ,
5 ACCT_FLAG VARCHAR2(1),
6 PHONE_NUM NUMBER(15)
7 )
8 partition by range (dt)
9 (
10 partition SYS_01 values less than (to_date('2013-04-01','yyyy-mm-dd')),
11 partition SYS_12 values less than (to_date('2013-05-01','yyyy-mm-dd')),
12 partition SYS_24 values less than (to_date('2013-06-01','yyyy-mm-dd')),
13 partition SYS_36 values less than (to_date('2013-07-01','yyyy-mm-dd'))
14 );
Table created
SQL> INSERT INTO tst_summary values (1,to_date('20130423','YYYYMMDD'), 'Y', 1234567890);
1 row inserted
SQL> INSERT INTO tst_summary values (2,to_date('20130412','YYYYMMDD'), 'Y', 5689742365);
1 row inserted
SQL> INSERT INTO tst_summary values (1,to_date('20130502','YYYYMMDD'), 'N', 1234567890);
1 row inserted
SQL> INSERT INTO tst_summary values (3,to_date('20130515','YYYYMMDD'), 'Y', 6987523158);
1 row inserted
SQL> commit;
Commit complete
Now lets truncate the partition SYS_12 which has rows of the month April-2013.
SQL> ALTER TABLE TST_SUMMARY TRUNCATE PARTITION SYS_12;
Table truncated
SQL> SELECT * FROM TST_SUMMARY;
CUST_ID DT ACCT_FLAG PHONE_NUM
--------------------------------------- ----------- --------- ----------------
1 5/2/2013 N 1234567890
3 5/15/2013 Y 6987523158
Since, we have truncated the partition, and not physically dropped, SYS_12 partition should exist.
SQL> SELECT TABLE_NAME,PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TST_SUMMARY';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TST_SUMMARY SYS_01
TST_SUMMARY SYS_12
TST_SUMMARY SYS_24
TST_SUMMARY SYS_36
Look out:-
http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin002.htm#i1008226
|
|
|
|
| Re: Truncate given partition [message #588310 is a reply to message #588118] |
Mon, 24 June 2013 15:21 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Written below procedure to truncate the partition.
PROCEDURE PR_TRUNCATE_TABLE_PARTITION(P_TABLE_NAME VARCHAR2, P_DATE DATE) IS
v_cnt pls_integer;
v_high_val date;
v_min_val date;
v_max_val date;
BEGIN
Select count(*)
into v_cnt
from user_tables
where table_name = upper(p_table_name);
if v_cnt > 0 then
v_max_val := add_months(p_date, 1);
v_min_val := add_months(v_max_val, -1);
for v_rec in (select partition_name, high_value
from user_tab_partitions
where table_name = upper(p_table_name)) loop
if v_rec.high_value != 'MAXVALUE' then
execute immediate 'begin :1 := ' || v_rec.high_value || '; end;'
using out v_high_val;
if v_high_val < v_max_val and v_high_val > v_min_val then
dbms_output.put_line('ALTER TABLE ' || p_table_name ||
' TRUNCATE PARTITION ' ||
v_rec.partition_name);
execute immediate 'ALTER TABLE ' || p_table_name || ' TRUNCATE PARTITION ' || v_rec.partition_name;
end if;
end if;
end loop;
end if;
EXCEPTION
WHEN OTHERS THEN
raise;
END;
Thanks
Sri
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Truncate given partition [message #588318 is a reply to message #588310] |
Mon, 24 June 2013 16:28 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Why so much of workaround in that PL/SQL?
All that PL/SQL code is nothing but:-
1. Finding the partition name respectively for a given date.
2. Truncate all those partitions found in step 1
To get partition name try doing this:-
select t.dt, uo.subobject_name from tst_summary t, user_objects uo where dbms_rowid.rowid_object(t.rowid) = uo.object_id;-- to get the partition names against the dates
So now you have the partition names to truncate.
|
|
|
|
|
|
|
|
| Re: Truncate given partition [message #588381 is a reply to message #588318] |
Tue, 25 June 2013 07:21 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Lalit Kumar B wrote on Mon, 24 June 2013 17:28So now you have the partition names to truncate.
Not reading docs and not looking for easy solutions? Discover PARTITION FOR syntax:
SQL> select partition_name,high_value from user_tab_partitions where table_name = 'RANGE_SALES'
2 /
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
SALES_Q1_1998 TO_DATE(' 1998-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q1_1999 TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q1_2000 TO_DATE(' 2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q2_1998 TO_DATE(' 1998-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q2_1999 TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q2_2000 TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q3_1998 TO_DATE(' 1998-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q3_1999 TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q3_2000 TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q4_1998 TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q4_1999 TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
SALES_Q4_2000 MAXVALUE
12 rows selected.
SQL> select count(*)
2 from range_sales partition(sales_q1_2000)
3 /
COUNT(*)
----------
1
SQL> alter table range_sales
2 truncate partition for(date '2000-02-15')
3 /
Table truncated.
SQL> select count(*)
2 from range_sales partition(sales_q1_2000)
3 /
COUNT(*)
----------
0
SQL>
SY.
|
|
|
|
| Re: Truncate given partition [message #588384 is a reply to message #588381] |
Tue, 25 June 2013 07:55 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Solomon Yakobson wrote on Tue, 25 June 2013 17:51 Not reading docs and not looking for easy solutions?
Sorry, I did not understand what you mean. I have already posted how to truncate the partition.
If you are highlighting the PARTITION FOR syntax, then I agree yes it gives more flexibility by just using a date value.
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
Connected as Lalit
SQL>
SQL> CREATE TABLE TST_SUMMARY
2 (
3 CUST_ID NUMBER(38) NOT NULL ,
4 DT DATE NOT NULL ,
5 ACCT_FLAG VARCHAR2(1),
6 PHONE_NUM NUMBER(15)
7 )
8 partition by range (dt)
9 (
10 partition SYS_01 values less than (to_date('2013-04-01','yyyy-mm-dd')),
11 partition SYS_12 values less than (to_date('2013-05-01','yyyy-mm-dd')),
12 partition SYS_24 values less than (to_date('2013-06-01','yyyy-mm-dd')),
13 partition SYS_36 values less than (to_date('2013-07-01','yyyy-mm-dd'))
14 );
Table created
SQL> INSERT INTO tst_summary values (1,to_date('20130423','YYYYMMDD'), 'Y', 1234567890);
1 row inserted
SQL> INSERT INTO tst_summary values (2,to_date('20130412','YYYYMMDD'), 'Y', 5689742365);
1 row inserted
SQL> INSERT INTO tst_summary values (1,to_date('20130502','YYYYMMDD'), 'N', 1234567890);
1 row inserted
SQL> INSERT INTO tst_summary values (3,to_date('20130515','YYYYMMDD'), 'Y', 6987523158);
1 row inserted
SQL> commit;
Commit complete
SQL> select t.dt, uo.subobject_name from tst_summary t, user_objects uo where dbms_rowid.rowid_object(t.rowid) = uo.object_id;
DT SUBOBJECT_NAME
----------- ------------------------------
5/15/2013 SYS_24
5/2/2013 SYS_24
4/12/2013 SYS_12
4/23/2013 SYS_12
SQL> SELECT COUNT(*) FROM tst_summary PARTITION(sys_12);
COUNT(*)
----------
2
SQL> alter table tst_summary truncate partition for(DATE '2013-04-23');
Table truncated
SQL> SELECT COUNT(*) FROM tst_summary PARTITION(sys_12);
COUNT(*)
----------
0
SQL>
Thanks to SY.
[Updated on: Tue, 25 June 2013 07:56] Report message to a moderator |
|
|
|
| Re: Truncate given partition [message #588394 is a reply to message #588384] |
Tue, 25 June 2013 09:13 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
We have partitioning column value (date in this case). We want to truncate partition that value belongs to but we do not know partition name, right? And I said why look fo partition name if all we need is PARTITION FOR syntax:
ALTER TABLE table_name TRUNCATE PARTITION FOR(partitioning_column_value);
SY.
|
|
|
|
| Re: Truncate given partition [message #588420 is a reply to message #588394] |
Tue, 25 June 2013 13:51 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Hi SY/Lait,
Thanks for giving info about PARTITION FOR. But when i try to run below getting ORA-1502 error. Do i need to re-build the index every time after truncating the partition?
CREATE TABLE TST_SUMMARY
(
CUST_ID NUMBER(38) NOT NULL ,
DT DATE NOT NULL ,
ACCT_FLAG VARCHAR2(1),
PHONE_NUM NUMBER(15)
)
partition by range (dt)
(
partition SYS_01 values less than (to_date('2013-04-01','yyyy-mm-dd')),
partition SYS_12 values less than (to_date('2013-05-01','yyyy-mm-dd')),
partition SYS_24 values less than (to_date('2013-06-01','yyyy-mm-dd')),
partition SYS_36 values less than (to_date('2013-07-01','yyyy-mm-dd'))
);
ALTER TABLE TST_SUMMARY ADD CONSTRAINT PK_TST_SUMMARY PRIMARY KEY (CUST_ID,DT)
using index
tablespace PENY_GC_INDEX;
INSERT INTO tst_summary values (1,to_date('20130423','YYYYMMDD'), 'Y', 1234567890);
INSERT INTO tst_summary values (2,to_date('20130412','YYYYMMDD'), 'Y', 5689742365);
INSERT INTO tst_summary values (1,to_date('20130502','YYYYMMDD'), 'N', 1234567890);
INSERT INTO tst_summary values (3,to_date('20130515','YYYYMMDD'), 'Y', 6987523158);
-- Create procedure to truncate partition
create or replace procedure truncate_table_partition(
p_tbl varchar2,
p_ts varchar2
)
is
begin
dbms_output.put_line('ALTER TABLE '||p_tbl ||' TRUNCATE PARTITION FOR(to_date('''||p_ts||''',''YYYYMMDD''))');
execute immediate 'ALTER TABLE '||p_tbl ||' TRUNCATE PARTITION FOR(to_date('''||p_ts||''',''YYYYMMDD''))';
exception
when others then
dbms_output.put_line(sqlerrm);
end;
/
-- Execute procedure
begin
-- Call the procedure
truncate_table_partition(p_tbl => 'TST_SUMMARY',
p_ts => '20130430');
end;
--PL/SQL procedure completed successfully
-- Now insert data into TST_SUMMARY Table
INSERT INTO tst_summary values (1,to_date('20130423','YYYYMMDD'), 'Y', 1234567890);
-- Getting error ORA-1502 : index PK_TST_SUMMARY or partition of such index is in unusable state
Thanks
Sri
|
|
|
|
| Re: Truncate given partition [message #588422 is a reply to message #588420] |
Tue, 25 June 2013 14:50 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Added code to the above procedure to re-build the partition after truncating the partition. Is this the right way of doing this?
create or replace procedure truncate_table_partition(
p_tbl varchar2,
p_ts varchar2
)
is
begin
dbms_output.put_line('ALTER TABLE '||p_tbl ||' TRUNCATE PARTITION FOR(to_date('''||p_ts||''',''YYYYMMDD''))');
execute immediate 'ALTER TABLE '||p_tbl ||' TRUNCATE PARTITION FOR(to_date('''||p_ts||''',''YYYYMMDD''))';
-- Rebuild the index
for rec in (select index_name from user_indexes where status = 'UNUSABLE' and table_name = p_tbl)
loop
execute immediate 'ALTER INDEX '||rec.index_name||' REBUILD';
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
Thanks
Sri
|
|
|
|
| Re: Truncate given partition [message #588425 is a reply to message #588420] |
Tue, 25 June 2013 14:53 |
John Watson
Messages: 8974
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:Do i need to re-build the index every time after truncating the partition? Are you looking for this bit of syntax:alter table ... truncate partition ... update indexes;
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 24 03:59:06 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
