Oracle Database Tip: Generate SQL Scripts to Change User Default Tablespaces 🚀

 Have you ever needed to change the default tablespace for multiple Oracle database users without manually writing individual ALTER USER statements?

Here’s a simple SQL*Plus scripting technique that can save time when working with database administration tasks.

The idea is to generate the SQL commands dynamically from DBA_USERS, spool them into a .sql file, and execute them only after reviewing the generated statements.

🔹 1. Generate a script to alter users to the SYSTEM tablespace

SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 32767
SET TRIMSPOOL ON
SET VERIFY OFF
SET TERMOUT OFF

SPOOL alter_non_oracle_users_to_system.sql

SELECT 'ALTER USER "' || username ||
       '" DEFAULT TABLESPACE SYSTEM;'
FROM   dba_users
WHERE  oracle_maintained = 'N'
AND    default_tablespace <> 'SYSTEM'
ORDER  BY username;

SPOOL OFF

This script generates ALTER USER statements for non-Oracle-maintained users whose current default tablespace is not SYSTEM.

🔹 2. Generate a script to restore the original default tablespaces

Before changing the users, it's useful to preserve their current default tablespace assignments.

SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 32767
SET TRIMSPOOL ON
SET VERIFY OFF
SET TERMOUT OFF

SPOOL restore_non_oracle_users_default_ts.sql

SELECT 'ALTER USER "' || username ||
       '" DEFAULT TABLESPACE "' ||
       default_tablespace || '";'
FROM   dba_users
WHERE  oracle_maintained = 'N'
AND    default_tablespace <> 'SYSTEM'
ORDER  BY username;

SPOOL OFF

This produces a separate SQL file containing the original default tablespace assignments for the users currently matching the filter.

💡 Why is this approach useful?

• Automation: Generate commands for multiple users in one query.
• Consistency: Apply the same change across the intended set of accounts.
• Reversibility: Keep a separate script to restore the captured tablespace assignments.
• Maintainability: Store generated scripts for review, auditing, and controlled execution.

⚠️ Important considerations

• Changing a user's default tablespace does not move existing objects or change their tablespace placement.
• SYSTEM is a critical Oracle-managed tablespace and should generally not be used as the default tablespace for application users. Prefer an appropriate application tablespace.
• Generate and review the restoration script before changing the users. The restoration query must be run against the original assignments; after the change, the original tablespace values are no longer available from that query.
• These scripts depend on access to DBA_USERS and should be tested in a non-production environment first.

The takeaway: A simple SQL query combined with SQL*Plus SPOOL can turn repetitive DBA work into a reusable, reviewable SQL script.

💬 Do you use SQL generation to automate Oracle Database administration tasks?

#OracleDatabase #OracleDBA #SQL #SQLPlus #DatabaseAdministration #Automation