delete entire rows and load the table instead of truncate
Moderators: chulett, rschirm, roy
delete entire rows and load the table instead of truncate
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?
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?
sun
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 28
- Joined: Tue Jul 01, 2008 9:23 am
-
- Premium Member
- Posts: 28
- Joined: Tue Jul 01, 2008 9:23 am
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"
sun
I mean to say that,DBA will not be allowing us with a truncate privilage on any production tableMike 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
sun
Two problems here.suneyes wrote:Hi when I try executing this stmt(with nologging) in oracle..it is giving me an error that "statement not ended properly"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.
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
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.suneyes wrote:I mean to say that,DBA will not be allowing us with a truncate privilage on any production tableMike 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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Hi,chulett wrote: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.suneyes wrote:I mean to say that,DBA will not be allowing us with a truncate privilage on any production tableMike 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.
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.
sun
chulett wrote:Two problems here.suneyes wrote:Hi when I try executing this stmt(with nologging) in oracle..it is giving me an error that "statement not ended properly"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.
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.
sun