how much time should 'truncate' take?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: how much time should 'truncate' take?

  1. #1
    Join Date
    Oct 2000
    Posts
    211
    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]

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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.
    Reddy,Sam

  3. #3
    Join Date
    Oct 2000
    Posts
    211
    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

  4. #4
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Try replacing v$sqltext instead v$sql in your select and see.
    Reddy,Sam

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    First LOCK the table in exclusive mode.
    And then issue TRUNCATE command.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width