Create tablespace and sign it to a user with all privileges

Syntax

SQL> ALTER USER <username> QUOTA 100M ON <tablespace name>;

SQL> GRANT UNLIMITED TABLESPACE TO <username>;
SQL> grant connect, resource to <username>;

Example:

- connect as sysdba

$ sqlplus / as sysdba 

- list tablespaces in database
 SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------SYSTEMSYSAUXUNDOTBS1TEMPUSERSEXAMPLEAPEX_1930613455248703APEX_2041602962184952APEX_2610402357158758APEX_2611417663389985APEX_2614203650434107- 

- list datafiles  in your database
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------/home/oracle/app/oracle/oradata/orcl/users01.dbf/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf/home/oracle/app/oracle/oradata/orcl/system01.dbf/home/oracle/app/oracle/oradata/orcl/example01.dbf/home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf/home/oracle/app/oracle/oradata/orcl/APEX_2041602962184952.dbf/home/oracle/app/oracle/oradata/orcl/APEX_2610402357158758.dbf/home/oracle/app/oracle/oradata/orcl/APEX_2611417663389985.dbf/home/oracle/app/oracle/oradata/orcl/APEX_2614203650434107.dbf      COL1

- create a new tablespace 'sales'

SQL> create tablespace sales datafile '/home/oracle/app/oracle/oradata/orcl/sales01.dbf' size 100M;

- add one more datafile 'sales02.dbf'  to 'sales' tablespace

SQL> alter tablespace sales add datafile '/home/oracle/app/oracle/oradata/orcl/sales02.dbf' size 50M;

- list datafiles with it's IDs
SQL> select file_name,file_id from dba_data_files;
FILE_NAME                                                                        FILE_ID
----------------------------------------------------------------------------------- ----------
/home/oracle/app/oracle/oradata/orcl/users01.dbf                                         4
/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf                                     3
/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf                                       2
/home/oracle/app/oracle/oradata/orcl/system01.dbf                                       1
/home/oracle/app/oracle/oradata/orcl/example01.dbf                                    5
/home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf            6
/home/oracle/app/oracle/oradata/orcl/APEX_2041602962184952.dbf            7
/home/oracle/app/oracle/oradata/orcl/APEX_2610402357158758.dbf            8
/home/oracle/app/oracle/oradata/orcl/APEX_2611417663389985.dbf            9
/home/oracle/app/oracle/oradata/orcl/APEX_2614203650434107.dbf           10

/home/oracle/app/oracle/oradata/orcl/sales01.dbf                                        11

- resize datafile 'sales02.dbf'  from 50M to 100M we need to know datafile ID as previous 

SQL> alter database datafile 12 resize 100M;

SQL> select bytes/1024/1024 datafile_MB from dba_data_files where file_id=12;

- create a user 'dbaslaes'

SQL> create user dbasales identified by oracle

2 default tablespace sales

3 temporary tablespace temp

4 profile dba-sales  - as you create dba-sales profile before this

5 account unlock

6  password expire;

- grant user dbasales all privileges at once on 'sales' tablespace 

SQL> alter user dbasales quota unlimited on sales;

SQL> grant unlimited tablespace to dbasales;

SQL> grant connect, resource to dbasales;

- test granted privileges to user dbasales

SQL> connect dbasales/oracle - it will ask to change password and reconnect again 

SQL> connect dbasales/salespass

Connected.

SQL> create table test1 (

  2  col1 number

  3  );

Table created.

SQL> insert into test1 values (20);

1 row created.

SQL> select * from test1;

----------

20

- drop tablespace 'sales'  with it's contents and datafiles

$ sqlplus / as sysdba 

SQL> drop tablespace sales including contents and datafiles;