No question at this time
DBA Top 10
1 M. Cadot 3900
2 B. Vroman 700
2 P. Tsongayinwe 700
2 P. Wisse 700
5 M. Pagac 400
6 G. De Paep3 200
6 A. Deledda 200
6 D. Walgude 200
6 J. Schnackenberg 200
6 B. M 200
About
DBA-Village
Advertise here
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48757
Total active users1320
Act. users last 24h0
Act. users last hour0
Registered user hits last week24
Registered user hits last month1443
Go up

truncate data from partitions
Next thread: catbundle.sql psu apply going to hung state after applying July PSU Patch
Prev thread: Golden Gate lag

Message Score Author Date
Hi Guys I have database that does a lots of dat...... Phuti Tsongayinwe Aug 17, 2015, 12:14
ALTER TABLE ... DROP PARTITION ...? Regards ...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Michel Cadot Aug 17, 2015, 13:21
Thanks Michel... Phuti Tsongayinwe Aug 17, 2015, 13:53
or ALTER TABLE ... TRUNCATE PARTITION ... DROP S...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Aug 17, 2015, 14:24
Thanks Michel I have decided to create a proced...... Phuti Tsongayinwe Aug 18, 2015, 15:18
If the partitions are named like this matching w...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Aug 18, 2015, 15:35
Thanks a million times... Much appreciated!!! ...... Phuti Tsongayinwe Aug 18, 2015, 15:41
Hi Michel I have tried to truncate the partitio...... Phuti Tsongayinwe Nov 06, 2015, 15:57
Pléase 1/ Copy and paste what you do and get 2...... Michel Cadot Nov 06, 2015, 16:49
Thanks Michel SQL> BEGIN 2 FOR x IN (SELECT...... Phuti Tsongayinwe Nov 09, 2015, 10:11
Since your cursor only has one column (ownertab) y...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Jan Schnackenberg Nov 09, 2015, 12:36
Thanks the pl/sql procedure ran successfully. T...... Phuti Tsongayinwe Nov 09, 2015, 13:28

Follow up by mail Click here

Subject: truncate data from partitions
Author: Phuti Tsongayinwe, South Africa
Date: Aug 17, 2015, 12:14, 2961 days ago
Os info: RHEL5
Oracle info: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Message: Hi Guys

I have database that does a lots of data load, we use this database for statistics.

We have large tables that we partitioned, now we want to truncate/delete data that is 3 years old on a particular schema. This is due to the space shortage on the O/S.

How can I dynamically perform this task?

Please Help!!!

Thanks in advance...
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: truncate data from partitions
Author: Michel Cadot, France
Date: Aug 17, 2015, 13:21, 2961 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message:
ALTER TABLE ... DROP PARTITION ...?

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: truncate data from partitions
Author: Phuti Tsongayinwe, South Africa
Date: Aug 17, 2015, 13:53, 2961 days ago
Message: Thanks Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: truncate data from partitions
Author: Michel Cadot, France
Date: Aug 17, 2015, 14:24, 2961 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
or ALTER TABLE ... TRUNCATE PARTITION ... DROP STORAGE if you want to keep the partition in the table definition.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: truncate data from partitions
Author: Phuti Tsongayinwe, South Africa
Date: Aug 18, 2015, 15:18, 2960 days ago
Message: Thanks Michel

I have decided to create a procedure to truncate all partitions that hold 2010 and 2011 data on two schemas.

Will the below procedure yield the desired results?

BEGIN
FOR x IN (SELECT table_owner||'.'||table_name ownertab
FROM dba_tab_partitions
WHERE table_owner
IN ('SCHEMA1', 'SCHEMA2')
AND partition_name like 'P_2010%' or partition_name like 'P_2011%')
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE '||x.ownertab||' TRUNCATE PARTITION '||x.partition_name'|| DROP STORAGE';
END LOOP;
END;

Please help!!!

Thanks in advance...
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: truncate data from partitions
Author: Michel Cadot, France
Date: Aug 18, 2015, 15:35, 2960 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
If the partitions are named like this matching with the actual years, yes, it seems to do the job but I can't test it.

You can create empty tables with the same partitions in a test schema and execute your script.
DBMS_METADATA.GET_DDL will give you the DDL to create these tables in a few seconds.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: truncate data from partitions
Author: Phuti Tsongayinwe, South Africa
Date: Aug 18, 2015, 15:41, 2960 days ago
Message: Thanks a million times...

Much appreciated!!!

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: truncate data from partitions
Author: Phuti Tsongayinwe, South Africa
Date: Nov 06, 2015, 15:57, 2880 days ago
Message: Hi Michel

I have tried to truncate the partitions using the scripts you helped with.

I did get the error that the partition_name should be declared.

Then I wrote these:

BEGIN
FOR x IN (SELECT table_owner||'.'||table_name ownertab, table_owner||'.'||partition_name ownerpart
FROM dba_tab_partitions
WHERE table_owner
IN ('SCHEMA1', 'SCHEMA2')
AND ( table_name like '%DD' OR table_name like '%60')
AND (partition_name like 'P_2008%' or partition_name like 'P_2009%' or partition_name like 'P_2010%' or partition_name like 'P_2011%' or partition_name like 'P_2012%'))
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE '||x.ownertab||' TRUNCATE PARTITION '||x.ownerpart||' DROP STORAGE';
END LOOP;
END;

Please let me know if these is the correct syntax.

Please Help!!!

Thanks in advance!!!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: truncate data from partitions
Author: Michel Cadot, France
Date: Nov 06, 2015, 16:49, 2880 days ago
Message:
Pléase
1/ Copy and paste what you do and get
2/ Provide something we can reproduce.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: truncate data from partitions
Author: Phuti Tsongayinwe, South Africa
Date: Nov 09, 2015, 10:11, 2877 days ago
Message: Thanks Michel

SQL> BEGIN
2 FOR x IN (SELECT table_owner||'.'||table_name ownertab
FROM dba_tab_partitions
3 4 WHERE table_owner
5 IN ('CORE_GMS','APP_GMS')
6 AND ( table_name like '%DD' OR table_name like '%60')
7 AND (partition_name like 'P_2008%' or partition_name like 'P_2009%' or partition_name like 'P_2010%' or partition_name like 'P_2011%' or partition_name like 'P_2012%'))
8 LOOP
9 EXECUTE IMMEDIATE 'ALTER TABLE '||x.ownertab||' TRUNCATE PARTITION '||x.partition_name||' DROP STORAGE';
10 END LOOP;
11 END;
12 /
EXECUTE IMMEDIATE 'ALTER TABLE '||x.ownertab||' TRUNCATE PARTITION '||x.partition_name||' DROP STORAGE';
*
ERROR at line 9:
ORA-06550: line 9, column 73:
PLS-00302: component 'PARTITION_NAME' must be declared
ORA-06550: line 9, column 1:
PL/SQL: Statement ignored

Thanks in advance...
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: truncate data from partitions
Author: Jan Schnackenberg, Germany
Date: Nov 09, 2015, 12:36, 2877 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message: Since your cursor only has one column (ownertab) you won't be able to access "partition_name". Just add it to the SELECT statement.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: truncate data from partitions
Author: Phuti Tsongayinwe, South Africa
Date: Nov 09, 2015, 13:28, 2877 days ago
Message: Thanks the pl/sql procedure ran successfully.

Thanks a lot for the help...
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here