Tools - How to VACUUM FULL tables of a schema

Purpose of Query

This page proposes a way to VACUUM FULL all the tables of a schema. This SQL command can help to improve the performance while executing queries, for more detailed description refer to Postgres - VACUUM

Applicable CAST Version

Query for CSS

1. Generate the Full vaccum queries:

SELECT ‘vacuum full ’ || table_name || ‘;’ FROM information_schema.tables WHERE table_schema = ‘delta_local’ ORDER BY table_schema, table_name;

   Here is an example of results [vacumm\_sample.txt](https://doc-data.castsoftware.com/TG/attachments/566526212/566526211.txt)
2. Create an sql file
3. Put the following in the created file:

   ```sql
set search_path = :schema;

4. Copy past the result of the query generated in point 1 in the sql file

5. Remove all the guillemets from the file

6. Trace the beginning of the vacuum full tables

7. Open CMD

8. Go to the location <CAST_install_director>\CSSAdmin\3rdParties\x64

9. Run the following command line:

psql –host -p -U -v schema=<schema_name> -f “<sql file generated in step 1>” -d postgres -L

   where <hostname> is the host, <username> is the username, <schema\_name> is the name of the schema <sql file generated in step 1> is the sql file generated in step 1, <log file> is log file that you can scpecified  
   Exemple:

   ```sql
psql --host localhost -p2280 -U operator -v schema=delta_local -f "C:\Users\myname\Documents\test.sql" -d postgres -L C:\Users\myname\Documents\test.log

10. In order to validate that the operation is successful, refer to the generated log file.

Query for Oracle

Enter the SQL query

Query result example

Query result interpretation

Query for SQL server

Enter the SQL query

Query result example

Query result interpretation

Notes/comments

Related Pages