DSXchange

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!!!

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!!!

Hi ,

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.

Or simply set the 'Update action' to 'Truncate then insert' if the clearing of the table can wait until just as the job starts.

Using a delete would leave empty blocks (holes)in the disk space.
Therefore always use the truncate facility

Regards

Sreenivasulu wrote:Using a delete would leave empty blocks (holes)in the disk space. Therefore always use the truncate facility

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.

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. Truncate is generally quick and painless, but you must be granted the ability to truncate tables that are owned by others - and that can be problematical at times, depending on the Organization and the DBA sub-culture.

OK, so you recommand me to use Truncate Facility. this table was installed by myself for the needs of the project. I don't really matter by DBA or others persons.
Can I use Truncate at the end of my job ? I'd seen that i could delete at the end of my job but what about Truncate ?

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?

Hi,
You can issue truncte table command in SQL After tab in OCI Stage or
In the Job Properties, you can issue a sql statement in the After Job Routine.

Ketfos

If it's a true Oracle temporary table just let it "expire" and it will empty itself.

Ogmios