HOW TO CREATE A NEW TABLESPACE AND USER/SCHEMA IN ORACLE DATABASE (FILE SYSTEM) ?

 Hi everyone,

Suppose you receive a request from a client to create a new user/schema in the Oracle Database. Here are the steps you need to follow after connecting to Database as sys user:

STEP 1: Determine the storage type of the database (file system, ASM, etc.).

STEP 2: Identify the location of the datafiles.

STEP 3: Create a tablespace and allocate an appropriate size for its datafile.

STEP 4: Create the user and associate the newly created tablespace with the user.

STEP 5: Grant necessary permissions to the user.

STEP 6: Verify connectivity in database.

By following these steps, you will be able to successfully create a new user in the Oracle Database.

STEP 1: Determine the storage type of the database (file system, ASM, etc.).

STEP 2: Identify the location of the datafiles.

set linesize 189 pages 2000

col tabelspace_name for a30

col file_name for a50

col bytes for a30

col bytes for 999999

select tablespace_name, file_name, bytes/1024/1024 as TOTAL_SIZE_IN_MB from dba_data_files;

As we could see in the below screenshot, datafiles are stored in filesystem. Check the highlighted text, that is the physical location of datafiles.

STEP 3: Create a tablespace and allocate an appropriate size for its datafile.

MB :

====

CREATE TABLESPACE tablespace_name DATAFILE 'FULL/LOCATION/OF/THE/DATAFILE/datafilename01.dbf' SIZE 20m;

GB :

====

CREATE TABLESPACE tablespace_name DATAFILE 'FULL/LOCATION/OF/THE/DATAFILE/datafilename01.dbf' SIZE 20g;

STEP 4: Create the user and associate the newly created tablespace with the user.

CREATE USER username IDENTIFIED BY password DEFAULT TABLESPACE default_tbs_name;

select username, default_tablespace from dba_users where username = 'username';

STEP 5: Grant necessary permissions to the user.

We will focus on granting the basic privileges to a user in Oracle Database like "CONNECT" and "CREATE SESSION" which allows the user to establish a connection and create a session in the database.

The other privileges such as DDL and DML, will be discussed in a separate post.

grant connect, resource to username;

grant create session to username;

STEP 6: Verify connectivity in database.

SHOW USER;

CONN username/password;

SHOW USER;

Lets connect and check the user

Please note that this is a general guide, and specific details may vary depending on your Oracle Database setup and requirements.

Let me know if you have any questions or need further clarification.