Discussions

Expand all | Collapse all

How to clean up the OS disk Space after deleting the Records

  • 1.  How to clean up the OS disk Space after deleting the Records

    Posted 13 days ago
    Hi,

    Please let us know how we can release the disk space after deleting the records from Table.

    Please Note- Creating a New Table (CTAS) is not a viable option if the Table size is really big (200GB+)

    Regards,
    sumit
    #Core


  • 2.  RE: How to clean up the OS disk Space after deleting the Records

    Posted 13 days ago
    If the problem is the CTAS is failing because of lack of memory you can do with ITAS within a loop, using the rowid pseudo column to limit the number of records, so the memory needed.


  • 3.  RE: How to clean up the OS disk Space after deleting the Records

    Posted 12 days ago
    Hi @Candido Dessanti

    I am not sure OMNISCI team op​inion on this point.

    However, when we are projecting OMNISCI Database for high volume of data records and we can expect 200-300 Million Record volumes per day. Assume if need to store 30 days records in a Table then record count in a table would be in range of 8-10 Billion.

    I feel Partition Table (let assume Partition Key is Date) drop/Truncate option would be very beneficial for database management of view if Administrator to drop a Partition in fraction of seconds and release OS volume in parallel.

    Regards,
    sumit



  • 4.  RE: How to clean up the OS disk Space after deleting the Records

    Posted 9 days ago
    Hi @Sumit Srivastava,

    Being a seasoned Oracle DBA I can agree to a certain extent, but in Oracle when you drop/truncate a partition you don't recover disk space on FS/Disk, but the system just mark as free a chunk of tablespace, and it's likely that to get back the FS space you have to export/import data or do a CTAS/IAS on a different table/tablespace.

    Having already something similar to partitions (shards and fragments), it's not likely to get a range partition anytime soon.

    If you know the aprox number or records inserted daily, you can limit the number of records setting max_rows in the table (e.g. 9000M....300Mx30 days); this would limit the number of records in the table and the space allocated on disk