Environment information:
* Oracle version: 10.2.0.3
* Operating system: Red Hat PPC 64
Gentlemen,
]
Good morning,
I need to make an purge in a table that has 696.276.322 lines.
My instance works 24x7x365
and the table that contains the data I need to do the purge is very accessible at any time.
I need to make the ombematus of +/- 10,000,000 of lines.
I have tried to do with a simple delete with a filter by a date range that need to delete. But it was taking too long, besides locking the table at a few moments. Outside archive generation that increased considerably.
and after deletes I would have to run some DBMs to return the deleted space to the disk, since the delete does not return the space to the disk, only erases the records what would utter the inverse in the case of a truncate.
My question: Is there a more efficient and faster way to do this purge?
Any tips? Suggestion?
Thanks.
Delete - Large number of records
-
- Rank: Programador Sênior
- Posts: 51
- Joined: Wed, 12 Mar 2008 2:11 am
- Location: São Bernardo - SP
Ricardo Moreno
DBA Oracle Pleno
“Só sei que nada sei, e o fato de saber isso, me coloca em vantagem sobre aqueles que acham que sabem alguma coisa.”
(Sócrates 470 a.C – 399 a.C)
DBA Oracle Pleno
“Só sei que nada sei, e o fato de saber isso, me coloca em vantagem sobre aqueles que acham que sabem alguma coisa.”
(Sócrates 470 a.C – 399 a.C)
-
- Rank: DBA Sênior
- Posts: 372
- Joined: Tue, 24 Jan 2006 3:33 pm
- Location: Palmas - TO
- Contact:
Exist restrictions on this object? (Trigger / PLSQL / Constraints)ricardocmorene wrote: Environment information:
* Oracle version: 10.2.0.3
* Operating system: Red Hat PPC 64
] Gentlemen,
Good morning,
I need to make an purge in a table that has 696.276.322 lines.
My instance works 24x7x365
and the table that contains the data I need to do the purge is very accessible at any time.
I need to make the ombematus of +/- 10,000,000 of lines.
I have tried to do with a simple delete with a filter by a date range that need to delete. But it was taking too long, besides locking the table at a few moments. Outside archive generation that increased considerably.
and after deletes I would have to run some DBMs to return the deleted space to the disk, since the delete does not return the space to the disk, only erases the records what would utter the inverse in the case of a truncate.
My question: Is there a more efficient and faster way to do this purge?
Any tips? Suggestion?
Thanks.
You can enable, operate, and then rehabilitate again.
You have [url=http://download.oracle.com/docs/cd/B193 ... m#i2112841]WITH READ ONLY
You can use the Rownum clause on your filter and delete gradually.
The PL / SQL galera can help here TB, there are tuning techniques in this area.
Another alternative is to create an equal object filtering the required lines, truncates the old one and create a synonym for the new (I think we can rename, I do not know for sure!)
1. Crie uma nova tabela
create table <newTable> as select * from <oldTable> where ...
2. Truncar e Deletar a antiga
truncate oldtable --sem necessidade de segmento de rollback
drop oldtable
3. Renomeie
rename <newTable> para <oldTable>
4. Crie índices necessários
create the index.
-
- Rank: Programador Sênior
- Posts: 51
- Joined: Wed, 12 Mar 2008 2:11 am
- Location: São Bernardo - SP
Ricardo Moreno
DBA Oracle Pleno
“Só sei que nada sei, e o fato de saber isso, me coloca em vantagem sobre aqueles que acham que sabem alguma coisa.”
(Sócrates 470 a.C – 399 a.C)
DBA Oracle Pleno
“Só sei que nada sei, e o fato de saber isso, me coloca em vantagem sobre aqueles que acham que sabem alguma coisa.”
(Sócrates 470 a.C – 399 a.C)
I opened a service request next to Oracle asking for a brief and follows the answer.
I will make the first option.
Please see the action plan below.
Thanks,
Hazem Ibrahim
Global Customer Services
ACTION PLAN
===========
The following options can be used to delete a large number of records from a table:
1) The ideal solution is to convert the table from non-partitioned to a partitioned table by day (or by week, by month - for
period which you want delete/drop), and instead of delete rows, you can drop
the old partition which you don't want to keep. You can drop or place its
tablespace in offline mode, By this you keep data, without any access. This can
be done *online* using DBMS_Redefinition package.
please refer to the following:
Note 472449.1 - How To Partition Existing Table Using DBMS_Redefinition
Note 177407.1 - How to Re-Organize a Table Online
Oracle® Database Administrator's Guide
10g Release 2 (10.2)
Part Number B14231-02
15 Managing Tables
Example 1 Redefines a table by adding new columns and adding partitioning
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#CJAEBFJA
2) You can break the delete operation to many by deleting a certain number of rows each time:
Ex:
SQL> delete from big_table
where <condition>
and rownum < 10000;
** The above will delete 10000 rows at a time
3) You can export/import
a) Export the table with query option
b) Truncate the table
c) Import the table back
However this operation has a higher risk and will not allow access to the table for some
time
-
- Rank: DBA Sênior
- Posts: 372
- Joined: Tue, 24 Jan 2006 3:33 pm
- Location: Palmas - TO
- Contact:
Well, you did not make it clear that you had this optional (which only exists in the Enterprise version ).ricardocmorene wrote:
1) The ideal Solution is to convert the table from non-partitioned to the partitioned table by day (or by month, by month - for
Period Which You Want Delete / Drop), and instead of delete rows, you can drop
The Old Partition Which You Do not Want to Keep. You can Drop or Place ITS
Tablespace in offline mode, by this you keep date, without any access. This Can
Be Done * Online * Using DBMS_REDEFINITION PACKAGE.
[] s
So I passed you as usual in these cases!
In any case, post here the procedures you have adopted (of course, if you do not infrigure some standard of your company!), So we can have as a reference.
Gilberto
-
- Rank: Programador Sênior
- Posts: 51
- Joined: Wed, 12 Mar 2008 2:11 am
- Location: São Bernardo - SP
Ricardo Moreno
DBA Oracle Pleno
“Só sei que nada sei, e o fato de saber isso, me coloca em vantagem sobre aqueles que acham que sabem alguma coisa.”
(Sócrates 470 a.C – 399 a.C)
DBA Oracle Pleno
“Só sei que nada sei, e o fato de saber isso, me coloca em vantagem sobre aqueles que acham que sabem alguma coisa.”
(Sócrates 470 a.C – 399 a.C)
Firmness ...
I am doing the procedures for two days because the table is very large.
even working in Parallel still takes a little ...
As soon as I get the result I will post here for the staff ..
Abçs
I am doing the procedures for two days because the table is very large.
even working in Parallel still takes a little ...
As soon as I get the result I will post here for the staff ..
Abçs
-
- Information
-
Who is online
Users browsing this forum: No registered users and 11 guests