DROP TABLESPACE usertemp1;

############################
create user $$user$$ identified by $$password$$
default tablespace $$defaultTablespace$$
temporary tablespace $$temporaryTablespace$$
quota unlimited on $$defaultTablespace$$

CREATE USER ${user} 
     IDENTIFIED BY ${password}
     DEFAULT TABLESPACE ${defaultTablespace}
     TEMPORARY TABLESPACE ${temporaryTablespace}
     QUOTA UNLIMITED ON ${defaultTablespace}

CREATE USER ${user} 
     IDENTIFIED BY ${password}
     DEFAULT TABLESPACE ${defaultTablespace}
     TEMPORARY TABLESPACE ${temporaryTablespace}
     QUOTA UNLIMITED ON ${defaultTablespace}

CREATE USER ${user} 
     IDENTIFIED BY ${password}
     DEFAULT TABLESPACE ${defaultTablespace}
     TEMPORARY TABLESPACE ${temporaryTablespace}
     QUOTA UNLIMITED ON ${defaultTablespace}

CREATE USER ${user} 
     IDENTIFIED BY ${password}
     DEFAULT TABLESPACE ${defaultTablespace}
     TEMPORARY TABLESPACE ${temporaryTablespace}
     QUOTA UNLIMITED ON ${defaultTablespace}

select distinct tablespace_name from dba_segments
order by tablespace_name

select distinct tablespace_name from dba_segments
order by tablespace_name

select distinct tablespace_name from dba_segments
order by tablespace_name

CREATE USER ${user} 
     IDENTIFIED BY ${password}
     DEFAULT TABLESPACE ${defaultTablespace}
     TEMPORARY TABLESPACE ${temporaryTablespace}
     QUOTA UNLIMITED ON ${defaultTablespace};

CREATE USER ${user} 
     IDENTIFIED BY ${password}
     DEFAULT TABLESPACE ${defaultTablespace}
     TEMPORARY TABLESPACE ${temporaryTablespace}
     QUOTA UNLIMITED ON ${defaultTablespace};

SELECT A.tablespace_name "Tablespace Name",
       round((sum(a.bytes)/(1024 * 1024)),2) "Free Mb"
from user_free_space a

CREATE USER "MAIL" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "MAIL" TEMPORARY TABLESPACE "TEMP" ACCOUNT ...  
GRANT UNLIMITED TABLESPACE TO "MAIL";
GRANT "CONNECT" TO "MAIL";

CREATE USER "MAIL" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "MAIL" TEMPORARY TABLESPACE "TEMP" ACCOUNT ...  
GRANT UNLIMITED TABLESPACE TO "MAIL";
GRANT "CONNECT" TO "MAIL";

CREATE USER TEMPORARY TABLESPACE usertemp1 MANAGED BY SYSTEM USING ('usertemp');

CREATE USER AWARDS IDENTIFIED BY "awd1234" DEFAULT TABLESPACE "AWARDS" ACCOUNT UNLOCK;
GRANT 
   CREATE SESSION, ALTER SESSION, CREATE TABLE,

/* This finds out the lowest percentage-to-go figure */
/* from amongst all the tablespaces */
SELECT  min((100 - (round((100 * (b.used / a.total)), 2)))) pct_to_go
FROM    (SELECT  tablespace_name, SUM (bytes) total

/* This finds out the lowest percentage-to-go figure */
/* from amongst all the tablespaces */
SELECT  min((100 - (round((100 * (b.used / a.total)), 2)))) pct_to_go
FROM    (SELECT  tablespace_name, SUM (bytes) total

CREATE USER XMLSAMPLES IDENTIFIED BY password
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK;

CREATE USER XMLSAMPLES IDENTIFIED BY password
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK;

select 'atot' flag1,a.tablespace_name,
round(sum(a.bytes/1048576),2)
from dba_data_files a

select distinct tablespace_name
from dba_tablespaces
order by 1

select distinct tablespace_name
from dba_tablespaces
order by 1

select 'tot' flag1,a.tablespace_name,
round(sum(a.bytes/1048576),2)
from dba_data_files a

  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default;