Truncate Table?

[TheKing]

Anyone here know the syntax for removing all data from an Oracle table?
I have been told that it is TRUNCATE TABLE table_name [drop storage]

but that doesn't seem to work

I have been doing the DELETE FROM table_name but it takes along time. Example I had 980,000 records in the table and it took 30 min. to delete all the records in that table.

So obviously I need a much faster way of getting rid of records in an Oracle database.

Any Ideas?
T[sup]h[/sup]e[sub]K[/sub]i[sup]n[/sup]g

 

[PraveenMenon]

post the code u were using.. may b we can optimize that for faster execution... All the Best
Praveen Menon
pcmin@rediffmail.com

 

[vb5prgrmr]

Create a stored procedure and call the stored procedure or get a beefier box to hold your database.

Good Luck

BTW that is a lot of records to delete. I am surprised it did not take longer.

 

[johnwm]

Truncate should work, and be MUCH faster than delete. Note that it can't be rolled back or recovered. You need appropriate rights to do it.

See this:

http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/2_005_24.htm

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[CCLINT]

>all data

Why not just drop the table and recreate it, or use a back-up copy which holds the structure? [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[TheKing]

Thanks all for your suggestions.

CCLINT I was thinking of that same thing.

Well last night at home I got the Truncate command to work. I am filling up the table this morning and will try it later in the day and see how it goes.

Set rstURA = New ADODB.Recordset
    rstURA.CursorType = adOpenKeyset
    rstURA.LockType = adLockOptimistic
    rstURA.Open "TRUNCATE TABLE UCR_ERROR", conn, , , adCmdText

T[sup]h[/sup]e[sub]K[/sub]i[sup]n[/sup]g

 

[TheKing]

Hi all, I have another question along these lines.

Now I need to write a Truncate Command in a VBA setting against a SQL Server table.

Code just doesn't seem to be coming to me.



T[sup]h[/sup]e[sub]K[/sub]i[sup]n[/sup]g

 

[ArtieChoke]

You don't need a recordset to do the truncate, just use the execute method of the connection object. This will work in VBA also.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[TheKing]

I am using DAO and my code:

        'Do the truncate here
        Dim dbs1 As DATABASE
        Set dbs1 = CurrentDb
        dbs1.Execute "Truncate Table " & TableName

Gives me this error:

Run-time error '3078':

The Microsoft Jet databse engine cannot find the input table or query 'Truncate Table MBI'. Make sure it exists and that its name is spelled correctly.

Would this be because I am not using ADO?



T[sup]h[/sup]e[sub]K[/sub]i[sup]n[/sup]g

 

[ArtieChoke]

I suspect DAO doesn't recognize the truncate command. It's probably looking for update, delete or insert and not finding one of those, it assumes you are trying to execute a stored procedure. There is a passthrough option that keeps DAO from interpreting the command and lets it go directly to the database (but I haven't tried that to know if it's successful in this case).

At any rate, ADO would be the better option to go with for non-Access databases, IMHO.

"I think we're all Bozos on this bus!" - Firesign Theatre