Delete rows in table Oracle
Moderators: chulett, rschirm, roy
Delete rows in table Oracle
At the end of a job, I want to clean a temporary table in oracle. I need to keep this table but a cleaning is necessary. Indeed, If we need to start again the job, data will be add to the table and data of my job of front are too old. Help me please!!!
-
- Participant
- Posts: 84
- Joined: Thu Dec 04, 2003 9:55 pm
Re: Delete rows in table Oracle
Hi ,besco wrote:At the end of a job, I want to clean a temporary table in oracle. I need to keep this table but a cleaning is necessary. Indeed, If we need to start again the job, data will be add to the table and data of my job of front are too old. Help me please!!!
If your talking about a temporary table and if you are using ODBC stage,you can DROP and CREATE table for each run.
In ORACLE Stage, Ifyou need to keep the table structure then, you can use a User Defined Query and in which you could use "DELETE FROM <TableName>".
Hope this helps.
Thanks & Regards
SMK
Just issue the truncate statement from a sqlplus script. This will preserve the table, grants, indices, and statistics. The dropping and recreating of a table is not a great choice for the above mentioned reasons.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
I think this is much less of an issue nowadays with modern storage sub-systems and improvements made in underlying database structures and space management like that built into Oracle. It does a much better job of handling fragmentation/extents and free space than it did in the past.Sreenivasulu wrote:Using a delete would leave empty blocks (holes)in the disk space. Therefore always use the truncate facility
More of an issue, to me at least, is the fact that the 'delete' is transactional - it must be logged and use rollback / redo space, can take quite a bit of time on a large table and even possibly fail.
![Shocked :shock:](./images/smilies/icon_eek.gif)
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
If you own the table, then you should be able to truncate it. If you know how to delete from it 'after job' then you know how to truncate it as well, it's just a slightly different SQL statement.
So, your answer should be Yes.
That being said, I still don't see this as a Good Practice.
Seems like it could be a little... dangerous. Depending on exactly what your job is doing, of course, and what comes after in the job stream. Are you sure the emptying of this table can't wait until the start of the next job run?
![Wink :wink:](./images/smilies/icon_wink.gif)
That being said, I still don't see this as a Good Practice.
![Confused :?](./images/smilies/icon_confused.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers