Table <-> Storage Location

Hi All,

I have a storage location which I'd like to retire and then drop.

  1. How can I findout/show which table is using this storage location?
  2. I've read in the documentation that a storage location should first be retired, then dropped.
    Can anynone comment on important things to check/do before retiring/dropping it?





  • Hi , 


    First check you storage loactions:


     select * from storage_locations;

    - and make sure you have them all labeld.



    Next query the storage_containers table for any projecitons stored in that storage location:


     select * from storage_containers 
    location_label='label of the storage to be retired';

    Or you can check the storage_policies table for any in place policies :



     select * from storage_policies;



    If you have objects that are still that storage you need to move them to another storage locaiton:


    select set_object_storage_policy('table_name', 'location_label', 'true');


    After you move all of your tables to a different locaiton you need ot change the type of the storage:



    SELECT ALTER_LOCATION_USE('fullpath' , 'node_name' , 'TEMP');


    And finally you can drop the location:

    SELECT DROP_LOCATION('fullpath' , 'node_name');

    Next see if the storage is still in your storage_locations table:

    select * from storage_locations;

    hope this helps.




  •  You cannot retire a location if is the only storage location available.

     When running retire location the data contained will be moved by Vertica using mergeouts. 

     Even if you don`t see any storage_containers under the label of your storage some data will be there(don`t know why), the retire location will make sure to move all of it to the default storage location and then you can drop it.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file