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.
SYSTEMis 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_USERSand 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
