how much time should 'truncate' take?

[manjunathk]

Hi friends,
I gave a command to truncate a partitioned table containg almost a billion records. Its almost 45 minutes but still the process is going on.The table was partitioned in to 45 partitions(based on range) and each partition was further subpartitioned into 256 based on a hash
Can someone tell me how much time should it take to truncate? For another table I used directly the drop command, but again this also is in the same state( i mean the command still seems to be executing)
My database is 8.1.6 (parallel server )on a compaq tru-64.
Also, how to confirm that the trucate or drop actions are being performed successfully? I tried v$transaction,but it does not have any info. Even the alert file, I think will get the information once the action is completed.
Thanks
manjunath


[Edited by manjunathk on 05-08-2001 at 06:17 PM]

[sreddy]

I guess its depend on the resources on the server. Hope your Oracle Server is not on MTS, if it is it takes lot of time.

Try giving a count(*) on the table and see the number of rows existys currently.

[manjunathk]

Thanks Reddy.
I have tried to count(*), but that command also seems to take endless time.
The following should return the truncate or drop commands along with other details, right?
SELECT OSUSER,
SERIAL#,
SQL_TEXT
FROM V$SESSION, V$SQL
WHERE V$SESSION.SQL_ADDRESS = V$SQL.ADDRESS
AND V$SESSION.STATUS = 'ACTIVE';
But the drop and truncate statements are not shown in the output. Is there a way to ensure that the commands are being performed at all?
Thanks
manjunath

[sreddy]

Try replacing v$sqltext instead v$sql in your select and see.

[tamilselvan]

First LOCK the table in exclusive mode.
And then issue TRUNCATE command.