DSXchange

I have a business rule that we wont be having truncate option on a production table.
Can I delete entire rows before loading the table instead of truncate in an ORACLE stage.
We can do it as a two step process but,can it be done in a single stage?

Oracle Enterprise stage? Use the OPEN command for that.

Resist stupid requirements. DELETE all rows is transactional, and takes a long time. TRUNCATE is non-transactional and is fast.

Business rule? The business users that I've known wouldn't utter any technical jargon like "truncate table".

If your DBAs are concerned about granting truncate privileges, they can always create a stored procedure and grant you access to that.

Mike

"The business has a rule..."

You can issue user defined query ' delete from tablename with nologging" that will do couple of things

1. It will not make log
2. It is very fast compared to normal delete.

Rgds,
Chakradhar.

You can issue user defined query ' delete from tablename with nologging" that will do couple of things

1. It will not make log
2. It is very fast compared to normal delete.

Rgds,
Chakradhar.

gxhpainter wrote:You can issue user defined query ' delete from tablename with nologging" that will do couple of things

1. It will not make log
2. It is very fast compared to normal delete.

Rgds,
Chakradhar.

Hi when I try executing this stmt(with nologging) in oracle..it is giving me an error that "statement not ended properly"

Mike wrote:Business rule? The business users that I've known wouldn't utter any technical jargon like "truncate table".

If your DBAs are concerned about granting truncate privileges, they can always create a stored procedure and grant you access to that.

Mike

I mean to say that,DBA will not be allowing us with a truncate privilage on any production table

suneyes wrote:

gxhpainter wrote:You can issue user defined query ' delete from tablename with nologging" that will do couple of things

1. It will not make log
2. It is very fast compared to normal delete.

Rgds,
Chakradhar.

Hi when I try executing this stmt(with nologging) in oracle..it is giving me an error that "statement not ended properly"

Two problems here.

1) That syntax is invalid. Remove "with" from the sql.
2) The advice is poor because 'normal' DML like deletes or inserts always log and the 'nologging' is ignored. Never mind the fact that not logging (and thus not having a mechanism to roll-back) kind of defeats the purpose of using a transactional delete.

A couple of references for the curious:

http://www.scribd.com/doc/3633656/Redo-paper-nologging

http://www.jlcomp.demon.co.uk/faq/nologging.html

suneyes wrote:

Mike wrote:Business rule? The business users that I've known wouldn't utter any technical jargon like "truncate table".
If your DBAs are concerned about granting truncate privileges, they can always create a stored procedure and grant you access to that.

I mean to say that,DBA will not be allowing us with a truncate privilage on any production table

Pretty unusual to have a need to truncate a core production table. However, as Mike notes, if you really need the ability for a table your DBA can create a stored procedure that can truncate that (or a restricted list) table and grant your user permissions to execute it. Very common solution when you need this functionality.

chulett wrote:

suneyes wrote:

Mike wrote:Business rule? The business users that I've known wouldn't utter any technical jargon like "truncate table".
If your DBAs are concerned about granting truncate privileges, they can always create a stored procedure and grant you access to that.

I mean to say that,DBA will not be allowing us with a truncate privilage on any production table

Pretty unusual to have a need to truncate a core production table. However, as Mike notes, if you really need the ability for a table your DBA can create a stored procedure that can truncate that (or a restricted list) table and grant your user permissions to execute it. Very common solution when you need this functionality.

Hi,
the table in a temporary table for storing data required for some jobs which needs to be truncated and Loaded before we execute these jobs.the DBA part which youve specified needs to be checked with my DBA.
Thanks for the reply.

chulett wrote:

suneyes wrote:

gxhpainter wrote:You can issue user defined query ' delete from tablename with nologging" that will do couple of things

1. It will not make log
2. It is very fast compared to normal delete.

Rgds,
Chakradhar.

Hi when I try executing this stmt(with nologging) in oracle..it is giving me an error that "statement not ended properly"

Two problems here.

1) That syntax is invalid. Remove "with" from the sql.
2) The advice is poor because 'normal' DML like deletes or inserts always log and the 'nologging' is ignored. Never mind the fact that not logging (and thus not having a mechanism to roll-back) kind of defeats the purpose of using a transactional delete.

A couple of references for the curious:

http://www.scribd.com/doc/3633656/Redo-paper-nologging

http://www.jlcomp.demon.co.uk/faq/nologging.html

Hi chulet,
Thanks for the info..it worked for me..I am running the job without logging.

You must have missed the "nologging is ignored" part, but ok - whatever works for you.