Resize tablespace in oracle

readme.md

1. Run a query similar to the following to identify if the tablespace is:

• Permanent, undo, or temporary
• Smallfile or bigfile

SQL> SELECT TABLESPACE_NAME, CONTENTS, BIGFILE FROM DBA_TABLESPACES;

2. Run a query similar to the following to check if the autoextend feature is turned on, the current size of the datafile, and the maximum configured limit:

For Permanent and undo tablespaces:

SQL> SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/(1024*1024*1024),2) SUM_GB, ROUND(MAXBYTES/(1024*1024*1024),2) MAX_GB, AUTOEXTENSIBLE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME, MAXBYTES,AUTOEXTENSIBLE;

For temporary tablespaces:

SQL> SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/(1024*1024*1024),2) SUM_GB, ROUND(MAXBYTES/(1024*1024*1024),2) MAX_GB, AUTOEXTENSIBLE FROM D

3. Move name space of the table

ALTER TABLE <TABLE NAME to be moved> MOVE TABLESPACE <destination TABLESPACE NAME>

refs

• https://aws.amazon.com/premiumsupport/knowledge-center/rds-oracle-resize-tablespace/
• https://stackoverflow.com/questions/18327671/how-to-move-table-from-one-tablespace-to-another-in-oracle-11g