Oracle/Create tablespace

Basic

Create a new tablespace:

CREATE TABLESPACE TEST LOGGING 
  DATAFILE '/usr/lib/oracle/xe/oradata/XE/test.dbf' 
  SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED 
  EXTENT MANAGEMENT LOCAL;

Create a new temporary tablespace:

CREATE TEMPORARY TABLESPACE TEST_TEMP
  TEMPFILE '/usr/lib/oracle/xe/oradata/XE/test_temp.dbf'
  SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED 
  EXTENT MANAGEMENT LOCAL;

Multiple at once:

set serveroutput on
declare
  my_array sys.dbms_debug_vc2coll
           := sys.dbms_debug_vc2coll('TS1', 'TS2', 'TS3');
begin
  for r in my_array.first..my_array.last
    loop
      dbms_output.put_line(my_array(r));
      EXECUTE IMMEDIATE 'CREATE TABLESPACE '||my_array(r)||' LOGGING DATAFILE ''d:/Apps/oraclexe/app/oracle/oradata/XE/'||my_array(r)||'.dbf'' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL';
      EXECUTE IMMEDIATE 'CREATE USER "'||my_array(r)||'" PROFILE "DEFAULT" IDENTIFIED BY "password" DEFAULT TABLESPACE "'||my_array(r)||'" ACCOUNT UNLOCK';
      EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE, CREATE VIEW, CONNECT, RESOURCE TO "'||my_array(r)||'"';
  end loop;
end;
/

Extended

CREATE TABLESPACE TEST
  DATAFILE '/oracle/app/oradata/devdb/TEST_0.dbf' SIZE 31G AUTOEXTEND ON NEXT 100M MAXSIZE 32767m,
           '/oracle/app/oradata/devdb/TEST_1.dbf' SIZE 31G AUTOEXTEND ON NEXT 100M MAXSIZE 32767m,
           '/oracle/app/oradata/devdb/TEST_2.dbf' SIZE 31G AUTOEXTEND ON NEXT 100M MAXSIZE 32767m,
           '/oracle/app/oradata/devdb/TEST_3.dbf' SIZE  1G AUTOEXTEND ON NEXT 100M MAXSIZE 32767m,
           '/oracle/app/oradata/devdb/TEST_4.dbf' SIZE  1G AUTOEXTEND ON NEXT 100M MAXSIZE 32767m,
           '/oracle/app/oradata/devdb/TEST_5.dbf' SIZE  1G AUTOEXTEND ON NEXT 100M MAXSIZE 32767m
  NOLOGGING;
-- import, then change to LOGGING
ALTER TABLESPACE TEST LOGGING;

CREATE USER "TEST_A" PROFILE "DEFAULT" IDENTIFIED BY "seeburger" DEFAULT TABLESPACE "TEST_A" ACCOUNT UNLOCK;
GRANT UNLIMITED TABLESPACE, CREATE VIEW, CONNECT, RESOURCE TO "TEST_A";
CREATE USER "TEST_B" PROFILE "DEFAULT" IDENTIFIED BY "seeburger" DEFAULT TABLESPACE "TEST_B" ACCOUNT UNLOCK;
GRANT UNLIMITED TABLESPACE, CREATE VIEW, CONNECT, RESOURCE TO "TEST_B";

User

Create a new user for this table space with appropriate rights (important: case must be the same for both, user name and table space!):

CREATE USER "TEST" PROFILE "DEFAULT" 
  IDENTIFIED BY "PASSWORD"
  DEFAULT TABLESPACE "TEST"
  TEMPORARY TABLESPACE "TEST_TEMP"
  ACCOUNT UNLOCK;

-- ROLE RESOURCE: CREATE CLUSTER, INDEXTYPE, OPERATOR, PROCEDURE, SEQUENCE, TABLE, TRIGGER, TYPE
-- ROLE CONNECT: CREATE SESSION
GRANT CONNECT, RESOURCE TO "TEST"; 
-- CREATE VIEW
GRANT CREATE VIEW TO "TEST";
GRANT UNLIMITED TABLESPACE TO "TEST";

Revoke rights again:

REVOKE UNLIMITED TABLESPACE FROM "TEST";

Or set default tablespace + temporary tablespace of an existing user:

ALTER USER "TEST"
  DEFAULT TABLESPACE "TEST"
  TEMPORARY TABLESPACE "TEST_TEMP";

Change user password:

ALTER USER "TEST" IDENTIFIED BY "NEW_PASSWORD";

Application Specific

JBoss 7

GRANT SELECT ON sys.dba_pending_transactions TO TEST; GRANT SELECT ON sys.pending_trans$ TO TEST; GRANT SELECT ON sys.dba_2pc_pending TO TEST; GRANT EXECUTE ON sys.dbms_xa TO TEST;

Drop

SELECT * FROM USER_TABLESPACES;
SELECT * FROM DBA_DIRECTORIES;
DROP TABLESPACE "TEST" INCLUDING CONTENTS AND DATAFILES;