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 09-06-2019 04:14
    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 09-06-2019 04:23
    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 09-06-2019 23:08
    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 09-10-2019 05:55
    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