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

Generating SQL scripts using the VALUES clause

Generating SQL Scripts Using the VALUES Clause in Oracle AI Database 26ai 🚀

Sometimes, a small SQL feature can make a big difference in developer productivity.

Oracle AI Database 26ai introduces a table value constructor for SELECT, allowing the VALUES clause to act as an inline table in the FROM or WITH clause.

Why is this useful? You can work with a small set of values directly in SQL without creating a physical table or writing multiple UNION ALL statements.

💡 A practical use case: Dynamically generating SQL scripts

Suppose you're building Oracle database demos or lab environments and need to create database users with specific privileges.

Instead of manually writing every GRANT statement, you can generate them dynamically:

select
    'grant ' || privilege || ' to  singhsha;'
from
    (
        values
            ('create assertion'),
            ('create domain'),
            ('create job'),
            ('create mle'),
            ('create session'),
            ('debug connect session'),
            ('execute dynamic mle'),
            ('create materialized view'),
            ('create procedure'),
            ('create property graph'),
            ('create sequence'),
            ('create synonym'),
            ('create table'),
            ('create trigger'),
            ('create type'),
            ('create view')
    ) p (privilege);

The VALUES clause creates an inline table named p, with a column called privilege. The SELECT statement then generates a GRANT command for each row.

The result? A ready-to-review list of SQL statements that can be incorporated into an initialization script, checked into Git, and reused when setting up demo environments.

Which results in this neat script:

grant create assertion to singhsha; grant create domain to singhsha; grant create job to singhsha; grant create mle to singhsha; grant create session to singhsha; grant debug connect session to singhsha; grant execute dynamic mle to singhsha; grant create materialized view to singhsha; grant create procedure to singhsha; grant create property graph to singhsha; grant create sequence to singhsha; grant create synonym to singhsha; grant create table to singhsha; grant create trigger to singhsha; grant create type to singhsha; grant create view to singhsha;

After careful review, it can be deployed to my demo/lab environment and checked into Git.

Why I like this approach

✅ Less boilerplate SQL

✅ Better readability and maintainability

✅ No need for repetitive UNION ALL queries

✅ Convenient generation of scripts for database initialization, migrations, and administrative tasks

The generated statements should always be reviewed before execution, particularly when dealing with database privileges.

The takeaway: The VALUES clause isn't just a convenient way to define inline data. In Oracle AI Database 26ai, it can also simplify the process of generating SQL scripts dynamically.

📚 Explore the Oracle AI Database 26ai documentation to learn more about the VALUES clause.

Happy scripting!

#OracleDatabase #Oracle26ai #SQL #DatabaseDevelopment #Automation #PLSQL