Скачать или смотреть How to Alter Multiple Users in Oracle Using PL/SQL

Learn how to alter multiple users in Oracle with effective PL/SQL scripts or simple SQL statements. This guide helps you change default tablespaces efficiently.
---
This video is based on the question https://stackoverflow.com/q/67349519/ asked by the user 'dandon223' ( https://stackoverflow.com/u/10786029/ ) and on the answer https://stackoverflow.com/a/67349574/ provided by the user 'Littlefoot' ( https://stackoverflow.com/u/9097906/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to alter more that one user using ALTER USER in ORACLE

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Alter Multiple Users in Oracle Using PL/SQL

Are you looking to change the DEFAULT TABLESPACE for multiple users in your Oracle database but unsure how? This common requirement often arises among database administrators who need to manage user settings efficiently. In this guide, we'll explore how you can alter multiple users at once without altering the SYS user or resorting to manual changes. Let's dive into the solution!

Understanding the Requirement

Changing the default tablespace for users in Oracle can streamline how your database allocates storage. As you may know, the default tablespace is where all user objects are stored if no specific tablespace is defined during their creation. For instance, you might want to change the default tablespace to USERS for a select few users such as SCOTT and MIKE, while leaving the vital SYS user unchanged.

SQL Query to Identify Target Users

To start, you can retrieve all users except for SYS using the following SQL query:

[[See Video to Reveal this Text or Code Snippet]]

This query gives you the list of users that you want to modify, making it easy to identify who needs their default tablespace changed.

Solutions to Alter Users

You have a couple of options to achieve this goal, ranging from using PL/SQL scripts for automated changes to executing straightforward SQL queries that generate the necessary ALTER statements.

Option 1: PL/SQL Script

Using a PL/SQL script is an efficient way to loop through all desired users and make the required changes. Here’s a step-by-step breakdown of the script you might use:

[[See Video to Reveal this Text or Code Snippet]]

How It Works:

Loop Through Users: The script queries usernames that are not SYS and are included in the defined list (in this case, SCOTT and MIKE).

Dynamic SQL: Each user's default tablespace is altered dynamically, allowing you to handle multiple users with ease.

Output Information: The script also outputs the executed statements so you can see what changes are made.

Option 2: Generate ALTER Statements via SQL

If you prefer a simpler approach that avoids looping through users, you can use SQL to create ALTER statements directly:

[[See Video to Reveal this Text or Code Snippet]]

Steps:

Generate Commands: This query generates the necessary ALTER USER commands.

Copy and Execute: You can copy these statements and execute them directly in your SQL environment.

Spool Output (Optional): If you wish, you can spool this output into a file called alter_file.sql and run:

[[See Video to Reveal this Text or Code Snippet]]

Summary

Both methods provide a reliable way to alter the default tablespace for multiple users in Oracle. The PL/SQL script automates the process, making adjustments easy and efficient, while the SQL generation method allows for quick alterations without additional scripting overhead.

Conclusion

Changing the default tablespace for users in your Oracle database doesn't have to be complicated. By leveraging PL/SQL or simple SQL queries, you can efficiently manage user settings while ensuring that critical system users such as SYS remain intact. Whether you prefer automation or straightforward execution, you have the tools to effectively manage your Oracle environment.

Now, give these methods a try and streamline your user management practices in Oracle today!