Table <-> Storage Location

yardenbar

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?

 

Thanks,

Yarden

Adrian_Oprea_1

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 
where 
   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.

 

 

 

Adrian_Oprea_1

 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.