How to create a new schema/new user in Oracle Database 11g?

sql

database-management

schema-creation

oracle-database

byAnton Shumikhin·Sep 12, 2024

For a new Oracle 11g schema/user, execute:

-- Abracadabra! A new user appears!
CREATE USER new_schema IDENTIFIED BY password;
-- Let the new kid in.
GRANT CREATE SESSION TO new_schema;

Here, new_schema is the username you opt for and password represents a secure password. Change them as per your requirements.

Step-by-step guide: Beyond the Basics

Grant 'em Privileges

A user with only CREATE SESSION privilege is like a book without words. Here's how to make their life more interesting:

-- Let's be generous, but not too much! 😉
GRANT RESOURCE, CONNECT, CREATE VIEW TO new_schema;

This endows the user with the power to create tables, views, and more!

Tablespaces: The custody of the schema!

In the world of Oracle, tablespaces are the eternal abodes of schema objects. Make sure to "house" your schema in the correct tablespace:

-- "Home sweet home" for all those tables and views!
ALTER USER new_schema DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

To create a new tablespace, chant:

-- A new home comes to life!
CREATE TABLESPACE new_tablespace DATAFILE 'new_tablespace.dat' SIZE 50M AUTOEXTEND ON;

The Art of Schema Creation: In-Depth

Securing the Fort

Security is paramount! Always choose a potent password for your user with IDENTIFIED BY clause:

-- You're not going to use "password123", right? 🙄
CREATE USER new_schema IDENTIFIED BY "S3cure$1";

Playing Tablespaces

To befriend tablespaces and handle object and space management, grant RESOURCE and UNLIMITED TABLESPACE:

-- Now you can CREATE things. Go wild, Picasso!
GRANT RESOURCE, UNLIMITED TABLESPACE TO new_schema;

But remember, balance is the key.

The Oracle's Whisper: DBMS_METADATA

The DBMS_METADATA package is your best friend for extracting schema objects. Especially useful when cloning and auditing:

-- Get those table beauties out!
SELECT DBMS_METADATA.GET_DDL('TABLE', 'table_name', 'user_name') FROM DUAL;

SQL Plus or SQL Developer: Take Your Pick

Both SQL Plus and SQL Developer are your allies. SQL Plus is a classic, but SQL Developer offers a more intuitive interface, wizard guides, and GUI.

Road Test the User

Once the user is created, test the login:

-- Let's see if the door opens
CONNECT new_schema/S3cure$1;

Checks if the new schema is accessible and, most importantly, usable.

User Clean-Up

To remove a user (and its associated schema), use the DROP USER command:

-- Sayonara, buddy 😭
DROP USER new_schema CASCADE;

Proceed with caution. People have been known to press enter a tad hastily!

explain-codes / Sql / How to create a new schema/new user in Oracle Database 11g?

Linked

Change Schema Name Of Table In SQL



How to get/generate the create statement for an existing hive table?



How do I find the creation date of a MySQL table?



How can I create a copy of an Oracle table without copying the data?



How to use (install) dblink in PostgreSQL?



Sqlplus statement from the command line



Db2 Query to retrieve all table names for a given schema



Step-by-step guide: Beyond the Basics The Art of Schema Creation: In-Depth