WMX attachments table "JTX_JOB_ATTACHMENTS" can grow very large.
The best practice is to move the table to its own filegroup / tablespace / datafile
to reduce fragmentation and contention, and to improve performance.
--create tablespaces
cd D:/postgresqldata/wmx
D:
mkdir wmx_att
mkdir wmx_att_index
DROP TABLESPACE wmx_att;
CREATE TABLESPACE wmx_att OWNER postgres LOCATION 'D:/postgresqldata/wmx/wmx_att';
DROP TABLESPACE wmx_att_index;
CREATE TABLESPACE wmx_att_index OWNER postgres LOCATION 'D:/postgresqldata/wmx/wmx_att_index';
GRANT CREATE ON TABLESPACE wmx_att TO wmx;
GRANT CREATE ON TABLESPACE wmx_att_index TO wmx;
-- move table wmx.jtx_job_attachments
select schemaname, tableowner, tablename, tablespace
from pg_tables
where tableowner = 'wmx'
order by schemaname, tableowner, tablename, tablespace;
select 'alter table '|| tableowner||'.'||tablename||' set tablespace wmx_att;' as SQLTXT
from pg_tables
where tableowner = 'wmx'
order by schemaname, tableowner, tablename, tablespace;
/*
Example:
alter table wmx.jtx_job_attachments set tablespace wmx_att;
*/
--move indexes
select schemaname,tablename, indexname, tablespace
from pg_indexes
where schemaname = 'wmx'
order by schemaname, tablename, indexname, tablespace;
select 'alter index '|| schemaname||'.'||indexname||' set tablespace wmx_att_index;' as SQLTXT
from pg_indexes
where schemaname = 'wmx'
order by schemaname, tablename, indexname, tablespace;
/*
Example:
alter index wmx.r311_sde_rowid_uk set tablespace wmx_att_index;
*/
--add tablespaces
DROP TABLESPACE GIS_ATT INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE GIS_ATT_INDEX INCLUDING CONTENTS AND DATAFILES;
--smallfile
CREATE SMALLFILE TABLESPACE "GIS_ATT"
DATAFILE '+DATA' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE SMALLFILE TABLESPACE "GIS_ATT_INDEX"
DATAFILE '+DATA' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
--bigfile
CREATE BIGFILE TABLESPACE "GIS_ATT"
DATAFILE '+DATA' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE BIGFILE TABLESPACE "GIS_ATT_INDEX"
DATAFILE '+DATA' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
--maxsize, resize
select * FROM V$DATAFILE;
select * from dba_data_files;
note: resize the datafiles to avoid fragmentation
/*
SELECT 'ALTER DATABASE DATAFILE ' || CHR(39) || file_name || CHR(39) || ' AUTOEXTEND ON MAXSIZE 2G;' AS SQLTXT
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME LIKE 'GIS_ATT%'
UNION ALL
SELECT 'ALTER DATABASE DATAFILE ' || CHR(39) || file_name || CHR(39) || ' RESIZE 2G;' AS SQLTXT
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME LIKE 'GIS_ATT%'
ORDER BY SQLTXT;
*/
--Example:
/*
ALTER DATABASE DATAFILE '+DATA/MCS1/ACF1/DATAFILE/gis_att.2464.1048514833' AUTOEXTEND ON MAXSIZE 10G;
ALTER DATABASE DATAFILE '+DATA/MCS1/ACF1/DATAFILE/gis_att.2464.1048514833' RESIZE 10G;
ALTER DATABASE DATAFILE '+DATA/MCS1/ACF1/DATAFILE/gis_att_index.2465.1048514833' AUTOEXTEND ON MAXSIZE 2G;
ALTER DATABASE DATAFILE '+DATA/MCS1/ACF1/DATAFILE/gis_att_index.2465.1048514833' RESIZE 2G;
*/
--quota
ALTER USER "WMX" QUOTA UNLIMITED ON "GIS_ATT";
ALTER USER "WMX" QUOTA UNLIMITED ON "GIS_ATT_INDEX";
--move table
SELECT 'ALTER TABLE WMX.' || TABLE_NAME || ' MOVE TABLESPACE GIS_ATT ONLINE PARALLEL 8;' AS SQLMOVE
FROM USER_TABLES
WHERE TABLE_NAME = 'JTX_JOB_ATTACHMENTS';
--Example:
/*
ALTER TABLE WMX.JTX_JOB_ATTACHMENTS MOVE TABLESPACE GIS_ATT ONLINE PARALLEL 8;
*/
--move index
SELECT 'ALTER INDEX ' || TABLE_OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE GIS_ATT_INDEX ONLINE PARALLEL 8;' AS SQLMOVE
FROM USER_INDEXES
WHERE TABLE_NAME = 'JTX_JOB_ATTACHMENTS'
ORDER BY INDEX_NAME;
--Example:
/*
ALTER INDEX WMX.R73153_SDE_ROWID_UK REBUILD TABLESPACE GIS_ATT_INDEX ONLINE PARALLEL 8;
ALTER INDEX WMX.SYS_IL0002043364C00005$$ REBUILD TABLESPACE GIS_ATT_INDEX ONLINE PARALLEL 8; --SKIP cannot rebuild - INDEX_TYPE=LOB
*/
SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE, TABLESPACE_NAME, STATUS
FROM USER_INDEXES WHERE INDEX_NAME = 'SYS_IL0002043364C00005$$';
/*
TABLE_OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE, TABLESPACE_NAME, STATUS
WMX JTX_JOB_ATTACHMENTS SYS_IL0002043364C00005$$ LOB GIS_BDATA VALID
*/
--add new filegroups and datafiles
USE [wmx]
GO
ALTER DATABASE [wmx] ADD FILEGROUP [WMX_ATT]
GO
ALTER DATABASE [wmx] ADD FILE(NAME = N'wmx_att_01',
FILENAME = N'D:\mssqldata_mcs\wmx\wmx_att_01.NDF',
SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1)
TO FILEGROUP [WMX_ATT]
GO
ALTER DATABASE [wmx] ADD FILEGROUP [WMX_ATT_INDEX]
GO
ALTER DATABASE [wmx] ADD FILE(NAME = N'wmx_att_index_01',
FILENAME = N'D:\mssqldata_mcs\wmx\wmx_att_index_01.NDF',
SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1)
TO FILEGROUP [WMX_ATT_INDEX]
GO
--By setting the datafiles initial size to 1MB, there is no delay in the creation of the FileGroups.
--You can then resize the datafiles to avoid fragmentation.
--set max size
USE [master]
GO
ALTER DATABASE [wmx] MODIFY FILE ( NAME = N'wmx_att_01', MAXSIZE = 10GB )
GO
ALTER DATABASE [wmx] MODIFY FILE ( NAME = N'wmx_att_index_01', MAXSIZE = 2GB )
GO
--resize datafiles
USE [master]
GO
ALTER DATABASE [wmx] MODIFY FILE ( NAME = N'wmx_att_01', SIZE = 10GB )
GO
ALTER DATABASE [wmx] MODIFY FILE ( NAME = N'wmx_att_index_01', SIZE = 2GB )
GO
--Verify storage
EXEC sp_helpdb [wmx]
GO
-- move Table JTX_JOB_ATTACHMENTS
--1. Generate Script for the NonClustered Index
/*
SSMS > Databases > wmx > Tables > wmx.JTX_JOB_ATTACHMENTS > Indexes >
"select the Non-Clustered Index" > Right Click >
"Script Index as" > "Create To ..." > "New Query Editor Window"
*/
--2. make the [WMX_ATT] FILEGROUP the DEFAULT filegroup
USE [wmx]
GO
ALTER DATABASE [wmx] MODIFY FILEGROUP [WMX_ATT] DEFAULT
GO
--3. this SELECT INTO will create the new table in the [WMX_ATT] FILEGROUP
USE [wmx]
GO
DROP TABLE [wmx].[JTX_JOB_ATTACHMENTS_new]
GO
SELECT * INTO [wmx].[JTX_JOB_ATTACHMENTS_NEW] from [wmx].[JTX_JOB_ATTACHMENTS]
GO
/*
--Check if new table was created on the filegroup WMX_ATT
SSMS > Databases > wmx > Tables > select "wmx.JTX_JOB_ATTACHMENTS_NEW" >
Right Click > Properties > Storage
*/
--4. make the original filegroup DEFAULT again
ALTER DATABASE [wmx] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
--5. rename table
EXEC sp_rename 'wmx.JTX_JOB_ATTACHMENTS', 'JTX_JOB_ATTACHMENTS_OLD'
GO
EXEC sp_rename 'wmx.JTX_JOB_ATTACHMENTS_NEW', 'JTX_JOB_ATTACHMENTS'
GO
--6. use the generated index statement from step 1 to create the same index, with same name, on the new table
-- change the filegroup for index to "ON WMX_ATT_INDEX"
CREATE UNIQUE NONCLUSTERED INDEX R19_SDE_ROWID_UK
ON wmx.JTX_JOB_ATTACHMENTS ([OBJECTID] ASC)
WITH (FILLFACTOR = 75, PAD_INDEX = OFF,
ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF, DATA_COMPRESSION = NONE,
DROP_EXISTING = OFF) ON WMX_ATT_INDEX;
GO
--NOTE: SET "DROP_EXISTING = OFF" OR IT WILL FAIL TO CREATE THE INDEX IN THE NEW TABLE
--7. drop the old table
DROP TABLE [wmx].[JTX_JOB_ATTACHMENTS_OLD]
GO